Sql Query Solution Needed

Question

AgencyId    SourceType    SourceCode    PropertyState    Code
a1002    NULL    Xyz1    NULL    test1
a1002    NULL    Xyz2    NULL    test2
a1002    NULL    Xyz3    NULL    test3
a1002    NULL    Xyz4    NULL    test4
a1002    NULL    Xyz5    NULL    test5
a1002    NULL    Xyz6    NULL    test6
a1002    NULL    Xyz7    NULL    test7
a1002    NULL    Xyz8    NULL    test8
a1002    NULL    Xyz9    NULL    test9
a1002    NULL    Xyz10    NULL    test10
a1002    NULL    Xyz11    NULL    test11
a1002    NULL    Xyz12    NULL    test12
a1002    NULL    Xyz13    NULL    test13
a1002    NULL    Xyz14    NULL    test14
a1002    NULL    Xyz15    NULL    test15
a1002    NULL    Xyz16    NULL    test16
a1002    NULL    Xyz17    NULL    test17
a1002    NULL    Xyz18    NULL    test18
a1002    NULL    Xyz19    NULL    test19
a1002    NULL    Xyz20    NULL    test20
a1002    NULL    Xyz21    NULL    test21
a1002    NULL    Xyz22    NULL    test22
a1002    NULL    Xyz23    NULL    test23
a1002    NULL    Xyz24    NULL    test24
a1002    NULL    Xyz25    NULL    test25
a1002    NULL    Xyz26    NULL    test26
a1003    NULL    Xyz27    FL    test27
a1003    NULL    Xyz28    NULL    test28
a1004    NULL    NULL    NULL    test29
a1005    NULL    NULL    NULL    test30
a1006    NULL    NULL    NULL    test31
a1006    NULL    NULL    NULL    test32
a1007    NULL    NULL    NULL    test33
a1008    B    NULL    NULL    test34
a1008    O    NULL    NULL    test35
I have a table name test,in that there are 5 columns AgencyId,SourceType,SourceCode,PropertyStae,Code. Want to write sql query which will give Code as output base on following drilldown logic
First match by AgencyId,SourceType,PropertyState if not then AgencyId,SourceType if not then AgencyId,SourceCode if not then AgencyId PropertyStae if not then SourceType PropertyState if not then only by AgencyId.
How to write sql query for this. Requirement is such that can not write sp nor functions.

Nitin Sontakke · Answer

Hello,   Something is wrong. I did the modification you suggested and I do get the expected result.   Please refer to the screen shot below.   If you want me to try some other combination, please do let me know.   If I am doing something wrong, please do let me know.

Aniket Narvankar · Answer

Thanks a lot

but the query is not giving require output for this condition

a1006 NULL NULL NULL test31
a1006 NULL NULL NULL test32

a1006 NULL NULL NULL test31 corrected record is

a1006 NULL NULL FL test31

so when input is AgencyId a1006 SourceType is NULL,sourcecode is NULL and propertystate is FL then output should be

a1006 NULL NULL FL test31

but output we get is

a1006 NULL NULL FL test31
a1006 NULL NULL NULL test32

which is not correct, a1006 NULL NULL NULL test32 is output only when it matches by AgencyId. Trying to resolve this,but not gettting it. Please help me on this.

Nitin Sontakke · Answer

I guess this is what you are probably looking for:   declare  @someTable  table    (      [AgencyId]  varchar (10)  not   null      ,[SourceType]  varchar (10)     ,[SourceCode]  varchar (10)     ,[PropertyState]  varchar (10)     ,[Code]  varchar (10)  not   null    )      insert   into  @someTable  values     ( 'a1002' ,  NULL ,  'Xyz1' ,  NULL ,  'test1' )   ,( 'a1002' ,  NULL ,  'Xyz2' ,  NULL ,  'test2' )   ,( 'a1002' ,  NULL ,  'Xyz3' ,  NULL ,  'test3' )   ,( 'a1002' ,  NULL ,  'Xyz4' ,  NULL ,  'test4' )   ,( 'a1002' ,  NULL ,  'Xyz5' ,  NULL ,  'test5' )   ,( 'a1002' ,  NULL ,  'Xyz6' ,  NULL ,  'test6' )   ,( 'a1002' ,  NULL ,  'Xyz7' ,  NULL ,  'test7' )   ,( 'a1002' ,  NULL ,  'Xyz8' ,  NULL ,  'test8' )   ,( 'a1002' ,  NULL ,  'Xyz9' ,  NULL ,  'test9' )   ,( 'a1002' ,  NULL ,  'Xyz10' ,  NULL ,  'test10' )   ,( 'a1002' ,  NULL ,  'Xyz11' ,  NULL ,  'test11' )   ,( 'a1002' ,  NULL ,  'Xyz12' ,  NULL ,  'test12' )   ,( 'a1002' ,  NULL ,  'Xyz13' ,  NULL ,  'test13' )   ,( 'a1002' ,  NULL ,  'Xyz14' ,  NULL ,  'test14' )   ,( 'a1002' ,  NULL ,  'Xyz15' ,  NULL ,  'test15' )   ,( 'a1002' ,  NULL ,  'Xyz16' ,  NULL ,  'test16' )   ,( 'a1002' ,  NULL ,  'Xyz17' ,  NULL ,  'test17' )   ,( 'a1002' ,  NULL ,  'Xyz18' ,  NULL ,  'test18' )   ,( 'a1002' ,  NULL ,  'Xyz19' ,  NULL ,  'test19' )   ,( 'a1002' ,  NULL ,  'Xyz20' ,  NULL ,  'test20' )   ,( 'a1002' ,  NULL ,  'Xyz21' ,  NULL ,  'test21' )   ,( 'a1002' ,  NULL ,  'Xyz22' ,  NULL ,  'test22' )   ,( 'a1002' ,  NULL ,  'Xyz23' ,  NULL ,  'test23' )   ,( 'a1002' ,  NULL ,  'Xyz24' ,  NULL ,  'test24' )   ,( 'a1002' ,  NULL ,  'Xyz25' ,  NULL ,  'test25' )   ,( 'a1002' ,  NULL ,  'Xyz26' ,  NULL ,  'test26' )   ,( 'a1003' ,  NULL ,  'Xyz27' ,  'FL' ,  'test27' )   ,( 'a1003' ,  NULL ,  'Xyz28' ,  NULL ,  'test28' )   ,( 'a1004' ,  NULL ,  NULL ,  NULL ,  'test29' )   ,( 'a1005' ,  NULL ,  NULL ,  NULL ,  'test30' )   ,( 'a1006' ,  NULL ,  NULL ,  NULL ,  'test31' )   ,( 'a1006' ,  NULL ,  NULL ,  NULL ,  'test32' )   ,( 'a1007' ,  NULL ,  NULL ,  NULL ,  'test33' )   ,( 'a1008' ,  'B' ,  NULL ,  NULL ,  'test34' )   ,( 'a1008' ,  'O' ,  NULL ,  NULL ,  'test35' )      declare  @AgencyId  varchar (10) =  'a1008' , @sourceCode  varchar (10) =  null , @propertyState   varchar (10) =  null , @sourceType   varchar (10) =  'B'       select  *   from  @someTable   where  1 = 1      and  [AgencyId] =  isnull (@AgencyId, [AgencyId])      and  1 =  case   when  @sourceCode  is   null   then  1  else   case   when  [SourceCode] = @sourceCode  then  1  else  0  end   end       and  1 =  case   when  @propertyState  is   null   then  1  else   case   when  [PropertyState] = @propertyState  then  1  else  0  end   end       and  1 =  case   when  @sourceType  is   null   then  1  else   case   when  [SourceType] = @sourceType  then  1  else  0  end   end      Just keep changing the variable values and see if you get desired results.   Hope it helps!

Aniket Narvankar · Answer

Following is the query i tried Select Id,Code from test where (AgencyId=@AgencyId and SourceCode=@sourceCode and PropertyState=@propertyState and (SourceType is null or SourceType != @sourceType)) OR (AgencyId=@AgencyId and SourceCode=@sourceCode and (PropertyState is null or PropertyState != @propertyState) and (SourceType is null or SourceType != @sourceType)) OR (AgencyId=@AgencyId and PropertyState=@propertyState and (SourceCode is null or SourceCode != @sourceCode) and (SourceType is null or SourceType != @sourceType)) OR (AgencyId=@AgencyId and SourceType=@sourceType and (PropertyState is null or PropertyState != @propertyState) and (SourceCode is null or SourceCode != @sourceCode)) OR (AgencyId=@AgencyId and isnull(Sourcecode,'') != @sourceCode and ISNULL(PropertyState,'') != @propertyState and isnull(SourceType,'') != @sourceType)   but this not working properly fails for some conditions,also requirement is such that can not write a  sp or function,need to write inline query in c sharp

Aniket Narvankar · Answer

Yes, Suppose AgencyId is a1003,sourceCode is Xyz27 and propertyState is FL then output will be test27 which is the Code against this   If this combination does not matches that is AgencyId,sourceCode and propertyState,then need to check wether AgencyId and sourceCode combination matches   For example if this is the input AgencyId a1003,sourceCode Xyz28 and propertyState FL,there is no such combination but AgencyId a1003 and sourceCode Xyz28 is there so output will be test28   Suppose this combination also does not match then have to check wether AgencyId and propertyState matches    If this combination does not match then have to check with AgencyId and SourceType, if this also does not matches finally check with only AgencyId   So these are combination Check by AgencyId,SourceCode,PropertyState if not then check with AgencyId SourceCode if not then check with AgencyId PropertyState if not then check with AgencyId SourceType if not then check with only AgencyId

Nitin Sontakke · Answer

Unfortunately, your requirement is not quite clear to me.   Match with what? What is the source of values to be matched with?   What is expected returned value? Just 1 if matched and 0 if not matched or something else?   And finally, why not stored procedure? Just out of curiosity!   Please respond!