Problem with t-sql command

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
GeertWillemarckAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GeertWillemarckAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.