In this query I am trying getting result with fincial year but I am using month instead of date.
I am passing start month and end month e. between 4 to 12. But if I passed month 1 or 2, 3 then it getting problem. So how it is possible use case in the where clause following sql query. Other wise my sql squery is getting large because there have so many field which is I require as bellow…
I ) if month between 4 to 12
Where Year(NWWDD.DAILY_DATE) = 2012
And Month(NWWDD.DAILY_DATE) >= 4 and Month(NWWDD.DAILY_DATE) <= 12
2) if month between 4 to 12 and month select ( 1 or 2 or 3)
Where Year(NWWDD.DAILY_DATE) = 2012
And (((Month(NWWDD.DAILY_DATE) Between 4 and 12) and Year(NWWDD.DAILY_DATE) = 2012)
oR (Month(NWWDD.DAILY_DATE) <= 1 and Year(NWWDD.DAILY_DATE) = 2012))
Declare @MONTH int
Set @MONTH = 5
-- PREVIOUS_YEAR_COLLECTION
If(@MONTH in (1,2,3))
Begin
Select NWWDD.REGION_ID, NWWRM.REGION, Sum(NWWDD.PER_DAY_COLLECTION) As 'PREVIOUS_YEAR_COLLECTION'
From NEW_WATER_WORK_DAILY_DETAIL NWWDD
Inner Join NEW_WATER_WORK_REGION_MASTER NWWRM On NWWRM.ID = NWWDD.REGION_ID
Inner Join NEW_WATER_WORK_MASTER NWWM On NWWM.ID = NWWDD.WW_ID
Where Year(NWWDD.DAILY_DATE) = 2012
And (((Month(NWWDD.DAILY_DATE) Between 4 and 12) and Year(NWWDD.DAILY_DATE) = 2012)
oR (Month(NWWDD.DAILY_DATE) <= 1 and Year(NWWDD.DAILY_DATE) = 2012))
Group by NWWDD.REGION_ID,NWWRM.REGION
Order by NWWDD.REGION_ID
End
Else
Begin
Select NWWDD.REGION_ID, NWWRM.REGION, Sum(NWWDD.PER_DAY_COLLECTION) As 'PREVIOUS_YEAR_COLLECTION'
From NEW_WATER_WORK_DAILY_DETAIL NWWDD
Inner Join NEW_WATER_WORK_REGION_MASTER NWWRM On NWWRM.ID = NWWDD.REGION_ID
Inner Join NEW_WATER_WORK_MASTER NWWM On NWWM.ID = NWWDD.WW_ID
Where Year(NWWDD.DAILY_DATE) = 2012
And Month(NWWDD.DAILY_DATE) >= 4 and Month(NWWDD.DAILY_DATE) <= 12
Group by NWWDD.REGION_ID,NWWRM.REGION
Order by NWWDD.REGION_ID
End