| Author |
 Topic  |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
 Posted - 06/02/2012 : 08:29:09
|
Is that even possible? Common sense tells me no but if anyone knows otherwise, it'll be someone here 
To summarise; I have a script that runs overnight and starts with GETDATE()-1 to get all data from the previous day. My company now want to get data from the previous 30 days to cover transactions that have been retrospectively actioned.
The problem is, we're using a datareader inside a third party piece of software that has limited SQL emulation......it can't handle variables 
The only solution I can think of is to write thirty datareader scripts, all identical except changing GETDATE()-1 to -2, -3, -4 etc but that's pants.
Anyone know if there's a workaround?
TIA. |
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 06/02/2012 : 09:59:37
|
SELECT * FROM myTable WHERE DateCol>=GETDATE()-30
Or, to be safer:
SELECT * FROM myTable WHERE DateCol>=DATEADD(day, DATEDIFF(day, 0,GETDATE())-30, 0) |
 |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 06/02/2012 : 11:47:27
|
Many thanks for the reply but that won't work in my case - there is a subsequent piece of code later in the query that checks if a policy end date is > GETDATE() and excludes that from the calculations.
I need to loop through the code 30 times, once for each date in the range.
So frustrating, stupid third party software |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 06/02/2012 : 13:58:11
|
quote:
Originally posted by Rasta Pickles
Many thanks for the reply but that won't work in my case - there is a subsequent piece of code later in the query that checks if a policy end date is > GETDATE() and excludes that from the calculations.
I need to loop through the code 30 times, once for each date in the range.
So frustrating, stupid third party software
Didnt understand why you cant use Robs suggestion still. Nothing you suggested above would cause a problem for that.
Also i didnt understnd why you need a loop 30 times once for each date?
why can you go for set based logic to manipulate whole 30 dates in a shot? perhaps you could explain what manipulation you're doing with each of dates data so that we can suggest an alternative if possible
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 06/02/2012 : 14:29:23
|
I think I see how the misunderstanding has occurred and it's entirely my own fault - for that I apologise.
The reporting I'm doing is per day.
SO I can't simply arbitrarilly grab everything from current date - 30 because subsequent bits of code want to know whether the policy end date is greater than the current date.
It needs a loop. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 06/02/2012 : 14:35:31
|
quote:
Originally posted by Rasta Pickles
I think I see how the misunderstanding has occurred and it's entirely my own fault - for that I apologise.
The reporting I'm doing is per day.
SO I can't simply arbitrarilly grab everything from current date - 30 because subsequent bits of code want to know whether the policy end date is greater than the current date.
It needs a loop.
for that isnt it enough to just do a day wise grouping after getting 30 days data? then do required manipulation for each day group.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Grabbing last thirty days data without using a var
