Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Posts
    7

    Unanswered: Help with Stored Procedure/IF Statement

    Hey everyone!

    I have a stored procedure that returns employee id's and how many shifts they have signed up for between 2 dates.

    If they have less then 3 entries between the date range specified I would like update their status field to inactive.

    So what I'm getting at is how would I go about doing an IF statement that would check through the results of my stored procedure to see who has worked less then 3 shifts and to execute another stored procedure to update their status.

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    You can have the current stored procedure return the data in a table variable, then have the new procedure call the old procedure, then use the returned table as a "join table" in the update select.

    Probably confusing, I'll see if I can wrangle up an example real quick-lahk
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    There are other ways to skin this cat also...meaning if you don't HAVE to use the code in the existing stored procedure (or rather, that procedure itself) then you can just combine the select in the existing procedure into a new update statement. This can be done in a join or a sub-select in the update.

    It would help to have examples of your table structure(s) and code for specific suggestions.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    In fact, the code I found as an example is structured as a function that returns a table. That might actually be better than the stored procedure (since the stored procedure you have now can't be used as part of the actual new update statement.

    For example:
    Code:
    CREATE FUNCTION [dbo].[fn_FindLazyGuys] (@firstDate smalldatetime, @lastDate smalldatetime)
    RETURNS table 
    AS
    
    RETURN (  SELECT EmpID as LazyGuyID, count(*) as ShiftsWorked
    	      FROM dbo.ShiftLog(nolock)
    	      WHERE ShiftDate BETWEEN @startdate AND @enddate
    	      GROUP BY EMPID
    	      HAVING COUNT(*) < 3)
    GO
    then you can just put that in the insert as in:
    Code:
    	UPDATE ET
    	SET EmpStatus = 'inactive'
    	FROM dbo.EmpTable ET
    	INNER JOIN dbo.fn_FindLazyGuys (@startdaterange, @enddaterange) FLG
    	ON ET.EmpId = FLG.LazyGuyID
    [edit]for that matter, you could also just put the code from the funtion into the update statement as a join also. I guess it depends on what fits in with your schema and requrements.
    Last edited by TallCowboy0614; 08-07-07 at 16:56.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Aug 2007
    Posts
    7
    Whoa thank you sooo much for an example on this!

    Here is a copy of my stored procedure so far.

    It finds all the employee's with a specific position id (4) and that have only worked less then 3 shifts between now and a month ago. Could you perhaps help me convert this into a function as I have never used them?

    Code:
    SELECT     tblEmployee.EMPID, COUNT(tblShift.shift_date) AS count
    FROM         tblEmployee INNER JOIN
                          tblShift ON tblEmployee.EMPID = tblShift.shift_empid INNER JOIN
                          tblPosition ON tblEmployee.PositionID = tblPosition.PositionID
    WHERE     (tblShift.shift_date BETWEEN DATEADD(month, - 1, { fn NOW() }) AND { fn NOW() })
    GROUP BY tblEmployee.EMPID, tblEmployee.Status, tblPosition.PositionID
    HAVING      (tblPosition.PositionID = 4) AND (tblEmployee.Status = 0) AND (COUNT(tblShift.shift_date) < 3)

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    It's pretty easy, you just replace the SELECT in my example above with your SELECT in your last post. Although since you already know your date ranges, you can take out those parameters being passed into the stored proc.
    Code:
    CREATE FUNCTION [dbo].[fn_FindLazyGuys]
    RETURNS table 
    AS
    
    RETURN (  SELECT     tblEmployee.EMPID, COUNT(tblShift.shift_date) AS count
    FROM         tblEmployee INNER JOIN
                          tblShift ON tblEmployee.EMPID = tblShift.shift_empid INNER JOIN
                          tblPosition ON tblEmployee.PositionID = tblPosition.PositionID
    WHERE     (tblShift.shift_date BETWEEN DATEADD(month, - 1, { fn NOW() }) AND { fn NOW() })
    GROUP BY tblEmployee.EMPID, tblEmployee.Status, tblPosition.PositionID
    HAVING      (tblPosition.PositionID = 4) AND (tblEmployee.Status = 0) AND (COUNT(tblShift.shift_date) < 3)
    GO
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Aug 2007
    Posts
    7
    :/ I feel a bit dumb, thanks so much.

    One more thing atm that I am confused about

    Code:
    UPDATE ET
    	SET EmpStatus = 'inactive'
    	FROM dbo.EmpTable ET
    	INNER JOIN dbo.fn_FindLazyGuys (@startdaterange, @enddaterange) FLG
    	ON ET.EmpId = FLG.LazyGuyID
    What is ET? Is that Employee Table? and the FROM dbo.EmpTable ET is kinda tossing my mind around.

    Are you calling the update ET ? :/

    Thanks for your patience.

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by wildgoosed
    :/ I feel a bit dumb, thanks so much.

    One more thing atm that I am confused about

    Code:
    UPDATE ET
    	SET EmpStatus = 'inactive'
    	FROM dbo.EmpTable ET
    	INNER JOIN dbo.fn_FindLazyGuys (@startdaterange, @enddaterange) FLG
    	ON ET.EmpId = FLG.LazyGuyID
    What is ET? Is that Employee Table? and the FROM dbo.EmpTable ET is kinda tossing my mind around.

    Are you calling the update ET ? :/

    Thanks for your patience.
    no problem, I have a nearly limitless reserve of patience unless it's nearly empty.

    ET is just a way to reference a table in the select. In the FROM clause
    Code:
    	FROM dbo.EmpTable ET
    is where I associate the shorthand (or as BOL calls it, a table_alias) "ET" with the EmpTable table. SQL Server lets you do that if you want to, rather than having to use the complete table name in each reference to it in the query.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Aug 2007
    Posts
    7
    I have a slight problem with my query. If staff haven't worked a single shift it doesn't pick them up. How would I go about showing them in my query results?

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I guess you'd have to change the join to a left join to the table you have that contains all the employee ID's. Try doing that instead of the inner join? Sorry, I'm not at work where I have access to the DB's that would allow me to test.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Aug 2007
    Posts
    7
    The following query will get my all the staff who don't have shifts, but I'm finding it hard now to piece it all together, got an ideas?

    Code:
    SELECT     tblEmployee.EMPID
    FROM         tblShift AS tblShift_1 CROSS JOIN
                          tblEmployee
    WHERE     (NOT EXISTS
                              (SELECT DISTINCT shift_empid
                                FROM          tblShift AS tblShift_2
                                WHERE      (shift_empid = tblEmployee.EMPID)
                                GROUP BY shift_empid))
    GROUP BY tblEmployee.EMPID

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •