question

Hugh209 avatar image
Hugh209 asked

Need help with a SQL query

HI, I have the query below and it works fine for most all situations, The issue is I need to make the query stop calculating on the record that has the 'T' as the empstatus. I know I need to add it to the where statement first. But what to do next. (See EXAMPLE 1) it should only do the datediff with these two rows insert into @YourTable select 19540, '23 Aug 2009', 'A', 1.00 insert into @YourTable select 19540, '23 Sep 2009', 'A', 1.00 Example two should calculate the datediff these rows --insert into @YourTable select 19540, '3 may 1993', 'A', 1.00 --insert into @YourTable select 19540, '20 Aug 2000', 'T', .60 In addition to these --insert into @YourTable select 19540, '23 Aug 2001', 'A', 1.00 --insert into @YourTable select 19540, '23 Sep 2009', 'A', 1.00 Any help would be greatly appreciated ! --declare @YourTable table -- (EmployeeID int, StatusDate date, EmpStatus char(1), Mutipler decimal(8,2)) EXAMPLE 1 --insert into @YourTable select 19540, '3 may 1993', 'A', 1.00 --insert into @YourTable select 19540, '20 Aug 2000', 'T', .60 --insert into @YourTable select 19540, '23 Aug 2009', 'A', 1.00 --insert into @YourTable select 19540, '23 Sep 2009', 'A', 1.00 EXAMPLE 2 --insert into @YourTable select 19540, '3 may 1993', 'A', 1.00 --insert into @YourTable select 19540, '20 Aug 2000', 'T', .60 --insert into @YourTable select 19540, '23 Aug 2001', 'A', 1.00 --insert into @YourTable select 19540, '23 Sep 2009', 'A', 1.00 declare @StaticDate Date set @StaticDate = '05/31/2012' Drop Table #UH_TEMP select T2.Empno, Days1 = SUM(case when T3.fileDate is null then datediff(dd, T2.fileDate, @StaticDate) * t2.fte else datediff(dd, T2.fileDate, T3.fileDate) * t2.fte end),Years1 = SUM(case when T3.fileDate is null then datediff(dd, T2.fileDate, @StaticDate) * t2.fte else datediff(dd, T2.fileDate, T3.fileDate) * t2.fte end)/365.25, Days2 =SUM(case when T3.fileDate is null then datediff(dd, T2.fileDate, @StaticDate) else datediff(dd, T2.fileDate, T3.fileDate) end),years2 = SUM(case when T3.fileDate is null then datediff(dd, T2.fileDate, @StaticDate) else datediff(dd, T2.fileDate, T3.fileDate) end)/365.25, Days3 = SUM(case when T3.fileDate is null and t2.FTE >= .90 then datediff(dd, T2.fileDate, @StaticDate) when T3.fileDate is not null and t2.FTE >= .90 then datediff(dd, T2.fileDate, T3.fileDate) else 0 end),years3 = SUM(case when T3.fileDate is null and t2.FTE >= .90 then datediff(dd, T2.fileDate, @StaticDate) when T3.fileDate is not null and t2.FTE >= .90 then datediff(dd, T2.fileDate, T3.fileDate) else 0 end)/365.25, Days4 = SUM(case when T3.fileDate is null and t2.FTE < .90 then datediff(dd, T2.fileDate, @StaticDate) when T3.fileDate is not null and t2.FTE < .90 then datediff(dd, T2.fileDate, T3.fileDate) else 0 end),years4 = SUM(case when T3.fileDate is null and t2.FTE < .90 then datediff(dd, T2.fileDate, @StaticDate) when T3.fileDate is not null and t2.FTE < .90 then datediff(dd, T2.fileDate, T3.fileDate) else 0 end)/365.25 Into #UH_TEMP from ( select Empno, FileDate, EmpStatus, FTE, row_number()over(partition by Empno order by fileDate desc) as rn from UH_TERM_CARD T1 where EmpStatus in ('A','L','C') and FTE > 0 ) T2 left join ( select Empno, FileDate, EmpStatus, FTE, row_number()over(partition by EmpNo order by fileDate desc) as rn from UH_TERM_CARD T1 where EmpStatus in ('A','L','C') and FTE > 0 ) T3 on T2.Empno = T3.Empno and T2.rn = T3.rn +1 group by T2.empno
sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Thanks for all the info, although it would be good if you could check that any example data and query is good to go - you seem to have setup a set of data into a temp table (@YourTable) but then referenced different columns and a different table in the query. It makes trying to understand your goal a little hard. Maybe if you gave us what the output should look like, it might make the question a little clearer.
1 Like 1 ·

1 Answer

·
Hugh209 avatar image
Hugh209 answered
I am trying to calculate service time for employees. What I have is these rows for this employee and here is how I need SQL to work. Kev you helped write the query above - Might need a cursor - I do not know. THANKS Empno FileDate EmpStatus FTE ConvFTE 05618 1972-03-30 00:00:00.000 A 0.50 0.50 05618 1972-06-12 00:00:00.000 A 1.00 1.00 05618 1973-11-21 00:00:00.000 T 1.00 1.00 05618 1975-01-06 00:00:00.000 A 0.50 0.50 05618 1975-06-09 00:00:00.000 A 1.00 1.00 The employee's original hire dtae is 3/30/1972. Starting with a constant date of '12/31/2012' I need to get the number of years this employee has worked. So it would be the date difference between '12/31/2012' and then '06/09/1975 multiplied by the FTE column. The next iteration would be the difference between '06/09/1975' and '01/06/1975' multiplied by .50. Here is the problem - The employee termed on 11/21/1973 signified by the 'T' in the empstatus column. I need the query to calculate the difference between the '11/21/1973' date abd the original hire date of '3/30/1972' If the difference is less than two years the caculation should stop if it is greater than 2 years and the difference between the 'T' record and the precending row is less than one year keep on adding up service. I hope I am not confusing anyone with this. So in the example above the years of service should be 37.78 below is an example of rows where it should keep on calculating. Empno FileDate EmpStatus FTE ConvFTE 30340 1993-12-13 00:00:00.000 A 1.00 1.00 30340 1998-09-20 00:00:00.000 A 0.30 0.30 30340 1999-04-04 00:00:00.000 A 1.00 0.90 30340 2001-01-07 00:00:00.000 A 0.30 0.30 30340 2001-05-06 00:00:00.000 L 0.30 0.30 30340 2001-07-28 00:00:00.000 T 0.30 0.30 30340 2002-01-21 00:00:00.000 A 0.30 0.30 30340 2004-08-10 00:00:00.000 T 0.30 0.30 30340 2004-10-31 00:00:00.000 A 0.80 0.80 30340 2005-03-27 00:00:00.000 A 1.00 0.90
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.