• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Help in reseting based on year

Morning experts I got help with the following code below. It works fine, but I need to change it to also reset based on year and value based on another column
/*-------------------------- SOLUTION -----------------------------------*/

--Declare Local Variables
DECLARE @CASE_TYPE VARCHAR(50), @CASE_NUMBER INT , @IncCASE_NUMBER INT, @IA_LOG_NUMBER INT

-- We are first going to get the unique CaseType AND Max value of CaseNumber for each CaseType from the existing Table
DECLARE cursorCases cursor fast_forward FOR 
Select CASE_TYPE, 
      CASE WHEN MAX(CASE_NUMBER) IS NULL 
      THEN 0 
      ELSE  MAX(CASE_NUMBER) 
      END CASE_NUMBER 
from  dbo.LOG_CASE_MAPPING_2012_2
GROUP BY CASE_TYPE 
ORDER BY CASE_TYPE

-- Open the first cursor.
OPEN cursorCases
-- Get the first record.
FETCH cursorCases INTO  @CASE_TYPE, @CASE_NUMBER
-- Start processing loop.
WHILE @@Fetch_Status = 0
      BEGIN
            
            -- Now Declare a second Cursor for each CaseType = @CASETYPE that has Casenumber = NULL
            DECLARE cursorNullCaseNumberCases cursor fast_forward FOR 
            
            Select CASE_TYPE, CASE_NUMBER,IA_LOG_NUMBER 
            FROM  dbo.LOG_CASE_MAPPING_2012_2
            WHERE CASE_NUMBER IS NULL and CASE_TYPE = @CASE_TYPE 
            ORDER BY  IA_LOG_NUMBER
            
            --Increment the Case Number by 1 for @CASETYPE
            SET @IncCASE_NUMBER = @CASE_NUMBER + 10000
            
            -- Open the first cursor.
            OPEN cursorNullCaseNumberCases
            -- Get the first record.
            FETCH cursorNullCaseNumberCases INTO @CASE_TYPE, @CASE_NUMBER, @IA_LOG_NUMBER
            -- Start processing loop.
            WHILE @@Fetch_Status = 0
                  BEGIN
                        UPDATE dbo.LOG_CASE_MAPPING_2012_2
                           SET  [CASE_NUMBER] = @IncCASE_NUMBER
                        WHERE [IA_LOG_NUMBER] = @IA_LOG_NUMBER
                        
                        --Increment the Case Number again.
                        SET @IncCASE_NUMBER = @IncCASE_NUMBER + 1

                        -- Get next record from cursor cursorNullCaseNumberCases.
                        FETCH cursorNullCaseNumberCases INTO @CASE_TYPE, @CASE_NUMBER, @IA_LOG_NUMBER
                  END
            -- Close the cursor cursorNullCaseNumberCases
            CLOSE cursorNullCaseNumberCases

            -- Remove cursor cursorNullCaseNumberCases from memory
            DEALLOCATE cursorNullCaseNumberCases

            -- Get next record from cursor cursorCases.
            FETCH cursorCases INTO  @CASE_TYPE, @CASE_NUMBER
      END

-- Close the cursor
CLOSE cursorCases

-- Remove cursor from memory
DEALLOCATE cursorCases

/*-------------------------- END SOLUTION -------------------------------*/

Open in new window


I'm not a programmer, and a friend wrote the above, but is now on vacation back in Europe and need help. Thanks to all ahead of time for the time and effort.
0
Tech315
Asked:
Tech315
  • 4
  • 4
1 Solution
 
nemws1Database AdministratorCommented:
I'm guessing you'll be modifying lines 28-31 of your code, as that is the query that selects the rows to be updated.

Can you describe *how* the year and "another column" will be used?  (please give a specific example)
0
 
BAKADYCommented:
if you aren't a programmer and you want help and that someone rewrite your code, please describe better what the program has to do and post some examples of your data, it means how the input looks like and how looks your desired output, in your case "UPDATE" how your data looks before and after this program run.

Regards
0
 
Tech315Author Commented:
What the program does is that it goes through the table and place an incrementing number, where it is null, I forgot to mention that Case year has to reflect what the LOG_YEAR is also if case_year is null

CURRENT OUTPUT
IA_LOG_NUMBER   IA_LOG_YEAR CASE_YEAR  CASE_TYPE  CASE_NUMBER
00001                      2012                NULL            CC               NULL
00002                      2012                NULL            OG               NULL
00003                      2012                NULL            OG               NULL
00004                      2012                NULL            OG               NULL
00005                      2012                NULL            OG               NULL
00006                      2012                NULL            OG               NULL
00007                      2012                NULL            OG               NULL
00008                      2012                NULL            OG               NULL

What I need
IA_LOG_NUMBER   IA_LOG_YEAR CASE_YEAR  CASE_TYPE  CASE_NUMBER
00001                      2012                2012            CC              10000
00002                      2012                2012            OG              10000
00003                      2012                2012            OG               10001
00004                      2012                2012            OG               10002
00005                      2012                2012            OG               10003
00006                      2013                2013            OG              10000
00007                      2013                2013            OG              10001
00008                      2013                2013            OG               10002
00009                      2013                2013            OG               10003
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
BAKADYCommented:
Test Input Data:
IA_LOG_NUMBER IA_LOG_YEAR CASE_YEAR CASE_TYPE CASE_NUMBER
00001         2012        NULL      CC        NULL
00002         2012        NULL      OG        NULL
00003         2012        NULL      OG        NULL
00004         2012        NULL      OG        NULL
00005         2012        NULL      OG        NULL
00006         2013        NULL      OG        NULL
00007         2013        NULL      OG        NULL
00008         2013        NULL      OG        NULL
00009         2013        NULL      OG        NULL
00010         2013        2013      TS        10000
00011         2013        NULL      TS        NULL
00012         2013        NULL      TS        NULL
00013         2013        NULL      TS        NULL

Open in new window

I added TS CaseType for test reasons...

This are the updated records:
IA_LOG_NUMBER IA_LOG_YEAR CASE_YEAR CASE_TYPE CASE_NUMBER
00001         2012        2012      CC        10000
00002         2012        2012      OG        10000
00003         2012        2012      OG        10001
00004         2012        2012      OG        10002
00005         2012        2012      OG        10003
00006         2013        2013      OG        10000
00007         2013        2013      OG        10001
00008         2013        2013      OG        10002
00009         2013        2013      OG        10003
00011         2013        2013      TS        20000
00012         2013        2013      TS        20001
00013         2013        2013      TS        20002

Open in new window

If all this is correct, here is how your end data has to look like:
IA_LOG_NUMBER IA_LOG_YEAR CASE_YEAR CASE_TYPE CASE_NUMBER
00001         2012        2012      CC        10000
00002         2012        2012      OG        10000
00003         2012        2012      OG        10001
00004         2012        2012      OG        10002
00005         2012        2012      OG        10003
00006         2013        2013      OG        10000
00007         2013        2013      OG        10001
00008         2013        2013      OG        10002
00009         2013        2013      OG        10003
00010         2013        2013      TS        10000
00011         2013        2013      TS        20000
00012         2013        2013      TS        20001
00013         2013        2013      TS        20002

Open in new window

is that right??

Than that is is your new code
WITH MAX_CASE_NUMBER (CASE_TYPE, CASE_NUMBER) AS (
  SELECT CASE_TYPE, 10000 +
        CASE WHEN MAX(CASE_NUMBER) IS NULL 
        THEN 0 
        ELSE  MAX(CASE_NUMBER) 
        END CASE_NUMBER 
  FROM  LOG_CASE_MAPPING_2012_2
  GROUP BY CASE_TYPE 
), TO_UPDATE_CASES (IA_LOG_NUMBER, IA_LOG_YEAR, CASE_YEAR, CASE_TYPE, CASE_NUMBER) AS (
  SELECT LOG.IA_LOG_NUMBER
      , LOG.IA_LOG_YEAR
      , LOG.IA_LOG_YEAR AS CASE_YEAR
      , LOG.CASE_TYPE
      , SEL.CASE_NUMBER - 1 + ROW_NUMBER() OVER (PARTITION BY LOG.IA_LOG_YEAR, LOG.CASE_TYPE ORDER BY LOG.IA_LOG_NUMBER) AS CASE_NUMBER
  FROM  LOG_CASE_MAPPING_2012_2 LOG, MAX_CASE_NUMBER SEL
  WHERE LOG.CASE_NUMBER IS NULL and LOG.CASE_TYPE = SEL.CASE_TYPE 
)
UPDATE
  LOG_CASE_MAPPING_2012_2
SET
  CASE_YEAR = SEL.IA_LOG_YEAR,
  CASE_NUMBER = SEL.CASE_NUMBER
FROM  LOG_CASE_MAPPING_2012_2 LOG, TO_UPDATE_CASES SEL
WHERE LOG.IA_LOG_NUMBER = SEL.IA_LOG_NUMBER

Open in new window

0
 
Tech315Author Commented:
Yep, correct
0
 
BAKADYCommented:
this should be faster too... ;)
can you tell me if the code is working properly for you too???

i have a question about  this line, this increment your caseno  by 10000 and not by 1, is it correct???

            --Increment the Case Number by 1 for @CASETYPE
            SET @IncCASE_NUMBER = @CASE_NUMBER + 10000
See this lines:
IA_LOG_NUMBER IA_LOG_YEAR CASE_YEAR CASE_TYPE CASE_NUMBER
00010         2013        2013      TS        10000
00011         2013        2013      TS        20000

Open in new window

0
 
Tech315Author Commented:
Hey Bakaday, awesome its quick and it works thanks for your time and effort
0
 
Tech315Author Commented:
B your correct in the increment of 10000. Thanks again. Wish I had your talent :)
0
 
BAKADYCommented:
I asked this because if your input is like this:
IA_LOG_NUMBER IA_LOG_YEAR CASE_YEAR CASE_TYPE CASE_NUMBER
00010         2013        2013      TS        10000
00011         2013        2013      TS        10001
00012         2013        NULL      TS        NULL
00013         2013        NULL      TS        NULL

Open in new window

Your will get this output:
IA_LOG_NUMBER IA_LOG_YEAR CASE_YEAR CASE_TYPE CASE_NUMBER
00010         2013        2013      TS        10000
00011         2013        2013      TS        10001
00012         2013        2013      TS        20001
00013         2013        2013      TS        20002

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now