SQL Report Server (t-sql) Parse Error

Hi Experts! How can I optimize/fix this query and why am I getting this error (attached)
SELECT     *
FROM         (SELECT     transDate, transYear, RecordID, QTY, extendedstandardCost, currencyCode, BusinessEntity, product, site, lineValue AS 'OrderAmount', 
                                              Cast(0 AS NUMERIC(30, 2)) AS 'InvoiceAmount', subproduct, CASE WHEN TransYear = Year(getdate()) 
                                              - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_LastYear', Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_LastYear', 
                                              CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_CurrentYear', 
                                              Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_CurrentYear'
                       FROM          vReportData
                       WHERE      (recType = 'order') AND (businessEntity = 'Energy')
                       UNION
                       SELECT     transDate, transYear, RecordID, QTY, extendedstandardCost, currencyCode, BusinessEntity, product, site, 0 AS 'OrderAmount', 
                                             lineValue AS 'InvoiceAmount', subproduct, Cast(0 AS Numeric(30, 2)) 'OrderAmount_LastYear', CASE WHEN TransYear = Year(getdate()) 
                                             - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_LastYear', Cast(0 AS Numeric(30, 2)) 
                                             'OrderAmount_CurrentYear', CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) 
                                             END AS 'InvoiceAmount_CurrentYear'
                       FROM         vReportData AS ReportData1
                       WHERE     (recType = 'invoice') AND (businessEntity = 'Energy')) A,
                          (SELECT     20 + COUNT(*) WorkDayCount
                            FROM          (SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 28) AS theDate
                                                    UNION
                                                    SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 29)
                                                    UNION
                                                    SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 30)) AS d
                            WHERE      DATEPART(DAY, theDate) > 28 AND DATEDIFF(DAY, 0, theDate) % 7 < 5) B

Open in new window

Error.doc
LVL 1
SPLadyAsked:
Who is Participating?
 
expert_dharamConnect With a Mentor Commented:
let me know wether this part of your query is successfully running..
i have modified it..
if it is.. i will assist u further..
SELECT transDate, 
				transYear, 
				RecordID, 
				QTY, 
				extendedstandardCost, 
				currencyCode, 
				BusinessEntity, 
				product, 
				site, 
				lineValue AS 'OrderAmount', 
                Cast(0 AS NUMERIC(30, 2)) AS 'InvoiceAmount', 
                subproduct, 
                CASE WHEN TransYear = Year(getdate()) - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_LastYear', 
                Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_LastYear', 
                CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_CurrentYear', 
                Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_CurrentYear'
			FROM vReportData
			WHERE (recType = 'order') AND (businessEntity = 'Energy')
			
				UNION
				
			SELECT	transDate, 
					transYear, 
					RecordID, 
					QTY, 
					extendedstandardCost, 
					currencyCode, 
					BusinessEntity, 
					product, 
					site, 
					0 AS 'OrderAmount', 
					lineValue AS 'InvoiceAmount', 
					subproduct, 
					Cast(0 AS Numeric(30, 2)) AS 'OrderAmount_LastYear', 
					CASE WHEN TransYear = Year(getdate()) - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_LastYear', 
					Cast(0 AS Numeric(30, 2)) AS 'OrderAmount_CurrentYear', 
					CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_CurrentYear'
			FROM         vReportData AS ReportData1
			WHERE     (recType = 'invoice') AND (businessEntity = 'Energy')

Open in new window

0
 
HumpdyCommented:
at the end of your first select statement, put in AS

 Cast(0 AS Numeric(30, 2)) AS 'InvoiceAmount_CurrentYear'
0
 
SPLadyAuthor Commented:
Thanks @Humpdy I added the 'as" and am still getting an parse error
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SPLadyAuthor Commented:
How can I rewrite this without the union?
0
 
SPLadyAuthor Commented:
@expert_dharam yes the above part is working!
0
 
expert_dharamCommented:
let me know what u want to acheive next..
0
 
SPLadyAuthor Commented:

Thank you @ expert_dharam
I am using it as part of the attached query is there a way to optimize the whole thing? Sometimes the above join indicates I need to declare the start and end and sometimes it doesnt.
SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity, 
                      vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear, 
                      vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear, 
                      vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate, 
                      (DATEDIFF(DD, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME), 
                      CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) 
                      AS DATETIME)) + 1) - (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) 
                      AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) 
                      AS DOUBLE PRECISION)) AS DATETIME)), DATEADD(DD, - ((DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(MONTH, 1, 
                      GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD, 
                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME))) - 1) 
                      / 7 * 2 - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME)) 
                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, 
                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) 
                      % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS MonthWorkingDays, (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD, 
                      6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1) 
                      / 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end) 
                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg, 
                      vReportdataCurrentPrior.QTY
FROM         vReportdataCurrentPrior INNER JOIN
                      ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND 
                      vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
                      EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber
WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end OR
                      vReportdataCurrentPrior.transDate BETWEEN DATEADD(yy, - 1, @start) AND DATEADD(yy, - 1, @end)) AND (EnergySites.EnergyOrg = @energyorg) AND
                       (vReportdataCurrentPrior.BusinessEntity = 'energy')
ORDER BY vReportdataCurrentPrior.transDate DESC

Open in new window

0
 
expert_dharamCommented:
sorry.. i am not able to understand the relationship between the two query sets..
0
 
SPLadyAuthor Commented:
The part of the query attached  below is from the first query set that you rewrote without the  b (date) part of the query which is used in the final query set above... I want to optimize/rewrite the final query set above with the working days and month working days--  
SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity, 
                      vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear, 
                      vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear, 
                      vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate

Open in new window

0
 
SPLadyAuthor Commented:
Thank you, removing the date caluculation did correct the parse error--
0
 
expert_dharamCommented:
Thanks for accepting my answer..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.