Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-02 : 05:14:08

Can someone look what's wrong with my code?

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'UNION'.


BEGIN
		  SELECT 	COMPANYID, SUM(BLCOUNT) AS BLCOUNT, 
			  [MONTHNAME] + ' ' + CAST([YEAR] AS VARCHAR(10)) AS [MONTHYEAR] 
		  FROM	(
				  SELECT 	COMPANYID, 
				  COUNT(BLNUMBER) AS BLCOUNT, 
				  DATENAME(m, RECEIVEDATE) AS [MONTHNAME], 
				  YEAR(RECEIVEDATE) AS [YEAR], 
				  DATEPART(m, RECEIVEDATE) AS SORTBYMONTH
				  FROM 	OSUSR_G8V_BLINFO
				  WHERE 	DATEPART(m, RECEIVEDATE) >= @MonthFrom AND DATEPART(m, RECEIVEDATE) <= @MonthTo 
				  AND 	DATEPART(year, RECEIVEDATE) = @Year
				  GROUP BY COMPANYID, RECEIVEDATE

				  UNION ALL

				  SELECT	COMPANYID, 
				  0 as BLCOUNT, 
				  DATENAME(month, dateadd(month, number - 1, CAST(DATEPART(YEAR, RECEIVEDATE) AS VARCHAR(5)) + '0101')) as MONTHNAME,
				  CAST(DATEPART(YEAR, RECEIVEDATE) AS VARCHAR(5)) AS [YEAR],
				  number as SORTBYMONTH
				  FROM	OSUSR_G8V_BLINFO CROSS JOIN master..spt_values
				  WHERE	type	= 'P'
				  AND	number	between @MonthFrom and @MonthTo
			  ) AS BLLIST
		  GROUP BY COMPANYID, [MONTHNAME], [YEAR], SORTBYMONTH
		  ORDER BY BLLIST.COMPANYID, SORTBYMONTH
		  
		  UNION ALL
		  
		  SELECT 	COMPANYID, SUM(BLCOUNT)/((@MonthFrom - @MonthTo) + 1) AS BLCOUNT, 
			  'AVERAGE' AS [MONTHYEAR] 
		  FROM	(
				  SELECT 	COMPANYID, 
				  COUNT(BLNUMBER) AS BLCOUNT, 
				  DATENAME(m, RECEIVEDATE) AS [MONTHNAME], 
				  YEAR(RECEIVEDATE) AS [YEAR], 
				  DATEPART(m, RECEIVEDATE) AS SORTBYMONTH
				  FROM 	OSUSR_G8V_BLINFO
				  WHERE 	DATEPART(m, RECEIVEDATE) >= @MonthFrom AND DATEPART(m, RECEIVEDATE) <= @MonthTo 
				  AND 	DATEPART(year, RECEIVEDATE) = @Year
				  GROUP BY COMPANYID, RECEIVEDATE
			  ) AS BLTOTAL
		  GROUP BY COMPANYID, SORTBYMONTH
		  ORDER BY BLTOTAL.COMPANYID, SORTBYMONTH	   
	   END

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 05:56:41

~~ORDER BY BLLIST.COMPANYID, SORTBYMONTH~~
UNION ALL

Remove ORDER BY clause before UNION ALL

--
Chandu

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-02 : 06:07:50

I need that line.

the how will I get the sum of BLCOUNT column and include in my result as my average without removing the order by part?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 08:04:19

>>the how will I get the sum of BLCOUNT column and include in my result as my average without removing the order by part?
I haven't get what does mean?

--Check once the below query

;WITH CteBLTOTAL AS 
(
	SELECT 	COMPANYID, 
	  COUNT(BLNUMBER) AS BLCOUNT, 
	  DATENAME(m, RECEIVEDATE) AS [MONTHNAME], 
	  YEAR(RECEIVEDATE) AS [YEAR], 
	  DATEPART(m, RECEIVEDATE) AS SORTBYMONTH
	  FROM 	OSUSR_G8V_BLINFO
	  WHERE 	DATEPART(m, RECEIVEDATE) >= @MonthFrom AND DATEPART(m, RECEIVEDATE) <= @MonthTo 
	  AND 	DATEPART(year, RECEIVEDATE) = @Year
	GROUP BY COMPANYID, RECEIVEDATE
)
SELECT 	COMPANYID, SUM(BLCOUNT) AS BLCOUNT, [MONTHNAME] + ' ' + CAST([YEAR] AS VARCHAR(10)) AS [MONTHYEAR] 
FROM (SELECT COMPANYID, BLCOUNT, [MONTHNAME], [YEAR], SORTBYMONTH FROM CteBLTOTAL 
		 UNION ALL
		SELECT	COMPANYID, 0 as BLCOUNT, 
		  DATENAME(month, dateadd(month, number - 1, CAST(DATEPART(YEAR, RECEIVEDATE) AS VARCHAR(5)) + '0101')) as MONTHNAME,
		  CAST(DATEPART(YEAR, RECEIVEDATE) AS VARCHAR(4)) AS [YEAR],
		  number as SORTBYMONTH
		  FROM	OSUSR_G8V_BLINFO CROSS JOIN master..spt_values
		  WHERE	type	= 'P'
		  AND	number	between @MonthFrom and @MonthTo
	  ) AS BLLIST
GROUP BY COMPANYID, [MONTHNAME], [YEAR], SORTBYMONTH
UNION ALL
SELECT 	COMPANYID, SUM(BLCOUNT)/((@MonthFrom - @MonthTo) + 1) AS BLCOUNT, 'AVERAGE' AS [MONTHYEAR] 
FROM CteBLTOTAL
GROUP BY COMPANYID, SORTBYMONTH
ORDER BY BLTOTAL.COMPANYID, SORTBYMONTH

--
Chandu

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-02 : 22:20:16

My result should be like this.


COMPANYID	BLCOUNT	MONTHYEAR
1	        0	April 2013
1	        15	May 2013
1	        67	June 2013
1	        9	July 2013
1               22.75   AVERAGE
2	        0	April 2013
2	        7	May 2013
2	        1	June 2013
2	        2	July 2013
2               2.5     AVERAGE

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-02 : 23:24:51

Got an error

Msg 104, Level 16, State 1, Line 33
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-03 : 02:18:06

Thanks @bandi. I'm no longer pursue this solution, I have made in other way. Thank you again.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-03 : 03:08:04

--May be this?

;WITH CteBLTOTAL AS 
(
	SELECT 	COMPANYID, 
	  COUNT(BLNUMBER) AS BLCOUNT, 
	  DATENAME(m, RECEIVEDATE) AS [MONTHNAME], 
	  YEAR(RECEIVEDATE) AS [YEAR], 
	  DATEPART(m, RECEIVEDATE) AS SORTBYMONTH
	  FROM 	OSUSR_G8V_BLINFO
	  WHERE 	DATEPART(m, RECEIVEDATE) >= @MonthFrom AND DATEPART(m, RECEIVEDATE) <= @MonthTo 
	  AND 	DATEPART(year, RECEIVEDATE) = @Year
	GROUP BY COMPANYID, RECEIVEDATE
)
SELECT 	COMPANYID, SUM(BLCOUNT) AS BLCOUNT, [MONTHNAME] + ' ' + CAST([YEAR] AS VARCHAR(10)) AS [MONTHYEAR], SORTBYMONTH 
FROM (SELECT COMPANYID, BLCOUNT, [MONTHNAME], [YEAR], SORTBYMONTH FROM CteBLTOTAL 
		 UNION ALL
		SELECT	COMPANYID, 0 as BLCOUNT, 
		  DATENAME(month, dateadd(month, number - 1, CAST(DATEPART(YEAR, RECEIVEDATE) AS VARCHAR(5)) + '0101')) as MONTHNAME,
		  CAST(DATEPART(YEAR, RECEIVEDATE) AS VARCHAR(4)) AS [YEAR],
		  number as SORTBYMONTH
		  FROM	OSUSR_G8V_BLINFO CROSS JOIN master..spt_values
		  WHERE	type	= 'P'
		  AND	number	between @MonthFrom and @MonthTo
	  ) AS BLLIST
GROUP BY COMPANYID, [MONTHNAME], [YEAR], SORTBYMONTH
UNION ALL
SELECT 	COMPANYID, SUM(BLCOUNT)/((@MonthFrom - @MonthTo) + 1) AS BLCOUNT, 'AVERAGE' AS [MONTHYEAR], SORTBYMONTH
FROM CteBLTOTAL
GROUP BY COMPANYID, SORTBYMONTH
ORDER BY BLTOTAL.COMPANYID, SORTBYMONTH

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources