SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strategy for n-level approvals


Strategy for n-level approvals

Author
Message
JimS-Indy
JimS-Indy
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1045 Visits: 444
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
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70306 Visits: 40923
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
JimS-Indy
JimS-Indy
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1045 Visits: 444
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
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70306 Visits: 40923
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
JimS-Indy
JimS-Indy
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1045 Visits: 444
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search