| Author |
Topic |
|
BankOfficerHere
Posting Yak Master
124 Posts |
 Posted - 2011-03-31 : 21:09:21
|
| I'm using the following where statement to filter dates:WHERE (WitnesD8 >= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')) AND (WitnesD8 <= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000201') - 1)However, what I want is I want the data to reflect most recent completed week only – not real-time data.Example, let say today is March 31, if I will view the report, under the March column, I will only see the scores of up to March 27 – is this possible? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-31 : 21:13:44
|
"what I want is I want the data to reflect most recent completed week only"Is it last 7 days ?"let say today is March 31, if I will view the report, under the March column, I will only see the scores of up to March 27 "why March 27 ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
BankOfficerHere
Posting Yak Master
124 Posts |
Posted - 2011-03-31 : 21:15:24
|
| no the last week..week range is from monday to sunday. thanks |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-31 : 23:42:24
|
this will give you 21st March to 27th March. select begin_last_week = dateadd(week, datediff(week, 0, getdate()) - 1, 0), end_last_week = dateadd(week, datediff(week, 0, getdate()), -1)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
BankOfficerHere
Posting Yak Master
124 Posts |
Posted - 2011-04-01 : 00:02:42
|
| I would like to start on March 1 and ends on March 27 and it rolls every week for the month. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-01 : 00:27:11
|
for the begining of the month, you can use below queryselectbegin_of_current_month = dateadd(month, datediff(month, 0, getdate()), 0),begin_of_prev_month = dateadd(month, datediff(month, 0, getdate()) - 1, 0) not sure what do you mean by rolls every week for the month . . .
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
BankOfficerHere
Posting Yak Master
124 Posts |
Posted - 2011-04-01 : 00:37:07
|
| I would like a data that filters begining of the month and ends last week in that case march 27...that would be March 1 to March 27... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-01 : 00:45:25
|
"begining of the month" based on which date ? begining of last month ?"ends last week". Use the query i posted on 03/31/2011 : 23:42:24
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
BankOfficerHere
Posting Yak Master
124 Posts |
Posted - 2011-04-01 : 00:59:09
|
| begining of the month..today is march 31...that would be march 1 til last week ending of march 27 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-01 : 02:32:07
|
what if when today is 1st of Apr ? the last week ending is still March 27 right ? and the begin of month is ?Basically what is your date of reference when you said "Begin of month" ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
where clause - month