July 23, 2013 at 9:14 am
Hi Im trying to return all rows based on the query below and have used the dateadd(datediff timestamps for first day of week and last day of week for last week.
Can someone please advice if the code looks correct, I have tested but want to make sure the syntax is correct as I cant see results until the next DB update of the DataMart.
Many thanks
Select Distinct /*f.IncidentID,*/ i.IncidentNumber, f.DateKey,
Case
When f.DateKey between convert(int,convert(varchar(8),DATEADD(week,DATEDIFF(week,7,GETDATE()),0),112),0) --first day of last week
And convert(int,convert(varchar(8),DateAdd(week,DATEDIFF(week,7,GETDATE()),6),112),0) --last day of last week
And f.[priority] = '1'
And f.SnapShotDateKey BETWEEN convert(int,convert(varchar(8),DATEADD(week,DATEDIFF(wk,0,GETDATE()),0),112),0)
And convert(int,convert(varchar(8),DATEADD(week,DATEDIFF(week,0,GETDATE()),0),112),0)
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On f.IncidentID = i.IncidentID
where f.Priority = 1
order by P1s Desc
July 23, 2013 at 9:23 am
szonkie (7/23/2013)
Hi Im trying to return all rows based on the query below and have used the dateadd(datediff timestamps for first day of week and last day of week for last week.Can someone please advice if the code looks correct, I have tested but want to make sure the syntax is correct as I cant see results until the next DB update of the DataMart.
Many thanks
Select Distinct /*f.IncidentID,*/ i.IncidentNumber, f.DateKey,
Case
When f.DateKey between convert(int,convert(varchar(8),DATEADD(week,DATEDIFF(week,7,GETDATE()),0),112),0) --first day of last week
And convert(int,convert(varchar(8),DateAdd(week,DATEDIFF(week,7,GETDATE()),6),112),0) --last day of last week
And f.[priority] = '1'
And f.SnapShotDateKey BETWEEN convert(int,convert(varchar(8),DATEADD(week,DATEDIFF(wk,0,GETDATE()),0),112),0)
And convert(int,convert(varchar(8),DATEADD(week,DATEDIFF(week,0,GETDATE()),0),112),0)
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On f.IncidentID = i.IncidentID
where f.Priority = 1
order by P1s Desc
What are the datatypes of the date columns? The conditions seem to be very over complicated but maybe that is because f.DateKey is not a datetime? It would be FAR better if you can change the datatype to the proper datatypes instead of using ints for dates.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2013 at 9:33 am
I've seen this before where dates are stored as INTs in fact tables. Your logic works but for this kind of thing I would create a Date dimension table to be honest.
I still don't get how the below works though!
select DATEDIFF(week,7,GETDATE())
select DATEADD(week,DATEDIFF(week,7,GETDATE()),0)
What's the 7?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
July 23, 2013 at 9:34 am
The f.DateKey is connected to the the actual createddatetime field in the DB and is the primary key in the Fact table. I could use the actual createddatetime field but have created keys for all the dimensions in the ETL and scripted them to use the createddatetime field which connects to the date and time dims.
Does that help?
Thanks
July 23, 2013 at 9:41 am
Im hoping the 7 calculates back 7 days then the 0 and 6 appended to the end of the Between statement works out the actual day, its strange I know Id expect Day not Week but I've been advised this works this way...I would have thought DateDiff(d,-7, getdate()),0).
Yes the int are for the Fact table but they are converted from the actual createddatetime datetime field.
July 23, 2013 at 7:05 pm
I'd say that if you want this
SELECT DATEADD(week,DATEDIFF(week,7,GETDATE()),0)
To always return the Mon of the prior week, it works pretty well.
SET DATEFIRST 1
SELECT DATEADD(week,DATEDIFF(week,7,GETDATE()),0)
SET DATEFIRST 7
SELECT DATEADD(week,DATEDIFF(week,7,GETDATE()),0)
SELECT DATEADD(week,DATEDIFF(week,7,'2014-01-15'),0)
SELECT DATEADD(week,DATEDIFF(week,7,'2012-03-15'),0)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply