Difference between dates

• ntreelevel

  Say Hey Kid

  Points: 663

  More actions

  November 20, 2013 at 8:33 am

  #279264

  Hi,

  I have the following data in a table :

  EmpNo BudgetYearStartBudgetYearEnd

  2698 2013-02-012014-01-31

  67682 2013-01-012013-12-31

  43320 2013-02-012014-01-31

  2849 2013-03-012014-02-28

  67687 2013-01-012013-12-31

  67675 2013-01-012013-12-31

  67678 2013-01-012013-12-31

  54870 2013-02-012014-01-31

  2564 2013-01-012013-12-31

  41777 2013-01-012013-12-31

  52890 2013-01-012013-12-31

  669 2013-01-012013-12-31

  54517 2013-02-012014-01-31

  68929 2012-12-282012-12-31----

  68929 2013-01-012013-12-31----

  68134 2013-01-012013-12-31

  67671 2013-01-012013-12-31

  54171 2013-01-012013-12-31

  52959 2013-01-012013-12-31

  41771 2013-01-012013-12-31

  676 2013-01-012013-12-31

  41628 2013-01-012013-12-31

  12387 2013-01-01 2013-12-31

  Now I need to get all the Emp Nos who have active current budget and also who have a previous budget for them which ended just one day prior to the current budget start date which means the difference between the current budget year start and the previoud budget year end should be 1.

  Example

  68929 2012-12-282012-12-31----

  68929 2013-01-012013-12-31---

  12387 2013-01-01 2013-12-31

  this person 68929 has a current budget which is still active till dec 31 2013 and also has a previous budget which ended on dec 31 2012.

  The person 12387 does not have a previous budget year end and has only current budget start and end.

  Now i should get only the emp no row with current budget and who has previous budget year end.

  Result should be only

  68929 2013-01-012013-12-31

  Thank You All in advance

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 20, 2013 at 8:45 am

  #1667834

  no solution, just consumable data:

  i ran out of interest int he issue after converting to usable data:

  ;WITH MyCTE([EmpNo],[BudgetYearStart],[BudgetYearEnd])

  AS

  (

  SELECT convert(int,'2698'),convert(date,' 2013-02-01'),convert(date,'2014-01-31') UNION ALL

  SELECT '67682',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '43320',' 2013-02-01','2014-01-31' UNION ALL

  SELECT '2849',' 2013-03-01','2014-02-28' UNION ALL

  SELECT '67687',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '67675',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '67678',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '54870',' 2013-02-01','2014-01-31' UNION ALL

  SELECT '2564',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '41777',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '52890',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '669',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '54517',' 2013-02-01','2014-01-31' UNION ALL

  SELECT '68929',' 2012-12-28','2012-12-31' UNION ALL

  SELECT '68929',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '68134',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '67671',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '54171',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '52959',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '41771',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '676',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '41628',' 2013-01-01','2013-12-31' UNION ALL

  SELECT '12387','2013-01-01','2013-12-31'

  )

  SELECT * FROM MyCTE

  where getdate() between [BudgetYearStart] AND [BudgetYearEnd];

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!