Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
DuncanE
Starting Member
2 Posts |
Posted - 2013-05-10 : 21:22:00
|
Hi, I'm a bit of a beginner at SQL, but would like to ask if the following query is possible.I have a table with the following records.empID____posID____posStartDate_____posEndDate_____trnDate------ ----- ------------- ----------- ---------021______P21______1-JAN-2013________1-MAR-2013______1-FEB-2013021______P22______1-JAN-2013________1-MAR-2013______5-MAR-2013021______P23______1-JAN-2013________1-MAR-2013______nullThe table holds position allocation details for an employee.So we have the EmployeeID, PositionID, Start & End dates for the position allocation, and the date the Training for the position was completed.I have a requirement to report on monthly stats for the employee.The query result I would like to achieve is:empID___Date (Month)___No of Positions Allocated______Completed----------------------------------------------------------------021_______JAN-2013_________3___________________________0021_______FEB-2013_________3___________________________1021_______MAR-2013_________3___________________________2Is this possible, or do I have to create a separate table for the statistics.Thank you to anyone who can help me with this. |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-11 : 12:47:26
|
You can start with the following query and tweak it to get exactly what you want, some of your requirements are not clear, for example what is 'No of Positions Allocated', how are you counting 'Completed' etc...[CODE]declare @t1 table (empID varchar(10), posID varchar(10), posStartDate date, posEndDate date, trnDate date)insert into @t1 values ('021', 'P21', '1-JAN-2013', '1-MAR-2013', '1-FEB-2013') insert into @t1 values ('021', 'P22', '1-JAN-2013', '1-MAR-2013', '5-MAR-2013')insert into @t1 values ('021', 'P23', '1-JAN-2013', '1-MAR-2013', '15-MAR-2013'); With CTE (mmonth, mn, yyear) as (SELECT DateName(mm, DATEADD(mm, number, '20130101')) as mmonth, month(DATEADD(mm, number, '20130101')) as mn, '2013' as yyear from master..spt_values where type = 'p' and number between 0 and 11),dd1 as (SELECT empID, month(trndate) as dmn, count(month(trndate)) OVER(partition by empID order by month(trndate)) as ccount from @t1 group by empID, posEndDate, month(trndate) ),dd2 as (SELECT month(posEndDate) as amn, count(month(posEndDate)) OVER(partition by empID order by empID) as Allocated from @T1)SELECT DISTINCT mn as RN, COALESCE(empID, '021') as empID, mmonth+'-'+yyear as Month_Date, Allocated, COALESCE(ccount, 0) as Completed from CTE LEFT JOIN dd1 on CTE.mn = dd1.dmn, dd2 where CTE.mn <= Month(getdate()) order by RN;[/CODE] |
|
|
DuncanE
Starting Member
2 Posts |
Posted - 2013-05-13 : 20:17:11
|
Thanks MuMu88,That's excellent. |
|
|
|
|
|
|
|