September 7, 2012 at 2:49 am
query was :
ALTER proc [dbo].[sp_spv_date]
@panelid int,
@red datetime ,
@product varchar(20) as
begin
declare @Reading_Date datetime
set @Reading_Date=@red
select DatePart(hour,Reading_Date) as hour,t.Tank_Product ,convert(Date,Reading_Date,103) as date, sum(tr.Sales) as sales, sum(tr.Price)as price ,sum(tr.Amount)as amount from
dbo.Tanks as t inner join Transactions as tr on
t.Simmons_PanelID=@panelid and tr.Simmons_PanelID=@panelid
where convert(Date,Reading_Date,103)=@Reading_Date and t.Tank_Product like '%' + @product +'%'
group by convert(Date,Reading_Date,103),DatePart(hour,Reading_Date),t.Tank_Product
order by DatePart(hour,Reading_Date)
end
output was :
hour tank_product date sales price amonut
0DIESEL2012-08-04563.8524.09761.352
0UNLEADED2012-08-04375.9016.06507.568
1DIESEL2012-08-04189.0912.15255.075
1UNLEADED2012-08-04126.068.10170.050
2DIESEL2012-08-04600.004.08816.000
2UNLEADED2012-08-04400.002.72544.000
3DIESEL2012-08-04182.224.08247.818
3UNLEADED2012-08-04121.482.72165.212
4DIESEL2012-08-04680.1020.13920.232
4UNLEADED2012-08-04453.4013.42613.488
5DIESEL2012-08-04761.4624.121031.148
5UNLEADED2012-08-04507.6416.08687.432
can we change row to columns
i expecting like this
hour date diesel unleaded
sales 0 2012-08-04 4216 2222
price 0 2012-08-04 423 555
any one help me pls
September 7, 2012 at 5:19 am
Like this?
select
DatePart(hour,Reading_Date) as [hour],
t.Tank_Product ,
convert(Date,Reading_Date,103) as [date],
sum(tr.Sales) as sales,
sum(tr.Price)as price ,
sum(tr.Amount)as amount
INTO #Results
from dbo.Tanks as t
inner join Transactions as tr
on t.Simmons_PanelID = @panelid
and tr.Simmons_PanelID = @panelid
where convert(Date,Reading_Date,103) = @Reading_Date
and t.Tank_Product like '%' + @product + '%'
group by
convert(Date,Reading_Date,103),
DatePart(hour,Reading_Date),
t.Tank_Product
order by DatePart(hour,Reading_Date)
/*
DROP TABLE #Results
CREATE TABLE #Results (hour INT, tank_product VARCHAR(10), [date] DATE, sales MONEY, price MONEY, amount DECIMAL(8,3))
INSERT INTO #Results (hour, tank_product, date, sales, price, amount)
SELECT 0, 'DIESEL', '2012-08-04', 563.85, 24.09, 761.352 UNION ALL
SELECT 0, 'UNLEADED', '2012-08-04', 375.90, 16.06, 507.568 UNION ALL
SELECT 1, 'DIESEL', '2012-08-04', 189.09, 12.15, 255.075 UNION ALL
SELECT 1, 'UNLEADED', '2012-08-04', 126.06, 8.10, 170.050 UNION ALL
SELECT 2, 'DIESEL', '2012-08-04', 600.00, 4.08, 816.000 UNION ALL
SELECT 2, 'UNLEADED', '2012-08-04', 400.00, 2.72, 544.000 UNION ALL
SELECT 3, 'DIESEL', '2012-08-04', 182.22, 4.08, 247.818 UNION ALL
SELECT 3, 'UNLEADED', '2012-08-04', 121.48, 2.72, 165.212 UNION ALL
SELECT 4, 'DIESEL', '2012-08-04', 680.10, 20.13, 920.232 UNION ALL
SELECT 4, 'UNLEADED', '2012-08-04', 453.40, 13.42, 613.488 UNION ALL
SELECT 5, 'DIESEL', '2012-08-04', 761.46, 24.12, 1031.148 UNION ALL
SELECT 5, 'UNLEADED', '2012-08-04', 507.64, 16.08, 687.432
*/
SELECT
[Title] = 'sales',
[date],
[DIESEL] = SUM(CASE WHEN tank_product = 'DIESEL' THEN sales ELSE 0 END),
[UNLEADED] = SUM(CASE WHEN tank_product = 'UNLEADED' THEN sales ELSE 0 END)
FROM #Results
GROUP BY [date]
UNION ALL
SELECT
[Title] = 'price',
[date],
[DIESEL] = SUM(CASE WHEN tank_product = 'DIESEL' THEN price ELSE 0 END),
[UNLEADED] = SUM(CASE WHEN tank_product = 'UNLEADED' THEN price ELSE 0 END)
FROM #Results
GROUP BY [date]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply