Solved

Help in reseting based on year

Posted on 2013-06-12
9
309 Views
Last Modified: 2013-06-12
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
Comment
Question by:Tech315
  • 4
  • 4
9 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39241260
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
 
LVL 4

Expert Comment

by:BAKADY
ID: 39241302
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
 

Author Comment

by:Tech315
ID: 39241383
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
 
LVL 4

Accepted Solution

by:
BAKADY earned 500 total points
ID: 39241905
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Tech315
ID: 39241971
Yep, correct
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39241983
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
 

Author Comment

by:Tech315
ID: 39242016
Hey Bakaday, awesome its quick and it works thanks for your time and effort
0
 

Author Comment

by:Tech315
ID: 39242026
B your correct in the increment of 10000. Thanks again. Wish I had your talent :)
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39242217
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now