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

    How use case in this sql queries

    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
  • #766245
    Hello Chandrasekhar,

    As per my understanding, after going through your issue and query is that I think there is one little mistake in the query. In the condition i.e. If(@MONTH in (1,2,3)) there is one mistake in the query i.e. you written that "oR (Month(NWWDD.DAILY_DATE) <= 1 and Year(NWWDD.DAILY_DATE) = 2012))" instead of this it will be "oR (Month(NWWDD.DAILY_DATE) >= 1 and Year(NWWDD.DAILY_DATE) = 2012))".It is not less than equal to 1 it will be greater than equal to 1.This is the main cause why you facing problem when the month in between 1 to 3.

    So, the whole query will be as follows:-

    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

    Hope that it will solve your problem.

    Besides that, if my understanding or solution is not to your expectation then please explain your issue in detail.

    Thanks


Sign In to post your comments