Using variable date as column heading

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)

Using variable date as column heading

Author

Topic

henryparsons10
Starting Member

23 Posts

Posted - 2008-12-09 : 14:45:27

I need help assigning a date as the column headings. Im not even sure it can be done.

Here is what I have:

declare @customercode nvarchar(3)
declare @fromdate datetime
declare @todate datetime
set @customercode = '200'
set @fromdate = '11/23/08'
set @todate = @fromdate + 6

Select
AP.Name Customer,
AP.Route,
min(Temp_Pay.Template_Pay) As Template_Commission,
min(AP.Day1_Pay) As Day1_Cour_Comm,
min(AP.Day2_Pay) As Day2_Cour_Comm,
min(AP.Day3_Pay) As Day3_Cour_Comm,
min(AP.Day4_Pay) As Day4_Cour_Comm,
min(AP.Day5_Pay) As Day5_Cour_Comm,
min(AP.Day6_Pay) As Day6_Cour_Comm,
min(AP.Day7_Pay) As Day7_Cour_Comm

From
(Select Customer.Name, Case When job.routenumber IS NULL Then ordermain.Auth Else job.routenumber End As Route,
Case when datepart(dw,ordermain.orderdate)=1 Then sum(isnull(Job.DriverPay1,0)+IsNull(Job.DriverPay2,0)) End As Day1_Pay,
Case when datepart(dw,ordermain.orderdate)=2 Then sum(isnull(Job.DriverPay1,0)+IsNull(Job.DriverPay2,0)) End As Day2_Pay,
Case when datepart(dw,ordermain.orderdate)=3 Then sum(isnull(Job.DriverPay1,0)+IsNull(Job.DriverPay2,0)) End As Day3_Pay,
Case when datepart(dw,ordermain.orderdate)=4 Then sum(isnull(Job.DriverPay1,0)+IsNull(Job.DriverPay2,0)) End As Day4_Pay,
Case when datepart(dw,ordermain.orderdate)=5 Then sum(isnull(Job.DriverPay1,0)+IsNull(Job.DriverPay2,0)) End As Day5_Pay,
Case when datepart(dw,ordermain.orderdate)=6 Then sum(isnull(Job.DriverPay1,0)+IsNull(Job.DriverPay2,0)) End As Day6_Pay,
Case when datepart(dw,ordermain.orderdate)=7 Then sum(isnull(Job.DriverPay1,0)+IsNull(Job.DriverPay2,0)) End As Day7_Pay
From
Ordermain with (readuncommitted, INDEX=OrderDateOrderID)
Left Outer Join Customer with (readuncommitted, INDEX=PK_Customer) on Ordermain.CustomerID=Customer.CustomerID
Left Outer Join Job with (readuncommitted, INDEX=OrderID) on Ordermain.OrderID=Job.OrderID
Where
Ordermain.OrderStatus IN ('N', 'A', 'I', 'P') and
Ordermain.OrderDate BETWEEN @fromdate AND @todate and
Customer.CustomerCode=@customercode and
left(Job.Service,3) IN ('Rou')
Group By
Ordermain.Orderdate,
Customer.Name,
Case When job.routenumber IS NULL Then ordermain.Auth Else job.routenumber End) As AP
Left Outer Join
(Select Case When Job.Routenumber IS NULL Then Ordermain.Auth Else Job.Routenumber End As Route, Case When JobStop.Sequence= '1' then Job.DriverPay1 End As Template_Pay From Ordermain with (readuncommitted) 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 with (readuncommitted,index=JobIDSequence) on Job.JobID=Jobstop.JobID Where Ordermain.OrderStatus='T' and Ordermain.Templatetype<2 and Left(Ordermain.Callbackname,8)<>'00000000' and (ordermain.callbackdatetime IS NULL OR ordermain.callbackdatetime>GetDate()) and Left(Job.Service,1) IN ('R') and
customer.customercode=@customercode) As Temp_Pay On AP.Route=Temp_Pay.Route

Group By
AP.Name,
AP.Route

Order By
AP.Name,
AP.Route

...okay. Instead of having my column headings be Day1, Day2, etc...can I have them be the actual date?

Using MySQL 2005...

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources