Pivot Table - Excel-Like Calculations

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

Transact-SQL (2005)

Pivot Table - Excel-Like Calculations

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2010-09-01 : 10:09:45

Im using a query in sql server to display a grid of gasoline readings during a weekly period.
I am trying to go across the columns from left to right and subtract starting and ending values of gallons to get a total of gallons pumped.
Is there a way to do this with a pivot table query?
Or, should I switch from this attempt to a UNION of the table to itself?
Below is the query and here is the values I would like to display:
The tough part is carrying down the previous day's end reading to become the next days starting reading.
Desired Output:
date starting gal delivered gal pumped inv. end inv.
7/1/2010 3184.00 0.00 705.00 2479.00 2557.95
7/2/2010 2557.95 5999.00 802.00 7754.95 7789.08
7/3/2010 7789.08 0.00 549.00 7240.08 7238.09

Thanks
John

SELECT *
FROM
( SELECT
A.[TANK_ID_FW] ,
A.[TRANSACTION_DATE_FW],-- + 1 AS tran_date_fw,
RIGHT(A.[TANK_ID_FW], 1) AS Tank,
--
(A.[TANK_GALLONS_READING_FW]-C.[QUANTITY_RECEIVED_FW]) AS diff,
A.[TANK_GALLONS_READING_FW],
A.[TANK_INCHES_READING_FW] ,
CASE
WHEN SUBSTRING(A.TANK_ID_FW,7,3)='B5-' THEN 'B5'
WHEN SUBSTRING(A.TANK_ID_FW,7,3)='E85' THEN 'E85'
WHEN SUBSTRING(A.TANK_ID_FW,7,3)='UL-' THEN 'UNLEADED'
END AS Type,
C.[TRANSACTION_TYPE_FW] ,
C.[QUANTITY_RECEIVED_FW]
FROM [USER_DEFINED56_FW] A
JOIN [FUEL_PUMPS_FW] B
ON A.TANK_ID_FW=B.TANK_ID_FW
JOIN FUEL_PRICES_FW C
ON A.[TANK_ID_FW]=C.TANK_ID_FW
WHERE (A.TRANSACTION_DATE_FW BETWEEN '2010-01-29' AND '2010-12-12')
--AND RIGHT(A.[TANK_ID_FW], 1)=1
--AND SUBSTRING(A.[TANK_ID_FW],7,3)='UL-'
--AND A.[ARCHIVE_STATUS_FW]='N'
--ORDER BY A.TRANSACTION_DATE_FW
) DataTable
PIVOT
(
SUM([TANK_GALLONS_READING_FW])
FOR [TANK_ID_FW]
IN ([19999-E85-3],[19999-B5-1],[19999-UL-2])

) PivotTable

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 10:46:53

Just some formatting for anyone else (wrap any sql you post in <square bracket>code<square bracket> tags.




SELECT * 
FROM    
( SELECT   
	A.[TANK_ID_FW] ,
	A.[TRANSACTION_DATE_FW],-- + 1 AS tran_date_fw,
	RIGHT(A.[TANK_ID_FW], 1) AS Tank,
	  --
    (A.[TANK_GALLONS_READING_FW]-C.[QUANTITY_RECEIVED_FW]) AS diff,
	A.[TANK_GALLONS_READING_FW],
	A.[TANK_INCHES_READING_FW] ,
	CASE 
	WHEN SUBSTRING(A.TANK_ID_FW,7,3)='B5-' THEN 'B5'
	WHEN SUBSTRING(A.TANK_ID_FW,7,3)='E85' THEN 'E85'
	WHEN SUBSTRING(A.TANK_ID_FW,7,3)='UL-' THEN 'UNLEADED'
    END AS Type,
    C.[TRANSACTION_TYPE_FW] ,
    C.[QUANTITY_RECEIVED_FW] 
    FROM [USER_DEFINED56_FW] A 
    JOIN [FUEL_PUMPS_FW] B 
    ON A.TANK_ID_FW=B.TANK_ID_FW 
    JOIN FUEL_PRICES_FW C
    ON A.[TANK_ID_FW]=C.TANK_ID_FW
WHERE (A.TRANSACTION_DATE_FW BETWEEN '2010-01-29' AND '2010-12-12')
	--AND RIGHT(A.[TANK_ID_FW], 1)=1
	--AND SUBSTRING(A.[TANK_ID_FW],7,3)='UL-'
	--AND A.[ARCHIVE_STATUS_FW]='N' 
	--ORDER BY A.TRANSACTION_DATE_FW 
) DataTable 
PIVOT 
( 
SUM([TANK_GALLONS_READING_FW])
                    FOR [TANK_ID_FW] 
                      IN ([19999-E85-3],[19999-B5-1],[19999-UL-2])
 
) PivotTable

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources