Solved

SQL 2008 Select

Posted on 2014-04-02
3
390 Views
Last Modified: 2014-04-02
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
0
Comment
Question by:W.E.B
3 Comments
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 400 total points
ID: 39973286
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
 

Author Closing Comment

by:W.E.B
ID: 39973301
Thank you,
works great.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39973955
>>"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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 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

15 Experts available now in Live!

Get 1:1 Help Now