How to Best Approach the Problem - Need Ideas

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

How to Best Approach the Problem - Need Ideas

Author

Topic

gualm
Starting Member

11 Posts

Posted - 2008-03-18 : 15:25:44

SELECT DISTINCT WO_NO, wo_type, wo_status (incomp,compl,open), wo_status_code, wo_create_date, wo_post_date

I need to do a stored proc in sql server 2005 where I have to count the total work orders based on their wo_type + wo_status, i.e. open, compltd, incomplete. For example I may have (wo_types) AB, AC, AD, orders that are in open status. I need to count the total AB_Type, AC_Type, AD_Type then the total of all 3 in another column (as Total Open). I must do this for all wo_status.

I also have to allow the user to be able to enter the date they want. I.e. if they would want to know on 3/17/08 how many orders were opened that day only. I know I have to use a parameter for this but how do i do that?

Should I do separate select statments for each status, multiple tables ?

Also for 'open' wo_status the wo_status_code can change from O to S but it is still considered 'open'. For this wo_status how can I get the most recent status_code for the open order, i.e., I want to count a particular 'open'work order's - most recent status_code in the work order count for a given wo_type -->> the order should only be counted once on any given date entered. So if the status changed from O then S the same at 1pm then 2pm, respectively, then only the 2pm status should be counted. Thanks in advance for your assistance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-19 : 05:16:09

You basically need group your data on the date and them apply SUM() function to get the values

Something like:-


SELECT DISTINCT 
COUNT(CASE WHEN wo_type='AB' AND wo_status ='open' THEN WO_NO ELSE NULL END) AS AB_Open,
COUNT(CASE WHEN wo_type='AB' AND wo_status ='incomp' THEN WO_NO ELSE NULL END) AS AB_incomplete,
...............,--similarly for other types
COUNT(WO_NO) AS 'Total'
FROM YourTable
WHERE wo_post_date = @Date OR @Date IS NULL
GROUP BY wo_post_date

Here @Date will be the parameter passed

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-19 : 06:55:56

post some sample input data and desired output,
as the above ll not give you the latest Status

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources