September 27, 2012 at 5:41 am
here i wrote query for today sale
select SUM(tr.Sales) t1.Tank from Transactions as tr
inner join Dispenser_Pump_Mapping as dpm1 ON tr.Simmons_PanelID = dpm1.Simmons_PanelID AND
tr.Dispenser = dpm1.Dispenser
INNER JOIN
Tanks as t1 ON dpm1.Simmons_PanelID = t1.Simmons_PanelID AND
dpm1.Tank = t1.Tank and tr.Simmons_PanelID=t1.Simmons_PanelID
where convert(Date,tr.Reading_Date,103)=convert(date,GETDATE(),103) and tr.Simmons_PanelID=5479
group by t1.Tank
..............
last week sale
select SUM(tr.Sales),t1.Tank from Transactions as tr
inner join Dispenser_Pump_Mapping as dpm1 ON tr.Simmons_PanelID = dpm1.Simmons_PanelID AND
tr.Dispenser = dpm1.Dispenser
INNER JOIN
Tanks as t1 ON dpm1.Simmons_PanelID = t1.Simmons_PanelID AND
dpm1.Tank = t1.Tank and tr.Simmons_PanelID=t1.Simmons_PanelID
where convert(date,tr.Reading_Date,103) = CONVERT(date,DATEADD(DAY,-7,getdate()),103) and tr.Simmons_PanelID=5479
group by t1.Tank
--------------
i want to combine both in stored procedure
alter PROCEDURE [dbo].[GetTankSalesToday+week]
-- Add the parameters for the stored procedure here
(@PanelID int, @wType varchar(20))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
-- Insert statements for procedure here
SELECT t.Tank,t.Tank_Product as Product, SUM(tr.Sales) as Sales, SUM(tr1.Sales) as week_sales
FROMTanks as t
INNER JOIN Dispenser_Pump_Mapping as dpm
ONt.Simmons_PanelID=dpm.Simmons_PanelID AND t.Tank=dpm.Tank
INNER JOIN Transactions as tr
ON tr.Simmons_PanelID = t.Simmons_PanelID AND
tr.Dispenser = dpm.Dispenser AND convert(Date,tr.Reading_Date,103)=convert(date,GETDATE(),103)
inner join Tanks as t1
on t1.Simmons_PanelID=t.Simmons_PanelID
INNER JOIN Dispenser_Pump_Mapping as dpm1
ONt1.Simmons_PanelID=dpm1.Simmons_PanelID AND t1.Tank=dpm1.Tank
INNER JOIN Transactions as tr1
ON tr1.Simmons_PanelID = t1.Simmons_PanelID AND
tr1.Dispenser = dpm1.Dispenser AND convert(date,tr1.Reading_Date,103) = CONVERT(date,DATEADD(DAY,-7,getdate()),103)
where t.Simmons_PanelID=@PanelID
group by t.Tank,t.Tank_Product
order by t.Tank
END
the output was like hell .. can you please help me in getting rid of this thanks ..
September 27, 2012 at 6:15 am
Here's the simplest way. Note that performance will suck because you're doing all of your table reads twice:
;WITH SalesToday AS (
select
SumSales = SUM(tr.Sales),
t1.Tank
from Transactions as tr
inner join Dispenser_Pump_Mapping as dpm1 ON tr.Simmons_PanelID = dpm1.Simmons_PanelID
AND tr.Dispenser = dpm1.Dispenser
INNER JOIN Tanks as t1 ON dpm1.Simmons_PanelID = t1.Simmons_PanelID
AND dpm1.Tank = t1.Tank
and tr.Simmons_PanelID = t1.Simmons_PanelID
where convert(Date,tr.Reading_Date,103) = convert(date,GETDATE(),103)
and tr.Simmons_PanelID = 5479
group by t1.Tank
),
SalesLastWeek AS (
select
SumSales = SUM(tr.Sales),
t1.Tank
from Transactions as tr
inner join Dispenser_Pump_Mapping as dpm1 ON tr.Simmons_PanelID = dpm1.Simmons_PanelID
AND tr.Dispenser = dpm1.Dispenser
INNER JOIN Tanks as t1 ON dpm1.Simmons_PanelID = t1.Simmons_PanelID
AND dpm1.Tank = t1.Tank
and tr.Simmons_PanelID = t1.Simmons_PanelID
where convert(date,tr.Reading_Date,103) = CONVERT(date,DATEADD(DAY,-7,getdate()),103)
and tr.Simmons_PanelID=5479
group by t1.Tank
)
SELECT t.*, w.SumSales
FROM SalesToday t
INNER JOIN SalesLastWeek w ON w.Tank = t.tank
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
September 27, 2012 at 6:31 am
This is a little more tricky but reads the tables only once.
SELECT
SumTodaySales = SUM(CASE WHEN CAST(tr.Reading_Date AS DATE) = dates.Today THEN tr.Sales ELSE 0 END),
SumLastweekSales = SUM(CASE WHEN CAST(tr.Reading_Date AS DATE) = dates.Lastweek THEN tr.Sales ELSE 0 END),
t1.Tank
FROM Transactions tr
INNER JOIN Dispenser_Pump_Mapping dpm1
ON tr.Simmons_PanelID = dpm1.Simmons_PanelID
AND tr.Dispenser = dpm1.Dispenser
INNER JOIN Tanks t1
ON dpm1.Simmons_PanelID = t1.Simmons_PanelID
AND dpm1.Tank = t1.Tank
AND tr.Simmons_PanelID = t1.Simmons_PanelID
CROSS APPLY (
SELECT
Today = CAST(GETDATE() AS DATE),
Lastweek = CAST(DATEADD(DAY,-7,getdate()) AS DATE)
) dates
WHERE tr.Simmons_PanelID = 5479
AND CAST(tr.Reading_Date AS DATE) IN (dates.Today, dates.Lastweek)
GROUP BY t1.Tank
The date filters may be SARGable, check the actual plan - if not, use date ranges. Ask if you are unsure.
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
September 27, 2012 at 6:53 am
thanks a lot ..
can I subtract todaysales- weeksale as other column
many thanks
September 27, 2012 at 8:39 am
vinay.varaala (9/27/2012)
thanks a lot ..can I subtract todaysales- weeksale as other column
many thanks
Yes of course. To keep the query nice, either call it as a CTE or use it as a derived table.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply