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

SQL Expression for Crystal Reports XI

As some of you may be aware, Crystal Reports XI is able to use SQL expressions for reporting. I've asked this question o the CR XI forum but I also wanted to ask it here to you SQL Experts.

I use Crystal Reports XI and I need to create a SQL Expression for my report. I need the expression to ASSIGN a FILE TYPE to each FILENUMBER in my data table when I run the report for use in SORTING in a crosstab.

My data table looks like this:

FILENUMBER      APPLICABLEDATE                ACTIVITYCODE     STATUS CODE      SUBJECTCODE
100                           01/10/06                    TR                                                              ABC
100                           01/10/06                    RR                                                              ABC
100                           01/19/06                    PS                                                              ABC
100                           01/19/06                                                       PF                            ABC
100                           02/13/06                                                       HS                           ABC
100                           02/18/06                                                       OE                           ABC
100                           02/13/06                     RC                                                               ABC
100                           01/11/06                     TR                                                               DOG
100                           01/11/06                     RR                                                               DOG
100                           01/18/06                     PS                                                               DOG
100                           01/18/06                                                       PF                             DOG
100                           02/14/06                                                       HS                             DOG
100                           02/19/06                                                       OE                               DOG
101                           01/10/06                     TR                                                                 ABC
101                           01/10/06                     RR                                                                 ABC
101                           01/19/06                     PS                                                                 ABC
101                           01/19/06                                                       PF                               ABC
101                           02/13/06                                                       HS                             ABC
101                           02/18/06                                                       OE                             ABC
101                           02/13/06                       RC                                                               ABC
101                           01/11/06                      TR                                                               DOG
101                           01/11/06                       RR                                                               DOG
101                           01/18/06                      PS                                                               DOG
101                           01/18/06                                                        PF                             DOG
101                           02/14/06                                                        HS                             DOG
101                           02/19/06                                                       OE                               DOG



I need for the SQL Expression to be able to assign a LITIGATIONTYPE based on the COMBINATIONS of ACTIVITY AND STATUS CODES.

The expression needs to first of all isolate the data for the FILENUMBER, then isolate by SUBJECTCODE and then, do the calc in order to assign a LITIGATION TYPE.

The following type of calc needs to be done.

FOR each SUBJECT CODE within a FILENUMBER the following analysis needs to be done:

-      If  there are ACTIVITY CODES of TR, RR, RC and a STATUS CODE of PF… then this is a LIT COMPLETED TYPE.
-      If there are ACTIVITY CODES of TR, RR but no STATUS CODE of PF then this is a LIT STARTED
-      If there are ACTIVITY CODES of TR, RR,RC but no STATUS CODE of PF then this is NO LIT NEEDED

I have more types, but I think that if I can get the SQL Expression, I can plug in the other types myself.

NOTE:  There can be numerous SUBJECT CODES per file but basically each SUBJECT CODE  should have the ACTIVITY AND STATUS CODE data to use for the LITIGATION TYPE assignment.

PLEASE, PLEASE let me know if you need more information.


0
MIKE
Asked:
MIKE
  • 30
  • 16
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think I would make that a stored function:


create function dbo.GetLitigationType ( @FileNumber int , Subjectcode varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int

  select @count_tr = count(*) from yourtable where FileNumber = @FileNumber and SubjectCode = @SubjectCode and ActivityCode = 'TR'
  select @count_rr = count(*) from yourtable where FileNumber = @FileNumber and SubjectCode = @SubjectCode and ActivityCode = 'RR'
  select @count_rc = count(*) from yourtable where FileNumber = @FileNumber and SubjectCode = @SubjectCode and ActivityCode = 'RC'
  select @count_pf = count(*) from yourtable where FileNumber = @FileNumber and SubjectCode = @SubjectCode and [Status Code] = 'PF'

  IF (@TR>0 AND @RR>0 AND @RC>0 AND @PF>0)
   RETURN ('LIT COMPLETED')

  IF (@TR>0 AND @RR>0 AND @PF=0)
   RETURN ('LIT STARTED')

  IF (@TR>0 AND @RR>0 AND @RC>0 AND @PF=0)
   RETURN ('NO LIT NEEDED')
END

and use it like this:

select FILENUMBER      ,APPLICABLEDATE                ,ACTIVITYCODE     ,[STATUS CODE]      ,SUBJECTCODE
, dbo.GetLitigationType ( FileNumber ,SubjectCode ) as LitigationType
from yourtable t


note: this is untested code (even syntax might have errors, as it is written directly here)
note: you will need to ensure you have an index (1 index) on the fields FileNumber and SubjectCode
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
AAwwweeesoommmeeeeee

I'll work on this...I knew I could count on you experts.

Be in touch..


THANKS

Mike
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Question,  how do I assign an index here???
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
MIKESoftware Solutions ConsultantAuthor Commented:
Also, should this portion:

IF (@TR>0 AND @RR>0 AND @RC>0 AND @PF>0)
   RETURN ('LIT COMPLETED')

Read like this:

IF (@count_tr >0 AND @count_rr>0 AND @count_rc >0 AND @count_pf >0)
   RETURN ('LIT COMPLETED')

??? or is the @TR enough etc...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Question,  how do I assign an index here???

simply run this statement (once):
CREATE INDEX idx_yourtable ON ( Filenumber, SubjectCode )

>Also, should this portion read like ...
yes, absolutely. looks like my fingers where tooo lazy to type :-)
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
I don't control the indexes and I just looked at the indexes on this table. They are:

1 FILE NUMBER
2 ENTRY DATE
3 ENTRY TIME


So, is there any way around having to reset the index or is that the only way for this function to work? I can check with my IT dept to see if there is a problem changing the index to FILENUMBER, SUBCODE if needed, but it will just take more time to accomplish it.

NE way to program around it? Do you think changing the index will affect anything?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the function will work with or without the index. the index is only to ensure best performance.
as there is already an index on filenumber, that should be fine already.

if that is 1 single index on the 3 fields, don't change it.
if it's 3 indexes (with each one 1 field), you can ask to get the subcode added as second field to the index.
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
ok, not sure I understood your 3 indexes question, but the indexes on this table are (File Number, Entry Date, Entry Time)

I'll see how it goes.
Thx
 M
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Thanks angelIII,

Now it gets a little silly.

What if I need to check for one code OR another...

like this....

IF (@TR>0 AND @RR>0 AND @RC>0 AND @PF>0 OR @CR >0)
   RETURN ('LIT COMPLETED')

I realize that each new code must be declared, but are there special parens that need to be around EACH 'or' statment within this Command?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
depends on how you want to relate the OR to the others...

one possiblity: either @cr > 0  or all the others are > 0
IF ((@TR>0 AND @RR>0 AND @RC>0 AND @PF>0) OR @CR >0)

they must be all >0 , except @pf and @cr where only one of them needs to be >0
IF (@TR>0 AND @RR>0 AND @RC>0 AND (@PF>0 OR @CR >0) )

if you don't place the brackets, it's like the first one
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Ok.....


And if I want to count ONLY IF there is NO "RC' CODE, ..then do I use this type of format:

@RC = 0

??

There are time when I need to check to see if a code is NOT present and if NOT then i can count that as a certain type. The "rc" code in our world means that the Record is Completed.

so does =0 mean the same as NOT PRESENT???
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, not present will give you a NULL and not 0:
if @RC IS NULL

to "solve" that, change:
select @count_tr = count(*) ...
into this:
select @count_tr = sum(1) ...

this will avoid the NULL and have 0 instead if there is no row.
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Here is what I've come up with.... please review...but I think I have a problem in my thinking....that I need your help on.....see below


--------------------------------------------------------------------------------------------------------------------------
--Function to identify Litigation Types for Crystal Reporting
--Created by Mike Vega
--LIT COMPLETED =  TR/RR  + CR/PF/PS/OE  + RC    
--LIT STARTED =       TR/RR  +  CR/PF/PS/OE  + NO RC
--LIT REVIEW =          TR/RR, NO RC,PF,PS,CR,OE
--NO LIT NEEDED =   TR/RR/RC NO CR,PF,PS,OE
--LIT MISC =               ALL OTHER COMBINATIONS
-----------------------------------------------------------------------------------------------------------------------------
create function dbo.GetLitigationType ( @FileNumber int , Subject Code varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int
  declare @count_ps int
  declare @count_cr int
  declare @count_oe int
 
  select @count_tr =sum(1) from dbo.RLS where FileNumber = @FileNumber and Subject Code = Subject Code and Activity Code = 'TR'
  select @count_rr = sum(1) from dbo.RLS where FileNumber = @FileNumber and Subject Code = Subject Code and Activity Code = 'RR'
  select @count_rc = sum(1) from dbo.RLS where FileNumber = @FileNumber and Subject Code = Subject Code and Activity Code = 'RC'
  select @count_cr = sum(1) from dbo.RLS where FileNumber = @FileNumber and Subject Code = Subject Code and Activity Code = 'CR'
  select @count_ps = sum(1) from dbo.RLS where FileNumber = @FileNumber and Subject Code = Subject Code and Activity Code = 'PS'
  select @count_pf = sum(1) from dbo.RLS where FileNumber = @FileNumber and Subject Code = Subject Code and Status Code = 'PF'
  select @count_oe = sum(1) from dbo.RLS where FileNumber = @FileNumber and Subject Code = Subject Code and Status Code = 'OE'

  IF (@count_tr>0 AND @count_rr >0 AND @count_rc >0 AND (@count_pf>0 or @count_cr >0 or @count_ps >0 or @count_oe >0))
   RETURN ('LIT COMPLETED')
  IF (@count_tr>0 AND @count_rr >0 AND @count_rc >0 AND (@count_pf>0 or @count_cr >0 or @count_ps >0 or @count_oe >0))  
   RETURN ('LIT STARTED')
  IF (@count_tr >0 and @count_rr >0 and @count_rc >0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))
   RETURN ('NO LIT NEEDED')
  IF (@count_tr >0 and @count_rr >0 and (@count_rc =0 or@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))
   RETURN ('LIT REVIEW')
 else ('LIT MISC')
END

What I need to do is use a function like this to summarize or count my types of Litigation. I need a Litigation Activity Summary.

I need to count EACH separate LIT SUBJECT code as a SEPARATE Litigation. However, this function is based on the FILENUMBER....(which can contain several SUBJECT Codes) which really means that each FILE NUMBER can contain muliple LITIGATIONS.

So do you think that this will do that for me? My concern is that IF I use this function as I think it is designed,...it will be trying to assign a LIT TYPE on a PER FILE BASIS and NOT a per SUBJECT CODE BASIS.

Am I correct in thinking this..and if so...do you know how I can work around this........the bottomline is that I need the ANALYSIS to calculate LIT TYPES by.....SUBJECT CODES within EACH FILENUMBER.

THANK you so much for your expertise!

Mike
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Recieving this error when I try to run the above statement:


Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 10
Line 10: Incorrect syntax near 'Subject'.
Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 22
Line 22: Incorrect syntax near 'Code'.
Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 23
Line 23: Incorrect syntax near 'Code'.
Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 24
Line 24: Incorrect syntax near 'Code'.
Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 25
Line 25: Incorrect syntax near 'Code'.
Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 26
Line 26: Incorrect syntax near 'Code'.
Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 27
Line 27: Incorrect syntax near 'Code'.
Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 28
Line 28: Incorrect syntax near 'Code'.
Server: Msg 178, Level 15, State 1, Procedure GetLitigationType, Line 32
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Procedure GetLitigationType, Line 34
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Procedure GetLitigationType, Line 36
A RETURN statement with a return value cannot be used in this context.
Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 36
Line 36: Incorrect syntax near 'or@count_cr'.
Server: Msg 178, Level 15, State 1, Procedure GetLitigationType, Line 38
A RETURN statement with a return value cannot be used in this context.

HELP>..pls..

Thx
M
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please correct the following:

create function dbo.GetLitigationType ( @FileNumber int , Subject Code varchar(10))
...
select @count_tr =sum(1) from dbo.RLS where FileNumber = @FileNumber and Subject Code = Subject Code and Activity Code = 'TR'
etc

to this:
create function dbo.GetLitigationType ( @FileNumber int , @Subject_Code varchar(10))
select @count_tr =sum(1) from dbo.RLS where FileNumber = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'TR'

* you cannot have spaces in variables or parameter names.
* when you have spaces in table or column names, you have to put [] around them
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Syntax error converting VARCHAR value '0000-N-0000' to a column of data type INT


the '0000-N-0000' is our FILE NUMBER format...and evidentally it cannot deal with this 000 number??? or NULLS??

Please advise..
THNKS
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I assumed filenumber was purely numeric, hence i suggested INT as data type...

simply change the header of the function like so:

create function dbo.GetLitigationType ( @FileNumber varchar(40) , Subject Code varchar(10))
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Hey is there a way to tell this function to use ONE of 3 indexes that is assigned to this RLS table?

The results are not correct and it is because of the indexing of the table. My DBA has assigned a 3rd index for me according to my request for an index of: FILE NUMBER, SUBJECT CODE, ENTRY DATE, ENTRY TIME.

Can I tell my function to use index no. 3 when processing this function???

thakns
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Ok... moving right along...going good...NOW...how do I tell this function to NOT include the following SUBJECT CODES??

341,NOA,PNS,POC,POI,PSS,RAP,TDP,TRN,UNK

Can you tell me where/how to add this logic to the function command below??? ..THANKSSSSS


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



--------------------------------------------------------------------------------------------------------------------------
--Function to identify Litigation Types for Crystal Reporting
--Created by Mike Vega
--LIT COMPLETED =  TR/RR  + CR/PF/PS/OE  + RC    
--LIT STARTED =       TR/RR  +  CR/PF/PS/OE  + NO RC
--LIT REVIEW =          TR/RR, NO RC,PF,PS,CR,OE
--NO LIT NEEDED =   TR/RR/RC NO CR,PF,PS,OE
--LIT MISC =               ALL OTHER COMBINATIONS
-----------------------------------------------------------------------------------------------------------------------------

ALTER    function dbo.GetLitigationType ( @FileNumber varchar (40) , @Subject_Code varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int
  declare @count_ps int
  declare @count_cr int
  declare @count_oe int
 
  select @count_tr =sum(1) from dbo.RLS where [File Number] = @FileNumber and [Subject Code] =@Subject_Code and [Activity Code] = 'TR'
  select @count_rr = sum(1) from dbo.RLS where [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RR'
  select @count_rc = sum(1) from dbo.RLS where [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RC'
  select @count_cr = sum(1) from dbo.RLS where [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'CR'
  select @count_ps = sum(1) from dbo.RLS where [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'PS'
  select @count_pf = sum(1) from dbo.RLS where [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Status Code] = 'PF'
  select @count_oe = sum(1) from dbo.RLS where [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Status Code] = 'OE'

 IF (@count_tr >0 and @count_rr >0 and @count_rc >0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))
   RETURN ('NO LIT NEEDED')
  IF (@count_tr>0 AND @count_rr >0 AND @count_rc >0 and (@count_pf>0 or @count_cr >0 or @count_ps >0 or @count_oe >0) )
   RETURN ('LIT COMPLETED')
  IF (@count_tr>0 AND @count_rr >0 AND (@count_rc =0 or @count_pf>0 or @count_cr >0 or @count_ps >0 or @count_oe >0))  
   RETURN ('LIT STARTED')
  IF (@count_tr >0 and @count_rr >0) --and (@count_rc=0 or @count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))
   RETURN ('LIT REVIEW')
   RETURN ('LIT MISC')
END






GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
The presence or abscence of indexes does not influence the output of a query, only the speed.

>how do I tell this function to NOT include the following SUBJECT CODES
well, I would say you should simply not pass those subject codes are parameters?

but maybe the explanation is not really good...
0
 
MIKESoftware Solutions ConsultantAuthor Commented:


OK I'll see what I can do. I recall in your comments earlier that I needed to concern my self with the indexing of the table..and that it had to be FILE NUMBER, SUBJECT CODE....???

oh well... THANKS
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
This logic is ONLY returning the LIT COMPLETED and LIT MISC...categories?? The COMPLETED appears to be working correctly, but the  LIT MISC is wrong. It seems like all other files are falling into the MISC category.

Can you tell me why?

Thanks

ALTER      function dbo.GetLitigationType ( @FileNumber varchar (40) , @Subject_Code varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int
  declare @count_ps int
  declare @count_cr int
  declare @count_oe int
 
  select @count_tr =sum(1) from dbo.RLS where
  [Subject Code] not in  ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK')  and
 [File Number] = @FileNumber and [Subject Code] =@Subject_Code and [Activity Code] = 'TR'
  select @count_rr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
[File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RR'
  select @count_rc = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RC'
  select @count_cr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'CR'
  select @count_ps = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'PS'
  select @count_pf = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Status Code] = 'PF'
  select @count_oe = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Status Code] = 'OE'

  IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))                                RETURN ('LIT COMPLETED')

  IF (@count_tr >0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))      RETURN ('LIT REVIEW')

  IF (@count_tr >0 and @count_rr >0 and @count_rc >0 and @count_cr=0 and (@count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('NO LIT NEEDED')

  IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT STARTED')

  IF (@count_tr>0 and @count_rr >0 and @count_rc >0  and @count_cr >0 and (@count_pf=0 or @count_ps =0 or @count_oe =0)) RETURN ('REFERRAL NO LIT')

   RETURN ('LIT MISC')
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't see anything wrong, but I don't really know the business logic about the conditions you put...
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Ok...I think it is going to be related to your original comment about the INDEXing order of the table..

our file data looks like this:


FILENUMBER    SUBJECTCODE               ACTCODE   STATCODE   ACTDATE
100                        ABC                            TR                                01/01/05
100                        ABC                            RR                                01/02/05
100                        ABC                            PS                                01/03/05
100                        ABC                                              PF               01/31/05
100                        ABC                           RC                                 02/01/05
101                        ABC                            TR                                01/01/05
101                        ABC                            RR                                01/02/05
101                        ABC                            PS                                01/03/05
101                        ABC                                              PF               01/31/05
101                        ABC                           RC                                 02/01/05
101                        DEF                            TR                                01/01/05
101                        DEF                            RR                                01/02/05

Part of the problem is when a file has MORE than one subject code. I think the SQL logic is looking at file 101 and saying that both ABC and DEF subject codes are Completed, when in reality only the ABC code is completed.

I'm not sure HOW the SQL "views" the table and choosed to apply it's logic? Do you? I would think that since I'm telling it to base its analysis on the FILENUMBER,SUBJECT CODE...that something more is needed to END the analysis with EACH subject code right?

To add to the problems...the Subject are NORMALY but NOT always,...in order. An exampl may be ONE subject code NOT being completed...but later after a second code has been complted...then...the completed RC code would call in line AFTER the second subject code...and so in my opinion..the SQL is not basing it's analysis and LIT TYPING based on FILENUMBER, SUBJECT CODE (EACH SEPARATE GROUPING.

I realize this is a difficult task.. but any help/recommendations you can offer are greatly appreciated.....I'm almost there....

THANKS
                               
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, then the conditions for the other values are possibly wrong (ie and/or and brackets are not correct).

I tried with your data sample and always got the expected results-
>saying that both ABC and DEF subject codes are Completed,
well, for DEF I got LIT MISC

Here my test script:

create table ee_test ([FILE NUMBER]    int, [SUBJECT CODE]            varchar(3),   [ACTIVITY CODE]   varchar(2),
 [STATUS CODE]  varchar(2), ACTDATE datetime )
set nocount on
go
insert into ee_test values (100                        ,'ABC'                            ,'TR'                             ,null,   convert(datetime, '01/01/05', 1) )
insert into ee_test values (100                        ,'ABC'                            ,'RR'                            ,null,    convert(datetime, '01/02/05', 1) )
insert into ee_test values (100                        ,'ABC'                            ,'PS'                             ,null,   convert(datetime, '01/03/05', 1) )
insert into ee_test values (100                        ,'ABC'                            , null,                  'PF',               convert(datetime, '01/31/05', 1) )
insert into ee_test values (100                        ,'ABC'                           ,'RC'                        ,null,         convert(datetime, '02/01/05', 1) )
insert into ee_test values (101                        ,'ABC'                            ,'TR'                           ,null,     convert(datetime, '01/01/05', 1) )
insert into ee_test values (101                        ,'ABC'                            ,'RR'                             ,null,   convert(datetime, '01/02/05', 1) )
insert into ee_test values (101                        ,'ABC'                            ,'PS'    ,null,                            convert(datetime, '01/03/05', 1) )
insert into ee_test values (101                        ,'ABC'                           ,null,                   'PF',               convert(datetime, '01/31/05', 1) )
insert into ee_test values (101                        ,'ABC'                           ,'RC'                      ,null,           convert(datetime, '02/01/05', 1) )
insert into ee_test values (101                        ,'DEF'                            ,'TR'                       ,null,         convert(datetime, '01/01/05', 1) )
insert into ee_test values (101                        ,'DEF'                            ,'RR'                          ,null,      convert(datetime, '01/02/05', 1) )
go
create function dbo.GetLitigationType ( @FileNumber varchar (40) , @Subject_Code varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int
  declare @count_ps int
  declare @count_cr int
  declare @count_oe int
 
  select @count_tr =sum(1) from dbo.ee_test where
  [Subject Code] not in  ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK')  and
 [File Number] = @FileNumber and [Subject Code] =@Subject_Code and [Activity Code] = 'TR'
  select @count_rr = sum(1) from dbo.ee_test where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
[File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RR'
  select @count_rc = sum(1) from dbo.ee_test where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RC'
  select @count_cr = sum(1) from dbo.ee_test where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'CR'
  select @count_ps = sum(1) from dbo.ee_test where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'PS'
  select @count_pf = sum(1) from dbo.ee_test where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Status Code] = 'PF'
  select @count_oe = sum(1) from dbo.ee_test where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Status Code] = 'OE'

  IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))                                RETURN ('LIT COMPLETED')

  IF (@count_tr >0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))      RETURN ('LIT REVIEW')

  IF (@count_tr >0 and @count_rr >0 and @count_rc >0 and @count_cr=0 and (@count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('NO LIT NEEDED')

  IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT STARTED')

  IF (@count_tr>0 and @count_rr >0 and @count_rc >0  and @count_cr >0 and (@count_pf=0 or @count_ps =0 or @count_oe =0)) RETURN ('REFERRAL NO LIT')

   RETURN ('LIT MISC')
END

go
select *, dbo.GetLitigationType([File Number], [Subject Code]) as lt from ee_test
go
drop function dbo.GetLitigationType
go
drop table ee_test


output:

FILE NUMBER SUBJECT CODE ACTIVITY CODE STATUS CODE ACTDATE                                                lt                            
----------- ------------ ------------- ----------- ------------------------------------------------------ ------------------------------
100         ABC          TR            NULL        2005-01-01 00:00:00.000                                LIT COMPLETED
100         ABC          RR            NULL        2005-01-02 00:00:00.000                                LIT COMPLETED
100         ABC          PS            NULL        2005-01-03 00:00:00.000                                LIT COMPLETED
100         ABC          NULL          PF          2005-01-31 00:00:00.000                                LIT COMPLETED
100         ABC          RC            NULL        2005-02-01 00:00:00.000                                LIT COMPLETED
101         ABC          TR            NULL        2005-01-01 00:00:00.000                                LIT COMPLETED
101         ABC          RR            NULL        2005-01-02 00:00:00.000                                LIT COMPLETED
101         ABC          PS            NULL        2005-01-03 00:00:00.000                                LIT COMPLETED
101         ABC          NULL          PF          2005-01-31 00:00:00.000                                LIT COMPLETED
101         ABC          RC            NULL        2005-02-01 00:00:00.000                                LIT COMPLETED
101         DEF          TR            NULL        2005-01-01 00:00:00.000                                LIT MISC
101         DEF          RR            NULL        2005-01-02 00:00:00.000                                LIT MISC




0
 
MIKESoftware Solutions ConsultantAuthor Commented:
See your above example 'should have' identified file 101 subject code DEF as LIT REVIEW, according to the logic below.....RIGHT?

IF (@count_tr >0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))      RETURN ('LIT REVIEW')

OR...is this logic CHECKING for the CODE first to see if it is in the record ...THEN... if it is NULL...is it not considering the RC equal to ZERO for NULLS?

0
 
MIKESoftware Solutions ConsultantAuthor Commented:
I think I need help checking for nulls..first in the logic???
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, seems it works differently than I first thought.

add the following lines, between the select @var = sum(1) ...  and the ifs ( )...
  if (@count_tr is null) set @count_tr = 0
  if (@count_rr is null) set @count_rr = 0
  if (@count_rc is null) set @count_rc = 0
  if (@count_cr is null) set @count_cr = 0
  if (@count_pf is null) set @count_pf = 0
  if (@count_ps is null) set @count_ps = 0
  if (@count_oe is null) set @count_oe = 0

that should help
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Awesome man... I think you got it dialed in...seems to be working perfectly.
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
awesome expert help....thanks again

Mike
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Need some more help plsss.

I need to add the following logic that is in ASTERICKS...below. I don't know the format of the SQL language needed. THANKS


ALTER         function dbo.GetLitigationType ( @FileNumber varchar (40) , @Subject_Code varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int
  declare @count_ps int
  declare @count_cr int
  declare @count_oe int
 
  select @count_tr =sum(1) from dbo.RLS where
  [Subject Code] not in  ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK')  and
 [File Number] = @FileNumber and [Subject Code] =@Subject_Code and [Activity Code] = 'TR'
  select @count_rr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
[File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'RR'
  select @count_rc = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RC'
  select @count_cr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'CR'
  select @count_ps = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'PS'
  select @count_pf = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Status Code] = 'PF'
  select @count_oe = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Status Code] = 'OE'
  if (@count_tr is null) set @count_tr = 0
  if (@count_rr is null) set @count_rr = 0
  if (@count_rc is null) set @count_rc = 0
  if (@count_cr is null) set @count_cr = 0
  if (@count_pf is null) set @count_pf = 0
  if (@count_ps is null) set @count_ps = 0
  if (@count_oe is null) set @count_oe = 0
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and  (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT COMPLETED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0  and @count_cr >0 and ( @count_pf=0 or @count_ps =0 or @count_oe =0))RETURN ('LC NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT STARTED')

***********for the LIT STARTED category above, I need for items that are NOT in depts 'RLS' or 'SRS' to be recategorized as 'EXCEPTIONS'..... the data table for the dept data element is [dbo.orafid.unit]************


 IF (@count_tr>0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))
RETURN ('LIT UNDER REVIEW')

***********for the LIT UNDER REVIEW category above, I need for items that are NOT in depts 'RLS' or 'SRS' to be recategorized as 'NO LIT NEEDED'..... the data table for the dept data element is [dbo.orafid.unit]************

RETURN ('EXCEPTIONS')
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I hope this makes sense...and if not, pls ask, I'll supply answer to questions...asap.

Thanks
Mike

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, I think I would COUNT as for the other counter fields if they are categorized as EXCEPTIONS resp as NO LIT NEEDED, and then add another if condition, for example like this:

IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF (@count_exceptions = 0)
    RETURN ('LIT STARTED')
  ELSE
    RETURN ('LIT EXCEPTION')
END

Hope this helps
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
So how do they get categorized as EXCEPTIONS without adding the logic for the UNIT (DEPT) from the table dbo.orafid.unit?

I need for this logic to check to see if the file is in UNIT 'RLS' or 'SRS' and if it is, then to reclass it as EXCEPTION, but only for the CATEGORIES that I mentioned.

Make sense?

I think I have the categories down correctly, but for those 2 categories I need to add some criteria to the logic so that an addtional CHECK can be done to place this FILE NUMBER, SUB CODE into the correct category.

ok?

 
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
The 2 tables involved are the original table     dbo.RLS   and this other table I need to add    dbo.orafid .

Then data element that I need to check this logic against is:   dbo.orafid.unit   (which is the dept#)

If that particular file is NOT in RLS or SRS depts...then the category needs to be changed from the above logic, but ONLY for those files that fall into those 2 categories...

0
 
MIKESoftware Solutions ConsultantAuthor Commented:
I think I need something like this:

IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF (dbo.orafid.unit not like 'SRS','RLS') RETURN ('EXCEPTIONS')
         ELSE RETURN ('LIT STARTED')
END


IF (@count_tr>0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))
BEGIN
IF (dbo.orafid.unit not like 'SRS','RLS') RETURN ('NO LIT NEEDED')
         ELSE RETURN ('LIT UNDER REVIEW')

I'm just guessing.....?? but I need that section of the logic to do an additional check or analysis to see what the location of the file is... which dept ...and based on that ...then assign a category.

THanks
M


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think this will be closed to what you need:

IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS') AND <condition about filenumber and/or code> )
       RETURN ('LIT STARTED')
    ELSE
       RETURN ('EXCEPTIONS')
END


0
 
MIKESoftware Solutions ConsultantAuthor Commented:

Thanks...I have this entered....

IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and  (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT COMPLETED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0  and @count_cr >0 and ( @count_pf=0 or @count_ps =0 or @count_oe =0))RETURN ('LC NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT STARTED')
BEGIN
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS')) -- AND <condition about filenumber and/or code> )
       RETURN ('LIT UNDER REVIEW')
    ELSE
       RETURN ('EXCEPTIONS')
END
 IF (@count_tr>0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('LIT UNDER REVIEW')
IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS')) -- AND <condition about filenumber and/or code> )
       RETURN ('LIT UNDER REVIEW')
    ELSE
       RETURN ('EXCEPTIONS')
RETURN ('EXCEPTIONS')
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


But I get this error:

/*-----------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
-----------------------------*/
Server: Msg 455, Level 16, State 2, Procedure GetLitigationType, Line 65535
The last statement included within a function must be a return statement.


???

Thx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
don't you have 2 times END instead of 1 times END ...
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Angel.. sorry..I'll be glad to get this one finished, but on the following logic...I get an error that I cannot figure out....

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--------------------------------------------------------------------------------------------------------------------------
--Function to identify Litigation Types for Crystal Reporting
--Created by Mike Vega
--NO LIT NEEDED =     TR,RR,RC and no [PF or PS or OE]
--LIT COMPLETED =    TR,RR,RC and [PF or PS or OE]    
--LIT STARTED =         TR,RR no RC no CR and [PF or PS or OE]
--REFERRAL NO LIT = TR,RR,RC,CR and no [PS or PF or OE]
--LIT REVIEW =             TR,RR no RC and no [PF or PS or CR or OE]
--LIT MISC =                  ALL OTHER COMBINATIONS
-----------------------------------------------------------------------------------------------------------------------------
ALTER          function dbo.GetLitigationType ( @FileNumber varchar (40) , @Subject_Code varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int
  declare @count_ps int
  declare @count_cr int
  declare @count_oe int
 
  select @count_tr =sum(1) from dbo.RLS where
  [Subject Code] not in  ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK')  and
 [File Number] = @FileNumber and [Subject Code] =@Subject_Code and [Activity Code] = 'TR'
  select @count_rr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
[File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'RR'
  select @count_rc = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RC'
  select @count_cr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'CR'
  select @count_ps = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'PS'
  select @count_pf = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Status Code] = 'PF'
  select @count_oe = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Status Code] = 'OE'
  if (@count_tr is null) set @count_tr = 0
  if (@count_rr is null) set @count_rr = 0
  if (@count_rc is null) set @count_rc = 0
  if (@count_cr is null) set @count_cr = 0
  if (@count_pf is null) set @count_pf = 0
  if (@count_ps is null) set @count_ps = 0
  if (@count_oe is null) set @count_oe = 0
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and  (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT COMPLETED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0  and @count_cr >0 and ( @count_pf=0 or @count_ps =0 or @count_oe =0))RETURN ('LC NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
begin
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS') and [file number]=dbo.rls.[file number])
      RETURN ('LIT STARTED')
       RETURN ('EXCEPTIONS')
end
 IF (@count_tr>0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('LIT UNDER REVIEW')
 IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
begin
 IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS') and [file number]=dbo.rls.[file number]) RETURN ('LIT UNDER REVIEW')
       RETURN ('EXCEPTIONS')
end
RETURN ('EXCEPTIONS')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


The error says:::

Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 66
Line 66: Incorrect syntax near ')'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS') and [file number]=dbo.rls.[file number])
      RETURN ('LIT STARTED')
       RETURN ('EXCEPTIONS')
end

you CANNOT retturn 2 values, only 1.
also, the query inside the EXISTS is not correct, as you use the dbo.rls table which is not in the FROM part.




0
 
MIKESoftware Solutions ConsultantAuthor Commented:
STILL getting this error:

Server: Msg 170, Level 15, State 1, Procedure GetLitigationType, Line 69
Line 69: Incorrect syntax near ')'.

On this SQL Statements: (see line 69???!??)

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--------------------------------------------------------------------------------------------------------------------------
--Function to identify Litigation Types for Crystal Reporting
--Created by Mike Vega
--NO LIT NEEDED =     TR,RR,RC and no [PF or PS or OE]
--LIT COMPLETED =    TR,RR,RC and [PF or PS or OE]    
--LIT STARTED =         TR,RR no RC no CR and [PF or PS or OE]
--REFERRAL NO LIT = TR,RR,RC,CR and no [PS or PF or OE]
--LIT REVIEW =             TR,RR no RC and no [PF or PS or CR or OE]
--LIT MISC =                  ALL OTHER COMBINATIONS
-----------------------------------------------------------------------------------------------------------------------------
ALTER          function dbo.GetLitigationType ( @FileNumber varchar (40) , @Subject_Code varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int
  declare @count_ps int
  declare @count_cr int
  declare @count_oe int
 
  select @count_tr =sum(1) from dbo.RLS where
  [Subject Code] not in  ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK')  and
 [File Number] = @FileNumber and [Subject Code] =@Subject_Code and [Activity Code] = 'TR'
  select @count_rr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
[File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'RR'
  select @count_rc = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RC'
  select @count_cr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'CR'
  select @count_ps = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'PS'
  select @count_pf = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Status Code] = 'PF'
  select @count_oe = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Status Code] = 'OE'
  if (@count_tr is null) set @count_tr = 0
  if (@count_rr is null) set @count_rr = 0
  if (@count_rc is null) set @count_rc = 0
  if (@count_cr is null) set @count_cr = 0
  if (@count_pf is null) set @count_pf = 0
  if (@count_ps is null) set @count_ps = 0
  if (@count_oe is null) set @count_oe = 0
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and  (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT COMPLETED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0  and @count_cr >0 and ( @count_pf=0 or @count_ps =0 or @count_oe =0))RETURN ('LC NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))RETURN ('NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('LIT UNDER REVIEW')
 IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS'))
      RETURN ('LIT STARTED')
              ELSE
                     RETURN ('EXCEPTIONS')
                             END
 IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS'))  
     RETURN ('LIT UNDER REVIEW')  --*********this is line 69??!!??***********
                 ELSE
                         RETURN ('EXCEPTIONS')
                                 END
RETURN ('EXCEPTIONS')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ALTER          function dbo.GetLitigationType ( @FileNumber varchar (40) , @Subject_Code varchar(10))
RETURNS VARCHAR(30)
AS
BEGIN
  declare @count_tr int
  declare @count_rr int
  declare @count_rc int
  declare @count_pf int
  declare @count_ps int
  declare @count_cr int
  declare @count_oe int
 
  select @count_tr =sum(1) from dbo.RLS where
  [Subject Code] not in  ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK')  and
 [File Number] = @FileNumber and [Subject Code] =@Subject_Code and [Activity Code] = 'TR'
  select @count_rr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
[File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'RR'
  select @count_rc = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code and [Activity Code] = 'RC'
  select @count_cr = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'CR'
  select @count_ps = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Activity Code] = 'PS'
  select @count_pf = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Status Code] = 'PF'
  select @count_oe = sum(1) from dbo.RLS where
 [Subject Code] not in ('341','NOA','PNS','POC','POI','PSS','RAP','TDP','TRN','UNK') and
 [File Number] = @FileNumber and [Subject Code] = @Subject_Code  and [Status Code] = 'OE'
  if (@count_tr is null) set @count_tr = 0
  if (@count_rr is null) set @count_rr = 0
  if (@count_rc is null) set @count_rc = 0
  if (@count_cr is null) set @count_cr = 0
  if (@count_pf is null) set @count_pf = 0
  if (@count_ps is null) set @count_ps = 0
  if (@count_oe is null) set @count_oe = 0
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and  (@count_pf>0 or @count_ps >0 or @count_oe >0)) RETURN ('LIT COMPLETED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0  and @count_cr >0 and ( @count_pf=0 or @count_ps =0 or @count_oe =0))RETURN ('LC NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc >0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0))RETURN ('NO LIT NEEDED')
 IF (@count_tr>0 and @count_rr >0 and @count_rc=0 and (@count_cr=0 or @count_pf=0 or @count_ps=0 or @count_oe=0)) RETURN ('LIT UNDER REVIEW')
 IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS'))
      RETURN ('LIT STARTED')
              ELSE
                     RETURN ('EXCEPTIONS')
                             END
 IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS'))  
     RETURN ('LIT UNDER REVIEW')  
                 ELSE
                         RETURN ('EXCEPTIONS')
                                 END
RETURN ('EXCEPTIONS')
END  << there is a END missing here
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Thanks man...that worked. Hopefully, I'm done with this one...we'll see.

I really appreciate your expertise!

Mike
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
I'm now having problems with the logic assigning the correct LIT TYPE when the IF EXISTS check is NOT true.

For example in this section:

IF (@count_tr>0 and @count_rr >0 and @count_rc =0  and @count_cr =0 and (@count_pf>0 or @count_ps >0 or @count_oe >0))
BEGIN
  IF EXISTS( SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS'))  
     RETURN ('LIT UNDER REVIEW')  
                 ELSE
                         RETURN ('NO LIT NEEDED')
                                 END
RETURN ('EXCEPTIONS')

It appears that it is totally ignoring the NO LIT NEEDED part. I have files that have a UNIT OF 'CNC' that are showing LIT UNDER REVIEW..but according to the above section of logic, they should be thrown into the NO LIT NEEDED type???

Your help is appreciated.

Mike
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
does it have something to do with me NOT specifying a linking data element,...like   WHERE [FILE NUMBER]=[FILE NUMBER]   within the EXISTS command line?

I'm not seeing HOW this EXISTS line can matchup the FILE NUMBER info....because the UNIT is NOT a data element in the dbo.RLS table. Make sense?

By me saying:

SELECT * FROM dbo.orafid WHERE unit IN ('SRS','RLS')....    I'm not sure HOW this is 'linked' to the dbo.RLS table...since the UNIT is NOT found in the dbo.RLS table.

0
 
MIKESoftware Solutions ConsultantAuthor Commented:
I need to find out IF the FILENUMBER is currently sitting in RLS or SRS and if so, then one LIT TYPE assigned..AND if not.then another LIT TYPE.

But it is all keying off of the actual FILENUMBER..and not whether a group of files are open in RLS/SRS.

The logic above is basically checking to see if ANY ROWS are present in ORAFID with SRS / RLS as a dept. I need to see if the FILENUMBER IN QUESTION is appearing in ORAFID with either SRS/RLS dept codes
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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