CODEWITHSUNDEEP
sqlsql-serversql-server-2016
huMpty duMpty
huMpty duMpty

Reputation: 14460

Sql query to get unique date based on month

I am working on pulling some data from a table.

declare @SampleData as Table(Id int, ContactId int, Item varchar(25),CreatedOn date)

insert into @SampleData
VALUES(100,2500,'Some item name 1212', '9/5/2020'),
      (104,2500,'Some item name 2232', '9/15/2020'),
      (109,2500,'Some item name 3434', '9/20/2020'),

      (112,3000,'Some item name 5422', '8/1/2020'),
      (132,3000,'Some item name 344', '9/5/2020'),
      (134,3000,'Some item name 454', '9/15/2020'),

      (139,3500,'Some item name 6455', '7/5/2020'),
      (146,3500,'Some item name 546', '8/5/2020'),
      (142,3500,'Some item name 867', '9/5/2020'),
      (149,3500,'Some item name 677', '9/15/2020'),
      (150,3500,'Some item name 888', '9/19/2020')

The logic here is so that you can find new contact id each month (so logic is if same contact dont have any record in last 28 days from 1st of that month, it consider as new contact)

When you have two date periods, this is easy to do so you can exclude the records you want as below

SELECT *
FROM @SampleData
WHERE CreatedOn> = @FromDate
    and CreatedOn <=@Date
    and ContactId not in (SELECT ContactId
                          FROM @SampleData
                          WHERE CreatedOn >= DateAdd(Day, -28,@FromDate)
                            AND CreatedOn < @FromDate)

What I want is to pre-populate this data without having parameters to a some table so that user can use.

In this example data, I am expecting contact 3500 for July, 3000 for August and 2500&3000 for September.

Also it need to display only record per contact and not duplicate.

DECLARE @From date,
        @To date
DECLARE date_cursor CURSOR FOR 
select distinct DATEADD(month, DATEDIFF(month, 0, CreatedOn), 0) FromDate,EOMONTH(CreatedOn) ToDate
from @SampleData

OPEN date_cursor  
FETCH NEXT FROM date_cursor INTO @From,@To  

WHILE @@FETCH_STATUS = 0  
BEGIN  

     SELECT *
     FROM (
             SELECT DISTINCT ContactId,@From 'From Date', @To 'To Date'
             FROM @SampleData D
             WHERE D.CreatedOn>= @From AND D.CreatedOn <= @To
                AND  ContactId NOT IN (SELECT ContactId
                                  FROM @SampleData
                                  WHERE CreatedOn >= DateAdd(Day, -28,@From)
                                    AND CreatedOn < @From)) ContactData
                    OUTER APPLY (
                            --pick first row for the contact as per the period
                            SELECT TOP 1 *
                            FROM @SampleData D
                            WHERE D.ContactId = ContactData.ContactId
                                AND D.CreatedOn >= ContactData.[From Date]
                                AND D.CreatedOn < ContactData.[To Date]
                            ORDER BY CreatedOn 
                            ) Records


      FETCH NEXT FROM date_cursor INTO  @From,@To   
END 

CLOSE date_cursor  
DEALLOCATE date_cursor

Result

ContactId   From Date   To Date     Id  Item                 CreatedOn
3500       01/07/2020   31/07/2020  139 Some item name 6455 05/07/2020
3000       01/08/2020   31/08/2020  112 Some item name 5422 01/08/2020
2500       01/09/2020   30/09/2020  100 Some item name 1212 05/09/2020
3000       01/09/2020   30/09/2020  132 Some item name 344  05/09/2020

I would like to get rid of cursor, is there any possibility

Upvotes: 1

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can assign a grouping to the contacts by using lag() and comparing the rows:

select sd.*,
       sum(case when prev_createdon > dateadd(day, -28, createdon) then 0 else 1 end) over
           (partition by contactid order by createdon) as grouping
from (select sd.*,
             lag(createdon) over (partition by contactid order by createdon) as prev_createdon
      from SampleData sd
     ) sd;

If you just want the first row in a series of adjacent records, then:

select sd.*
from (select sd.*,
             lag(createdon) over (partition by contactid order by createdon) as prev_createdon
      from SampleData sd
     ) sd
where prev_createdon < dateadd(day, -28, createdon) or prev_createdon is null;

Here is a db<>fiddle.

EDIT:

Based on the revised question, you want to summarize by group. You an do this using:

select contactid, min(createdon), max(createdon), min(id),
       max(case when seqnum = 1 then item end) as item
from (select sd.*,
             row_number() over (partition by contactid, grouping order by createdon) as seqnum
      from (select sd.*,
                   sum(case when prev_createdon > dateadd(day, -28, createdon) then 0 else 1 end) over
                        (partition by contactid order by createdon) as grouping
            from (select sd.*,
                         lag(createdon) over (partition by contactid order by createdon) as prev_createdon
                  from SampleData sd
                 ) sd
           ) sd
     ) sd
group by contactid, grouping;

I updated the DB fiddle to have this as well.

Upvotes: 1

Related Questions