1

Current result

-- Scenario1--For ID 1 and 2, I want to compare if Diff Between (DD,Begin date of ID 2 , End Date OF ID 1) < = 1, then the value should be true and be displayed other wise don't display the value

Scenario2 For ID 10 and 11 -- Begin Date for ID 10 and Begin Date of ID 11 is same, so I want to compare based on Paid date, As Paid Date of ID 11 is > Paid date of ID 10. I want the value to be displayed which is greater

Id  Employeekey ReceiptNo   BeginDate   endDate     PaidDate    main    Supplier    RollNo
1   101         5505        3/28/2016   3/29/2016   4/29/2016   1       2001    655
2   101         5506        3/30/2016   4/1/2016    4/30/2016   1       2001    666
3   101         5507        4/5/2016    4/6/2016    4/30/2016   1       2001    155
4   101         5508        4/7/2016    4/10/2016   5/1/2016    1       2001    155
5   101         5509        4/11/2016   4/14/2016   5/5/2016    1       2001    155
6   101         5510        5/1/2016    5/3/2016    6/24/2016   1       2001    255
7   101         5511        5/1/2016    5/3/2016    6/30/2016   1       2001    265
8   102         5512        3/28/2017   3/29/2016   4/29/2017   1       2001    655
9   102         5513        3/28/2017   3/29/2016   4/29/2017   1       2001    655
10  102         5514        3/28/2017   3/29/2016   4/29/2017   1       2001    655
11  102         5515        3/28/2016   3/29/2016   5/29/2016   1       2001    655
12  102         5515        3/28/2016   3/29/2016   5/29/2016   1       2001    659
Omal Perera
  • 2,971
  • 3
  • 21
  • 26
Avi
  • 1,795
  • 3
  • 16
  • 29
  • 1
    Hint: Row_number() in a cte or lead() or lag() for sql 2012+. Also you need to show expected results and what you have tried. Your question is unclear. – S3S Jun 06 '17 at 02:52
  • Thank you for the information and your help. I have added scenarios to make it more clear and added some columns. – Avi Jun 06 '17 at 04:04
  • Your question is still a bit unclear, can you add expected results. What do you mean by 'value' in scenario1? Do you always compare ID n and ID n+1 or should it be grouped by employeekey or receiptno? – Peter Jun 06 '17 at 09:45

1 Answers1

1

Look at the dates for your second scenario. The dates aren't the same for BeginDate for ID 10 and ID 11. Also, the PaidDate is not >, it's < (look at the years in both cases). With that being said, i edited your test data. Here is how you would do this. You can alter it to fit your needs but this will get you going.

declare @table table (id int, 
                     Employeekey int, 
                     RecieptNo int, 
                     BeginDate datetime, 
                     endDate datetime, 
                     PaidDate datetime, 
                     main int, 
                     Supplier int, 
                     RollNo int)
 insert into @table 
 values
(1,101,5505,'3/28/2016','3/29/2016','4/29/2016',1,2001,655),
(2,101,5506,'3/30/2016','4/1/2016','4/30/2016',1,2001,666),
(3,101,5507,'4/5/2016','4/6/2016','4/30/2016',1,2001,155),
(4,101,5508,'4/7/2016','4/10/2016','5/1/2016',1,2001,155),
(5,101,5509,'4/11/2016','4/14/2016','5/5/2016',1,2001,155),
(6,101,5510,'5/1/2016','5/3/2016','6/24/2016',1,2001,255),
(7,101,5511,'5/1/2016','5/3/2016','6/30/2016',1,2001,265),
(8,102,5512,'3/28/2017','3/29/2016','4/29/2017',1,2001,655),
(9,102,5513,'3/28/2017','3/29/2016','4/29/2017',1,2001,655),
(10,102,5514,'3/28/2016','3/29/2016','4/29/2016',1,2001,655),   --changed this to 2016 for being date and paid date
(11,102,5515,'3/28/2016','3/29/2016','5/29/2017',1,2001,655),   --changed this to 2017 for paid date
(12,102,5515,'3/28/2016','3/29/2016','5/29/2016',1,2001,659)



select
    *
    --scenario 1
    ,case 
        when datediff(day,lead(BeginDate) over (partition by EmployeeKey order by Id),endDate) <=1 then 'True' 
    end
    --scenario 2... id 10 and 11 have different years in your test but i fixed this in my test
    ,case 
        when lead(BeginDate) over (partition by EmployeeKey order by Id) = BeginDate
             and  lead(PaidDate) over (partition by EmployeeKey order by Id) > PaidDate then 'True' 
    end
from @table
S3S
  • 24,809
  • 5
  • 26
  • 45