using partition by, group by and count together

Question

Blockquote Copy/Paste data:

productNumber   Account BuyDate SellDate    RelevantYYYYMM
27530   8605148 11/5/15 7:03 PM 11/11/15 2:02 PM    201511
27530   8582063 10/14/15 7:17 PM    10/16/15 1:48 PM    201510
27530   8566748 9/30/15 6:51 PM 10/13/15 2:09 PM    201510
101167  8622268 11/27/15 7:50 PM    11/27/15 8:35 PM    201511
101167  8622267 11/27/15 8:49 AM    11/27/15 11:02 AM   201511
101167  8622265 11/26/15 8:47 AM    11/26/15 11:14 AM   201511
101167  8622272 11/25/15 7:43 PM    11/25/15 8:40 PM    201511
102432  8611407 11/12/15 5:41 PM    11/25/15 11:50 AM   201511
102432  8600664 11/2/15 12:04 AM    11/4/15 12:56 PM    201511
102432  8573179 10/5/15 2:03 PM 10/7/15 3:47 PM 201510
127819  8581822 10/14/15 7:49 PM    10/18/15 10:46 AM   201510
127819  8572308 10/2/15 4:38 PM 10/4/15 2:28 PM 201510
127819  8571220 10/2/15 9:34 AM 10/2/15 4:25 PM 201510
141105  8612529 11/16/15 7:45 AM    11/16/15 3:06 PM    201511
141105  8612460 11/14/15 10:04 AM   11/14/15 12:48 PM   201511
141105  8608738 11/10/15 12:53 PM   11/10/15 3:19 PM    201511
141105  8603007 11/3/15 5:28 PM 11/9/15 3:54 PM 201511
141105  8595018 10/28/15 7:23 PM    11/2/15 2:09 PM 201511
141105  8562238 10/22/15 8:40 AM    10/27/15 5:11 PM    201510

I need to get a count of ProductNumber group by RelevantYYYYMM (201512 means December 2015). However, the logic should be:

ProductNumber and its most recent BuyDate should be within that RelevantYYYYMM.
Previous BuyDate associated with the ProductNumber's latest BuyDate should be within 30 days range. Example: Most recent BuyDate for Product 27530 is 11/5/15. Previous BuyDate for product is 10/14/15, which is within 30 days from 11.5.15.
3rd BuyDate should be within 30 days range from 2nd BuyDate. Example: 2nd BuyDate for ProductNumber 27530 is 10/14/15 and 3rd BuyDate is 9/30/15, which is within 30 days range of 10/14/15.
This validation should be done for each ProductNumber and RelevantYYYYMM associated with that Number. Example: For RelevantYYYYMM 201510 (October 2015), ProductNumber 27530 should not show up in my count because Stpes 1-3 aren't met. Explanationn: The most recent date for the ProductNumber 27530 in 201510 (October 2015) is 10/14/2015. Previous BuyDate is 9/30/2015, which is within 30 days range of 10/14/2015. However, there isn't another BuyDate for that ProductNumber prior to 9/30/2015 that is within 30 days range. It fails to validate step 3 so it gets eliminated from the count.

code:

--get all products and assign YYYYMM
drop table #firstbuy
declare @StartDate as date  = '01/01/2015'
declare @EndDate as date = '12/31/2015'

select ProductNumber, Account, BuyDate, SellDate,
CAST(DATEPART(yyyy, BuyDate) AS varchar(4))+ 
CAST(REPLICATE('0', 2-LEN(MONTH(BuyDate))) AS varchar(2)) +
CAST(DATEPART(MONTH, BuyDate) AS varchar(2)) AS RelevantYYYYMM
into #firstbuy
From products V
WHERE
V.BuyDate BETWEEN @StartDate AND @EndDate 
--select * from #firstbuy

--Rank by BuyDate desc.  Use ProductNumber and RelevantYYYYMM for partition     by
drop table #firstbuypart2
select * 
into #firstbuypart2
from (
select ProductNumber, Account, BuyDate, SellDate, RelevantYYYYMM,
                ROW_NUMBER() OVER (PARTITION BY ProductNumber,     RelevantYYYYMM ORDER BY BuyDate DESC) AS     getcount1                               
from #firstbuy
) a
--select * from #firstbuypart2 

--Grab records that has count = 1.  This will give you most recent BuyDate per RelevantYYYYMM
drop table #firstbuypart3
select ProductNumber, BuyDate, SellDate, RelevantYYYYMM 
into #firstbuypart3
from #firstbuypart2
where getcount1 = 1
Group BY ProductNumber, RelevantYYYYMM, BuyDate, SellDate,
order by ProductNumber
--select * from #firstbuypart3


--Not sure what logic to use to get only one specific row per ProductNumber and RelevantYYYYMM
drop table #firstbuypart4
select b.*, a.BuyDate as BuyDateA, a.SellDate as SellDateA,
CAST(DATEPART(yyyy, a.BuyDateA) AS varchar(4))+ 

CAST(REPLICATE('0', 2-LEN(MONTH(a.BuyDateA))) AS varchar(2)) +

CAST(DATEPART(MONTH, a.BuyDateA) AS varchar(2)) AS RelevantYYYYMM_A  
into #firstdischargepart4
From products a
JOIN #firstbuypart3 b on a.ProductNumber = b.ProductNumber
WHERE 
DATEDIFF(DAY,b.BuyDate,a.BuyDate) BETWEEN 0 AND 30
--select * from #firstbuypart4

Does that work, or is there another way to copy/paste sample data? — NonProgrammer, Dec 21 '15 at 20:45

score 1 · Accepted Answer · edited May 23 '17 at 12:15

I think this could be what you need:

ATTENTION_1: You should NOT use culture specific date time formats! Look here: https://stackoverflow.com/a/34275965/5089204

ATTENTION_2: Be aware of DateTime! BETWEEN @StartDate AND @EndDate will NOT include BuyDates from @EndDate, if there is a time!

SET LANGUAGE ENGLISH;

DECLARE @tbl TABLE(productNumber INT,Account INT,BuyDate DATETIME,SellDate DATETIME);
INSERT INTO @tbl VALUES
 (27530,8605148,'11/5/15 7:03 PM','11/11/15 2:02 PM')
,(27530,8582063,'10/14/15 7:17 PM','10/16/15 1:48 PM')
,(27530,8566748,'9/30/15 6:51 PM','10/13/15 2:09 PM')
,(101167,8622268,'11/27/15 7:50 PM','11/27/15 8:35 PM')
,(101167,8622267,'11/27/15 8:49 AM','11/27/15 11:02 AM')
,(101167,8622265,'11/26/15 8:47 AM','11/26/15 11:14 AM')
,(101167,8622272,'11/25/15 7:43 PM','11/25/15 8:40 PM')
,(102432,8611407,'11/12/15 5:41 PM','11/25/15 11:50 AM')
,(102432,8600664,'11/2/15 12:04 AM','11/4/15 12:56 PM')
,(102432,8573179,'10/5/15 2:03 PM','10/7/15 3:47 PM')
,(127819,8581822,'10/14/15 7:49 PM','10/18/15 10:46 AM')
,(127819,8572308,'10/2/15 4:38 PM','10/4/15 2:28 PM')
,(127819,8571220,'10/2/15 9:34 AM','10/2/15 4:25 PM')
,(141105,8612529,'11/16/15 7:45 AM','11/16/15 3:06 PM')
,(141105,8612460,'11/14/15 10:04 AM','11/14/15 12:48 PM')
,(141105,8608738,'11/10/15 12:53 PM','11/10/15 3:19 PM')
,(141105,8603007,'11/3/15 5:28 PM','11/9/15 3:54 PM')
,(141105,8595018,'10/28/15 7:23 PM','11/2/15 2:09 PM')
,(141105,8562238,'10/22/15 8:40 AM','10/27/15 5:11 PM');

declare @StartDate as date  = '01/01/2015';
declare @EndDate as date = '12/31/2015';

WITH Extended AS
(
    SELECT ProductNumber
          ,Account
          ,BuyDate
          ,SellDate
          ,CAST(YEAR(BuyDate) AS VARCHAR(4))+ REPLACE(STR(MONTH(BuyDate),2),' ','0') AS RelevantYYYYMM
          ,ROW_NUMBER() OVER(PARTITION BY ProductNumber ORDER BY productNumber,BuyDate DESC) AS RowInx
          ,COUNT(BuyDate) OVER(PARTITION BY ProductNumber) AS CountBuyDates
    FROM @tbl
    WHERE BuyDate BETWEEN @StartDate AND @EndDate --ATTENTION!!! Be aware of DateTime! this will NOT include BuyDates from @EndDate, if there is a time!
)
,DayDiffs AS
(
    SELECT *
          ,DATEDIFF(DAY,ext.BuyDate
                      ,(SELECT innerExt.BuyDate FROM Extended AS innerExt WHERE innerExt.productNumber=ext.productNumber AND innerExt.RowInx=ext.RowInx-1)) AS IntervalRecentToSecond 
          ,DATEDIFF(DAY,(SELECT innerExt.BuyDate FROM Extended AS innerExt WHERE innerExt.productNumber=ext.productNumber AND innerExt.RowInx=ext.RowInx-1)
                       ,(SELECT innerExt.BuyDate FROM Extended AS innerExt WHERE innerExt.productNumber=ext.productNumber AND innerExt.RowInx=ext.RowInx-2)) AS IntervalSecondToThird
    FROM Extended AS ext
    WHERE ext.CountBuyDates>=3 --min three
      AND ext.RowInx=3 --this is the relevant row
)
SELECT productNumber 
      ,Account 
      ,RelevantYYYYMM AS Relev
      ,IntervalRecentToSecond AS Diff1To2
      ,IntervalSecondToThird AS Diff2To3
FROM DayDiffs

The result would be:

productNumber   Account Relev   Diff1To2    Diff2To3
27530           8566748 201509  14          22
101167          8622265 201511  1           0
102432          8573179 201510  28          10
127819          8571220 201510  0           12
141105          8608738 201511  4           2

I must admit, that I did not really understand, how the grouping by your "RelevantYYYYMM" should work. But I think, you'll be able to fiddle this in your self... If not: just come back with a short explanation.

This query will be used in a dashboard that displays data for this year and last year per month in YYYYMM format. Here's a better example: A patient was admitted to a hospital on 12/22/15. A doctor wants to know if the patient came to the hospital within 30 days prior to 12/22/15. The patient did on 12/11/15, which is within 30 days. Then doctor wants to know whether the patient was here within 30 days prior to 12/11/15. If yes, the patient should show up on dashboard for 201512. This patient shouldn't show up under dashboard for 201511 because prior to November, patient never came to the hosp — NonProgrammer, Dec 22 '15 at 20:03
In my case, I want to know if a Product (ProductNumber) bought on 12/22/15 (in December), was ever bought 30 days prior to 12/22/15. Let's say it was also bought on 12/11/15 and 11/28/15. 12/11/15 is in 30 days range and 11/28/15 is in 30 days range from 12/11/15. So for December, month you would include this product in the dashboard count. You wouldn't include it in November, because when you look from all the products bought on November, it would fail to create the same scenario it did for December. — NonProgrammer, Dec 22 '15 at 20:20
Let me know if I need to explain it better. I think what I didn't clarify was that we're looking at the data from each month's perspective. Looking at a product in December, you see if the scenario from my description exists or not. If it does, it gets added to December month's count. Then you move to November and see if in November, was the logic from description satisfied...and then October and so on. — NonProgrammer, Dec 22 '15 at 23:53
@NonProgrammer, hi sorry for this late reply, X.mas, family, :-) Your problem is very easy to solve AFAIC: Wouldn't it suffice to set your @endDate to the end of the month in question? Or you add a Parameter `@RelevantYYYYMM` and place this in the WHERE-clause of the "DayDiffs"-CTE (something like `AND @RelevantYYYYMM<=ext.RelevantYYYYMM`). If I understand you correctly: If you cut the resultset at the upper border of the relevant month the logic I gave you should come back with the right data. Just add `WHERE Diff1To2<=30 AND Diff2To3<=30` at the end, Does this help? — Shnugo, Dec 23 '15 at 07:13
Making few changes and adding a cursor, I was able to get what I needed! The issue was the dashboard application only takes Start and End date parameter. So I had to create a cursor and assign YYYYMM and loop it. — NonProgrammer, Dec 24 '15 at 15:44

using partition by, group by and count together

1 Answers1