CODEWITHSUNDEEP
sql-serversql-server-2012
Vinayaka N
Vinayaka N

Reputation: 123

Union All with If else condition in sql server

I am trying to use union all with If else condition my query is below

DECLARE @Date DATE = '2016-03-25'
SELECT DATEPART(WEEK, @date)
IF (DATEPART(WEEK, @date) <= 13)
  BEGIN
    SELECT
      count(ip),
      datepart(YEAR, crn_dt)
    FROM C_User_Profile
    WHERE DATEPART(WEEK, C_User_Profile.crn_dt) BETWEEN 0 AND 13
    GROUP BY datepart(YEAR, crn_dt)
  END
ELSE
  BEGIN
    PRINT 0
  END
    UNION ALL
IF (DATEPART(WEEK, @date) > 13 AND DATEPART(WEEK, @date) <= 26)
  BEGIN
    SELECT
      count(ip),
      datepart(YEAR, crn_dt)
    FROM C_User_Profile
    WHERE DATEPART(WEEK, C_User_Profile.crn_dt) BETWEEN 14 AND 26
    GROUP BY datepart(YEAR, crn_dt)
  END
ELSE
  BEGIN
    PRINT 0
  END
    UNION ALL
IF (DATEPART(WEEK, @date) > 26 AND DATEPART(WEEK, @date) <= 39)
  BEGIN
    SELECT
      count(ip),
      datepart(YEAR, crn_dt)
    FROM C_User_Profile
    WHERE DATEPART(WEEK, C_User_Profile.crn_dt) BETWEEN 27 AND 39
    GROUP BY datepart(YEAR, crn_dt)
  END
ELSE
  BEGIN
    PRINT 0
  END
    UNION ALL
IF (DATEPART(WEEK, @date) > 39)
  BEGIN
    SELECT
      count(ip),
      datepart(YEAR, crn_dt)
    FROM C_User_Profile
    WHERE DATEPART(WEEK, C_User_Profile.crn_dt) BETWEEN 40 AND 54
    GROUP BY datepart(YEAR, crn_dt)
  END
ELSE
  BEGIN
    PRINT 0
  END

because I need to display all result so I am using union all with if else condition but I am getting error showing Incorrect syntax near Union Please help me

Upvotes: 2

Views: 2927

Answers (2)

Ashu
Ashu

Reputation: 482

This might work

select count(ip),datepart(year,crn_dt) from C_User_Profile where (DATEPART(week,C_User_Profile.crn_dt) between 0 and 13) and (DATEPART(week,@date)<=13) group by datepart(year,crn_dt)
union all 
select count(ip),datepart(year,crn_dt) from C_User_Profile where (DATEPART(week,C_User_Profile.crn_dt) between 14 and 26) and (DATEPART(week,@date)>13 and DATEPART(week,@date)<=26) group by datepart(year,crn_dt)
union all 
select count(ip),datepart(year,crn_dt) from C_User_Profile where (DATEPART(week,C_User_Profile.crn_dt) between 27 and 39) and (DATEPART(week,@date)>26 and DATEPART(week,@date)<=39) group by datepart(year,crn_dt)
union all 
select count(ip),datepart(year,crn_dt) from C_User_Profile where (DATEPART(week,C_User_Profile.crn_dt) between 40 and 54) and (DATEPART(week,@date)>39) group by datepart(year,crn_dt)

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

That's not a valid syntax in SQL SERVER

Here is one way 

SELECT Count(CASE
               WHEN Datepart(week, C_User_Profile.crn_dt) BETWEEN 0 AND 13
                    AND Datepart(week, @date) <= 13 THEN 1
               WHEN Datepart(week, C_User_Profile.crn_dt) BETWEEN 0 AND 13
                    AND Datepart(week, @date) > 13
                    AND Datepart(week, @date) <= 26 THEN 1
               WHEN Datepart(week, C_User_Profile.crn_dt) BETWEEN 27 AND 39
                    AND Datepart(week, @date) > 26
                    AND Datepart(week, @date) <= 39 THEN 1
               WHEN Datepart(week, C_User_Profile.crn_dt) BETWEEN 40 AND 54
                    AND Datepart(week, @date) > 39 THEN 1
             END),
       Datepart(year, crn_dt)
FROM   C_User_Profile
WHERE  Datepart(week, C_User_Profile.crn_dt) BETWEEN 0 AND 54
GROUP  BY Datepart(year, crn_dt)

Upvotes: 2

Related Questions