Sending out an SOS - 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)

Sending out an SOS

Author

Topic

gualm
Starting Member

11 Posts

Posted - 2008-04-01 : 15:00:46

Thanks in advance...
What I have to do is take orders and generate a report that gives the daily totals of the orders The dates will be on the vertical and the subclass titles on the horizontal: Output

date SO PS Total
1/1/08 5 4 9
1/2/08 3 2 5
1/3/08 5 4 9

mtd 13 10 23
ytd 13 10 23

The SO orders for example can have a diff status each day and I will
need to count this .
1 - how would I pull the orders that I want. I have pulled all credit work orders in but I only want the ones from 2008. Then once I pull them in how do I get the daily info., ie what records fell into the categories for that day. Do I need a loop, cursor, case statement? I also need to count the # of any give group as above. I am new at this and really do not know where to proceed.

see table below
CREATE TABLE #CREDIT_WO (
WO_No int,
WO_Status varchar (1),
WO_Class varchar (30),
WO_Subclass varchar (30),
WO_Create_Date datetime,
WO_Sch_Date datetime,
WO_Post_Date datetime,
WO_State varchar (2)
)
INSERT INTO #CREDIT_WO
(WO_NO, WO_Status, WO_Subclass, WO_Create_Date, WO_Sch_Date, WO_Post_Date, WO_State)
SELECT wowo_wono, wowo_status, wtyp_wo_class, wowo_wo_date, wowo_sch_date, wowo_post_date, wowo_state
FROM WO_WO with(nolock)
inner join wo_type with (nolock)
on wowo_wo_type = wtyp_wo_type
WHERE wo_class = 'Credit'
and subclass in ('so', 'ps')
and wowo_wo_date between @Start_date and @End_date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 15:43:55

Is this the same as this question (for which you were given an answer but didn't bother to reply)?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99800

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-02 : 13:05:07

Thank you for the wonderful email I got from you

quote:
Email from Gualm
If you do not know the answer. You do not need to reply.

You have to change your attitude about follow-up questions.

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

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-02 : 13:16:06

SELECT YEAR([date]), MONTH([date]), SUM(SO), SUM(PS), SUM(Total)
FROM TABLE
GROUP BY YEAR([date]), MONTH([date])
WITH ROLLUP

??????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 13:26:05

[code]SELECT wowo_wo_date,
COUNT(CASE WHEN subclass='so' THEN WO_No ELSE NULL END) AS SO,
COUNT(CASE WHEN subclass='ps' THEN WO_No ELSE NULL END) AS PS,
COUNT(WO_No) AS Total
FROM WO_WO with(nolock)
inner join wo_type with (nolock)
on wowo_wo_type = wtyp_wo_type
WHERE wo_class = 'Credit'
and subclass in ('so', 'ps')
GROUP BY wowo_wo_date[/code]

for daily count. For mtd and ytd take COUNT of each for the particular month/year for which you're running the report

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources