July 22, 2015 at 3:04 pm
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.
July 22, 2015 at 4:26 pm
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
July 22, 2015 at 8:16 pm
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.
July 23, 2015 at 6:15 pm
How would I go about getting additional information from the subquery, like the ServiceDate for the record?
July 23, 2015 at 8:29 pm
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
July 24, 2015 at 1:10 am
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)
____________________________________________________________
APJuly 24, 2015 at 8:22 am
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.
July 24, 2015 at 8:28 am
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
July 24, 2015 at 10:23 am
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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 11:19 am
--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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 24, 2015 at 1:05 pm
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
July 24, 2015 at 1:05 pm
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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 1:25 pm
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.
July 24, 2015 at 1:57 pm
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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 2:01 pm
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