Solved

SQL Case when

Posted on 2013-12-05
8
277 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:W.E.B
  • 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:W.E.B
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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:W.E.B
ID: 39699898
You Rock,
Thank you.
0
 

Author Closing Comment

by:W.E.B
ID: 39699900
Thank you very much.
0
 
LVL 48

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

21 Experts available now in Live!

Get 1:1 Help Now