[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

SQL Case when

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
W.E.B
Asked:
W.E.B
  • 5
  • 3
1 Solution
 
PortletPaulCommented:
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
 
W.E.BAuthor Commented:
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
 
PortletPaulCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
PortletPaulCommented:
>>"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
 
PortletPaulCommented:
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
 
W.E.BAuthor Commented:
You Rock,
Thank you.
0
 
W.E.BAuthor Commented:
Thank you very much.
0
 
PortletPaulCommented:
Pleased I could help. Cheers, Paul
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now