SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

DuncanE
Starting Member

Australia
2 Posts

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

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


Go to Top of Page

DuncanE
Starting Member

Australia
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  
 New 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.08 seconds. Powered By: Snitz Forums 2000