Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Challenge
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 05/10/2013 :  21:22:00  Show Profile  Reply with Quote
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.

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

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

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.

Aged Yak Warrior

549 Posts

Posted - 05/11/2013 :  12:47:26  Show Profile  Reply with Quote
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...

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 = dd1.dmn, dd2 where <= Month(getdate()) order by RN;

Go to Top of Page

Starting Member

2 Posts

Posted - 05/13/2013 :  20:17:11  Show Profile  Reply with Quote
Thanks MuMu88,
That's excellent.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000