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