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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Challenge

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-2013
021______P22______1-JAN-2013________1-MAR-2013______5-MAR-2013
021______P23______1-JAN-2013________1-MAR-2013______null


The 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___________________________0
021_______FEB-2013_________3___________________________1
021_______MAR-2013_________3___________________________2

Is 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]
Go to Top of Page

DuncanE
Starting Member

2 Posts

Posted - 2013-05-13 : 20:17:11
Thanks MuMu88,
That's excellent.
Go to Top of Page
   

- Advertisement -