Help selecting records not between 2 dates

  • I’m trying to extract a list of records that don’t appear between 2 dates and could use a bit of help figuring it out. I have 4 tables:

    tblCustomers

    tblMachines

    tblServiceOrders

    tblMachinesServiced

    tblCustomers contains a CustomerID that is unique to each customer.

    tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.

    tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.

    tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.

    What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.

    For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.

    I’ve tried several different queries but here is an example:

    SELECT tblMachines.MachineID,ServiceMachines.ServiceDate

    FROM tblMachines

    LEFT JOIN

    (SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced

    WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo

    ) ServicedMachines

    ON tblMachines.MachineID=ServicedMachines.MachineID

    WHERE YEAR(ServiceDate) != '2014'

    I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.

  • greggg (7/22/2015)


    I’m trying to extract a list of records that don’t appear between 2 dates and could use a bit of help figuring it out. I have 4 tables:

    tblCustomers

    tblMachines

    tblServiceOrders

    tblMachinesServiced

    tblCustomers contains a CustomerID that is unique to each customer.

    tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.

    tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.

    tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.

    What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.

    For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.

    I’ve tried several different queries but here is an example:

    SELECT tblMachines.MachineID,ServiceMachines.ServiceDate

    FROM tblMachines

    LEFT JOIN

    (SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced

    WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo

    ) ServicedMachines

    ON tblMachines.MachineID=ServicedMachines.MachineID

    WHERE YEAR(ServiceDate) != '2014'

    I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.

    First off, you should give us test data in a form where folks can just copy/paste it. Like this:

    DECLARE @tblCustomers TABLE (CustomerID INTEGER);

    DECLARE @tblMachines TABLE (MachineID INTEGER, CustomerID INTEGER);

    DECLARE @tblServiceOrders TABLE (ServiceOrderNo INTEGER, CustomerID INTEGER, ServiceDate DATETIME);

    DECLARE @tblMachinesServiced TABLE (ServiceOrderNo INTEGER, MachineID INTEGER);

    INSERT INTO @tblCustomers (CustomerID) VALUES (1);

    INSERT INTO @tblMachines (MachineID, CustomerID)

    VALUES (1, 1), -- A

    (2, 1); -- B

    INSERT INTO @tblServiceOrders (ServiceOrderNo, CustomerID, ServiceDate )

    VALUES (1, 1, '2013-01-01'), -- A

    (2, 1, '2015-02-01'), -- A

    (3, 1, '2013-03-03'), -- B

    (4, 1, '2014-04-04'), -- B

    (5, 1, '2015-05-05'); -- B

    INSERT INTO @tblMachinesServiced (ServiceOrderNo, MachineID)

    VALUES (1, 1),

    (2, 1),

    (3, 2),

    (4, 2),

    (5, 2);

    Okay, you want the Machines not serviced in between 2 dates. Let's get some dates for 2014 in there:

    DECLARE @StartDate DATETIME = '2014-01-01',

    @EndDate DATETIME = '2014-12-31';

    Now we get the machines that WERE serviced between the dates, then get the machines not in this list.

    SELECT MachineID

    FROM @tblMachines

    WHERE MachineID NOT IN (

    -- Gets machines serviced during the interested time period

    SELECT MachineID

    FROM @tblMachinesServiced t1

    JOIN @tblServiceOrders t2 ON t1.ServiceOrderNo = t2.ServiceOrderNo

    -- get the ones that WERE serviced between these dates

    WHERE ServiceDate >= @StartDate

    AND t2.ServiceDate <= @EndDate

    );

    You can see other examples of using IN / NOT IN in the book linked to in my signature.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WOW! Thanks for the help! It works wonderfully! I didn't realize that you could place a subquery within an IN list. That is a neat little trick.

    Sorry about not providing the test data. I will in the future.

    I've got to pick up a copy of your book, thanks for the reference and again thanks a lot of the help.

  • How would I go about getting additional information from the subquery, like the ServiceDate for the record?

  • That's going to be a bit more difficult. For one thing, there isn't a row being returned for these machines for that year in that subquery... the NOT IN ensures that.

    And, in the case of your example, you have a machine that was serviced in 2013 and 2015. When showing this for 2014, which one would you want to use?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • greggg (7/23/2015)


    How would I go about getting additional information from the subquery, like the ServiceDate for the record?

    You can use a CTE if required. If you can provide some more detail on what you want to achieve it would help us in getting your query resolved.

    ;WITH CTE AS

    (

    SELECT MachineID, ServiceDate

    FROM @tblMachinesServiced t1

    JOIN @tblServiceOrders t2 ON t1.ServiceOrderNo = t2.ServiceOrderNo

    WHERE ServiceDate >= @StartDate

    AND t2.ServiceDate <= @EndDate

    )

    SELECT tblM.MachineID

    FROM @tblMachines tblM

    WHERE tblM.MachineID NOT IN (SELECT CTE.MachineID FROM CTE)

    ____________________________________________________________

    AP
  • Thanks for the responses. The ultimate goal is an annual report that contains an accurate count on the number of machines that were serviced during the year, grouped by month. Sounds simple when you say it but the trouble is that a machine may be serviced multiple times throughout the year, even multiple times within a single month in some cases. And in the report, I need to count the machine only once, in the first month that it was serviced and ignore it from then on for that year. Any other services are irrelevant for the report.

    I was trying to count the ones NOT serviced in order to verify my results for the ones that were serviced. As it turns out, my numbers were not valid. I was able to adapt Wayne’s solution to get accurate numbers for the entire year, but I need to group that by month.

    I suppose I could do a separate query for each month. But I would have to verify the machine had not been counted in a previous month as well. I could do that within the subquery without extracting the date. That may be the simplest solution but a single query for the entire year would be better.

  • I need to count the machine only once, in the first month that it was serviced and ignore it from then on for that year. Any other services are irrelevant for the report.

    That's a very different problem to what you initially asked.

    Could you please add on to the test data Wayne posted and then, for that set of test data, show what the report should look like?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • greggg (7/24/2015)


    Thanks for the responses. The ultimate goal is an annual report that contains an accurate count on the number of machines that were serviced during the year, grouped by month. Sounds simple when you say it but the trouble is that a machine may be serviced multiple times throughout the year, even multiple times within a single month in some cases. And in the report, I need to count the machine only once, in the first month that it was serviced and ignore it from then on for that year. Any other services are irrelevant for the report.

    I was trying to count the ones NOT serviced in order to verify my results for the ones that were serviced. As it turns out, my numbers were not valid. I was able to adapt Wayne’s solution to get accurate numbers for the entire year, but I need to group that by month.

    I suppose I could do a separate query for each month. But I would have to verify the machine had not been counted in a previous month as well. I could do that within the subquery without extracting the date. That may be the simplest solution but a single query for the entire year would be better.

    While you're contemplating that, take a look at this:

    DECLARE @tblCustomers TABLE (CustomerID INTEGER);

    DECLARE @tblMachines TABLE (MachineID INTEGER, CustomerID INTEGER);

    DECLARE @tblServiceOrders TABLE (ServiceOrderNo INTEGER, CustomerID INTEGER, ServiceDate DATETIME);

    DECLARE @tblMachinesServiced TABLE (ServiceOrderNo INTEGER, MachineID INTEGER);

    INSERT INTO @tblCustomers (CustomerID) VALUES (1);

    INSERT INTO @tblMachines (MachineID, CustomerID)

    VALUES (1, 1), -- A

    (2, 1); -- B

    INSERT INTO @tblServiceOrders (ServiceOrderNo, CustomerID, ServiceDate )

    VALUES (1, 1, '2013-01-01'), -- A

    (2, 1, '2015-02-01'), -- A

    (3, 1, '2013-03-03'), -- B

    (4, 1, '2014-04-04'), -- B

    (5, 1, '2015-05-05'); -- B

    INSERT INTO @tblMachinesServiced (ServiceOrderNo, MachineID)

    VALUES (1, 1),

    (2, 1),

    (3, 2),

    (4, 2),

    (5, 2);

    DECLARE @MAX_YEAR AS int = (SELECT MAX(YEAR(ServiceDate)) FROM @tblServiceOrders);

    WITH THE_YEARS AS (

    SELECT MIN(YEAR(ServiceDate)) AS THE_YEAR

    FROM @tblServiceOrders

    UNION ALL

    SELECT THE_YEAR + 1

    FROM THE_YEARS

    WHERE THE_YEAR + 1 <= @MAX_YEAR

    ),

    THE_MONTHS AS (

    SELECT 1 AS THE_MONTH

    UNION ALL

    SELECT THE_MONTH + 1

    FROM THE_MONTHS

    WHERE THE_MONTH + 1 <= 12

    ),

    YEAR_MONTH AS (

    SELECT Y.THE_YEAR, M.THE_MONTH, CAST(CAST(Y.THE_YEAR AS char(4)) + '-' + RIGHT('0' + CAST(M.THE_MONTH AS varchar(2)), 2) + '-01' AS date) AS MONTH_START_DT

    FROM THE_YEARS AS Y, THE_MONTHS AS M

    ),

    THE_DATA AS (

    SELECT X.*,

    DATEADD(day, 1 - DATEPART(day,MIN_SVC_DATE), MIN_SVC_DATE) AS SVC_MONTH_START

    FROM (

    SELECT YEAR(SO.ServiceDate) AS SVC_YEAR, M.MachineID, MIN(SO.ServiceDate) AS MIN_SVC_DATE

    FROM @tblMachines AS M

    INNER JOIN @tblMachinesServiced AS MS

    ON M.MachineID = MS.MachineID

    INNER JOIN @tblServiceOrders AS SO

    ON M.CustomerID = SO.CustomerID

    GROUP BY YEAR(SO.ServiceDate), M.MachineID

    ) AS X

    )

    SELECT YM.THE_YEAR, YM.THE_MONTH, TD.MachineID,

    CASE

    WHEN TD.SVC_MONTH_START >= YM.MONTH_START_DT THEN 1

    ELSE 0

    END AS X

    FROM YEAR_MONTH AS YM

    LEFT OUTER JOIN THE_DATA AS TD

    ON YM.THE_YEAR = TD.SVC_YEAR

    ORDER BY YM.THE_YEAR, YM.THE_MONTH, TD.MachineID

    It uses the sample data from a previous post.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • --Edit:

    --this provides the years which were serviced;

    --could add years not serviced by generating all years, using a tally table,

    --and doing a left join from that.

    --unfortunately, my work server blocks ctes (it thinks they are injection attempts), so

    --i can't easily code that part of it using an inline tally table

    DECLARE @min_year_to_report_on int

    SET @min_year_to_report_on = 2013

    ------------------------------------------------------------------------------------------------------------------------

    SELECT ms.MachineID, YEAR(so.ServiceDate) AS ServiceYear, MIN(so.ServiceDate) AS Min_ServiceDate

    FROM @tblServiceOrders so

    INNER JOIN @tblMachinesServiced ms ON

    ms.ServiceOrderNo = so.ServiceOrderNo

    WHERE

    so.ServiceDate >= CAST(@min_year_to_report_on AS char(4)) + '0101'

    GROUP BY

    ms.MachineID, YEAR(so.ServiceDate)

    ORDER BY

    ms.MachineID, YEAR(so.ServiceDate)

    Here's sample data for that query with multiple dates for the same year:

    DECLARE @tblCustomers TABLE (CustomerID int);

    DECLARE @tblMachines TABLE (MachineID int, CustomerID int);

    DECLARE @tblServiceOrders TABLE (ServiceOrderNo int, CustomerID int, ServiceDate datetime);

    DECLARE @tblMachinesServiced TABLE (ServiceOrderNo int, MachineID int);

    SET NOCOUNT ON;

    INSERT INTO @tblCustomers (CustomerID) VALUES(1);

    INSERT INTO @tblMachines (MachineID, CustomerID)

    VALUES (1, 1), -- A

    (2, 1); -- B

    INSERT INTO @tblServiceOrders (ServiceOrderNo, CustomerID, ServiceDate )

    VALUES (1, 1, '2013-06-17'), --A:

    (11,1, '2013-12-11'),

    (2, 1, '2015-02-01'),

    (22,1, '2015-06-17'),

    (3, 1, '2013-03-03'), --B:

    (33,1, '2013-11-09'),

    (4, 1, '2014-04-04'),

    (44,1, '2014-01-09'),

    (5, 1, '2015-05-05'),

    (55,1, '2015-11-11');

    INSERT INTO @tblMachinesServiced (ServiceOrderNo, MachineID)

    VALUES (1, 1),

    (11,1),

    (2, 1),

    (22,1),

    (3, 2),

    (33,2),

    (4, 2),

    (44,2),

    (5, 2),

    (55,2);

    SET NOCOUNT OFF;

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • GilaMonster (7/24/2015)


    I need to count the machine only once, in the first month that it was serviced and ignore it from then on for that year. Any other services are irrelevant for the report.

    That's a very different problem to what you initially asked.

    Could you please add on to the test data Wayne posted and then, for that set of test data, show what the report should look like?

    You're right, it is. My original question was aimed merely at being able to extract/count the machines that had not been serviced in an attempt to help validate the numbers that I was getting with a query I was using to extract the ones that had been serviced. Once I realized that those values weren't accurate, I tried to adapt Wayne's suggestion to the real goal and hit a road block.

    Anyway, here is a bit more data.

    DECLARE @tblCustomers TABLE (CustomerID INTEGER);

    DECLARE @tblMachines TABLE (MachineID INTEGER, CustomerID INTEGER);

    DECLARE @tblServiceOrders TABLE (ServiceOrderNo INTEGER, CustomerID INTEGER, ServiceDate DATETIME);

    DECLARE @tblMachinesServiced TABLE (ServiceOrderNo INTEGER, MachineID INTEGER, ServicedID INTEGER);

    INSERT INTO @tblCustomers (CustomerID)

    VALUES (1),

    (2);

    INSERT INTO @tblMachines (MachineID, CustomerID)

    VALUES (1, 1), -- A

    (2, 1), -- B

    (3, 1), -- C

    (1, 2), -- A

    (2, 2), -- B

    INSERT INTO @tblServiceOrders (ServiceOrderNo, CustomerID, ServiceDate)

    VALUES (1, 1, '1/1/2013 11:30:50 AM'), -- A

    (2, 1, '2/1/2015 9:23:27 AM'), -- A

    (3, 1, '3/3/2014 1:03:49 PM'), -- B

    (4, 1, '4/4/2014 12:09:38 PM'), -- B

    (5, 1, '5/5/2015 3:34:56 PM'), -- B

    (6, 1, '5/20/2015 10:03:58 AM'), -- B

    (7, 1, '5/20/2015 4:46:29 PM'), -- C

    (8, 2, '2/1/2015 9:39:18 AM'), -- A

    (9, 2, '7/23/2015 10:26:39 AM'), -- A

    (10, 2, '12/27/2015 11:02:39 AM'); -- B

    INSERT INTO @tblMachinesServiced (ServiceOrderNo, MachineID, ServicedID)

    VALUES (1, 1, 1),

    (2, 1, 1),

    (3, 2, 1),

    (4, 2, 1),

    (5, 2, 1);

    (6, 1, 1),

    (6, 2, 2),

    (7, 3, 1),

    (8, 1, 1),

    (8, 2, 2),

    (8, 3, 3),

    (9, 1, 1),

    (9, 1, 2),

    (10, 2, 1);

    The dates are stored in the format shown here, if that matters. I also added a new column to tblMachinesServiced. ServicedID tracks the number of machines serviced for each service order. The number is duplicated throughout the data, but never within the same service order. I didn't mention it before because I think it was relevant to the task I was trying to accomplish, but it may be.

    As far as the report goes, I'm just looking to extract data from the database that will ultimately be used in a chart on an Excel spreadsheet. The format I'm looking for is just a delimited list like so:

    Month Machines Serviced

    Jan 189

    Feb 157

    Mar 207

    Apr 138

    May 209

    Jun 100

    Jul 176

    Aug 200

    Sep 149

    Oct 186

    Nov 210

    Dec 151

  • Scott,

    Your sample data broke my code, and I soon realized I had a bigger problem to solve, especially when I read greggg's last post.

    Here's the new code:

    DECLARE @tblCustomers TABLE (CustomerID INTEGER);

    DECLARE @tblMachines TABLE (MachineID INTEGER, CustomerID INTEGER);

    DECLARE @tblServiceOrders TABLE (ServiceOrderNo INTEGER, CustomerID INTEGER, ServiceDate DATETIME);

    DECLARE @tblMachinesServiced TABLE (ServiceOrderNo INTEGER, MachineID INTEGER);

    INSERT INTO @tblCustomers (CustomerID) VALUES(1);

    INSERT INTO @tblMachines (MachineID, CustomerID)

    VALUES (1, 1), -- A

    (2, 1); -- B

    INSERT INTO @tblServiceOrders (ServiceOrderNo, CustomerID, ServiceDate )

    VALUES (1, 1, '2013-06-17'), --A:

    (11,1, '2013-12-11'),

    (2, 1, '2015-02-01'),

    (22,1, '2015-06-17'),

    (3, 1, '2013-03-03'), --B:

    (33,1, '2013-11-09'),

    (4, 1, '2014-04-04'),

    (44,1, '2014-01-09'),

    (5, 1, '2015-05-05'),

    (55,1, '2015-11-11');

    INSERT INTO @tblMachinesServiced (ServiceOrderNo, MachineID)

    VALUES (1, 1),

    (11,1),

    (2, 1),

    (22,1),

    (3, 2),

    (33,2),

    (4, 2),

    (44,2),

    (5, 2),

    (55,2);

    DECLARE @MAX_YEAR AS int = (SELECT MAX(YEAR(ServiceDate)) FROM @tblServiceOrders);

    WITH THE_YEARS AS (

    SELECT MIN(YEAR(ServiceDate)) AS THE_YEAR

    FROM @tblServiceOrders

    UNION ALL

    SELECT THE_YEAR + 1

    FROM THE_YEARS

    WHERE THE_YEAR + 1 <= @MAX_YEAR

    ),

    SO_YEAR_ASSIGNED AS (

    SELECT YEAR(S.ServiceDate) AS SERVICE_YEAR, S.*

    FROM @tblServiceOrders AS S

    ),

    THE_MONTHS AS (

    SELECT 1 AS THE_MONTH

    UNION ALL

    SELECT THE_MONTH + 1

    FROM THE_MONTHS

    WHERE THE_MONTH + 1 <= 12

    ),

    YEAR_MONTH AS (

    SELECT Y.THE_YEAR, M.THE_MONTH,

    CAST(CAST(Y.THE_YEAR AS char(4)) + '-' + RIGHT('0' + CAST(M.THE_MONTH AS varchar(2)), 2) + '-01' AS date) AS MONTH_START_DT,

    C.CustomerID, TM.MachineID

    FROM THE_YEARS AS Y, THE_MONTHS AS M, @tblCustomers AS C

    CROSS APPLY @tblMachines AS TM

    ),

    THE_DATA AS (

    SELECT X.SERVICE_YEAR, X.MachineID,

    DATEADD(day, 1 - DATEPART(day,MIN_SVC_DATE), MIN_SVC_DATE) AS SVC_MONTH_START

    FROM (

    SELECT SO.SERVICE_YEAR, M.MachineID, NULLIF(MIN(ISNULL(SO.ServiceDate, '1900-01-01')), '1900-01-01') AS MIN_SVC_DATE

    FROM @tblCustomers AS C

    INNER JOIN @tblMachines AS M

    ON M.CustomerID = C.CustomerID

    INNER JOIN @tblMachinesServiced AS MS

    ON M.MachineID = MS.MachineID

    LEFT OUTER JOIN SO_YEAR_ASSIGNED AS SO

    ON M.CustomerID = SO.CustomerID

    AND SO.ServiceOrderNo = MS.ServiceOrderNo

    GROUP BY SO.SERVICE_YEAR, M.MachineID

    ) AS X

    )

    SELECT YM.THE_YEAR, YM.THE_MONTH, COUNT(DISTINCT TD.MachineID) AS ServicedMachines

    FROM YEAR_MONTH AS YM

    LEFT OUTER JOIN THE_DATA AS TD

    ON YM.THE_YEAR = TD.SERVICE_YEAR

    AND YM.MachineID = TD.MachineID

    AND YM.MONTH_START_DT = TD.SVC_MONTH_START

    GROUP BY YM.THE_YEAR, YM.THE_MONTH

    ORDER BY YM.THE_YEAR, YM.THE_MONTH

    It reports multiple years of data, but that can easily be changed.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Thanks for the assistance and suggestions, Steve & Scott. You guys here are very generous to say the least. I'm going to study the code you both posted.

  • greggg (7/24/2015)


    Thanks for the assistance and suggestions, Steve & Scott. You guys here are very generous to say the least. I'm going to study the code you both posted.

    Now that you've posted updated sample data with a new field, that kind of invalidates the previous work, so here's a further update based on the new field.

    I'm assuming that the occurrence of a MachineId for the first time in a year is the only time we count the ServicedQTY for the entire year. If that assumption

    is not valid, let me know, as I'm carrying it over from the previous code attempts.

    DECLARE @tblCustomers TABLE (CustomerID INTEGER);

    DECLARE @tblMachines TABLE (MachineID INTEGER, CustomerID INTEGER);

    DECLARE @tblServiceOrders TABLE (ServiceOrderNo INTEGER, CustomerID INTEGER, ServiceDate DATETIME);

    DECLARE @tblMachinesServiced TABLE (ServiceOrderNo INTEGER, MachineID INTEGER, ServicedQTY INTEGER);

    INSERT INTO @tblCustomers (CustomerID)

    VALUES (1),

    (2);

    INSERT INTO @tblMachines (MachineID, CustomerID)

    VALUES (1, 1), -- A

    (2, 1), -- B

    (3, 1), -- C

    (1, 2), -- A

    (2, 2); -- B

    INSERT INTO @tblServiceOrders (ServiceOrderNo, CustomerID, ServiceDate)

    VALUES (1, 1, '1/1/2013 11:30:50 AM'), -- A

    (2, 1, '2/1/2015 9:23:27 AM'), -- A

    (3, 1, '3/3/2014 1:03:49 PM'), -- B

    (4, 1, '4/4/2014 12:09:38 PM'), -- B

    (5, 1, '5/5/2015 3:34:56 PM'), -- B

    (6, 1, '5/20/2015 10:03:58 AM'), -- B

    (7, 1, '5/20/2015 4:46:29 PM'), -- C

    (8, 2, '2/1/2015 9:39:18 AM'), -- A

    (9, 2, '7/23/2015 10:26:39 AM'), -- A

    (10, 2, '12/27/2015 11:02:39 AM'); -- B

    INSERT INTO @tblMachinesServiced (ServiceOrderNo, MachineID, ServicedQTY)

    VALUES (1, 1, 1),

    (2, 1, 1),

    (3, 2, 1),

    (4, 2, 1),

    (5, 2, 1),

    (6, 1, 1),

    (6, 2, 2),

    (7, 3, 1),

    (8, 1, 1),

    (8, 2, 2),

    (8, 3, 3),

    (9, 1, 1),

    (9, 1, 2),

    (10, 2, 1);

    DECLARE @MAX_YEAR AS int = (SELECT MAX(YEAR(ServiceDate)) FROM @tblServiceOrders);

    WITH THE_YEARS AS (

    SELECT MIN(YEAR(ServiceDate)) AS THE_YEAR

    FROM @tblServiceOrders

    UNION ALL

    SELECT THE_YEAR + 1

    FROM THE_YEARS

    WHERE THE_YEAR + 1 <= @MAX_YEAR

    ),

    SO_YEAR_ASSIGNED AS (

    SELECT YEAR(S.ServiceDate) AS SERVICE_YEAR, S.*

    FROM @tblServiceOrders AS S

    ),

    THE_MONTHS AS (

    SELECT 1 AS THE_MONTH

    UNION ALL

    SELECT THE_MONTH + 1

    FROM THE_MONTHS

    WHERE THE_MONTH + 1 <= 12

    ),

    YEAR_MONTH AS (

    SELECT Y.THE_YEAR, M.THE_MONTH,

    CAST(CAST(Y.THE_YEAR AS char(4)) + '-' + RIGHT('0' + CAST(M.THE_MONTH AS varchar(2)), 2) + '-01' AS date) AS MONTH_START_DT,

    C.CustomerID, TM.MachineID

    FROM THE_YEARS AS Y, THE_MONTHS AS M, @tblCustomers AS C

    CROSS APPLY @tblMachines AS TM

    ),

    THE_DATA AS (

    SELECT X.SERVICE_YEAR, X.MachineID, MS2.ServicedQTY,

    DATEADD(day, 1 - DATEPART(day, MIN_SVC_DATE), CAST(MIN_SVC_DATE AS date)) AS SVC_MONTH_START

    FROM (

    SELECT SO.SERVICE_YEAR, M.MachineID,

    NULLIF(MIN(ISNULL(SO.ServiceDate, '1900-01-01')), '1900-01-01') AS MIN_SVC_DATE

    FROM @tblCustomers AS C

    INNER JOIN @tblMachines AS M

    ON M.CustomerID = C.CustomerID

    INNER JOIN @tblMachinesServiced AS MS

    ON M.MachineID = MS.MachineID

    INNER JOIN SO_YEAR_ASSIGNED AS SO

    ON M.CustomerID = SO.CustomerID

    AND SO.ServiceOrderNo = MS.ServiceOrderNo

    GROUP BY SO.SERVICE_YEAR, M.MachineID

    ) AS X

    INNER JOIN SO_YEAR_ASSIGNED AS SYA

    ON X.SERVICE_YEAR = SYA.SERVICE_YEAR

    AND X.MIN_SVC_DATE = SYA.ServiceDate

    INNER JOIN @tblMachinesServiced AS MS2

    ON X.MachineID = MS2.MachineID

    AND SYA.ServiceOrderNo = MS2.ServiceOrderNo

    )

    SELECT YM.THE_YEAR, YM.THE_MONTH,

    COUNT(DISTINCT TD.MachineID) AS ServicedMachineIDs

    , SUM(ISNULL(TD.ServicedQTY, 0)) AS [Machines Serviced]

    FROM YEAR_MONTH AS YM

    LEFT OUTER JOIN THE_DATA AS TD

    ON YM.THE_YEAR = TD.SERVICE_YEAR

    AND YM.MachineID = TD.MachineID

    AND YM.MONTH_START_DT = TD.SVC_MONTH_START

    GROUP BY YM.THE_YEAR, YM.THE_MONTH

    ORDER BY YM.THE_YEAR, YM.THE_MONTH

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (7/24/2015)


    greggg (7/24/2015)


    Thanks for the assistance and suggestions, Steve & Scott. You guys here are very generous to say the least. I'm going to study the code you both posted.

    Now that you've posted updated sample data with a new field, that kind of invalidates the previous work, so here's a further update based on the new field.

    I'm assuming that the occurrence of a MachineId for the first time in a year is the only time we count the ServicedQTY for the entire year. If that assumption

    is not valid, let me know, as I'm carrying it over from the previous code attempts.

    Yes, I only want to count the MachineID once for the entire year (in the month it first occurred), although it may appear numerous times throughout the year.

Viewing 15 posts - 1 through 14 (of 14 total)

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