Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

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]*[SumOfCOSTPERGAL]) 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
0
campbme
Asked:
campbme
1 Solution
 
SimonCommented:
I don't have Access on this machine, but this is what I think you need.
PARAMETERS [start date] DateTime, [end date] DateTime;

TRANSFORM Sum(TotalCost) AS Cost
SELECT ID
  FROM (SELECT month(wo.eventdate) as TheMonth, WO.ID as ID, Sum(WO.GALLONSGAS) AS SumOfGALLONSGAS, Sum(WO.COSTPERGAL) AS SumOfCOSTPERGAL, ([SumOfGALLONSGAS]*[SumOfCOSTPERGAL]) 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)
GROUP BY ID
PIVOT theMonth

Open in new window


If you have any problems with this, please post some sample data from the two tables.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now