May 4, 2011 at 11:10 am
This is an SQL that gets SALES information. But I am having difficulty getting SALES information for last year.
Therefore I would like to see SALES and SALES LY for the day.
Can anyone assist? THANKS!!!!
SELECT DimGeography.GeographyKey,
DimGeography.Country,
DimGeography.GeneralRegion,
DimGeography.LocalRegion,
DimGeography.City,
tblStore.Longitude,
tblStore.Latitude,
DimDate.Year,
DimDate.Month,
DimDate.FeeWeek,
DimDate.FeeWeekEndDate, DimDate.Week,
DimDate.Day, DimDate.Date,
FactDailyItemSales_Summary.Sales,
FROM DimGeography INNER JOIN
DimStore ON DimGeography.GeographyKey = DimStore.GeographyKey INNER JOIN FactDailyItemSales_Summary ON DimStore.StoreKey = FactDailyItemSales_Summary.StoreKey INNER JOIN tblStore ON DimStore.StoreID = tblStore.StoreID INNER JOIN DimDate ON FactDailyItemSales_Summary.DateKey = DimDate.DateKey
WHERE (DimDate.FeeWeekEndDate IN ('2010-03-28 00:00:00.000'))
May 4, 2011 at 11:16 am
You want to get Sales Information for last year or this year or both?
May 4, 2011 at 11:19 am
Both.
I've seen it done elegantly with a calendar table with OR C.Y = @Year - 1 AND C.month = month and C.day = day.
Or redo the query with union all.
OR SUM(CASE WHEN DAte = 1 year back then sales else 0 end) AS previous
OR SUM(CASE WHEN DAte = 0 year back then sales else 0 end) AS current
May 4, 2011 at 11:24 am
Gathering information - is it sales for today and the same day last year? If not, please explain in detail.
Do you want sales for this year / last year on the same or separate rows?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 4, 2011 at 11:27 am
I would like SALES today and SALES for same day last year.
On the same row
May 4, 2011 at 11:32 am
Do you have a calendar table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 4, 2011 at 11:35 am
I do have a calendar table.
And it is down to the DAY grain.
To be more specific I am creating a view with all the fields I need including the day info and need to add SALES LY at the end.
Thank You for all the help.
May 4, 2011 at 11:38 am
Try the first suggestion by Ninja and see if that works in this case.
If not, please post some sample data and desired results
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 4, 2011 at 11:44 am
Try this:
DECLARE @Today DATETIME = DATEADD( DD , DATEDIFF(DD,0,GETDATE()),0),
@ThisDayLastYR DATETIME
SELECT @ThisDayLastYR = DATEADD( YEAR , -1,@Today)
select @Today , @ThisDayLastYR
SELECT
DimGeography.GeographyKey,
DimGeography.Country,
DimGeography.GeneralRegion,
DimGeography.LocalRegion,
DimGeography.City,
tblStore.Longitude,
tblStore.Latitude,
DimDate.Year,
DimDate.Month,
DimDate.FeeWeek,
DimDate.FeeWeekEndDate, DimDate.Week,
DimDate.Day, DimDate.Date,
SUM ( CASE WHEN DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @ThisDayLastYR
THEN FactDailyItemSales_Summary.Sales
ELSE 0
END
) 'Sales_ThisDayLastYR' ,
SUM ( CASE WHEN DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @Today
THEN FactDailyItemSales_Summary.Sales
ELSE 0
END
) AS 'Sales_Today'
FROM DimGeography
INNER JOIN DimStore
ON DimGeography.GeographyKey = DimStore.GeographyKey
INNER JOIN FactDailyItemSales_Summary
ON DimStore.StoreKey = FactDailyItemSales_Summary.StoreKey
INNER JOIN tblStore
ON DimStore.StoreID = tblStore.StoreID
INNER JOIN DimDate
ON FactDailyItemSales_Summary.DateKey = DimDate.DateKey
WHERE (
DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @ThisDayLastYR
OR
DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @Today
)
GROUP BY
DimGeography.GeographyKey,
DimGeography.Country,
DimGeography.GeneralRegion,
DimGeography.LocalRegion,
DimGeography.City,
tblStore.Longitude,
tblStore.Latitude,
DimDate.Year,
DimDate.Month,
DimDate.FeeWeek,
DimDate.FeeWeekEndDate, DimDate.Week,
DimDate.Day, DimDate.Date
{EDIT: Changed @ThisDayLatYr to @Today and added = sign between them}
May 4, 2011 at 11:47 am
WHERE (
DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @ThisDayLastYR
OR
DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @Today
)
Wouldn't it be better if you used between for those 2 filters?
May 4, 2011 at 11:47 am
ColdCoffee (5/4/2011)
Try this:
DECLARE @Today DATETIME = DATEADD( DD , DATEDIFF(DD,0,GETDATE()),0),@ThisDayLastYR DATETIME
SELECT @ThisDayLastYR = DATEADD( YEAR , -1,@Today)
select @Today , @ThisDayLastYR
SELECT
DimGeography.GeographyKey,
DimGeography.Country,
DimGeography.GeneralRegion,
DimGeography.LocalRegion,
DimGeography.City,
tblStore.Longitude,
tblStore.Latitude,
DimDate.Year,
DimDate.Month,
DimDate.FeeWeek,
DimDate.FeeWeekEndDate, DimDate.Week,
DimDate.Day, DimDate.Date,
SUM ( CASE WHEN DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) @ThisDayLastYR
THEN FactDailyItemSales_Summary.Sales
ELSE 0
END
) 'Sales_ThisDayLastYR' ,
SUM ( CASE WHEN DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) @ThisDayLastYR
THEN FactDailyItemSales_Summary.Sales
ELSE 0
END
) AS 'Sales_Today'
FROM DimGeography
INNER JOIN DimStore
ON DimGeography.GeographyKey = DimStore.GeographyKey
INNER JOIN FactDailyItemSales_Summary
ON DimStore.StoreKey = FactDailyItemSales_Summary.StoreKey
INNER JOIN tblStore
ON DimStore.StoreID = tblStore.StoreID
INNER JOIN DimDate
ON FactDailyItemSales_Summary.DateKey = DimDate.DateKey
WHERE (
DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @ThisDayLastYR
OR
DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @Today
)
GROUP BY
DimGeography.GeographyKey,
DimGeography.Country,
DimGeography.GeneralRegion,
DimGeography.LocalRegion,
DimGeography.City,
tblStore.Longitude,
tblStore.Latitude,
DimDate.Year,
DimDate.Month,
DimDate.FeeWeek,
DimDate.FeeWeekEndDate, DimDate.Week,
DimDate.Day, DimDate.Date
You might want to change the variable in the first case statement to be @Today
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 4, 2011 at 11:47 am
You need to join the query to itself.
There may be a better way to do it than this, but this should work. You know more about the tables than I do, so you can probably make the query more efficient.
;
WITH SalesByDate
AS (SELECT DimGeography.GeographyKey,
DimGeography.Country,
DimGeography.GeneralRegion,
DimGeography.LocalRegion,
DimGeography.City,
tblStore.Longitude,
tblStore.Latitude,
DimDate.Year,
DimDate.Month,
DimDate.FeeWeek,
DimDate.FeeWeekEndDate,
DimDate.Week,
DimDate.Day,
DimDate.Date,
FactDailyItemSales_Summary.Sales
FROM DimGeography
INNER JOIN DimStore
ON DimGeography.GeographyKey = DimStore.GeographyKey
INNER JOIN FactDailyItemSales_Summary
ON DimStore.StoreKey = FactDailyItemSales_Summary.StoreKey
INNER JOIN tblStore
ON DimStore.StoreID = tblStore.StoreID
INNER JOIN DimDate
ON FactDailyItemSales_Summary.DateKey = DimDate.DateKey)
SELECT SBD1.*,
SBD2.Sales AS SalesLY
FROM SalesByDate AS SBD1
INNER JOIN SalesByDate AS SBD2
ON SBD1.GeographyKey = SBD2.GeographyKey
AND SBD1.Longitude = SBD2.Longitude
AND SBD1.Latitude = SBD2.Latitude
AND SBD1.MONTH = SBD2.MONTH
AND SBD1.DAY = SBD2.DAY
AND SBD1.YEAR = SBD2.YEAR + 1
WHERE (SBD1.FeeWeekEndDate IN ('2010-03-28 00:00:00.000'));
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 4, 2011 at 11:53 am
SQLRNNR (5/4/2011)
You might want to change the variable in the first case statement to be @Today
Yep, Jason is right. Post edited to correct the error.
May 4, 2011 at 11:53 am
I've had a lot of success doing 1 union all in a derived table to avoid the self join in a similar query.
BETWEEN OR BETWEEN in that case gave me scans. Union all on a single between always did seeks.
May 4, 2011 at 11:55 am
Ninja's_RGR'us (5/4/2011)
WHERE (
DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @ThisDayLastYR
OR
DATEADD( DD , DATEDIFF(DD,0,DimDate.FeeWeekEndDate ),0) = @Today
)
Wouldn't it be better if you used between for those 2 filters?
Ninja, i think the OP is interesed in getting the Sales Info only for 2 days, today and same day last year. Thats my understanding though, only the OP can correct it.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply