Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Problem with t-sql command

Posted on 2014-09-16
Medium Priority
441 Views
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
Question by:GeertWillemarck
[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

LVL 40

Expert Comment

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;
``````
0

LVL 70

Accepted Solution

Scott Pletcher earned 2000 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

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

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
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.
###### Suggested Courses
Course of the Month8 days, 22 hours left to enroll