Solved

sql code for crosstab

Posted on 2014-12-02
1
103 Views
Last Modified: 2014-12-11
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
Comment
Question by:campbme
1 Comment
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40477462
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
A quick guide on how to use Group Policy to create a custom power plan and set it active on Windows 7.
This Micro Tutorial will teach you how to the overview of Microsoft Security Essentials. This is a free anti-virus software that guards your PC against viruses, spyware, worms, and other malicious software. This will be demonstrated using Windows…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question