Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    8

    Lightbulb Unanswered: Updating Store Proc to necessary requirement

    Hi all, im new to Sql and finding some issues on my current sproc'

    The report needs updating to include changes as well as new entries.

    The columns that can be used are
    Last_Modify_Date
    Create_Date


    SELECT
    of_Skills, LEFT(ac_nAME,25) Site, LEFT( A.OF_PIN, 5) Pin, LEFT(OF_DISPLAYNAME, 25) Name, license_no, a.Last_Modify_Date,
    CASE Expiry_Date

    WHEN '31 Dec 2099' THEN ''
    WHEN '1 Jan 2066' THEN ''
    WHEN '1 Jan 2099' THEN ''
    WHEN '1 Dec 2099' THEN ''
    WHEN '31 Dec 2066' THEN ''

    ELSE CONVERT(CHAR(11),Expiry_Date,106)
    END 'Expiry Date', CONVERT(CHAR(11),a.create_date,106) 'Date Added',
    CASE ISNULL(E.CA_CandidateID, 0) WHEN 0 THEN '' ELSE 'Recruitment' END Source
    FROM
    HR.dbo.hrst_License a ,
    HR.dbo.HRST_Officer D
    LEFT OUTER JOIN RS.dbo.RST_Candidate E ON E.CA_CandidateID = D.Candidate_No
    AND EXISTS (SELECT CL_CandidateLicenceID
    FROM RS.dbo.RST_CandidateLicence
    WHERE CL_CandidateID = E.CA_CandidateID AND CL_LicenseNo IS NOT NULL),
    Rolecall.dbo.officer b
    LEFT OUTER JOIN (SELECT
    TO_Pin, AC_Pin, Ac_Name
    FROM
    Rolecall.dbo.Activity,
    Rolecall.dbo.Template
    WHERE
    AC_Pin = TO_Activity
    AND UNICODE(To_Type) = UNICODE('M')) C ON Of_pin = To_Pin

    WHERE
    a.of_pin =b.of_pin
    AND of_payroll_no in ('s','w')
    AND d.of_pin = a.of_pin
    AND (of_dol is NULL OR of_Dol > GETDATE())
    AND a.create_date > getdate()-1

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This is just a re-formatting of your code (no changes), so the regulars can get a better idea of it.
    Code:
    SELECT of_Skills, 
    	LEFT(ac_nAME,25) 
    	Site, 
    	LEFT( A.OF_PIN, 5) Pin, 
    	LEFT(OF_DISPLAYNAME, 25) Name, 
    	license_no, 
    	a.Last_Modify_Date,
    	CASE Expiry_Date
    		WHEN '31 Dec 2099' THEN ''
    		WHEN '1 Jan 2066' THEN ''
    		WHEN '1 Jan 2099' THEN ''
    		WHEN '1 Dec 2099' THEN ''
    		WHEN '31 Dec 2066' THEN ''
    	ELSE CONVERT(CHAR(11),Expiry_Date,106) END 'Expiry Date', 
    	CONVERT(CHAR(11),a.create_date,106) 'Date Added',
    	CASE ISNULL(E.CA_CandidateID, 0) 
    		WHEN 0 THEN '' 
    	ELSE 'Recruitment' END Source
    FROM HR.dbo.hrst_License a ,
    	HR.dbo.HRST_Officer D LEFT OUTER JOIN 
    	RS.dbo.RST_Candidate E ON E.CA_CandidateID = D.Candidate_No
    		AND EXISTS (SELECT CL_CandidateLicenceID
    			FROM RS.dbo.RST_CandidateLicence
    			WHERE CL_CandidateID = E.CA_CandidateID 
    			  AND CL_LicenseNo IS NOT NULL),
    	Rolecall.dbo.officer b LEFT OUTER JOIN 
    	(SELECT TO_Pin, AC_Pin, Ac_Name
    	FROM Rolecall.dbo.Activity,
    		Rolecall.dbo.Template
    	WHERE AC_Pin = TO_Activity
    	  AND UNICODE(To_Type) = UNICODE('M')) C ON Of_pin = To_Pin
    WHERE a.of_pin =b.of_pin
      AND of_payroll_no in ('s','w')
      AND d.of_pin = a.of_pin
      AND (of_dol is NULL OR of_Dol > GETDATE())
      AND a.create_date > getdate()-1
    So, you wanted what, then? New and changed Licenses, Officers, Candidates, or maybe templates? So many choices.

  3. #3
    Join Date
    Jun 2014
    Posts
    8

    Reply

    at the moment entries in the database are updated via another system, where a user can change the license number , which updates the same table which is being called in the sproc. The problem is that when the report is being pulled off, it isn't picking up the updated field. Instead it is getting the old information.

    So i thought by adding the last modified date column i could issue it to pick up the values at a certain time, just before the report pulls out at 7:00pm

Tags for this Thread

Posting Permissions

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