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
 Help for a noob - 'how-to' structure a query

Author  Topic 

Windza
Yak Posting Veteran

61 Posts
Posted - 2008-09-16 : 00:26:20
Hi all - I'm well aware that this is possibly a typical noob question but I'm pretty much left with no option but to request some assistance (I hadn't heard of SQL until 2 months ago if that gives any indication )
Basically I'm attempting to assemble data for a monthly maintenance report (to show number of work orders created per week for four different departments)... I'm aiming for a format similar to this:

|_WeekofYr_|_Coal_|_Dirt_|_Track_|
|_25_______|_16__|_71__|_22____|
|_26_______|_18__|_82__|_25____|
|_27_______|_10__|_62__|_27____|
|_28_______|_08__|_69__|_21____|
etc...

All the data is in a workorder table that is a detailed listing of every work order created. I need a single query that will count the workorders created in each week of a particular month against the four departments...

Currently I'm attempting to use nested select clauses and can get count totals for every wo created against a department but it's not broken down into the per week basis like I would like...
Here's what I have so far (no laughing allowed!)

SELECT
(SELECT COUNT(dbo.WorkOrder.WorkOrderID)FROM dbo.WorkOrder WHERE dbo.WorkOrder.DepartmentID = '2') AS ChartValueTT,
(SELECT COUNT(dbo.WorkOrder.WorkOrderID)FROM dbo.WorkOrder WHERE dbo.WorkOrder.DepartmentID = '3') AS ChartValueCT,
(SELECT COUNT(dbo.WorkOrder.WorkOrderID)FROM dbo.WorkOrder WHERE dbo.WorkOrder.DepartmentID = '4') AS ChartValueDT,
(SELECT COUNT(dbo.WorkOrder.WorkOrderID)FROM dbo.WorkOrder WHERE dbo.WorkOrder.DepartmentID = '17') AS ChartValueAE

Results:
|_CVTT_|_CVCT_|_CVDT_|_CVAE_|
|_277___|_51___|_195___|_53___|

Can anyone direct me on what I need to add (and where) to break these figures into a 'per week' basis... I've tried various things but I can't seem to get what I need...
Any help would be appreciated.
Regards -


visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-16 : 00:30:47
just use
SELECT DATEPART(wk,yourdatecolumn),DeptID,COUNT(dbo.WorkOrder.WorkOrderID)
FROM dbo.WorkOrder 
GROUP BY DATEPART(wk,yourdatecolumn),DeptID
Go to Top of Page

Windza
Yak Posting Veteran

61 Posts
Posted - 2008-09-16 : 00:57:51
Apologies - I made a hash of the first format...
It should look like this...(updated in first post)

|_WeekofYr_|_Coal_|_Dirt_|_Track_|
|_25_______|_16__|_71__|_22____|
|_26_______|_18__|_82__|_25____|
|_27_______|_10__|_62__|_27____|
|_28_______|_08__|_69__|_21____|
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-16 : 01:04:58
may be this

SELECT DATEPART(wk,yourdatecolumn),
COUNT(CASE WHEN DeptID=2 THEN dbo.WorkOrder.WorkOrderID ELSE NULL END) AS First,
COUNT(CASE WHEN DeptID=3 THEN dbo.WorkOrder.WorkOrderID ELSE NULL END) AS Second,
COUNT(CASE WHEN DeptID=4 THEN dbo.WorkOrder.WorkOrderID ELSE NULL END) AS Third,
COUNT(CASE WHEN DeptID=17 THEN dbo.WorkOrder.WorkOrderID ELSE NULL END) AS Fourth
FROM dbo.WorkOrder 
GROUP BY DATEPART(wk,yourdatecolumn)
Go to Top of Page

Windza
Yak Posting Veteran

61 Posts
Posted - 2008-09-17 : 03:29:33
Visakh16... immense thanks for that, it was exactly what I needed.

Just in addition to this, is it possible to add a field with counts the number of work orders that were completed for each week within the same statement?

The 'completed' status needs to come from the number of work orders that have a FinishedDateTime value that falls within each week... if that makes any sense...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-17 : 03:51:17
quote:
Originally posted by Windza

Visakh16... immense thanks for that, it was exactly what I needed.

Just in addition to this, is it possible to add a field with counts the number of work orders that were completed for each week within the same statement?

The 'completed' status needs to come from the number of work orders that have a FinishedDateTime value that falls within each week... if that makes any sense...

is FinishedDateTime field existing in the same table as others?
Go to Top of Page

Windza
Yak Posting Veteran

61 Posts
Posted - 2008-09-17 : 21:50:58
quote:
is FinishedDateTime field existing in the same table as others?


Sorry - yes it is... all info is coming from workorders
Go to Top of Page

Windza
Yak Posting Veteran

61 Posts
Posted - 2008-09-17 : 22:35:49
Well I came up with something... I will check it thoroughly when I get a chance - but does this look like it'd be correct...

SELECT DATEPART(wk,RaisedDateTime) AS WeekOfYear,
COUNT(CASE WHEN DepartmentID=2 THEN WorkOrderID ELSE NULL END) AS TrackTeam,
COUNT(CASE WHEN DepartmentID=3 THEN WorkOrderID ELSE NULL END) AS CoalTeam,
COUNT(CASE WHEN DepartmentID=4 THEN .WorkOrderID ELSE NULL END) AS DirtTeam,
COUNT(CASE WHEN DepartmentID=17 THEN WorkOrderID ELSE NULL END) AS AElec,
*This Line*--->COUNT(CASE WHEN DepartmentID=2 AND DATEPART(wk, RaisedDateTime)=DATEPART(wk,FinishedDateTime) THEN WorkOrderID ELSE NULL END) AS TrackTeamFWO
FROM WorkOrder
WHERE CONVERT(datetime, RaisedDateTime, 103) BETWEEN CONVERT(datetime, GETDATE() -30, 103) AND CONVERT(datetime, GETDATE(), 103)
GROUP BY DATEPART(wk, RaisedDateTime)

Obviously I'm only counting the one department ATM... ignore the seemingly unnecessary CONVERT's... I'm not normally using a GETDATE()value.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-17 : 23:57:34
quote:
Originally posted by Windza

Well I came up with something... I will check it thoroughly when I get a chance - but does this look like it'd be correct...

SELECT DATEPART(wk,RaisedDateTime) AS WeekOfYear,
COUNT(CASE WHEN DepartmentID=2 THEN WorkOrderID ELSE NULL END) AS TrackTeam,
COUNT(CASE WHEN DepartmentID=3 THEN WorkOrderID ELSE NULL END) AS CoalTeam,
COUNT(CASE WHEN DepartmentID=4 THEN .WorkOrderID ELSE NULL END) AS DirtTeam,
COUNT(CASE WHEN DepartmentID=17 THEN WorkOrderID ELSE NULL END) AS AElec,
*This Line*--->COUNT(CASE WHEN DepartmentID=2 AND DATEPART(wk, RaisedDateTime)=DATEPART(wk,FinishedDateTime) THEN WorkOrderID ELSE NULL END) AS TrackTeamFWO
FROM WorkOrder
WHERE CONVERT(datetime, RaisedDateTime, 103) BETWEEN CONVERT(datetime, GETDATE() -30, 103) AND CONVERT(datetime, GETDATE(), 103)
GROUP BY DATEPART(wk, RaisedDateTime)

Obviously I'm only counting the one department ATM... ignore the seemingly unnecessary CONVERT's... I'm not normally using a GETDATE()value.

you should also be checking year inside CASE WHEN
DATEPART(yy, RaisedDateTime)=DATEPART(yy,FinishedDateTime)
as there can records with same week number value but for different years.
Go to Top of Page

Windza
Yak Posting Veteran

61 Posts
Posted - 2008-09-18 : 01:24:36
quote:
as there can records with same week number value but for different years


Thanks for pointing that out - may have saved some very confusing monthly reports next year (we've only been entering data since May)...

Your assistance has been most appreciated,
Cheers...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-18 : 01:46:37
quote:
Originally posted by Windza

quote:
as there can records with same week number value but for different years


Thanks for pointing that out - may have saved some very confusing monthly reports next year (we've only been entering data since May)...

Your assistance has been most appreciated,
Cheers...

welcome
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site