Date Before Problem

Mark Wilson
Mark Wilson used Ask the Experts™
on
Hi

Using SQL 2008

I have put together the following query

select h.[Person number], pd.[Post Name], ch.[Pattern Number],min(ch.[Effective Date]) SDate,
cc.[Dept]
FROM Employee.Appointment_History h
    JOIN Employee.Career_History ch ON h.[Appointment Number] = ch.[Appointment Number]
    JOIN Organisation.Post_Details pd ON pd.[Post Number] = ch.[Post Number]
      JOIN Person.Details p ON h.[Person Number] = p.[Person Number]
    JOIN Organisation.Cost_Centre_Details cc ON cc.[Cost To Number] = ch.[Cost To Number]
'
where p.[Informal Name] in ('John Smith', 'Sarah Smith')
group by h.[Person number], PD.[Post Name], CH.[Pattern Number], cc.[Dept]
 order by  p.[Informal Name] , sdate

Example outputs is below
             
Person number      Post Name      Pattern Number      SDate                                     Dept      
10921                      Solicitor              15760                      2013-11-13 00:00:00.000      xxxx             
10921                      Solicitor              15760                      2014-04-28 00:00:00.000      xxxx                     
10921                      Solicitor              15760                      2014-05-16 00:00:00.000      xxxx                    
10921                      Solicitor              14766                      2018-05-25 00:00:00.000      xxxx             
12205                      Handler           15760                      2015-08-17 00:00:00.000      xxxx                  
12205                      Paralegal      15760                      2018-06-25 00:00:00.000      xxx          
I want to add an enddate to the query, I have put the output I want based on the example above below

Person number      Post Name      Pattern Number      SDate                                        EndDate                              Dept                        
10921                      Solicitor             15760                      2013-11-13 00:00:00.000         2014-04-27 00:00:00.000   xxxx                       
10921                      Solicitor          15760                      2014-04-28 00:00:00.000         2014-05-15 00:00:00.000   xxxx              
10921                      Solicitor              15760                      2014-05-16 00:00:00.000         2018-05-24 00:00:00.000   xxxx                 
10921                      Solicitor               14766                      2018-05-25 00:00:00.000         2018-06-15 00:00:00.000   xxxx                      
12205                      Handler               15760                      2015-08-17 00:00:00.000         2018-06-24 00:00:00.000   xxxx                 
12205                      Paralegal       15760                      2018-06-25 00:00:00.000         2018-06-15 00:00:00.000   xxxx                 

if you look at the sdate and enddate above the end date will be one day before the next startdate for that person.  For the last startdate for that person the endate will be todays date


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this

with t01
as (
 select h.[Person number], pd.[Post Name], ch.[Pattern Number],min(ch.[Effective Date]) SDate,
cc.[Dept]
FROM Employee.Appointment_History h
    JOIN Employee.Career_History ch ON h.[Appointment Number] = ch.[Appointment Number]
    JOIN Organisation.Post_Details pd ON pd.[Post Number] = ch.[Post Number]
      JOIN Person.Details p ON h.[Person Number] = p.[Person Number]
    JOIN Organisation.Cost_Centre_Details cc ON cc.[Cost To Number] = ch.[Cost To Number]
where p.[Informal Name] in ('John Smith', 'Sarah Smith')
group by h.[Person number], PD.[Post Name], CH.[Pattern Number], cc.[Dept]
 order by  p.[Informal Name] , sdate
)

select [Person number],  [Post Name],   [Pattern Number],   SDate, Dept, isnull(EDate,getdate()) EDate 
from 
(select *, 
 (select 
  dateadd(day, -1, SDate) EDate
from t01 t11 where not exists 
   (select 1 from t01 t12 where t12.SDate > t10.SDate and t12.SDate < t11.SDate and t11.[Post Name] = t12.[Post Name]
   ) and t10.SDate < t11.SDate and t11.[Post Name] = t10.[Post Name]
 ) EDate
from t01 t10) a

Open in new window

Mark WilsonBI Developer

Author

Commented:
Thanks the answer

Seems to work for some but not others

For example

Person number      Post Name                                                Pattern Number      SDate                            EndDate                              Dept    
5962                         Associate Solicitor - Team Leader         16155                       2013-07-02                   2014-04-06                          T1
5962                         Associate Solicitor - Team Leader         16155                       2014-04-07                   2018-06-16                          T2
5962                         Partner                                                    15760                      2016-05-01                    2018-06-16                          T2

i.e. line 2 should be 2016-04-30 and not today's date

Thanks
Mark WilsonBI Developer

Author

Commented:
Hi I have resolved this by removing and t11.[Post Name] = t12.[Post Name] and and t11.[Post Name] = t10.[Post Name]

I have another problem

I have removed where p.[Informal Name] in ('John Smith', 'Sarah Smith') so I can bring through all names

I now get the error message

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=
 or when the subquery is used as an expression.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this helps..
; WITH cte as(
select h.[Person number], pd.[Post Name], ch.[Pattern Number],min(ch.[Effective Date]) SDate,cc.[Dept]
FROM Employee.Appointment_History h
JOIN Employee.Career_History ch ON h.[Appointment Number] = ch.[Appointment Number]
JOIN Organisation.Post_Details pd ON pd.[Post Number] = ch.[Post Number]
JOIN Person.Details p ON h.[Person Number] = p.[Person Number]
JOIN Organisation.Cost_Centre_Details cc ON cc.[Cost To Number] = ch.[Cost To Number]
where p.[Informal Name] in ('John Smith', 'Sarah Smith')
group by h.[Person number], PD.[Post Name], CH.[Pattern Number], cc.[Dept]
)
, temp as (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Person number], [Post Name], [Pattern Number],[Dept] ORDER BY SDate) RNUM
FROM cte
)
SELECT t1.[Person number], t1.[Post Name], t1.[Pattern Number], t1.[Dept], ISNULL(t2.Sdate, GETDATE()) EndDate
FROM temp t1
LEFT JOIN temp t2 on t1.[Person number] = t2.[Person number] AND t1.[Post Name] = t2.[Post Name] AND t1.[Pattern Number] = t2.[Pattern Number] AND t1.[Dept] = t2.[Dept]
AND t1.rnum = t2.rnum - 1

Open in new window

Mark WilsonBI Developer

Author

Commented:
Hi Thanks for the reply. Tried the above comes back with null for all SDate and todays date for all End dates
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hi Mark,

Can you show me some sample result set to understand better and modify accordingly..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Okay, try this one, it should work..
; WITH cte as(
select h.[Person number], pd.[Post Name], ch.[Pattern Number],min(ch.[Effective Date]) SDate,cc.[Dept]
FROM Employee.Appointment_History h
JOIN Employee.Career_History ch ON h.[Appointment Number] = ch.[Appointment Number]
JOIN Organisation.Post_Details pd ON pd.[Post Number] = ch.[Post Number]
JOIN Person.Details p ON h.[Person Number] = p.[Person Number]
JOIN Organisation.Cost_Centre_Details cc ON cc.[Cost To Number] = ch.[Cost To Number]
where p.[Informal Name] in ('John Smith', 'Sarah Smith')
group by h.[Person number], PD.[Post Name], CH.[Pattern Number], cc.[Dept]
)
, temp as (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Person number], [Post Name], [Pattern Number],[Dept] ORDER BY SDate) RNUM
FROM cte
)
SELECT t1.[Person number], t1.[Post Name], t1.[Pattern Number], t1.[Dept], ISNULL(t2.Sdate, GETDATE()) EndDate
FROM temp t1
LEFT JOIN temp t2 on t1.[Person number] = t2.[Person number] AND t1.[Post Name] = t2.[Post Name] AND t1.[Pattern Number] = t2.[Pattern Number] AND t1.[Dept] = t2.[Dept]
AND t1.rnum = t2.rnum - 1

Open in new window


For the sample data in the question, above should work, tested with the below script..
create table test1 ( [Person number] int, [Post Name] varchar(50), [Pattern Number] int, SDate date, Dept varchar(10))
INSERT INTO test1 values
(10921                      ,'Solicitor',               15760                      ,'2013-11-13 00:00:00.000',      'xxxx')             
,(10921                      ,'Solicitor',               15760                      ,'2014-04-28 00:00:00.000',      'xxxx')                     
,(10921                      ,'Solicitor',               15760                      ,'2014-05-16 00:00:00.000',      'xxxx')                    
,(10921                      ,'Solicitor',               14766                      ,'2018-05-25 00:00:00.000',      'xxxx')             
,(12205                      ,'Handler',           15760                      ,'2015-08-17 00:00:00.000',      'xxxx')                  
,(12205                      ,'Paralegal',  15760                      ,'2018-06-25 00:00:00.000',      'xxx')         

; WITH cte as(
SELECT * FROM test1
)
, temp as (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Person number], [Post Name], [Pattern Number],[Dept] ORDER BY SDate) RNUM
FROM cte
)
SELECT t1.[Person number], t1.[Post Name], t1.[Pattern Number], t1.[Dept], t1.Sdate, t2.Sdate EndDate
FROM temp t1
LEFT JOIN temp t2 on t1.[Person number] = t2.[Person number] AND t1.[Post Name] = t2.[Post Name] AND t1.[Pattern Number] = t2.[Pattern Number] AND t1.[Dept] = t2.[Dept]
AND t1.rnum = t2.rnum - 1

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Hi Mark,

Yep, another expert with a "please try"

I have no way of testing, but fairly sure it will do the job....
;with cte_people as
( select top 100 percent h.[Person number], pd.[Post Name], ch.[Pattern Number],min(ch.[Effective Date]) SDate,
         cc.[Dept], p.[informal Name]
  FROM Employee.Appointment_History h
  JOIN Employee.Career_History ch ON h.[Appointment Number] = ch.[Appointment Number]
  JOIN Organisation.Post_Details pd ON pd.[Post Number] = ch.[Post Number]
  JOIN Person.Details p ON h.[Person Number] = p.[Person Number]
  JOIN Organisation.Cost_Centre_Details cc ON cc.[Cost To Number] = ch.[Cost To Number]

  where p.[Informal Name] in ('John Smith', 'Sarah Smith')
  group by h.[Person number], PD.[Post Name], CH.[Pattern Number], cc.[Dept], p.[Informal Name]
  order by  p.[Informal Name] , sdate
), cte_dates as
( select [Person number], [Post Name], [Pattern Number], SDate, [Dept], 
         row_number() over (order by [informal name], sdate) as rn
  from cte_people
) select c.[Person number], c.[Post Name], c.[Pattern Number], c.SDate, isnull(e.sdate,getdate()) eDate, c.[Dept]
  from cte_dates c
  outer apply (select dateadd(d,-1,sdate) from cte_dates d where d.rn = c.rn + 1) e(sdate) 
  order by c.rn

Open in new window

Now, I do have an extra cte in there cte_dates, and shouldnt really need it, but we are talking 2008, and not confident that it will handle that row_number() function in cte_people. It is important to get the sort order the way you want it.... Now, if it can handle (in cte_people) the row_number() over (order by p.[informal name],min(ch.[Effective Date])) as rn, then we could get rid of the "top" and the "order by" and cte_dates

e.g. (try the above first though)
  ;with cte_people as
( select h.[Person number], pd.[Post Name], ch.[Pattern Number],min(ch.[Effective Date]) SDate,
         cc.[Dept], p.[informal Name], row_number() over (order by p.[informal name], min(ch.[Effective Date])) as rn
  FROM Employee.Appointment_History h
  JOIN Employee.Career_History ch ON h.[Appointment Number] = ch.[Appointment Number]
  JOIN Organisation.Post_Details pd ON pd.[Post Number] = ch.[Post Number]
  JOIN Person.Details p ON h.[Person Number] = p.[Person Number]
  JOIN Organisation.Cost_Centre_Details cc ON cc.[Cost To Number] = ch.[Cost To Number]

  where p.[Informal Name] in ('John Smith', 'Sarah Smith')
  group by h.[Person number], PD.[Post Name], CH.[Pattern Number], cc.[Dept], p.[Informal Name]
   
) select c.[Person number], c.[Post Name], c.[Pattern Number], c.SDate, isnull(e.sdate,getdate()) eDate, c.[Dept]
  from cte_people c
  outer apply (select dateadd(d,-1,sdate) from cte_people d where d.rn = c.rn + 1) e(sdate) 
  order by c.rn

Open in new window


We can explore some more if it looks interesting to you...

You should also be able to comment out your "where p.[informal name] in (....) " without breaking it.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Hello Mr. Wilson,

Are you there ? Have you tried the above ?

Even if there are some issues, I am confident they can be very readily dealt with.

Would like to hear back from you.
Mark WilsonBI Developer

Author

Commented:
Apologies I seem to be constantly ill at present - I will give the above reply a go - will report back
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Take care of your health and please give a try on my comment as well..
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Whenever you are ready. There is no stress, because we will still be here when you are ready. Look after your health - that is more important.
Mark WilsonBI Developer

Author

Commented:
Hi Thanks for the feedback. I have tried running the latest queries from Mark Wills. They work to an extent.

When the line  below is in the query it works

where p.[Informal Name] in ('John Smith', 'Sarah Smith')

When I remove the line to run for everyone which I will need to - then last date i.e. e(sdate) changes for everyone. So instead of today's date it can be any date i.e. some are in 2098, some 2014 etc
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Glad to hear from you - Everything OK ?

Well, when I say "Glad" I am referring to your health, but not so glad because I previously said you could comment out that "where" clause and run for everyone...

So, obviously have more work to do....

Can you provide a couple of print outs (as per the question header) so I can visualise the error ?

Oh, and use the cte_dates version...
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hi Mark Wilson,

Kindly let me know whether you got a chance to review the scripts I've provided..
For the sample script you have provided, it worked fine so try it once..
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Hi Mark,

I might have been / was over engineering it by miles....
  ;with cte_people as
( select top 100 percent h.[Person number], pd.[Post Name], ch.[Pattern Number],min(ch.[Effective Date]) SDate,
         cc.[Dept], p.[informal Name]
  FROM Employee.Appointment_History h
  JOIN Employee.Career_History ch ON h.[Appointment Number] = ch.[Appointment Number]
  JOIN Organisation.Post_Details pd ON pd.[Post Number] = ch.[Post Number]
  JOIN Person.Details p ON h.[Person Number] = p.[Person Number]
  JOIN Organisation.Cost_Centre_Details cc ON cc.[Cost To Number] = ch.[Cost To Number]

  where p.[Informal Name] in ('John Smith', 'Sarah Smith')
  group by h.[Person number], PD.[Post Name], CH.[Pattern Number], cc.[Dept], p.[Informal Name]
  order by  p.[Informal Name] , sdate
) 
  select c.[Person number], c.[Post Name], c.[Pattern Number], c.SDate, case when isnull(e.sdate,getdate()) < c.Sdate then c.sdate else isnull(e.sdate,getdate()) end eDate , c.[Dept]
  from cte_people c
  outer apply (select top 1 dateadd(d,-1,sdate) from cte_people d where d.sdate > c.sdate order by d.sdate) e(sdate) 
  order by 1,4,5

Open in new window

The problem was using row_number() - we were never going to get it right because the only seqeunce needed must be sdate - which we already have. And so, that row_number() business is redundant. The reason John and Sarah Smith happened to work is their name sequence coincided with date sequence.

Fixed up the edate (hopefully to your liking) where it wont automatically default do getdate() but will check, and if needed, will default to the the greater of getdate() or the same sdate.

There is a potential problem. Multiple rows with the same sdate will pick up the same edate.

Any way, have a look and let me know.

Cheers,
Mark Wills
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Hello ? No pressure, just want to see if there is more we can do for you...

And keen to get your feedback from the above :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial