Query calculation help needed 2

[danwand]

Hi all,

I have the following query:

SELECT tblHSTemp.EmpID, tblHSTemp.pCovered, tblHSTemp.Type, Sum(tblHSTemp.aTime) AS SumOfaTime
FROM tblHSTemp
GROUP BY tblHSTemp.EmpID, tblHSTemp.pCovered, tblHSTemp.Type;

which when run produces the following output

EmpID pCovered Type SumOfaTime
11 2003/2004 TOFD 180
11 2003/2004 TDW 60
11 2003/2004 LTA 120
11 2003/2004 TIL 30
11 2004/2005 TOFD 240
11 2004/2005 TDW 120


-------------------------------------------------------

TOFD = Time off for dependants
TDW = Dependent Time Worked
LTA = Lieu Time Accumalated
TIL = Time in Lieu

Now what i need to do is perform calculations to get the following 2 results grouped by each pCovered:

Dependant Time Status: = TOFD - TDW
Lieu Time Status: = LTA - TIL

I need the it to produce the following results (or similar):

EmpID pCovered Type SumOfaTime dtStatus LTStatus
11 2003/2004 TOFD 180 120 0
11 2003/2004 TDW 60 120 0
11 2003/2004 LTA 120 0 90
11 2003/2004 TIL 30 0 90
11 2004/2005 TOFD 240 120 0
11 2004/2005 TDW 120 120 0

---------------------------------------------------

No sure about '0' values and whether they should be there, but hopefully you understand where i am going with this example.

Have been trying various different ways around this to no avail, any help would be gratefully appreciated

Dan

 

[Crowley16]

use a crosstab query...

--------------------
Procrastinate Now!

 

[danwand]

thanks for the reply, i do use crosstab queries in my application but i am unsure on how to structure this one, any ideas?

 

[Crowley16]

group on pCovered and type, i.e. pCovered as row headding and type as column headding.

then you need another query which takes this crosstab in and just subtracts the 2 different types as a result...

--------------------
Procrastinate Now!

 

[danwand]

Excellent work Crowley,

Thanks alot worked perfectly, just wish i'd posted sooner rather than spending 2 days trying to do these calculations using another table and lots of coding!!!!

Dan