September 14, 2017 at 10:49 pm
Hello Experts,
Need an efficient query to pull data for below scenario. Greatly appreciate your help.
------------------------------
Scenario -
After a customer completes an AppointmentType AP1, an AppointmentType AP2 service is due + 3 months, for a total of 3 AP2 delivered after a AP1.
For example,
Service - Due
AP1– 1/1/2017
AP2– 4/1/2017
AP2– 7/1/2017
AP2– 10/1/2017
AP1 – 1/1/2018
An AP1 is limited to 1 in a 12 month period
Need to get a list of customers who is due for an appointment with what next appointmenttype and date due should be along with last appointment details.
In below case, the expected result
customer,lastappttype,lastapptdate,nextappttype,nextapptdate
C1, AP2,10/01/2017,AP1 01/01/2018
C2, AP2,04/01/2017, AP2 07/01/2017
-------------------------------------------
Scripts for Table and sample data below
CREATE TABLE [dbo].[tbl_Test](
[CustID] [char](5) NULL,
[ApptType] [char](5) NULL,
[ApptDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tbl_Test]
([CustID]
,[ApptType]
,[ApptDate])
VALUES
('C1',
'AP1',
'01/01/2017')
INSERT INTO [dbo].[tbl_Test]
([CustID]
,[ApptType]
,[ApptDate])
VALUES
('C1',
'AP2',
'04/01/2017')
INSERT INTO [dbo].[tbl_Test]
([CustID]
,[ApptType]
,[ApptDate])
VALUES
('C1',
'AP2',
'07/01/2017')
INSERT INTO [dbo].[tbl_Test]
([CustID]
,[ApptType]
,[ApptDate])
VALUES
('C1',
'AP2',
'10/01/2017')
INSERT INTO [dbo].[tbl_Test]
([CustID]
,[ApptType]
,[ApptDate])
VALUES
('C2',
'AP1',
'01/01/2017')
INSERT INTO [dbo].[tbl_Test]
([CustID]
,[ApptType]
,[ApptDate])
VALUES
('C2',
'AP2',
'04/01/2017')
September 15, 2017 at 7:08 am
Not sure if this is sufficiently tested, so please do that, but here's an attempt:CREATE TABLE #tbl_Test (
[CustID] char(5) NULL,
[ApptType] char(5) NULL,
[ApptDate] datetime NULL
);
GO
INSERT INTO #tbl_Test ([CustID], [ApptType], [ApptDate])
VALUES ('C1', 'AP1', '01/01/2017'),
('C1', 'AP2', '04/01/2017'),
('C1', 'AP2', '07/01/2017'),
('C1', 'AP2', '10/01/2017'),
('C2', 'AP1', '01/01/2017'),
('C2', 'AP2', '04/01/2017');
DECLARE @TODAY AS date = GETDATE();
DECLARE @YEAR_START AS date = DATEFROMPARTS(YEAR(@TODAY), 1, 1);
WITH ORDERED_APPTS AS (
SELECT T.CustID, T.ApptType, T.ApptDate, ROW_NUMBER() OVER(PARTITION BY T.CustID ORDER BY T.ApptDate) AS ROW_NUM
FROM #tbl_Test AS T
),
AP1S AS (
SELECT OA.CustID, MAX(OA.ApptDate) AS LastAP1Date, MAX(OA.ROW_NUM) AS LastAP1RowNum
FROM ORDERED_APPTS AS OA
WHERE OA.ApptType = 'AP1'
GROUP BY OA.CustID
),
AP2S AS (
SELECT OA.CustID, COUNT(*) AS AP2_Count, MAX(OA.ApptDate) AS LastAP2Date
FROM ORDERED_APPTS AS OA
WHERE OA.ApptType = 'AP2'
AND OA.ApptDate >= @YEAR_START
GROUP BY OA.CustID
),
LAST_APPTS AS (
SELECT OA.CustID, CONVERT(date, MAX(OA.ApptDate)) AS LastApptDate, MAX(OA.ROW_NUM) AS ROW_NUM,
CASE
WHEN MIN(OA.ApptType) = 'AP1' AND MAX(AP1S.LastAP1Date) >= @YEAR_START THEN MAX(AP1S.LastAP1Date)
ELSE NULL
END AS LastAP1Date
FROM ORDERED_APPTS AS OA
LEFT OUTER JOIN AP1S
ON OA.CustID = AP1S.CustID
GROUP BY OA.CustID
)
SELECT LA.CustID, (SELECT ApptType FROM ORDERED_APPTS AS A WHERE A.CustID = OA.CustID AND A.ROW_NUM = OA.ROW_NUM) AS LastApptType,
LA.LastApptDate,
CASE WHEN AP2S.AP2_Count = 3 THEN 'AP1' ELSE 'AP2' END AS NextApptType,
CASE WHEN AP2S.AP2_Count = 3 THEN CONVERT(date, DATEADD(year, 1, LA.LastAP1Date)) ELSE CONVERT(date, DATEADD(month, 3, AP2S.LastAP2Date)) END AS NextApptDate
FROM LAST_APPTS AS LA
LEFT OUTER JOIN ORDERED_APPTS AS OA
ON LA.CustID = OA.CustID
AND LA.LastApptDate = OA.ApptDate
AND LA.ROW_NUM = OA.ROW_NUM
LEFT OUTER JOIN AP2S
ON OA.CustID = AP2S.CustID
ORDER BY OA.CustID;
DROP TABLE #tbl_Test;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 15, 2017 at 8:19 am
That's way too many reads (12) on the base table. Here is an approach that requires only ONE read.
;
WITH Ordered_Appts AS
(
SELECT *,
DATEADD(MONTH, 3, ApptDate) AS NextApptDate,
MAX(CASE WHEN ApptType = 'AP1' THEN ApptDate END) OVER(PARTITION BY CustID ORDER BY ApptDate ROWS UNBOUNDED PRECEDING) AS LastAP1Date,
ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY ApptDate DESC) AS rn
FROM #tbl_Test
)
SELECT
CustID,
ApptType AS LastApptType,
ApptDate AS LastApptDate,
CASE WHEN NextApptDate > DATEADD(MONTH, 11, LastAP1Date) THEN 'AP1' ELSE 'AP2' END AS NextApptType,
NextApptDate
FROM Ordered_Appts
WHERE rn = 1
I added an allowance in the calculation for the date between AP1 appointments not being exactly a year. People's schedules are messy and they can't always schedule things exactly to the year, so I said that if their last AP1 appointment was over 11 months ago, then to schedule an AP1 appointment.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 15, 2017 at 11:34 am
Thank you so much Drew and Steve. I will run against real-time data and see. Works perfect with the test data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply