根据最近的验证日期根据2周的支架检索销售记录计数

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

每个客户都有一个唯一的CustomerRef。可以多次验证同一个人（customerref），因此在验证表中可能具有多个记录。

对于验证表中的每一行，我想获得sales的数量日期。

这是十字架应用查询：

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

我相信这可以通过第1个计算主表进行操作，然后应用每个十字申请每行。有更好的方法吗？

例如 - 我尝试使用SQL LEAD将十字架应用于1个十字架，但它给出了一个错误：

窗口函数只能出现在选择或顺序中。

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

Answer 1

您需要将Lead放入派生表中，以便在查询的其他部分中使用它，

• 您还需要考虑到可能没有“下一个”行，因此请给出Lead默认值。这将替换第一个应用
• 您的第二个Apply似乎是完全不必要的，可以删除。必须保留第三和第四。

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