Post reply

Joining 2 queries

  • October 31, 2017 at 5:55 am

    #402634

    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

    #1965938

    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:01 am

    #1965939

    Nevermind

    Thom~

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

  • October 31, 2017 at 6:03 am

    #1965940

    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

    #1965943

    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

    #1965945

    Amazing thank you so much

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

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