| Author |
Topic |
|
nietzky
Yak Posting Veteran
75 Posts |
 Posted - 2012-07-09 : 17:01:00
|
| I am trying to write a statement to show all data with rundate which matches always last 4(four) Fridays. Data is coming weekly. The issue I am facing is the data sometimes comes in on Saturday instead of Friday 85% of the time the data comes in on Fridays, 15% of the time on Sundays). I need to narrow down the data set so I have only last 4 weeks of the data. How do i accomplish this? Unremarked statement works and narrows the data to Fridays however it does not pickup Saturday if Friday's data came late. Also how do I narrow the data to last 4 dates - Fridays data or Saurdays data if Friday data did not arrive. select * into dbo.TEST1 FROM dbo.TESTwhere rundate = DATEADD(DAY, DATEDIFF(DAY, 4,rundate) / 7 * 7, 4)--AND--rundate in (select distinct top 4 rundate from dbo.TEST order by rundate desc) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-09 : 17:35:25
|
| when will you be running script on? Next Monday?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nietzky
Yak Posting Veteran
75 Posts |
Posted - 2012-07-10 : 05:32:26
|
| script is running every day, I just need to make sure I have 1 weekly date ( 1 good data per week). I chose Friday but if there is no data I would like to get max per week. New week starts on Monday.Thank you |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-10 : 07:24:47
|
Would picking all the data that arrived starting four Friday's ago meet your needs? If so, change your query to this:select * into dbo.TEST1 FROM dbo.TESTwhere rundate >= DATEADD(DAY, DATEDIFF(DAY, 4,GETDATE()) / 7 * 7-21, 4) |
|
|
|
nietzky
Yak Posting Veteran
75 Posts |
Posted - 2012-07-11 : 06:00:59
|
| I think the way to do this is to select a MAX from a date range. Just not sure how to do this. I could take for example Sunday or Suturday as weekly date. This way if I miss Saturday, I can get other days as week's max. Any idea how to translate this to SQl? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-11 : 06:59:22
|
| After reading your original post again, it is not clear to me what you are trying to get. What I understood was this:a) You get a data feed once a week.b) The data feed could come in on Friday, Saturday or Sunday.c) There is a date associated with the data feed which indicates the date on which you received the data feed. That is available in column rundate.d) When you do a query (on a Monday for example), you want to get the data from the last four data feeds.If all of that is true, the query I posted on 07/10/2012 : 07:24:47 would work. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-11 : 10:05:16
|
quote:
Originally posted by nietzky
I think the way to do this is to select a MAX from a date range. Just not sure how to do this. I could take for example Sunday or Suturday as weekly date. This way if I miss Saturday, I can get other days as week's max. Any idea how to translate this to SQl?
so are you looking at max week dates within date range(from to dates)?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nietzky
Yak Posting Veteran
75 Posts |
Posted - 2012-07-11 : 13:05:15
|
| from Monday to Sunday (no specific dates), I could take any max on a week. MAX(RUNDATE) per week where week is Monday to Sunday starting 1/1/2012 |
|
|
|
|
WEEKLY DATE issue