?
Solved

SQL 2008 Select

Posted on 2014-04-02
3
Medium Priority
?
423 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

765 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