title: How to get the target achieved date when the target count is derived based on a field in a CROSS APPLY?
body: <pre><code>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
  </code></pre>
  <p>I have written the following query to get the number of sales in the 2-week period from the 1st sale date after the verification date and prior to the next verification date.</p>
  <pre><code>;WITH CTE AS(
  SELECT *,LEAD(VERIFICATIONDATE,1,'20990101') OVER(PARTITION BY CUSTOMERREF ORDER BY VERIFICATIONDATE) AS NEXTVERIFICATIONDATE
  FROM VERIFICATION A
  )
  SELECT * 
  FROM CTE A
  --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 &gt;= A.VERIFICATIONDATE
         AND B.SALEDATE &lt; NEXTVERIFICATIONDATE) 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 &gt;= Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
         AND SALEDATE &lt; DATEADD(WEEK, 2, Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate)
         AND SALEDATE &lt; NEXTVERIFICATIONDATE) U
  </code></pre>
  <p>Against each row, how can I retrieve the sale date value when the count crossed the value of 4?</p>
  <p>Expected output is (look at the last column):</p>
  <pre><code>+-------------+------------------+----------------------+----------------------------------------------------------------+-------+------------+
  | CustomerRef | VerificationDate | NEXTVERIFICATIONDATE | FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate | COUNT | TARGETDATE |
  +-------------+------------------+----------------------+----------------------------------------------------------------+-------+------------+
  |           1 | 2022-02-01       | 2022-03-10           | 2022-02-01                                                     |     5 | 2022-02-13 |
  |           1 | 2022-03-10       | 2099-01-01           | 2022-03-10                                                     |     5 | 2022-03-13 |
  |           2 | 2022-02-10       | 2022-03-20           | 2022-02-10                                                     |     4 | 2022-02-20 |
  |           2 | 2022-03-20       | 2099-01-01           | 2022-03-20                                                     |     1 |            |
  |           3 | 2022-02-15       | 2099-01-01           | 2022-02-15                                                     |     2 |            |
  |           4 | 2022-02-20       | 2099-01-01           | 2022-02-20                                                     |     2 |            |
  +-------------+------------------+----------------------+----------------------------------------------------------------+-------+------------+
  </code></pre>
  <p>I tried adding another CROSS APPLY as shown below:</p>
  <pre><code>CROSS APPLY 
      (SELECT B.SaleDate
       FROM SALE B 
       WHERE A.CUSTOMERREF = B.CUSTOMERREF
         AND B.SALEDATE &gt;= Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
         AND SALEDATE &lt; DATEADD(WEEK, 2, Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate)
         AND SALEDATE &lt; NEXTVERIFICATIONDATE
         AND ROW_NUMBER()over(partition by b.customerref order by b.saledate)=4) V
  </code></pre>
  <p>This gives an error: <code>Windowed functions can only appear in the SELECT or ORDER BY clauses.</code></p>
  <p>Based on one of the comments I have written the following which I suppose is the answer:</p>
  <pre><code>OUTER APPLY(
      SELECT SALEDATE FROM(
      SELECT SALEDATE,ROW_NUMBER()OVER(PARTITION BY B.CUSTOMERREF ORDER BY B.SALEDATE) RNO
       FROM SALE B 
       WHERE A.CUSTOMERREF = B.CUSTOMERREF
         AND B.SALEDATE &gt;= Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate
         AND SALEDATE &lt; DATEADD(WEEK, 2, Z.FirstSaleDatePostVerificationDateAndPriorToNextVerifiationDate)
         AND SALEDATE &lt; NEXTVERIFICATIONDATE
                     ) TBL
                     WHERE RNO=4
  ) V
  </code></pre>