Stored Procedure

Question

Hello,

i'm stuck with creating a stored procedure that should update a calculated column daily. Unfortunately my knowledge about stored procedures is limited but it seems to be the right place. I try to give more background informations:

The Main-Table stores claims and is called tabData. It has a column 'IsReturn' with datatype bit(boolean).

1.Rule: a claim is a Return when its column-value fimaxActionCode is one of these 1, 2, 3, 4, 5, 8, 9, 12, 14, 17, 18, 20, 21, 22, 23, 24, 25, 30, 31, 32, 35.

2.Rule: a claim is not a Return when it has a previous claim(identified through a date value and an IMEI(Id-Number for mobiles) with fimaxActionCode of 8, 10, 11, 13, 19, 23, 24, 26, 27, 28, 29, 30, 33, 34, 36, 37 in fiClaimeStatus 1, 4, 6, 254, 255.

The first Return-Rule was the old one and is implemented as a calculated column in our ssas cube. Because things are more complicated now i thought it would be better to put that calculation in a stored procedure which is executed every morning.

This is what i have to check if a candidate claim(Rule 1) fails with Rule 2: I have created a View 'PrevClaim' which selects all previous claims:

SELECT     TOP (100) PERCENT Claim.idData AS ClaimID, PrevClaim.idData AS PrevClaimID, Claim.IMEI, 
                      Claim.Repair_Completion_Date AS ClaimRepDate, PrevClaim.Repair_Completion_Date AS PrevClaimRepDate, 
                      PrevClaim.fimaxActionCode AS PrevFiMaxActionCode, PrevClaim.fiClaimStatus AS PrevFiClaimStatus, Claim.IsReturn AS ClaimIsReturn
FROM         dbo.tabData AS Claim INNER JOIN
                      dbo.tabData AS PrevClaim ON Claim.IMEI = PrevClaim.IMEI AND Claim.idData <> PrevClaim.idData AND 
                      Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date
ORDER BY PrevClaimRepDate DESC

I can get the last Parent-Claim for a given ID with this sql-statement(this claim must be validated against Rule 2):

SELECT     TOP (1) ClaimID, PrevClaimID, IMEI, ClaimRepDate, PrevClaimRepDate, PrevFiMaxActionCode, PrevFiClaimStatus
FROM         PrevClaim
WHERE     (ClaimID = 44921287)
ORDER BY PrevClaimRepDate DESC

But now i dont know how/where to check if this parent-claim was a claim that fails on Rule 2. If i modify this sql and put the condition into the where clause i will get the wrong claim(consider there are 4 parent claims, the last in time is ok but the fourth fails, hence i will get the fourth back but it must be checked against the last):

SELECT     TOP (1) ClaimID, PrevClaimID, ClaimRepDate, PrevClaimRepDate, PrevFiMaxActionCode, PrevFiClaimStatus
FROM         PrevClaim
WHERE     (ClaimID = 44921287) AND (PrevFiMaxActionCode IN (8, 10, 11, 13, 19, 23, 24, 26, 27, 28, 29, 30, 33, 34, 36, 37)) AND (PrevFiClaimStatus IN (1, 4, 254, 255, 6))
ORDER BY PrevClaimRepDate DESC

I'm going in circles or maybe its too hot for something like that. I hope somebody could lead me in the right direction and/or could show me the way how to implement it in a stored procedure(UPDATE...CASE but how?).

EDIT: I think i'm on the right track now. Maybe somebody could show me the way how to Update the whole Table in one sql-statement. Following user defined function gives me the right value:

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo._isClaimReturn 
(
    @claimID  int,
    @fimaxActionCode int
)
RETURNS int
AS
BEGIN
 DECLARE @isReturn int

  IF(@fimaxActionCode in (1, 2, 3, 4, 5, 8, 9, 12, 14, 17, 18, 20, 21, 22, 23, 24, 25, 30, 31, 32, 35))
  AND (SELECT     TOP (1) PrevFiMaxActionCode
  FROM   PrevClaim
  WHERE     (ClaimID = @claimID))IN(8, 10, 11, 13, 19, 23, 24, 26, 27, 28, 29, 30, 33, 34, 36, 37)
  AND 
  (SELECT     TOP (1)PrevFiClaimStatus
  FROM         PrevClaim
  WHERE     (ClaimID = @claimID))IN(1, 4, 254, 255, 6)
  BEGIN
   Set @isReturn = 0
  END
 ELSE
  BEGIN
   Set @isReturn = 1
  END 

 RETURN @isReturn

END
GO

Another question: is it possible to use only one query? In my function i use two queries on the PrevClaim-View to check if the Prev.Claim is in critical state and has a critical MaxActioncode.

Answer 1

Would something along the following lines be what you are looking for?

UPDATE tabData TD1
    SET TD1.IsReturn = 1
    WHERE TD1.fimaxActionCode IN (1, ...etc... , 35) AND
          NOT EXISTS (SELECT *
                          FROM tabData TD2
                          WHERE TD1.IMEI = TD2.IMEI AND
                                TD2.fimaxActionCode IN (8, ...etc... , 35) AND
                                TD2.fiClaimStatus   IN (1, 4, 254, 255, 6) AND
                                TD2.Repair_Completion_Date > TD1.Repair_Completion_Date);

What I think is the first rule is the the WHERE TD1.fimaxActionCode IN (1, ...etc... , 35) part. The query inside the NOT EXISTS is intended to check for the second rule. If I'm slightly wrong, you may be able to see your way to the correct solution. If I'm completely wrong...er...well, we'll try to sort something out, hoping I'm not sending you off in the wrong direction and wasting your time.

Edited to take into account the first comment.

Answer 2

You can create a new calculated column that uses the UDF you have listed in your edit. This would ensure the value is alway up to date.