Need Help with finding record and flaging them

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

General SQL Server Forums

New to SQL Server Programming

Need Help with finding record and flaging them

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-06-18 : 13:07:55

Hi:
I am building stored procedure that helps me find records older than 3 days and flagging those records. So, I can then, send an e-mail reminder to the Approvers than they have not yet approved those accounts. Please need help!!!!

If @SQLType = 'S'
BEGIN
 SELECT DISTINCT    a.Unique_Identifier, a.Requestor_Name,  a.Submitted, a.Submitted_by, a.Status, a.Authorized_by, a.Authorized_Datetime, a.flag, a.flag_date, a.Application_Name
FROM    DR_Authorization_Log_Velocity AS a INNER JOIN   DR_Approver_Email_Accounts AS b ON a.Application_Name = b.Application_Name_ID INNER JOIN
                      DR_Account_Approvers AS c ON c.UserId = b.Approver_UserID
WHERE     (b.Email_Type = '3') AND (a.Status IS NULL) AND (a.Submitted < { fn NOW() } - 3)
ORDER BY a.Submitted
End

The Table looks like this:

Unique_Identifier, Requestor_Name, Submitted, Submitted_by, Status, Authorized_by, Authorized_Datetime, flag, flag_date, Application_Name


Unique_Identifier--Requestor_Name---Submitted---Status----Flag--Flag_date--- Application_Name 
100                John Smith       05/01/2009   'NULL'                       Requesting MS Excel

*For example in this case this record was selected because the record is older than 3 days and the Status is NULL. I have at least 50 of these records. I need script that reads record by record set the flag to 1 once read it and set the Flag_date to today. So, I can then pick it up on .Net and send out the e-mail.

Thank you for your help!!!!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-18 : 13:35:40

Are you wanting to update the values of flag and flag date so that you can later select them?

Jim

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-18 : 13:40:20

I was not sure which table needed to be updated but, maybey this will help get you going:

UPDATE
	T
SET
	Flag = 1,
	Flag_Date = CURRENT_TIMESTAMP
FROM
	(
		SELECT *
		FROM <myTables>
		WHERE 
			Submitted < DATEADD(DAY, DATEDIFF(DAY, 3, CURRENT_TIMESTAMP), 0)
			AND Status IS NULL
	) AS T

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-06-18 : 13:54:38

Thank You. My objective is read record by record, of the already selected records from DR_Authorization_Log_Velocity(table) and then as I am reading each record, I will mark then down (flag) as I already read this record and date stamp the record on (flag_date) and then go to the next record, until I read all records.

Thank you for the help

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-18 : 14:05:30

You'll have to update them first and then read from them. I don't believe you can do both at the same time.

Jim

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-06-18 : 14:12:41

Thanks Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-18 : 14:18:05