May 4, 2011 at 11:58 am
ColdCoffee (5/4/2011)
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.
I know, I'm saying to use between so that you don't force a scan because of non sargable condition.
DimDate.FeeWeekEndDate > DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND DimDate.FeeWeekEndDate < DATEADD(D, 1, DATEDIFF(D, 0, GETDATE()))
May 4, 2011 at 12:05 pm
Ninja's_RGR'us (5/4/2011)
I know, I'm saying to use between so that you don't force a scan because of non sargable condition.DimDate.FeeWeekEndDate > DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND DimDate.FeeWeekEndDate < DATEADD(D, 1, DATEDIFF(D, 0, GETDATE()))
Wouldn't this give ALL the dates betwen today and same date last year and NOT today + same date last year. He need only for 2 days, not for 365 days, is my understanding.
May 4, 2011 at 12:06 pm
that is correct. I am just looking to get two days sales...
Yestrerday Sales (becasue im reporting of a datawarehouse) and same time last yeear.
May 4, 2011 at 12:23 pm
DSR0308 (5/4/2011)
that is correct. I am just looking to get two days sales...Yestrerday Sales (becasue im reporting of a datawarehouse) and same time last yeear.
Then the query i gave you worked?
May 4, 2011 at 12:33 pm
Unfortunately it did not.
I think its my fault of not explainning myself properly.
I truly appreciate all the help.
Ill give it one more try.
I have a datawarehouse that loads once a day. The data in in the database is always for the day before.
I would like to SELECT ALL the days SALES in the datawarehouse + show the SALES data for the same day last year.
ex:
Province City Region Store Year MOnth Day SALES SALES LY
Alberta City 1 Region 1 Store 1 2011 May 5 100 150
Alberta City 2 Region 2 Store 2 2011 May 5 120 100
etc
May 4, 2011 at 12:44 pm
DSR0308 (5/4/2011)
Unfortunately it did not.I think its my fault of not explainning myself properly.
I truly appreciate all the help.
Ill give it one more try.
I have a datawarehouse that loads once a day. The data in in the database is always for the day before.
I would like to SELECT ALL the days SALES in the datawarehouse + show the SALES data for the same day last year.
ex:
Province City Region Store Year MOnth Day SALES SALES LY
Alberta City 1 Region 1 Store 1 2011 May 5 100 150
Alberta City 2 Region 2 Store 2 2011 May 5 120 100
etc
Did you try the query I wrote? It should do that.
- 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 12:48 pm
I did try and getting a conversion error when converting a data type to int.
Trying to find where the issue is
May 4, 2011 at 12:54 pm
DSR0308 (5/4/2011)
I did try and getting a conversion error when converting a data type to int.Trying to find where the issue is
That will be from the Year + 1 that I put in there. What data type is your Year column?
- 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 2:11 pm
ColdCoffee (5/4/2011)
Ninja's_RGR'us (5/4/2011)
I know, I'm saying to use between so that you don't force a scan because of non sargable condition.DimDate.FeeWeekEndDate > DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND DimDate.FeeWeekEndDate < DATEADD(D, 1, DATEDIFF(D, 0, GETDATE()))
Wouldn't this give ALL the dates betwen today and same date last year and NOT today + same date last year. He need only for 2 days, not for 365 days, is my understanding.
Not what I mean >>
Where (dw_datecol >= '2011-5-5' and dw_datecol < '2011-5-6')
OR (dw_datecol >= '2010-5-5' and dw_datecol < '2010-5-6')
This will have a chance to use index seeks, your version would force a scan every time.
May 5, 2011 at 10:12 am
thank you everyone for their help, GSquared solution did it for me.
May 5, 2011 at 10:15 am
DSR0308 (5/5/2011)
thank you everyone for their help, GSquared solution did it for me.
Good to know. Congrats on getting it working.
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 9, 2011 at 6:33 am
You're welcome.
- 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
Viewing 12 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply