Search code examples
sql-serverpartition-by

SQL Server PARTITION BY with date calcuation

am trying to run some SQL which needs a PARTITION BY but can't manage to tackle the complexity required. The below data is simplified but the idea is to use the first three columns of the table to find the 4th column (I've added values manually), while also displaying the other columns in the table.

For each customer in the table, it needs to count the number of days that the last enquiry for this customer fell on before the current record's enquiry date as long as it is less than 28 days before otherwise show a NULL. Also need to allow for ties which can be broken via the Enquiry column value being smaller - the smaller value being counted as first. 

Customer Enquiry EnquiryDate   DaysSinceLastEnquiryForCustomer
522181   232853  19/01/2014    NULL
522181   234750  30/01/2014    11
522181   235141  03/02/2014    4
522181   235210  03/02/2014    4
522181   262015  23/09/2014    NULL
522181   263942  09/10/2014    16
522181   265583  22/10/2014    13
522181   311345  01/10/2015    NULL
522181   321938  31/12/2015    NULL
522181   322404  31/12/2015    0
522181   328057  27/01/2016    23
522181   329164  02/02/2016    6
522181   329426  03/02/2016    1
522181   336409  17/03/2016    14
522181   336581  18/03/2016    1
522181   337003  22/03/2016    4
522181   343338  15/05/2016    NULL
522181   344185  23/05/2016    8
522181   352323  06/08/2016    14

Thanks in advance

Solution

  • Not sure I understand how that logic produces those results. But here's a sample to play with:

    Note in SSMS (or VS) if you hold down Shift+Alt and Arrow up/down you get a "vertical selection", into which you can type the same value in multiple columns. So easily turning the above fixed-width table into an INSERT .. VALUES query.

    use tempdb
go

drop table if exists C
create table C(Customer int, Enquiry int, EnquiryDate date)
insert into C(Customer,Enquiry,EnquiryDate)
values

--Customer Enquiry EnquiryDate   DaysSinceLastEnquiryForCustomer
(522181, 232853,parse('19/01/2014' as date using 'en-GB')),--  NULL
(522181, 234750,parse('30/01/2014' as date using 'en-GB')),--  11
(522181, 235141,parse('03/02/2014' as date using 'en-GB')),--  5
(522181, 235210,parse('03/02/2014' as date using 'en-GB')),--  5
(522181, 262015,parse('23/09/2014' as date using 'en-GB')),--  NULL
(522181, 263942,parse('09/10/2014' as date using 'en-GB')),--  NULL
(522181, 265583,parse('22/10/2014' as date using 'en-GB')),--  13
(522181, 311345,parse('01/10/2015' as date using 'en-GB')),--  10
(522181, 321938,parse('31/12/2015' as date using 'en-GB')),--  NULL
(522181, 322404,parse('31/12/2015' as date using 'en-GB')),--  0
(522181, 328057,parse('27/01/2016' as date using 'en-GB')),--  23
(522181, 329164,parse('02/02/2016' as date using 'en-GB')),--  6
(522181, 329426,parse('03/02/2016' as date using 'en-GB')),--  1
(522181, 336409,parse('17/03/2016' as date using 'en-GB')),--  14
(522181, 336581,parse('18/03/2016' as date using 'en-GB')),--  1
(522181, 337003,parse('22/03/2016' as date using 'en-GB')),--  4
(522181, 343338,parse('15/05/2016' as date using 'en-GB')),--  NULL
(522181, 344185,parse('23/05/2016' as date using 'en-GB')),--  8
(522181, 352323,parse('06/08/2016' as date using 'en-GB'))--  14


select *, prev.daysSince
from C c1
outer apply 
(
  select top 1 *, datediff(day, c2.EnquiryDate, c1.EnquiryDate) daysSince
  from C c2
  where c2.Customer = c1.Customer
    and c2.Enquiry != c1.Enquiry
    and c2.EnquiryDate < c1.EnquiryDate
    and datediff(day, c2.EnquiryDate, c1.EnquiryDate) < 28
  order by c2.EnquiryDate desc
) prev
order by c1.Customer,c1.Enquiry