March 8, 2013 at 10:50 am
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
March 8, 2013 at 11:40 am
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
March 8, 2013 at 12:20 pm
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
March 8, 2013 at 12:37 pm
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
March 8, 2013 at 12:51 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply