Perform SUM on COUNT field or similar result

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

Perform SUM on COUNT field or similar result

Author

Topic

Windza
Yak Posting Veteran

61 Posts

Posted - 2008-09-23 : 03:28:05

Hi all... I'm trying to formulate a backlog work order report that requires a count of workorderscreated minus workordersfinished.

I have no prob's doing this for a current summary, but trying to look at the historic weekly backlog is a little more difficult... essentially what I need to do is perform a count of workorderscreated minus workordersfinished and have it sum for each week...

This is what I'd like to happen - but obviously I can't use the WeekBacklog field

SELECT
DATEPART(wk, workorderscreated),
COUNT(workorderscreated) AS OpenCount,
COUNT(workordersfinished) AS ClosedCount,
COUNT(workorderscreated)-COUNT(workordersfinished) AS WeekBacklog,
SUM(WeekBacklog) AS HistBacklog
FROM dbo.WorkOrder
GROUP BY
DATEPART(wk, workorderscreated)

Any suggestions for how I can achieve a (weekly)cumulative total of the WeekBacklog field ?
Cheers...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 03:35:06

so what you want is sum of all WeekBacklog values untill current week in the same year?

Windza
Yak Posting Veteran

61 Posts

Posted - 2008-09-23 : 03:54:14

This may help
where
hb = histbacklog (weekly summary of wb)
wb = weekbacklog ...

|-wk-|-oc-|-cc-|-wb-|-hb-|
|-24-|-42-|-40-|-02-|-02-|
|-25-|-64-|-57-|-07-|-09-|
|-26-|-44-|-41-|-03-|-12-|
|-27-|-50-|-38-|-12-|-24-|

etc...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 04:01:01

didnt get what you're telling. Can you explain in words please?

Windza
Yak Posting Veteran

61 Posts

Posted - 2008-09-23 : 04:07:54

quote:
Originally posted by visakh16

didnt get what you're telling. Can you explain in words please?

Sure - the HB field is the weekly running total (or sum) of the WeekBacklog field.

So if in week one there are 10 work orders opened and 2 closed, the WeekBacklog is 8 - HB will be 8 also.
In week two, 10 work orders are opened and 5 closed, WeekBacklog is 5 and HB should be 13 (8+5)... and so on...

Windza
Yak Posting Veteran

61 Posts

Posted - 2008-09-23 : 04:11:05

Or as in my second post...
Week 24 - 42 workorderscreated - 40 workordersclosed - 2 histbacklog - 2 weekbacklog
Week 25 - 64 " " - 57 " " - 7 " " - 9 " " etc...

Windza
Yak Posting Veteran

61 Posts

Posted - 2008-09-23 : 15:56:34

Well this is what I've come up with so far...

SELECT a.WeekOfYear,SUM(b.WeekTotals) AS HistBacklog
FROM
(
SELECT
DATEPART(wk, workorderscreated) AS WeekOfYear,
COUNT(workorderscreated)-COUNT(workordersfinished) AS 'WeekTotals'
FROM dbo.WorkOrder
GROUP BY DATEPART(wk, workorderscreated)
)AS a
INNER JOIN
(
SELECT
DATEPART(wk, workorderscreated) AS WeekOfYear,
COUNT(workorderscreated)-COUNT(workordersfinished) AS 'WeekTotals'
FROM dbo.WorkOrder
GROUP BY DATEPART(wk, workorderscreated)
)AS b
ON a.WeekOfYear >= b.WeekOfYear
GROUP BY a.WeekOfYear

Results are (portion only - not complete set):
wk HB
26 12
27 24
28 26
29 31
30 33
31 34
32 43
33 46
34 52
35 63
36 71
37 82
38 108
39 133

I'm aware of the need to include the year within my grouping clause (thanks visakh16).

If you spot any problems with what I'm doing here please let me know...
Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources