campbme
asked on
sql code for crosstab
PARAMETERS [start date] DateTime, [end date] DateTime;
SELECT WO.ID, Sum(WO.GALLONSGAS) AS SumOfGALLONSGAS, Sum(WO.COSTPERGAL) AS SumOfCOSTPERGAL, ([SumOfGALLONSGAS]*[SumOfC OSTPERGAL] ) AS TOTALCOST
FROM WO INNER JOIN VEHICLE ON WO.ID = VEHICLE.Id
WHERE (((WO.EVENTDATE) Between [Start Date] And [End Date]))
GROUP BY WO.ID
HAVING (((WO.ID) Not Like "GA*" And (WO.ID) Not Like "FT*" And (WO.ID) Not Like "BH*" And (WO.ID) Not Like "BD*" And (WO.ID) Not Like "BC*" And (WO.ID) Not Like "BL*" And (WO.ID) Not Like "GOLFCT*" And (WO.ID) Not Like "KUBOTA*" And (WO.ID) Not Like "PW*" And (WO.ID) Not Like "TR*" And (WO.ID) Not Like "WE*" And (WO.ID) Not Like "WG*" And (WO.ID) Not Like "EXMARK*"))
ORDER BY WO.ID;
I am trying to get the totalcost for August, Sept., and November of 2014. I want the sql code create sample below. What can I add to the code to produce sample below? The id are rows, the months are column headings, and totalcost is value for each month.
Id Aug Sept Nov
Id totalcost totalcost totalcost
Id
id
SELECT WO.ID, Sum(WO.GALLONSGAS) AS SumOfGALLONSGAS, Sum(WO.COSTPERGAL) AS SumOfCOSTPERGAL, ([SumOfGALLONSGAS]*[SumOfC
FROM WO INNER JOIN VEHICLE ON WO.ID = VEHICLE.Id
WHERE (((WO.EVENTDATE) Between [Start Date] And [End Date]))
GROUP BY WO.ID
HAVING (((WO.ID) Not Like "GA*" And (WO.ID) Not Like "FT*" And (WO.ID) Not Like "BH*" And (WO.ID) Not Like "BD*" And (WO.ID) Not Like "BC*" And (WO.ID) Not Like "BL*" And (WO.ID) Not Like "GOLFCT*" And (WO.ID) Not Like "KUBOTA*" And (WO.ID) Not Like "PW*" And (WO.ID) Not Like "TR*" And (WO.ID) Not Like "WE*" And (WO.ID) Not Like "WG*" And (WO.ID) Not Like "EXMARK*"))
ORDER BY WO.ID;
I am trying to get the totalcost for August, Sept., and November of 2014. I want the sql code create sample below. What can I add to the code to produce sample below? The id are rows, the months are column headings, and totalcost is value for each month.
Id Aug Sept Nov
Id totalcost totalcost totalcost
Id
id
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.