Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Case when

Posted on 2013-12-05
8
Medium Priority
?
288 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 1200 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 your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 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