Calculating column values - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Calculating column values

Author

Topic

alberto_alvarado
Starting Member

3 Posts

Posted - 2007-09-19 : 09:02:05

Hi, this is my problem. The query listed below gets a list of employees asigned to a project, to a customer and to a business unit (the Dimension field). The last two fields are the start date and the end date.

select
P.DIMENSION, P.CUSTACCOUNT, C.NAME as CUSTOMERNAME, PE.PROJID, P.NAME as PROJNAME, PE.EMPLID, E.NAME as EMPLNAME,
PE.FECHAASIGNACION, PE.FECHADESASIGNACION
from
PROJVALEMPLPROJSETUP PE, EMPLTABLE E, PROJTABLE P, CUSTTABLE C
where
PE.PROJID = P.PROJID and PE.EMPLID = E.EMPLID and P.CUSTACCOUNT = C.ACCOUNTNUM and (PE.FECHAASIGNACION >= '01/01/2007'
and PE.FECHADESASIGNACION >= '01/01/2007')
order by
P.DIMENSION, P.CUSTACCOUNT, PE.PROJID, PE.EMPLID

I need to get a listing showing how many employees started or ended their projects every months in the following format:

Business UNIT CLIENT PROJECT STARTING TOTAL JANUARY FEBRUARY .. TOTAL VARIATION
--------------------------------------------------------------------------------------------------------
1 1 1 25 3 -2 26 1

Starting total refers to all the employees that have a starting date before 1/1/2007 and a ending date after 1/1/2007

The monthly columns would be the total of employees with a starting date (+1) or ending date (-1) in that month.

The total column is the starting total plus the monthly totals.

And the variation is the difference of the total with the starting total.

Any ideas?

Thanks a lot.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 09:08:48

Something similar to


SELECT		A,
		B,
		C,
		SUM(CASE WHEN ProjStart >= '20070101' AND ProjStart < '20070201' OR ProjEnd >= '20070101' AND ProjEnd < '20070201' THEN 1 ELSE 0 END) AS Jan07,
		SUM(CASE WHEN ProjStart >= '20070201' AND ProjStart < '20070301' OR ProjEnd >= '20070201' AND ProjEnd < '20070301' THEN 1 ELSE 0 END) AS Feb07,
		SUM(CASE WHEN ProjStart >= '20070301' AND ProjStart < '20070401' OR ProjEnd >= '20070301' AND ProjEnd < '20070401' THEN 1 ELSE 0 END) AS Mar07,
		SUM(CASE WHEN ProjStart >= '20070401' AND ProjStart < '20070501' OR ProjEnd >= '20070401' AND ProjEnd < '20070501' THEN 1 ELSE 0 END) AS Apr07,
		SUM(CASE WHEN ProjStart >= '20070501' AND ProjStart < '20070601' OR ProjEnd >= '20070501' AND ProjEnd < '20070601' THEN 1 ELSE 0 END) AS May07,
		SUM(CASE WHEN ProjStart >= '20070601' AND ProjStart < '20070701' OR ProjEnd >= '20070601' AND ProjEnd < '20070701' THEN 1 ELSE 0 END) AS Jun07,
		SUM(CASE WHEN ProjStart >= '20070701' AND ProjStart < '20070801' OR ProjEnd >= '20070701' AND ProjEnd < '20070801' THEN 1 ELSE 0 END) AS Jul07,
		SUM(CASE WHEN ProjStart >= '20070801' AND ProjStart < '20070901' OR ProjEnd >= '20070801' AND ProjEnd < '20070901' THEN 1 ELSE 0 END) AS Aug07,
		SUM(CASE WHEN ProjStart >= '20070901' AND ProjStart < '20071001' OR ProjEnd >= '20070901' AND ProjEnd < '20071001' THEN 1 ELSE 0 END) AS Sep07,
		SUM(CASE WHEN ProjStart >= '20071001' AND ProjStart < '20071101' OR ProjEnd >= '20071001' AND ProjEnd < '20071101' THEN 1 ELSE 0 END) AS Oct07,
		SUM(CASE WHEN ProjStart >= '20071101' AND ProjStart < '20071201' OR ProjEnd >= '20071101' AND ProjEnd < '20071201' THEN 1 ELSE 0 END) AS Nov07,
		SUM(CASE WHEN ProjStart >= '20071201' AND ProjStart < '20080101' OR ProjEnd >= '20071201' AND ProjEnd < '20080101' THEN 1 ELSE 0 END) AS Dec07,
		COUNT(*) AS Total07
FROM		Table1
WHERE		ProjStart >= '20070101' AND ProjStart < '20080101'
		OR ProjEnd >= '20070101' AND ProjStart < '20080101'
GROUP BY	A,
		B,
		C
ORDER BY	A,
		B,
		C

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources