Retrieve the count of Sale records based on 2 week bracket from the recent verification date

Question

CREATE TABLE [dbo].[Sale](
    [ID] [int] NOT NULL,
    [SaleDate] [date] NOT NULL,
    [CustomerRef] [varchar](20) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Verification](
    [CustomerRef] [varchar](20) NOT NULL,
    [VerificationDate] [date] NOT NULL
) ON [PRIMARY]

INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (1, CAST(N'2022-02-01' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (2, CAST(N'2022-02-02' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (3, CAST(N'2022-02-03' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (4, CAST(N'2022-02-13' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (5, CAST(N'2022-02-14' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (6, CAST(N'2022-02-15' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (7, CAST(N'2022-02-16' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (8, CAST(N'2022-03-08' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (9, CAST(N'2022-03-08' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (10, CAST(N'2022-03-10' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (11, CAST(N'2022-03-11' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (12, CAST(N'2022-03-12' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (13, CAST(N'2022-03-13' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (14, CAST(N'2022-02-20' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (15, CAST(N'2022-03-14' AS Date), N'1')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (16, CAST(N'2022-02-10' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (17, CAST(N'2022-02-11' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (18, CAST(N'2022-02-12' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (19, CAST(N'2022-03-18' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (20, CAST(N'2022-03-19' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (21, CAST(N'2022-03-20' AS Date), N'2')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (22, CAST(N'2022-02-15' AS Date), N'3')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (23, CAST(N'2022-02-16' AS Date), N'3')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (24, CAST(N'2022-02-20' AS Date), N'4')
GO
INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES (25, CAST(N'2022-02-21' AS Date), N'4')
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'1', CAST(N'2022-02-01' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'2', CAST(N'2022-02-10' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'3', CAST(N'2022-02-15' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'4', CAST(N'2022-02-20' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'1', CAST(N'2022-03-10' AS Date))
GO
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES (N'2', CAST(N'2022-03-20' AS Date))
GO

Each customer has a unique CustomerRef. The same person (CustomerRef) can be verified multiple times, so may have multiple records in the Verification table.

For each row in the Verification table, I want to get the number of Sales that have happened within 2 weeks from the first Sale after the verification date.

Here is the CROSS APPLY query:

SELECT *
FROM VERIFICATION A
--GET NEXT VERIFICATION DATE
CROSS APPLY 
    (SELECT MIN(VerificationDate) NextVerificationDate 
     FROM VERIFICATION B
     WHERE A.CUSTOMERREF = B.CUSTOMERREF 
       AND B.VERIFICATIONDATE > A.VERIFICATIONDATE) X
--GET FIRST SALE DATE POST VERIFICATION DATE
CROSS APPLY 
    (SELECT MIN(SaleDate) FirstSaleDatePostVerificationDate 
     FROM SALE B 
     WHERE A.CUSTOMERREF = B.CUSTOMERREF 
       AND B.SALEDATE >= A.VERIFICATIONDATE) Y
--GET FIRST SALE DATE POST VERIFICATION DATE AND PRIOR TO NEXT VERIFICATION DATE
CROSS APPLY 
    (SELECT MIN(SaleDate) FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
     FROM SALE B 
     WHERE A.CUSTOMERREF = B.CUSTOMERREF 
       AND B.SALEDATE >= A.VERIFICATIONDATE
       AND B.SALEDATE < ISNULL(X.NextVerificationDate,'20990101')) Z
--GET COUNT OF SALES IN 2 WEEKS FROM THE FIRST SALE DATE POST VERIFICATION DATE (AND PRIOR TO NEXT VERIFICATION DATE)
CROSS APPLY 
    (SELECT COUNT(*) COUNT 
     FROM SALE B 
     WHERE A.CUSTOMERREF = B.CUSTOMERREF
       AND B.SALEDATE >= Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
       AND SALEDATE < DATEADD(WEEK, 2, Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate)) U

I believe this works by 1st computing the main table, and then applying each cross apply for each row. Is there a better way to do this?

For example - I tried clubbing both the CROSS APPLY's into 1 CROSS APPLY using the SQL LEAD, but it gives an error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

SELECT *
FROM verification A
CROSS APPLY 
    (SELECT COUNT(*) COUNT 
     FROM SALE B 
     WHERE A.CUSTOMERREF = B.CUSTOMERREF
       AND B.SALEDATE >= A.VERIFICATIONDATE 
       AND B.SALEDATE < LEAD(A.VERIFICATIONDATE) OVER 
                            (PARTITION BY CUSTOMERREF ORDER BY VERIFICATIONDATE)) X

Charlieface · Answer 1 · 2022-05-31T08:21:41.500

0

You need to put LEAD into a derived table in order to use it in other parts of the query

You also need to take into account there may not be a "next" row, so give LEAD a default. This will replace the first APPLY
Your second APPLY seems entirely unnecessary, and can be removed. The third and fourth must remain.

SELECT v.*, s.*
FROM (
    SELECT *,
      NextDate = LEAD(v.VerificationDate, 1,  '20990101') OVER (PARTITION BY v.CustomerRef ORDER BY v.VerificationDate)
    FROM Verification v
) v
OUTER APPLY (
    SELECT TOP (1) sFirst.*
    FROM Sale sFirst
    WHERE sFirst.CustomerRef = v.CustomerRef
      AND sFirst.SaleDate < v.NextDate
    ORDER BY sFirst.SaleDate
) sFirst
CROSS APPLY (
    SELECT count = COUNT(*)
    FROM Sale s
    WHERE s.CustomerRef = v.CustomerRef
      AND s.SaleDate > sFirst.SaleDate
      AND s.SaleDate < DATEADD(day, 14, sFirst.SaleDate)
      AND s.SaleDate < v.NextDate
) s

db<>fiddle

edited May 31 '22 at 08:21

answered May 31 '22 at 01:20

Charlieface

52,284
6
19
43

Is TOP/Order by better than using MIN? Also, you haven't accounted for the the 2 week logic. – variable May 31 '22 at 06:06
Quite right, fixed now. Yes, it's often better, although it does depend on indexing (occasionally it's worth doing a full scan and aggregating, rather than doing lots of small lookups) – Charlieface May 31 '22 at 08:22
I'm curious to know why you had to write the `OUTER APPLY` where as my query worked with CROSS APPLY? I did replace your outer apply with cross apply and the output is missing the 1 record (I have added later without any sale). Is it to handle the fact that TOP returns no rows for missing customerref? – variable May 31 '22 at 08:32
Yes that's correct. Because that one does not have a `sFirst` row. The second `APPLY` can still be `CROSS APPLY` because even if it gets no results it's [guaranteed to always have one row](https://www.sql.kiwi/2012/03/fun-with-aggregates.html) – Charlieface May 31 '22 at 08:37

Retrieve the count of Sale records based on 2 week bracket from the recent verification date

1 Answers1