SPLady
asked on
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
ASKER
Thanks @Humpdy I added the 'as" and am still getting an parse error
ASKER
How can I rewrite this without the union?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@expert_dharam yes the above part is working!
let me know what u want to acheive next..
ASKER
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
sorry.. i am not able to understand the relationship between the two query sets..
ASKER
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
ASKER
Thank you, removing the date caluculation did correct the parse error--
Thanks for accepting my answer..
Cast(0 AS Numeric(30, 2)) AS 'InvoiceAmount_CurrentYear