January 26, 2012 at 8:44 am
I am working on SQL server 2008 R2
I have a table as following TableA
clientcode | days
101 | 30
101 | 60
105 | 15
I need to build the sp
My query looks comething like this
SELECT Col1, col2,col3 from TABLE A
WHERE (Datediff(dd,receivedDate,getdate() >=) @days1 --30 days and Datediff(dd,receivedDate,getdate()) < @days1 - 5 )
UNION ALL
SELECT Col1, col2,col3 from TABLE A
WHERE (Datediff(dd,receivedDate,getdate() >=) @days2 --60 days and Datediff(dd,receivedDate,getdate()) < @days2 - 5 )
In other words ...I should be able to build the UNION query based on how many rows of days I have in TableA
any help would be greatly appreciated or is there any better approach to do this....thanks a lot.
January 26, 2012 at 9:06 am
Guras (1/26/2012)
I am working on SQL server 2008 R2I have a table as following TableA
clientcode | days
101 | 30
101 | 60
105 | 15
I need to build the sp
My query looks comething like this
SELECT Col1, col2,col3 from TABLE A
WHERE (Datediff(dd,receivedDate,getdate() >=) @days1 --30 days and Datediff(dd,receivedDate,getdate()) < @days1 - 5 )
UNION ALL
SELECT Col1, col2,col3 from TABLE A
WHERE (Datediff(dd,receivedDate,getdate() >=) @days2 --60 days and Datediff(dd,receivedDate,getdate()) < @days2 - 5 )
First, don't use the same name for different tables. If TableA consists of clientcode and days, then Col1, Col2, Col3, and ReceivedDate are invalid columns for TableA.
Second, you're thinking about your problem in the wrong way. You're taking a set and analyzing it row by row (Jeff Moden has labelled this RBAR for "row by agonizing row") and then unioning it back into a set. If you keep the set as a set, you can get rid of the union.
Try the following
SELECT a.Col1, a.Col2, a.Col3
FROM TableA AS a
INNER JOIN TableB as b
ON Datediff(dd,receivedDate,getdate()) >= b.Days
AND Datediff(dd,receivedDate,getdate()) < b.Days - 5
The ON clause may also benefit from rewriting to use an index on ReceivedDate rather than Days, assuming that there are many more records in the first table, so that an index seek there might save more time than an index seek on the second table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 26, 2012 at 9:15 am
In addition, I think you need to change the comparisons on these.
Datediff(dd,receivedDate,getdate()) >= b.Days
AND Datediff(dd,receivedDate,getdate()) < b.Days - 5
By the law of transitivity, if n > d and d > d-5 then n > d-5.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 26, 2012 at 9:25 am
Sorry , that was a typo....
Thank you so much for the reply. The query results data perfect!!!! Just the way it was supposed to. Have a wonderful day ahead....
January 26, 2012 at 9:28 am
Changed it to...
Datediff(dd,receivedDate,getdate()) >= b.Days
AND Datediff(dd,receivedDate,getdate()) < b.Days + 5
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply