Tryin to get daily amounts charged

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 2000 Forums

SQL Server Development (2000)

Tryin to get daily amounts charged

Author

Topic

henryparsons10
Starting Member

23 Posts

Posted - 2008-07-09 : 11:20:27

This is my issue. I want the return set to show days across the top. Day 1, Day 2, Day 3, etc....

Then, I want the route names to be in the column on the left hand side.

Then I want to see the daily route charges across the rows for each day in my dater range.

My problem is that I cannot group by amount charged or orderdate because it screws up my formatting. How can I get around this issue?

Any help would be great.

Here is my query.

Select
Customer.Name,
Case When job.routenumber IS NULL Then ISNULL(ordermain.Auth,'None') else job.routenumber End As Route,
Case When datepart(dd,ordermain.orderdate)=1 Then Ordermain.AmountCharged END AS Day_1,
Case When datepart(dd,ordermain.orderdate)=2 Then Ordermain.AmountCharged END AS Day_2,
Case When datepart(dd,ordermain.orderdate)=3 Then Ordermain.AmountCharged END AS Day_3,
Case When datepart(dd,ordermain.orderdate)=4 Then Ordermain.AmountCharged END AS Day_4,
Case When datepart(dd,ordermain.orderdate)=5 Then Ordermain.AmountCharged END AS Day_5,
Case When datepart(dd,ordermain.orderdate)=6 Then Ordermain.AmountCharged END AS Day_6,
Case When datepart(dd,ordermain.orderdate)=7 Then Ordermain.AmountCharged END AS Day_7,
Case When datepart(dd,ordermain.orderdate)=8 Then Ordermain.AmountCharged END AS Day_8,
Case When datepart(dd,ordermain.orderdate)=9 Then Ordermain.AmountCharged END AS Day_9,
Case When datepart(dd,ordermain.orderdate)=10 Then Ordermain.AmountCharged END AS Day_10,
Case When datepart(dd,ordermain.orderdate)=11 Then Ordermain.AmountCharged END AS Day_11,
Case When datepart(dd,ordermain.orderdate)=12 Then Ordermain.AmountCharged END AS Day_12,
Case When datepart(dd,ordermain.orderdate)=13 Then Ordermain.AmountCharged END AS Day_13,
Case When datepart(dd,ordermain.orderdate)=14 Then Ordermain.AmountCharged END AS Day_14,
Case When datepart(dd,ordermain.orderdate)=15 Then Ordermain.AmountCharged END AS Day_15,
Case When datepart(dd,ordermain.orderdate)=16 Then Ordermain.AmountCharged END AS Day_16,
Case When datepart(dd,ordermain.orderdate)=17 Then Ordermain.AmountCharged END AS Day_17,
Case When datepart(dd,ordermain.orderdate)=18 Then Ordermain.AmountCharged END AS Day_18,
Case When datepart(dd,ordermain.orderdate)=19 Then Ordermain.AmountCharged END AS Day_19,
Case When datepart(dd,ordermain.orderdate)=20 Then Ordermain.AmountCharged END AS Day_20,
Case When datepart(dd,ordermain.orderdate)=21 Then Ordermain.AmountCharged END AS Day_21,
Case When datepart(dd,ordermain.orderdate)=22 Then Ordermain.AmountCharged END AS Day_22,
Case When datepart(dd,ordermain.orderdate)=23 Then Ordermain.AmountCharged END AS Day_23,
Case When datepart(dd,ordermain.orderdate)=24 Then Ordermain.AmountCharged END AS Day_24,
Case When datepart(dd,ordermain.orderdate)=25 Then Ordermain.AmountCharged END AS Day_25,
Case When datepart(dd,ordermain.orderdate)=26 Then Ordermain.AmountCharged END AS Day_26,
Case When datepart(dd,ordermain.orderdate)=27 Then Ordermain.AmountCharged END AS Day_27,
Case When datepart(dd,ordermain.orderdate)=28 Then Ordermain.AmountCharged END AS Day_28,
Case When datepart(dd,ordermain.orderdate)=29 Then Ordermain.AmountCharged END AS Day_29,
Case When datepart(dd,ordermain.orderdate)=30 Then Ordermain.AmountCharged END AS Day_30,
Case When datepart(dd,ordermain.orderdate)=31 Then Ordermain.AmountCharged END AS Day_31

From
Ordermain with (readuncommitted, INDEX=OrderDateOrderID)
Left Outer Join Customer with (readuncommitted) on Ordermain.CustomerID=Customer.CustomerID Left Outer Join Job with (readuncommitted) on Ordermain.OrderID=Job.OrderID
Left Outer Join JobStop JS1 with (readuncommitted,index=JobIDSequence) on Job.JobID=JS1.JobID
Left Outer Join Stop St1 with (readuncommitted,index=PK_Stop) on JS1.StopID=St1.StopID

Where
Ordermain.OrderStatus IN ('N', 'A', 'I', 'P') and
Ordermain.OrderDate BETWEEN '6/1/08' AND '6/30/08' and
Customer.CustomerCode IN ('297') and
left(Job.Service,3) IN ('Rou')

Group By
Customer.Name,
Case When job.routenumber IS NULL Then ISNULL(ordermain.Auth,'None') else job.routenumber End

Order By
Customer.Name,
Case When job.routenumber IS NULL Then ISNULL(ordermain.Auth,'None') else job.routenumber End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 01:30:50

You could probably explain what you're trying to get here with some table data so that its easier for us to understand.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-10 : 01:47:18

[code]sum(Case When datepart(dd,ordermain.orderdate)=1 Then Ordermain.AmountCharged END) AS Day_1,
sum(Case When datepart(dd,ordermain.orderdate)=2 Then Ordermain.AmountCharged END) AS Day_2,
sum(Case When datepart(dd,ordermain.orderdate)=3 Then Ordermain.AmountCharged END) AS Day_3, [/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources