August 4, 2008 at 11:05 am
I wrote a stored procedure that selects data for a week, 2 weeks, etc. based on user input.
Since today is 8/4 I am unable to select records back to 7/28.
The @days variable would be accepting a value of 7, 14, etc.
The procedure runs fine if the date range falls within a single month.
Please help,
Jason
ALTER procedure [dbo].[usp_GetDupes]
(@days int)
as
SELECT RX_NUMBER, FD_FACILITY_ID, POS_POS_FINALIZATION_NUM,
COUNT(RX_NUMBER) AS TALLY
FROM POS_DATA
WHERE POS_POS_FINALIZATION_NUM IN
(
SELECT POS_POS_FINALIZATION_NUM
FROM POS_DATA
GROUP BY POS_POS_FINALIZATION_NUM
HAVING (COUNT(POS_POS_FINALIZATION_NUM) > 1)
) AND (DAY(POS_DATESTAMP) BETWEEN DAY(GETDATE()-@days-1) AND DAY(GETDATE()-1))
GROUP BY RX_NUMBER, FD_FACILITY_ID, POS_POS_FINALIZATION_NUM
HAVING (COUNT(RX_NUMBER) > 1 )[/CODE]
August 4, 2008 at 11:22 am
Try it like this:
ALTER procedure [dbo].[usp_GetDupes]
(@days int)
as
SELECT RX_NUMBER, FD_FACILITY_ID, POS_POS_FINALIZATION_NUM,
COUNT(RX_NUMBER) AS TALLY
FROM POS_DATA
WHERE POS_POS_FINALIZATION_NUM IN
(
SELECT POS_POS_FINALIZATION_NUM
FROM POS_DATA
GROUP BY POS_POS_FINALIZATION_NUM
HAVING (COUNT(POS_POS_FINALIZATION_NUM) > 1)
) AND
AND POS_DATESTAMP BETWEEN DateAdd(Day, -@days-1, Getdate()) AND DateAdd(Day, -1, GETDATE())
GROUP BY RX_NUMBER, FD_FACILITY_ID, POS_POS_FINALIZATION_NUM
HAVING (COUNT(RX_NUMBER) > 1 )[/CODE]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 4, 2008 at 11:35 am
That did the trick!!!
Thank you very much 😀 😀
August 4, 2008 at 11:47 am
You should note that my technique preserves the "time" part of the dates (including Getdate()) instead of knocking them down to midnight. This might produce slightly different results than you are expecting if the dates in your table have time values attached to the dates also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply