October 31, 2017 at 5:55 am
Sorry for the beginners question but can some help me put these 2 queries into 1. I've had a quick look at sub queries but can't seem to figure it out.
Thanks
SELECT Dealer
,COUNT (*) as [Total Vehicles on Site]
,COUNT(CASE InputCompleted WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InputCompleted END) AS [Total Inspections Carried Out]
FROM ISHeaders
WHERE Created >='2017-10-30' AND Created < '2017-10-31'
GROUP by Dealer
SELECT Dealer
,COUNT (*) as [Total Vehicles on Site]
,COUNT(CASE InputCompleted WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InputCompleted END) AS [Total Inspections Carried Out]
FROM ISHeaders
WHERE InspectionClosed >='2017-10-30' AND InspectionClosed < '2017-10-31'
GROUP by Dealer
October 31, 2017 at 6:00 am
should have metioned i would like to have 5 columns in total
Dealer
Tot vehi on site
Tot inspections carried out
Tot vehi on site 2
Tot inspections carried out 2
October 31, 2017 at 6:03 am
Sorry for the beginners question but can some help me put these 2 queries into 1. I've had a quick look at sub queries but can't seem to figure it out.
Thanks
======
Apologies
SELECT Dealer
,COUNT (*) as [Total Vehicles on Site]
,COUNT(CASE InputCompleted WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InputCompleted END) AS [Total Inspections Carried Out]
FROM ISHeaders
WHERE Created >='2017-10-30' AND Created < '2017-10-31'
GROUP by Dealer
SELECT Dealer
,COUNT (*) as [Total Vehicles on Site]
,COUNT(CASE InputCompleted WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InputCompleted END) AS [Total Inspections Carried Out]
FROM ISHeaders
WHERE InspectionClosed >='2017-10-30' AND InspectionClosed < '2017-10-31'
GROUP by Dealer
October 31, 2017 at 6:10 am
I've assumed that you will likely want to parametrise this. Without DDL and DLM, this is completely untested, however:DECLARE @DateFrom date, @DateTo date;
SET @DateFrom = '20171030';
SET @DateTo = '20171031';
SELECT Dealer,
COUNT(CASE WHEN Created >= @DateFrom AND Created < @DateTo THEN Dealer END) AS CreatedVehiclesOnSite,
COUNT(CASE WHEN Created >= @DateFrom AND Created < @DateTo THEN CASE InputCompleted WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InputCompleted END END) AS CreatedInputs,
COUNT(CASE WHEN InspectionClosed >= @DateFrom AND InspectionClosed < @DateTo THEN Dealer END) AS ClosedVehiclesOnSite,
COUNT(CASE WHEN InspectionClosed >= @DateFrom AND InspectionClosed < @DateTo THEN CASE InputCompleted WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InputCompleted END END) AS ClosedInptes
FROM ISHeaders
WHERE ((Created >= @DateFrom AND Created < @DateTo)
OR (InspectionClosed >= @DateFrom AND InspectionClosed < @DateTo))
GROUP BY Dealer;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2017 at 6:14 am
Amazing thank you so much
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