Solved

SQL Case when

Posted on 2013-12-05
8
287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 49

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 49

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 49

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
 
LVL 49

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 49

Expert Comment

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

631 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