Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Strategy for n-level approvals Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 10:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 320, Visits: 440
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
Posted Friday, March 8, 2013 11:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 12,915, Visits: 32,075
JimS-Indy (3/8/2013)
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 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
Posted Friday, March 8, 2013 12:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 320, Visits: 440
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
Posted Friday, March 8, 2013 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 12,915, Visits: 32,075
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
Posted Friday, March 8, 2013 12:51 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 320, Visits: 440
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 »

Add to briefcase

Permissions Expand / Collapse