Solved

SQL Case when

Posted on 2013-12-05
8
275 Views
Last Modified: 2013-12-05
Hello,
can you please help,
I need to sort the taxes to go into the proper Tax column name
I think I need to change the  CONVERT(varchar,(Select (Tax ......
to case when

.Select Distinct 'OrderDate' AS [OrdeDater],
                'Reference' AS [Reference],
                'NetCost' AS [NetCost],
                'PST_BC' AS [PST_BC],
                'TVQ' AS [TVQ],
                'HST' AS [HST],
                'GST' AS [GST],
                'HST_PEI' AS [HST_PEI],
                'HST_ON' AS [HST_ON],
                'HST_NS' AS [HST_NS],
                'Total Cost' AS [TotalCost],
                'InvoiceNumber' AS [InvoiceNumber],
		'OrderNumber' AS [OrderNumber],
                'Contact' AS [Contact],
                'Pieces' AS [Pieces]
     FROM  FinalizedOrders F, FinalizedOrdersInvoices FOI
     WHERE 1 = 1
Union All
SELECT 
	CONVERT(varchar, F.OrderDate, 1)  AS [OrderDatte],
	Replace (Reference,  ',', '') AS[Reference],
    CONVERT(varchar,SubTotalAmount) as [NetCost],
    
    CONVERT(varchar,(Select (Tax2Amount) from FinalizedOrders where Tax1ID = 1)) AS [PST_BC],
    CONVERT(varchar,(Select (Tax2Amount) from FinalizedOrders where Tax1ID = 2)) AS [TVQ],
    CONVERT(varchar,(Select (Tax1Amount) from FinalizedOrders where Tax1ID = 3)) AS [HST],
    CONVERT(varchar,(Select (Tax1Amount) from FinalizedOrders where Tax1ID = 4)) AS [GST],
    CONVERT(varchar,(Select (Tax1Amount) from FinalizedOrders where Tax1ID = 7)) AS [HST_PEI],
    CONVERT(varchar,(Select (Tax1Amount) from FinalizedOrders where Tax1ID = 8)) AS [HST_ON],
    CONVERT(varchar,(Select (Tax1Amount) from FinalizedOrders where Tax1ID = 9)) AS [HST_NS],

 
    CONVERT(varchar,TotalAmount) AS [TotalCost],
	CONVERT(varchar,InvoiceNumber) AS [InvoiceNumber],
	CONVERT(varchar,F.OrderNo) AS [OrderNumber],
		PickupContact AS [Contact],
	Convert (Varchar,(select count(PieceNo) from finalizedorderpackages where finalizedorderpackages.orderno = F.orderno)) as [Piece Count]
	FROM FinalizedOrders F,
		finalizedOrdersInvoices FOI
	WHERE 
		F.OrderNo = FOI.OrderNo AND
		FOI.InvoiceNumber = 534599 AND
		AccountNumber = 1077

Open in new window

Thanks for any help.
0
Comment
Question by:Wass_QA
  • 5
  • 3
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39699856
Are you sure that existing query is what you want?
You have created a cartesian product between 2 tables (lines 16 & 17)

Are you trying to provide a "grand total" row?
(i.e. is the second query just there to provide column totals?)
0
 

Author Comment

by:Wass_QA
ID: 39699866
Hello,
yes, this is a good query,
each order has 1 or 2 taxes (there are 7 different tax types),
I need to show the order taxes each order / in the proper column.

thanks,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39699871
The more I look at this the less I understand it. Take the first column. In the upper part of the query it is a date, but in the lower part it's converted to varchar. The second column appears to be numeric but again converted to varchar in the lower query.

All columns must remain the same (or compatible) data types when using a union.

Also. in the lower query you have subqueries like this:
SELECT
      (Tax2Amount)
FROM FinalizedOrders
WHERE Tax1ID = 1

These will fail because they will return more than one value.

================
PLEASE provide some sample data (for both tables) and the expected result.

That will be the best/fastest way of helping you.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39699873
>>"this is a good query, "
I do not think that is true, for many reasons.

>>"I need to show the order taxes each order / in the proper column."
This is not difficult. e.g.

select
case when Tax1ID = 1 then Tax2Amount end as PST_BC
....


================
PLEASE provide some sample data (for both tables) and the expected result.

That will be the best/fastest way of helping you.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 39699888
maybe this will help:
SELECT
      CONVERT(varchar, F.OrderDate, 1)                           AS [OrderDatte]
    , REPLACE(Reference, ',', '')                                AS [Reference]
    , CONVERT(varchar, SubTotalAmount)                           AS [NetCost]
    , CONVERT(varchar, CASE WHEN Tax1ID = 1 THEN Tax2Amount END) AS [PST_BC]
    , CONVERT(varchar, CASE WHEN Tax1ID = 2 THEN Tax2Amount END) AS [TVQ]
    , CONVERT(varchar, CASE WHEN Tax1ID = 3 THEN Tax1Amount END) AS [HST]
    , CONVERT(varchar, CASE WHEN Tax1ID = 4 THEN Tax1Amount END) AS [GST]
    , CONVERT(varchar, CASE WHEN Tax1ID = 7 THEN Tax1Amount END) AS [HST_PEI]
    , CONVERT(varchar, CASE WHEN Tax1ID = 8 THEN Tax1Amount END) AS [HST_ON]
    , CONVERT(varchar, CASE WHEN Tax1ID = 9 THEN Tax1Amount END) AS [HST_NS]
    , CONVERT(varchar, TotalAmount)                              AS [TotalCost]
    , CONVERT(varchar, InvoiceNumber)                            AS [InvoiceNumber]
    , CONVERT(varchar, F.OrderNo)                                AS [OrderNumber]
    , PickupContact                                              AS [Contact]
    , CONVERT(varchar, (
            SELECT
                  COUNT(PieceNo)
            FROM finalizedorderpackages
            WHERE finalizedorderpackages.orderno = F.orderno
      )
      )                                                          AS [Piece Count]
FROM FinalizedOrders F
      INNER JOIN FinalizedOrdersInvoices FOI
            ON F.OrderNo = FOI.OrderNo
WHERE FOI.InvoiceNumber = 534599
      AND AccountNumber = 1077
;

Open in new window

0
 

Author Comment

by:Wass_QA
ID: 39699898
You Rock,
Thank you.
0
 

Author Closing Comment

by:Wass_QA
ID: 39699900
Thank you very much.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39700014
Pleased I could help. Cheers, Paul
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
STDEVP in SQL 2 34
TSQL - Rollup data set 7 29
XML to SQL Table using c# 5 45
T-SQL Using IN with a subquery 3 12
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now