COUNT CASE WHEN

Question

Post reply

COUNT CASE WHEN

craig.jenkins

SSCommitted

Points: 1643
More actions
December 13, 2017 at 6:19 am

#409855

Hi Guys,
Can someone please help me get the results from query 1 into query 2 (IE a count case when). I've tried many different scenarios but just can't get this. This will help as i will not need to run 2 separate queries. Many thanks
-- QUERY 1
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM (VALUES
('BA', '2017-12-07', '2017-12-11', '2017-12-08'),
('CA', '2017-12-07', '2017-12-11', '2017-12-08'),
('BA', '2017-12-07', '2017-12-11', '2017-12-08'),
('BAA', '2017-12-07', '2017-12-11', '2017-12-08'),
('BA', '2017-12-07', '2017-12-11', '2017-12-08'),
('NSK', '2017-12-07', '2017-12-11', '2017-12-08')
) d (DEALER, CREATED, INPUTSTARTED, INPUTCOMPLETED)
DECLARE @sd DATETIME, @ed DATETIME;
-- set the start date to the first day of this month
SET @sd = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
SET @ed = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
SELECT Dealer
, ISNULL(SUM(Total),0) AS [Created Inspections]
FROM (SELECT i.Dealer
, COUNT(*) AS [Total]
FROM #SampleData AS i WITH(NOLOCK)
WHERE ISNULL(InputStarted,'01-01-1900') <> '01-01-1900' AND ISNULL(InputCompleted,'01-01-1900') <> '01-01-1900'
AND ISNULL(Created,'01-01-1900') BETWEEN @sd AND @ed
GROUP BY i.Dealer
) AS Created
GROUP BY Dealer

-- QUERY 2
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM (VALUES
('BA', '2017-12-07', '2017-12-11', '2017-12-08'),
('CA', '2017-12-07', '2017-12-11', '2017-12-08'),
('BA', '2017-12-07', '2017-12-11', '2017-12-08'),
('BAA', '2017-12-07', '2017-12-11', '2017-12-08'),
('BA', '2017-12-07', '2017-12-11', '2017-12-08'),
('NSK', '2017-12-07', '2017-12-11', '2017-12-08')
) d (DEALER, CREATED, INSPECTIONCLOSED, INPUTCOMPLETED)
DECLARE @sd DATETIME, @ed DATETIME;
-- set the start date to the first day of this month
SET @sd = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
SET @ed = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
SELECT Dealer,
COUNT(CASE WHEN Created >= @sd AND Created < @ed THEN Dealer END) AS [Created Vehicles On Site],
COUNT(CASE WHEN InspectionClosed >= @sd AND InspectionClosed < @ed THEN Dealer END) AS [Closed Vehicles On Site],
COUNT(CASE WHEN InspectionClosed >= @sd AND InspectionClosed < @ed THEN CASE InputCompleted WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InputCompleted END END) AS [Closed Inspections Carried Out]
FROM #SampleData
WHERE ((Created >= @sd AND Created < @ed)
OR (InspectionClosed >= @sd AND InspectionClosed < @ed))
GROUP BY Dealer;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

What do you mean by "get the results into". What is your expected result set?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

craig.jenkins SSCommitted Points: 1643 More actions · Answer 2

Hi Thom A

So ideally i would like to have another column in Query2 showing the output from Query1 so something like the below as a final query

Thom A SSC Guru Points: 99126 More actions · Answer 3

Have you tried running that SQL you provided? Can you provide some working DDL, Sample data, and SQL please?

Edit: just noticed that the table is called #SampleData in both queries, despite having different DDL.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Is the data coming from different tables?
It seems that the easiest way would be to use both queries as CTEs and use a FULL JOIN between them.
Something like this:
WITH cteInspections AS( SELECT Dealer , ISNULL(SUM(Total),0) AS [Created Inspections] FROM (SELECT i.Dealer , COUNT(*) AS [Total] FROM #SampleData AS i WITH(NOLOCK) WHERE InputStarted <> '19000101' AND InputCompleted <> '19000101' AND Created BETWEEN @sd AND @ed GROUP BY i.Dealer ) AS Created GROUP BY Dealer ), cteVehicles AS( SELECT Dealer, COUNT(CASE WHEN Created >= @sd AND Created < @ed THEN Dealer END) AS [Created Vehicles On Site], COUNT(CASE WHEN InspectionClosed >= @sd AND InspectionClosed < @ed THEN Dealer END) AS [Closed Vehicles On Site], COUNT(CASE WHEN InspectionClosed >= @sd AND InspectionClosed < @ed THEN CASE InputCompleted WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InputCompleted END END) AS [Closed Inspections Carried Out] FROM #SampleData2 WHERE ((Created >= @sd AND Created < @ed) OR (InspectionClosed >= @sd AND InspectionClosed < @ed)) GROUP BY Dealer ) SELECT v.*, i.[Created Inspections] FROM cteInspections i FULL JOIN cteVehicles v ON i.DEALER = v.DEALER;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

craig.jenkins SSCommitted Points: 1643 More actions · Answer 5

thanks guys, yes all data is in the same table i'm just trying to get the below from query 1 to show as a new column in query 2 if this is possible? Just to add these are 2 separate queries written by two different people. Essentially i understand query 2 but not too sure on what query one is doing but i know the output from query 1 are correct. However, as all data is coming from the same table i assume you could just use one query for all.

WHERE ISNULL(InputStarted,'01-01-1900') <> '01-01-1900' AND ISNULL(InputCompleted,'01-01-1900') <> '01-01-1900'
AND ISNULL(Created,'01-01-1900') BETWEEN @sd AND @ed