asked on

Date Before Problem

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

OMC2000

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 Wilson

ASKER

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 Wilson

ASKER

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.

Raja Jegan R

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 Wilson

ASKER

Hi Thanks for the reply. Tried the above comes back with null for all SDate and todays date for all End dates

Raja Jegan R

Hi Mark,

Can you show me some sample result set to understand better and modify accordingly..

Raja Jegan R

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 Wills

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 Wills

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 Wilson

ASKER

Apologies I seem to be constantly ill at present - I will give the above reply a go - will report back

Raja Jegan R

Take care of your health and please give a try on my comment as well..

Mark Wills

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 Wilson

ASKER

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 Wills

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 R

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..

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

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 :)