June 9, 2016 at 10:40 am
Hello,
I need to know what is the logic that is going on each statement.
SET @Pay = (SELECT MAX(Id) FROM dbo.PayFeCon WHERE Status = 'F')
SET @CycDate = (SELECT CycleDate FROM dbo.PayFeCon WHERE ID = @Pay and Status = 'F')
SET @PayCycle = (SELECT Pay_Cycle FROM dbo.Period WHERE End_Dt = @CycDate)
SET @TimeProcessDate = (SELECT Process_Date FROM Calendar WHERE Payroll_Cycle = @PayCycle)
SET @Begin_Dt = (SELECT Begin_Dt FROM dbo.Calendar WHERE Payroll_Cycle = @PayCycle)
June 9, 2016 at 10:53 am
Maybe start by telling us what you think is the logic. Not sure of the question, though... One usually starts with a question about what one wants to see from the database and then writes the query... this seems backwards.
June 9, 2016 at 10:53 am
mcfarlandparkway (6/9/2016)
Hello,I need to know what is the logic that is going on each statement.
SET @Pay = (SELECT MAX(Id) FROM dbo.PayFeCon WHERE Status = 'F')
SET @CycDate = (SELECT CycleDate FROM dbo.PayFeCon WHERE ID = @Pay and Status = 'F')
SET @PayCycle = (SELECT Pay_Cycle FROM dbo.Period WHERE End_Dt = @CycDate)
SET @TimeProcessDate = (SELECT Process_Date FROM Calendar WHERE Payroll_Cycle = @PayCycle)
SET @Begin_Dt = (SELECT Begin_Dt FROM dbo.Calendar WHERE Payroll_Cycle = @PayCycle)
Well, it is quite obvious to me what is occurring in each statement, and that there is a definite order in which they need to be processed.
I'd be curious what you think is going on in each statement before providing an answer to what may be either homework or an interview.
June 9, 2016 at 11:03 am
Hello Insane,
I understand, these are very basic statements,but my concern is like I am designing an activity diagram I just stpped here like inorder to mention some matter into the flow diagram I am asking the logic.
June 9, 2016 at 11:06 am
mcfarlandparkway (6/9/2016)
Hello Insane,I understand, these are very basic statements,but my concern is like I am designing an activity diagram I just stpped here like inorder to mention some matter into the flow diagram I am asking the logic.
And we are asking you to tell us what you think is going on here. Just want you to put in some of the effort to know what is going on rather than just telling you.
This is exactly what I would do if we were talking face to face.
June 9, 2016 at 11:15 am
In @pay we are getting max id from payfecon table where status is f.
and we get cycdate from payfecon where id is the @pay that we got maxid form the @pay.and status Is f
we get paycycle from period where endata is the date that we got from cycdate.
we get processdate from calendar where payrollcycle is the cycle which we got from period table where end date is cycdate..
begindate is the date we are getting from paycycle.
Basically its going reverse engineering.
June 9, 2016 at 11:22 am
Each statement is assigning a value to a variable. This values will be lost after the batch ends, so basically, they're not doing anything useful unless they're combined with something else.
June 9, 2016 at 11:25 am
I would check to see that when you're doing an assignment like that, are you returning a single value... Does all the code work as expected?
Normally, I would document this kind of thing inside the code comments, because if I screw up, it's obvious what I meant to do, even if I made a mistake in the coding. For example, if I create a variable and use a query to assign a value to it, what if the query can return more than one value?
June 9, 2016 at 11:29 am
The logic I see is a cascading calculation from some row in a table with an "F" in it, up to a value from another table.
If the variables aren't used for anything other than this calculation, the whole thing could be a single SQL statement.
So, the logic depends on the context.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2016 at 11:36 am
TY Champion,
These variables we are using in the joins of the select statement,I am preparing a flow diagram for this procedure. I just need the flow that's it ,I mean in order to put in activity diagram.
June 9, 2016 at 11:43 am
mcfarlandparkway (6/9/2016)
TY Champion,These variables we are using in the joins of the select statement,I am preparing a flow diagram for this procedure. I just need the flow that's it ,I mean in order to put in activity diagram.
Looks to me that they are in the proper order as posted. This does assume that the values of the variables are subsequently used in the code that follows the SET statements.
June 9, 2016 at 11:50 am
mcfarlandparkway (6/9/2016)
TY Champion,These variables we are using in the joins of the select statement,I am preparing a flow diagram for this procedure. I just need the flow that's it ,I mean in order to put in activity diagram.
Use the order they are in the script you provided. It's just a "find this from this" cascade. The first one tells the second one what to look for. The second tells the third what to look for. Etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply