December 13, 2017 at 6:19 am
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;
December 13, 2017 at 7:23 am
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
December 13, 2017 at 7:29 am
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
December 13, 2017 at 7:47 am
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;
December 13, 2017 at 8:13 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply