You must Sign In to post a response.
  • Category: SQL Server

    How do small query instead of big query in Sql Server

    Hi, I want below sql query in small instead of big write. I am sending some parameter.

    ---profile id
    1. Admin
    2. Region Manger
    3. District Manger
    4. Tahsil Manger (Supervisor)
    5. village Manger (communicator)

    ---Output of Temp table Data
    FARMER_ID NEXT_CALL_DATE REGION_ID DISTRICT_ID TAHSIL_ID VILLAGE_ID Status
    1 2016-11-15 1 1 1 1 Pending Activity
    2 2016-11-23 1 1 1 1 Future Activity
    3 2016-11-25 4 8 21 278 Future Activity

    Set Nocount on
    Declare @ACTIVITY_STATUS varchar(100)
    DECLARE @USER_ID int

    Declare @ASSIGNTO varchar(100)
    Declare @PROFILE_ID int
    Declare @LOCATION_ID int
    Set @ACTIVITY_STATUS = 'FUTURE ACTIVITY'

    Set @USER_ID = 1
    Set @ASSIGNTO = '-1'

    Set @PROFILE_ID = (Select PROFILE_ID From
    USER_MASTER Where ID = @USER_ID)
    Set @LOCATION_ID = (Select LOCATION_ID From
    USER_MASTER Where ID = @USER_ID)


    Select AM.FARMER_ID,AM.NEXT_CALL_DATE,FM.REGION_ID, FM.DISTRICT_ID, FM.TAHSIL_ID, FM.VILLAGE_ID,
    'Status' = (Case when AM.NEXT_CALL_DATE = CONVERT(DATE, GETDATE()) Then 'Current Activity'
    when (AM.NEXT_CALL_DATE <> CONVERT(DATE, AM.LAST_MODIFIED)
    And AM.NEXT_CALL_DATE <= CONVERT(DATE, GETDATE())) Then 'Pending Activity'
    when AM.NEXT_CALL_DATE > CONVERT(DATE, GETDATE()) Then 'Future Activity'
    when AM.STATUS_ID = 4 And AM.STAGE_ID = 9 Then 'Closed Activity' End)
    Into #Temp From FARMER_MASTER FM
    Inner Join ACTIVITY_MASTER AM On AM.FARMER_ID = FM.ID
    Inner Join (Select FARMER_ID, Max(LAST_MODIFIED) As 'MaxDate' FROM ACTIVITY_MASTER
    Group by FARMER_ID ) As T1 On T1.FARMER_ID = AM.FARMER_ID AND T1.MaxDate = AM.LAST_MODIFIED
    Group by AM.FARMER_ID,AM.NEXT_CALL_DATE,AM.LAST_MODIFIED,AM.STATUS_ID, AM.STAGE_ID,
    FM.REGION_ID, FM.DISTRICT_ID, FM.TAHSIL_ID, FM.VILLAGE_ID

    If(@ACTIVITY_STATUS = 'All')
    Begin
    If (@PROFILE_ID = 1)
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End),
    'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End),
    'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End),
    'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    End
    Else if (@PROFILE_ID = 2)
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End),
    'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End),
    'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End),
    'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    Else if (@PROFILE_ID = 3)
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End),
    'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End),
    'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End),
    'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.DISTRICT_ID= @LOCATION_ID
    End
    Else if (@PROFILE_ID = 4)
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End),
    'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End),
    'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End),
    'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.TAHSIL_ID = @LOCATION_ID
    End
    Else if (@PROFILE_ID = 5)
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End),
    'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End),
    'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End),
    'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.VILLAGE_ID = @LOCATION_ID
    End
    End
    Else If(@ACTIVITY_STATUS != 'All')
    Begin
    If (@PROFILE_ID = 1)
    Begin
    If(@ACTIVITY_STATUS = 'CURRENT ACTIVITY')
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End)
    From #Temp TM
    End
    Else If(@ACTIVITY_STATUS = 'PENDING ACTIVITY')
    Begin
    Select 'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End)
    From #Temp TM
    End
    If(@ACTIVITY_STATUS = 'FUTURE ACTIVITY')
    Begin
    Select 'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End)
    From #Temp TM
    End
    If(@ACTIVITY_STATUS = 'CLOSE ACTIVITY')
    Begin
    Select 'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    End
    End
    Else If (@PROFILE_ID = 2)
    Begin
    If(@ACTIVITY_STATUS = 'CURRENT ACTIVITY')
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    Else If(@ACTIVITY_STATUS = 'PENDING ACTIVITY')
    Begin
    Select 'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'FUTURE ACTIVITY')
    Begin
    Select 'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'CLOSE ACTIVITY')
    Begin
    Select 'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    End
    Else If (@PROFILE_ID = 2)
    Begin
    If(@ACTIVITY_STATUS = 'CURRENT ACTIVITY')
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    Else If(@ACTIVITY_STATUS = 'PENDING ACTIVITY')
    Begin
    Select 'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'FUTURE ACTIVITY')
    Begin
    Select 'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'CLOSE ACTIVITY')
    Begin
    Select 'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.REGION_ID = @LOCATION_ID
    End
    End
    Else If (@PROFILE_ID = 3)
    Begin
    If(@ACTIVITY_STATUS = 'CURRENT ACTIVITY')
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.DISTRICT_ID = @LOCATION_ID
    End
    Else If(@ACTIVITY_STATUS = 'PENDING ACTIVITY')
    Begin
    Select 'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.DISTRICT_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'FUTURE ACTIVITY')
    Begin
    Select 'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.DISTRICT_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'CLOSE ACTIVITY')
    Begin
    Select 'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.DISTRICT_ID = @LOCATION_ID
    End
    End
    Else If (@PROFILE_ID = 4)
    Begin
    If(@ACTIVITY_STATUS = 'CURRENT ACTIVITY')
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.TAHSIL_ID = @LOCATION_ID
    End
    Else If(@ACTIVITY_STATUS = 'PENDING ACTIVITY')
    Begin
    Select 'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.TAHSIL_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'FUTURE ACTIVITY')
    Begin
    Select 'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.TAHSIL_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'CLOSE ACTIVITY')
    Begin
    Select 'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.TAHSIL_ID = @LOCATION_ID
    End
    End
    Else If (@PROFILE_ID = 5)
    Begin
    If(@ACTIVITY_STATUS = 'CURRENT ACTIVITY')
    Begin
    Select 'CURRENT_ACTIVITY' = Sum(Case When TM.Status = 'Current Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.VILLAGE_ID = @LOCATION_ID
    End
    Else If(@ACTIVITY_STATUS = 'PENDING ACTIVITY')
    Begin
    Select 'PENDING_ACTIVITY' = Sum(Case When TM.Status = 'Pending Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.VILLAGE_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'FUTURE ACTIVITY')
    Begin
    Select 'FUTURE_ACTIVITY' = Sum(Case When TM.Status = 'Future Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.VILLAGE_ID = @LOCATION_ID
    End
    If(@ACTIVITY_STATUS = 'CLOSE ACTIVITY')
    Begin
    Select 'CLOSE_ACTIVITY' = Sum(Case When TM.Status = 'Closed Activity' Then 1 Else 0 End)
    From #Temp TM
    Where TM.VILLAGE_ID = @LOCATION_ID
    End
    End
    End
    Drop Table #Temp
    Set Nocount off
    Go
  • #768377
    Hi,

    You have used Select Case for each @PROFILE_ID, that's why it looks very bigger query.
    Instead you can use Union/UnionAll operation.
    Or you can check your existing queries optimization plan from SQL and follow necessary steps to minimize it.


Sign In to post your comments