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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy