[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2008 Select

Posted on 2014-04-02
3
Medium Priority
?
429 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
[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
3 Comments
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 1600 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 49

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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