Solved

SQL 2008 Select

Posted on 2014-04-02
3
385 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:Wass_QA
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:Wass_QA
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

12 Experts available now in Live!

Get 1:1 Help Now