SQL 2008 Select

Hello,
can you please help,
I use below code to select from table.

The issue I have is with TAX1,
TAX1 includes 6 taxes (PST_BC, HST, GST, HST_PEI, HST_ON, HST_NS)
I need to have separate SUM for each tax.

Example
SUM (PST_BC,)
SUM (HST)
SUM (GST) ...

P Lease see attached.

Select SUM (SubtotalAmount) AS  [SubtotalAmount],SUM (Tax1Amount) AS [TAX1],SUM (Tax2Amount) AS [TVQ],SUM (TotalAmount) AS [TotalAmount]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersInvoices ON FinalizedOrdersInvoices.OrderNo = FinalizedOrders.OrderNo
WHERE FinalizedOrdersInvoices.InvoiceNumber = (Select Distinct InvoiceNumber From (Select InvoiceNumber, orderno From finalizedordersinvoices
Where Invoicenumber = (Select top 1 invoicenumber from finalizedordersinvoices i inner join finalizedorders o on o.orderno= i.orderno  where ((AccountNumber = 116)) order by Invoicenumber desc))
foi left join finalizedorders fo on fo.orderno  = foi.orderno WHERE ((AccountNumber = 116)))

thanks
Example.xlsx
W.E.BAsked:
Who is Participating?
 
Harish VargheseConnect With a Mentor Project LeaderCommented:
Hello,

You can use a case statement inside the SUM function as below (assuming the column that stores Tax1 Type is TaxType):
Select SUM (SubtotalAmount) AS  [SubtotalAmount],
SUM (case when TaxType = 'PST_BC' Then Tax1Amount else 0 end) AS PST_BC,
SUM (case when TaxType = 'HST' Then Tax1Amount else 0 end) AS HST,
SUM (case when TaxType = 'GST' Then Tax1Amount else 0 end) AS GST,
SUM (Tax2Amount) AS [TVQ],SUM (TotalAmount) AS [TotalAmount]
FROM FinalizedOrders...........

Your WHERE clause looks very confusing. We may be able to make it simpler if you mention what are you really trying to achieve, along with the structure of tables.

-Harish
0
 
W.E.BAuthor Commented:
Thank you,
works great.
0
 
PortletPaulfreelancerCommented:
>>"Your WHERE clause looks very confusing. We may be able to make it simpler..."

ABSOLUTELY

Your existing where clause appears to several unnecessary parts:
AS IS:
WHERE FinalizedOrdersInvoices.InvoiceNumber = (
            SELECT DISTINCT
                  InvoiceNumber
            FROM (
                        SELECT
                              InvoiceNumber
                            , orderno
                        FROM finalizedordersinvoices
                        WHERE Invoicenumber = (
                                    SELECT TOP 1
                                          invoicenumber
                                    FROM finalizedordersinvoices i
                                          INNER JOIN finalizedorders o
                                                ON o.orderno = i.orderno
                                    WHERE ((AccountNumber = 116))
                                    ORDER BY
                                          Invoicenumber DESC
                              )
                  ) foi
                  LEFT JOIN finalizedorders fo
                        ON fo.orderno = foi.orderno
            WHERE ((AccountNumber = 116))
      )

Open in new window

TO BE:
WHERE FinalizedOrdersInvoices.InvoiceNumber = (
                                    SELECT TOP 1
                                          invoicenumber
                                    FROM finalizedordersinvoices i
                                          INNER JOIN finalizedorders o
                                                ON o.orderno = i.orderno
                                    WHERE ((AccountNumber = 116))
                                    ORDER BY
                                          Invoicenumber DESC
                              )

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.