problem with a grouping - 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

SQL Server 2005 Forums

Transact-SQL (2005)

problem with a grouping

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-06-08 : 13:08:13

Morning,

I'm having a time trying to get this to sum up. I have a cost and a week. I want it to look at the data for each week, and take the cost of just that week. Unfortunately the only way to get weekly data is join the job table to the time table. Like so:


SELECT DATEPART(wk,t.Work_Date) AS 'Week #', 
(J.Act_Labor+J.Act_Labor_Burden+J.Act_Material)Cost
FROM Time t
	INNER JOIN Operation  o
		ON t.Job_Operation = o.Job_Operation
	INNER JOIN Job j
		ON o.Job = j.Job
WHERE J.Type <> 'blanket'
and J.Customer_PO = '2345689'
and DATEPART(wk,t.Work_Date)= 2
order by t.Work_Date desc

This is what I get for data:


2	450.6858
2	19552.42
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	16626.00
2	16626.00
2	390.7857
2	538.8133
2	15081.15
2	15081.15
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	1178.6611
2	1178.6611
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	223583.95
2	538.8133
2	16626.00
2	19552.42
2	390.7857
2	691.11
2	1115.22
2	15081.15
2	0.00
2	223583.95
2	5815.77
2	223583.95
2	223583.95
2	223583.95

So what I would like to see is this:


2	0
2	390.7857
2	450.6858
2	538.8133
2	691.11
2	1115.22
2	1178.6611
2	5815.77
2	15081.15
2	16626
2	19552.42
2	223583.95

I've tried grouping, rollup, distinct and group by with no success. Eventually I need it to take the last set of data and sum it by week.

Any ideas would be appreciated.

Thanks

Laura

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-06-08 : 17:24:08

Can you provide the DDL for those tables and some sample data?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-08 : 18:41:34

Just a WAG:

SELECT DISTINCT
[Week #],
Cost
FROM
(
	SELECT DATEPART(wk,t.Work_Date) AS 'Week #', 
	(J.Act_Labor+J.Act_Labor_Burden+J.Act_Material)Cost
	FROM Time t
		INNER JOIN Operation  o
			ON t.Job_Operation = o.Job_Operation
		INNER JOIN Job j
			ON o.Job = j.Job
	WHERE J.Type <> 'blanket'
	and J.Customer_PO = '2345689'
	and DATEPART(wk,t.Work_Date)= 2
) AS T
ORDER BY [Week #] DESC, Cost

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-06-09 : 08:00:53

That did it Lamprey. Thanks so much.

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources