Strategy for n-level approvals

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Strategy for n-level approvals

Strategy for n-level approvals

Rate Topic

Display Mode

Topic Options

Author

Message

JimS-Indy

Posted Friday, March 08, 2013 10:50 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317

I have a TimesheetMaster table which requires 1 or more "approvals". I've created an eventlog table to keep track of approval events. Now, if any approver rejects a timesheet, the approval process must begin again, but I don't want to lose track of all the events.

If I didn't care about keeping track of all the events, I could just purge the approval events whenever there was a rejection event. What would your strategy be?

One idea I had was to code every approval as "current", or "expired", so a rejection event would, instead of deleting all related events, would just mark them as "expired". My program would seek out only "current" approvals to see if the timesheet is finally approved.

Other cool ideas? You guys always have the neatest tricks....

Jim

Post #1428702

Lowell

Posted Friday, March 08, 2013 11:40 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

JimS-Indy (3/8/2013)

Jim i would think a view, which selects the data would be the way to do it; the view can join your rejection events, and return an items current status based on the join, right? no need to purge ort delete data, i think, just group or row_number to get the right data.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1428715

JimS-Indy

Posted Friday, March 08, 2013 12:20 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317

Lowell, not quite understanding. Here's what I think you said...

SELECT * from tblEvents 
WHERE (tblEvents.EventDateTime  > 
  (Select Max(EventDateTime) from tblEvents 
    WHERE EventType='Rejection' and TimesheetID=23))
AND tblEvents.TimesheetID=23

Jim

Post #1428728

Lowell

Posted Friday, March 08, 2013 12:37 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

Jim i am thinking something more like this, i think; i'd need the DDL and sample data to really make a solid mockup

CREATE VIEW VW_TimeSheetStatus
AS

SELECT 
CASE 
  WHEN tblRejections.EventDateTime > tblEvents.EventDateTime
	THEN 'Rejected'
	ELSe 'Approved'
END As Status,
tblRejections.EventDateTime As RejectionDate,
tblEvents.*
from (SELECT Max(EventDateTime),TimesheetID,OtherColumns tblEvents WHERE EventType <>'Rejection' GROUP BY TimesheetID,OtherColumns) tblEvents
LEFT OUTER JOIN (SELECT Max(EventDateTime),TimesheetID,OtherColumns tblEvents WHERE EventType ='Rejection' GROUP BY TimesheetID,OtherColumns) tblRejections
ON tblEvents.TimesheetID = tblRejections.TimesheetID

then i could query the view, like SELECT * FROM VW_TimeSheetStatus WHERE TimesheetID=23, and i'd see the status automatically, right?
i might need to modify the grouping, but that's the gist of what i was thinking.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1428736

JimS-Indy

Posted Friday, March 08, 2013 12:51 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317

I probably wasn't clear (or else, just as likely, I didn't understand your answer...)

Each event has an event type (typically, 'Reject' or 'Approve'.) The application knows to expect, say, two approvals. The number of approvals (actually, approval types...) is known only to the TimesheetMaster, and enforced by the app.

If a timesheet is approved, then subsequently rejected, all prior approvals must be re-approved, so they're no longer valid. So, I want to be able to select only approvals that have taken place since the last rejection (if any....) The app will need to examine all of 'em to see if they're of the correct type and number, so the app will want a subset of the total number of events.

I feel like the scrap of SQL I suggested does that (except for the "if any..." degenerate case, which should be easy to resolve....)

If I understand your SQL, it's looking to make a decision 'Approved' or 'Rejected', as a scalar value...??

Jim

Post #1428741

« Prev Topic | Next Topic »

Permissions