Changing "date" in my Stored Procedure at runtime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Changing "date" in my Stored Procedure at runtime

Author

Topic

amgrace
Starting Member

30 Posts

Posted - 2004-06-03 : 08:03:32

Hello everyone,

I order to check that the main fields (i.e MemberID and Name)of allowed "duplicate" records on my table are the same, I do the following:

1. I create 2 identical views of the required data (MemberID and Name).
2. Then a SELECT query to compare/filter the two views and show the ones where the MemberID is same but Name different.

NOW TO THE PROBLEM - I need to do this check every month in arrears (i.e I am doing the data for May now). I've created a stored procedure for these steps but dates on the "alter view" select will need to be manually changed to reflect the new month to be check.

Is it possible to pass in these new dates at runtime (i.e for the procedure to ask me for the new dates to refresh the views on).

Here's my procedure at the moment. Thanks

CREATE Procedure ProcessDupMembers
AS

------- Refreshes the data in View 1 based on the new dates entered.
ALTER VIEW DupMember1View
AS

SELECT DISTINCT
MemberID, Surname
FROM
Members
WHERE
ActivityEndDate BETWEEN CONVERT(char (10),'01/02/04',103) AND CONVERT(char (10),'29/02/04', 103)

------- Refreshes the data in View 2 based on the new dates entered.
ALTER VIEW DupMember2View
AS

SELECT DISTINCT
MemberID, Surname
FROM
Members
WHERE
ActivityEndDate BETWEEN CONVERT(char (10),'01/02/04',103) AND CONVERT(char (10),'29/02/04', 103)

-------Compare/Filter data in both Views and display results
SELECT DISTINCT a.*
FROM DupMember1View a

INNER JOIN DupMember2View b
ON
a.MemberID = b.MemberID

WHERE
a.Surname<>b.Surname

ORDER BY a.MemberID

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 08:23:12

Why not create a table with two fields, monthStart and monthEnd, then add this table into the view and use it for your between??

Or you can use


between dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) and dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0))

if it will always be the previous month...

amgrace
Starting Member

30 Posts

Posted - 2004-06-03 : 09:18:26

what excatly is this code (in ENGLISH):

between dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) and dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0))

I cant seem to understand how it works

thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 09:23:49

dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0))

Will give you the first day of the previous month and

dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0))

Will give you the last day of the previous month at 3ms before midnight...

run the following in QA and you will see the results...


select dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0))

select dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0))

amgrace
Starting Member

30 Posts

Posted - 2004-06-03 : 10:35:47

Thanks very much....this way I won't ever need to change the date before running the procedure.

I've just heard that the request for the duplicate data check might be changed to 3 months in arrears.
e.g
in April 2004, I am checking duplicates made during January 2004. In May 2004, I am checking February 04 data, In June 2004, I am checking March 2004 data, and so on.

I need to make the code get data from 3 months before.

I changed your start-of-month code to the one below and it gave me the start of 3 months ago (march) - which is what i hoped for:

select dateadd(mm,-3,dateadd(mm,datediff(mm,0,getdate()),0))

2004-03-01 00:00:00.000

but I can't seem to change the end-of-month one successfully. Please help.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-04 : 04:09:25

So what's wrong with:-


select dateadd(mm,-2,dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0)))

amgrace
Starting Member

30 Posts

Posted - 2004-06-04 : 07:58:21

code works perfectly. Thanks.

output
2004-03-31 23:59:59.997

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources