Solved

Problem with t-sql command

Posted on 2014-09-16
3
418 Views
Last Modified: 2014-09-17
Hi experts,

Below is a sql command that calculates the total of all orders of our new clients (clients with Bon_blnNieuweKlant set on 'True').
I have a second sql command that calculates the same for the other clients (Bon_blnNieuweKlant <> 'True').

Is there a way combine both sql commands in one command that shows the output of the first command + an extra column with the result for the older columns.
The desired output would then look like:

Name      Year        Month      TotalNewClients     TotalOtherClients
John         2014            1                   5,000                         3,500


SELECT           V.Vrt_strNaam,
                        YEAR(B.Bon_dtmAfrekenen) AS Jaar,
                       MONTH(B.Bon_dtmAfrekenen) AS Maand,
                       SUM(D.Bdt_bdrNetto) as 'New clients'
                       FROM             Bestelbons  B
INNER JOIN   Bestelbondetails D
ON                  B.Bon_intBonNr = D.Bdt_intBonNr
INNER JOIN    Vertegenwoordigers V
ON                  B.Bon_VrtId = V.Vrt_VrtId
GROUP BY     V.Vrt_strNaam, YEAR(B.Bon_dtmAfrekenen),
                        MONTH(B.Bon_dtmAfrekenen), B.Bon_blnNieuweKlant
HAVING          YEAR(B.Bon_dtmAfrekenen) = 2014 AND B.Bon_blnNieuweKlant = 'True'
ORDER BY      Jaar, Maand;

Thanks you
0
Comment
Question by:GeertWillemarck
3 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40325592
Assuming they are in the same table you can do a sum case statement.

SELECT           V.Vrt_strNaam, 
                        YEAR(B.Bon_dtmAfrekenen) AS Jaar, 
                       MONTH(B.Bon_dtmAfrekenen) AS Maand, 
                       SUM(D.Bdt_bdrNetto) as 'New clients'
                       cast (0 as numeric(18,2)) as 'Old clients'
                      into #temp
                       FROM             Bestelbons  B 
INNER JOIN   Bestelbondetails D 
ON                  B.Bon_intBonNr = D.Bdt_intBonNr 
INNER JOIN    Vertegenwoordigers V 
ON                  B.Bon_VrtId = V.Vrt_VrtId 
GROUP BY     V.Vrt_strNaam, YEAR(B.Bon_dtmAfrekenen), 
                        MONTH(B.Bon_dtmAfrekenen), B.Bon_blnNieuweKlant 
HAVING          YEAR(B.Bon_dtmAfrekenen) = 2014 AND B.Bon_blnNieuweKlant = 'True'
ORDER BY      Jaar, Maand;

Open in new window

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40325784
I adjusted the date check to use the WHERE below, as that comparison technique will allow full use of indexes, and a WHERE check reduces the rows that have to be processed before the HAVING clause does, saving unnecessary processing.  For example, it's a waste to compute totals for, say, years 2010 thru 2014, and then discard 2010-2013 because you didn't want to select those totals anyway.


SELECT           V.Vrt_strNaam,
                         YEAR(B.Bon_dtmAfrekenen) AS Jaar,
                        MONTH(B.Bon_dtmAfrekenen) AS Maand,
                        SUM(CASE WHEN B.Bon_blnNieuweKlant = 'True' THEN 1 ELSE 0 END) AS 'New clients',
                        SUM(CASE WHEN B.Bon_blnNieuweKlant IS NULL OR B.Bon_blnNieuweKlant <> 'True' THEN 1 ELSE 0 END) AS 'Old clients'
 FROM         Bestelbons  B
 INNER JOIN   Bestelbondetails D
 ON                  B.Bon_intBonNr = D.Bdt_intBonNr
 INNER JOIN    Vertegenwoordigers V
 ON                  B.Bon_VrtId = V.Vrt_VrtId
 WHERE
                      B.Bon_dtmAfrekenen >= '20140101' AND
                      B.Bon_dtmAfrekenen <  '20150101'
 GROUP BY     V.Vrt_strNaam, YEAR(B.Bon_dtmAfrekenen),
                         MONTH(B.Bon_dtmAfrekenen)
 ORDER BY      Jaar, Maand;
0
 

Author Closing Comment

by:GeertWillemarck
ID: 40327614
The solution was perfect. I suppose my question was not clear enough so, there was a little misunderstanding. But the solution showed me the light.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

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

16 Experts available now in Live!

Get 1:1 Help Now