Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8

    Question Unanswered: create procedure Help (CloseTheseCases)

    I Have a Created a View Query(msaccess Hat)

    Call CloseTheseCases

    It has the caseID of the ones I want to Close (put now() into Cdate (CloseDate feild) in the Cases Table

    So My Thinking is schedule it to run once a Day Close the casees in the Cases table by using the CloseTheseCases Query


    so my first problem is
    write the Procedure that does that
    can someone point down the right track
    Last edited by myle; 12-03-09 at 16:04. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What platform are you writing this in?

    Can you describe in process/business terms what you are trying to do

    How does on "Close a case"?

    On what event or update of data?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    OK
    The front end is a web application

    backend is SQL 2005 server

    so my thinking was use SQL 2005 and use the schedule inside that do the work for me



    what the site does it help User manage there clients I was told Last month
    that any Client cases that have not been Look at for 3 months have to be closed and that where the "CloseTheseCases" query came from.

    I have built it as a .asp page but I have run manually
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by myle View Post
    any Client cases that have not been Look at for 3 months have to be closed:

    OK...warmer....(playing hot & cold)

    HOW do you know that?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    ok

    when a case is open task/jobs are added to it

    so what I have done "CloseTheseCases" look at of the Open Case join the Task/jobs show me me the last Task/job done do a day count base on the last task/job if > 90 days
    show me it .

    CloseTheseCases gives me the right data just need to loop though these records and put today date in the Cdate feild base on the CASEID form the "CloseTheseCases" query
    Last edited by myle; 12-03-09 at 18:31. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    ok
    Been a Long couple of days (Lot of reading)

    this what I can do close but not there yet

    This Does what I Want Update but I have to tell it what CaseID I want to close
    Code:
    ALTER PROCEDURE Closeit(@CaseID Int)
    AS
    BEGIN
    DECLARE @TOODAY datetime;
    SET @TOODAY = GETDATE();
    END
    UPDATE SDST
    SET CDATE=@TOODAY
    WHERE SDSTID = @CaseID
    RETURN

    This Below also works show me all the OPen case that have not been edited for more than 3 months
    Code:
    ALTER PROCEDURE CloseALL
    AS
    SELECT [Caseto close].caseID, DATEDIFF(month, [Last Edited], { fn NOW() }) AS MM
    FROM  [Caseto close]
    WHERE (DATEDIFF(month, [Last Edited], { fn NOW() }) > 3)
    
    RETURN
    Question now
    who do I get CloseALL to pass the CASEID to the Closeit PROCEDURE

    I have a feeling that it can be done in one PROCEDURE I have no idea how
    Last edited by myle; 12-06-09 at 01:02. Reason: spellng
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    So after some more reading
    I have this
    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    SET NOCOUNT ON
    
    DECLARE @CASEID int
    DECLARE @mm int
    DECLARE @mess varchar(80)
    DECLARE @TOODAY datetime;
    SET @TOODAY = GETDATE();
    DECLARE my_Case CURSOR FOR
    SELECT    [Caseto close].caseID, DATEDIFF(month, [Last Edited], { fn NOW() }) AS MM
    FROM         [Caseto close]
    WHERE     (DATEDIFF(month, [Last Edited], { fn NOW() }) > 3)
    
    OPEN my_Case
    FETCH NEXT FROM my_case
    INTO @CASEID,@MM
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    UPDATE SDST
    SET CDATE=@TOODAY
    WHERE SDSTID = @CaseID
    FETCH NEXT FROM my_Case
    END 
    CLOSE my_Case
    DEALLOCATE my_Case
    GO
    or am getting on the wrong boat
    Last edited by myle; 12-06-09 at 14:49. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    After some more reading I think I have Done it

    Could some have a look at this and tell if its OK

    I don't know what I done I can 1/2 understand it

    Its work on the test server OK


    Code:
    ALTER PROCEDURE Closethem
    --******************************************************
    -- Created By StePhan Mckillen
    -- This will Close all cases that have been open for more 90 days
    -- and not been edited
    --*****************************************************
    AS
    SET NOCOUNT ON
    DECLARE @CASEID int
    DECLARE @mm int
    DECLARE @mess varchar(80)
    DECLARE @TOODAY datetime;
    SET @TOODAY = GETDATE();
    DECLARE my_Case CURSOR FOR
    SELECT [Casetoclose].caseID, DATEDIFF(day, [Last Edited], { fn NOW() }) AS MM
    FROM [Casetoclose]
    WHERE (DATEDIFF(day, [Last Edited], { fn NOW() }) > 90);
    
    OPEN my_Case
    FETCH my_case INTO @CASEID,@MM
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	UPDATE SDST
    	SET CDATE=@TOODAY
    	WHERE SDSTID = @CASEID
    	FETCH my_Case INTO @CASEID,@MM
    END 
    CLOSE my_Case
    DEALLOCATE my_Case 
    RETURN
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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