November 16, 2015 at 9:33 am
This statement was written by another person. It is not calculating the correct value. I want to remove the group by function to see the individual line items. I cannot figure out what to remove. I keep getting a ")" error.
As is it runs perfectly and gives me one single line value. There should be about 350 individual lines. I want to eliminate the group by function and re-run to see the individual lines. Any help is appreciated.
SELECT t00.ScenarioID, t01.[GROUP], '' AS GroupName, t01.companygroupaccount, t01.accountnum, t01.accountname, SUM(t00.ActualValue + t00.Forecast)
AS Forecast, 0 AS ForecastPercentage, SUM(t00.BudgetValue) AS Budget, 0 AS BudgetPercentage,
SUM(t00.BudgetValue - (t00.ActualValue + t00.Forecast)) AS Variance, 0 AS VariancePercentage
FROM (SELECT ScenarioID, AnualSalary, ISNULL(EndSalary, 0) AS EndSalary, ISNULL(AutoAllow, 0) AS AutoAllow, ISNULL(FuelAllow, 0) AS FuelAllow,
ISNULL(((ISNULL(AnualSalary, 0) + ISNULL(AutoAllow, 0) + ISNULL(FuelAllow, 0)) / 12) * (CASE WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
- 1 < HeadcountEndYear OR
ISNULL(HeadcountEndYear, '') = '' OR
HeadcountEndYear = 0 THEN 13 WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
- 1 = HeadcountEndYear THEN CASE WHEN HeadcountEndMonth < 10 THEN 0 ELSE HeadcountEndMonth + 1 END WHEN CAST(RIGHT(ScenarioID,
4) AS INT) - 1 > HeadcountEndYear THEN 0 END - CASE WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
- 1 < HeadcountStartYear THEN CASE WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 < HeadcountEndYear OR
ISNULL(HeadcountEndYear, '') = '' OR
HeadcountEndYear = 0 THEN 13 WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
- 1 = HeadcountEndYear THEN CASE WHEN HeadcountEndMonth < 10 THEN 0 ELSE HeadcountEndMonth + 1 END WHEN CAST(RIGHT(ScenarioID,
4) AS INT) - 1 > HeadcountEndYear THEN 0 END WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
- 1 = HeadcountStartYear THEN CASE WHEN HeadcountStartMonth < 10 THEN 10 ELSE HeadcountStartMonth END WHEN CAST(RIGHT(ScenarioID,
4) AS INT) - 1 > HeadcountStartYear THEN 1 END), 0) AS Forecast, ISNULL((EndSalary / 12) * (CASE WHEN CAST(RIGHT(ScenarioID, 4)
AS INT) < HeadcountEndYear OR
ISNULL(HeadcountEndYear, '') = '' OR
HeadcountEndYear = 0 THEN 13 WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
= HeadcountEndYear THEN HeadcountEndMonth + 1 WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
> HeadcountEndYear THEN 0 END - CASE WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
< HeadcountStartYear THEN CASE WHEN CAST(RIGHT(ScenarioID, 4) AS INT) < HeadcountEndYear OR
ISNULL(HeadcountEndYear, '') = '' OR
HeadcountEndYear = 0 THEN 13 WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
= HeadcountEndYear THEN HeadcountEndMonth + 1 WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
> HeadcountEndYear THEN 0 END WHEN CAST(RIGHT(ScenarioID, 4) AS INT)
= HeadcountStartYear THEN HeadcountStartMonth WHEN CAST(RIGHT(ScenarioID, 4) AS INT) > HeadcountStartYear THEN 1 END), 0)
AS BudgetValue, 0 AS ActualValue
FROM US_Planning_HR.dbo.BudgetHR_OutputPayRollTotal AS T00
WHERE (HRType <> 'Temp')
UNION ALL
SELECT (SELECT ScenarioID
FROM US_Planning_HR.dbo.BudgetHR_InputScenario) AS ScenarioID, 0 AS AnualSalary, 0 AS EndSalary, 0 AS AutoAllow,
0 AS FuelAllow, 0 AS Forecast, SUM(BudgetValue) AS BudgetValue, SUM(ActualValue) AS ActualValue
FROM (SELECT t10.YearDate, t10.AccountNum, t10.AccountName, 0 AS BudgetValue, SUM(t10.ActualValue) AS ActualValue
FROM dbo.BudgetOpExpense_ActualSpendings AS t10
WHERE (t10.MonthDate <= '9')
GROUP BY t10.YearDate, t10.AccountNum, t10.AccountName) AS t00
WHERE (AccountNum = '63100300') AND (YearDate ='2016')
GROUP BY YearDate, AccountNum, AccountName) AS t00 LEFT OUTER JOIN
dbo.wikaGLmapping AS t01 ON '63100300' = t01.accountnum
WHERE (t00.ScenarioID =
(SELECT ScenarioID
FROM US_Planning_HR.dbo.BudgetHR_InputScenario))
GROUP BY t00.ScenarioID, t01.[GROUP], t01.companygroupaccount, t01.accountnum, t01.accountname
November 16, 2015 at 9:56 am
Let's start with some formatting so it is legible.
SELECT t00.ScenarioID
,t01.[GROUP]
,'' AS GroupName
,t01.companygroupaccount
,t01.accountnum
,t01.accountname
,SUM(t00.ActualValue + t00.Forecast) AS Forecast
,0 AS ForecastPercentage
,SUM(t00.BudgetValue) AS Budget
,0 AS BudgetPercentage
,SUM(t00.BudgetValue - (t00.ActualValue + t00.Forecast)) AS Variance
,0 AS VariancePercentage
FROM (
SELECT ScenarioID
,AnualSalary
,ISNULL(EndSalary, 0) AS EndSalary
,ISNULL(AutoAllow, 0) AS AutoAllow
,ISNULL(FuelAllow, 0) AS FuelAllow
,ISNULL(((ISNULL(AnualSalary, 0) + ISNULL(AutoAllow, 0) + ISNULL(FuelAllow, 0)) / 12) * (
CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 < HeadcountEndYear
OR ISNULL(HeadcountEndYear, '') = ''
OR HeadcountEndYear = 0
THEN 13
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 = HeadcountEndYear
THEN CASE
WHEN HeadcountEndMonth < 10
THEN 0
ELSE HeadcountEndMonth + 1
END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 > HeadcountEndYear
THEN 0
END - CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 < HeadcountStartYear
THEN CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 < HeadcountEndYear
OR ISNULL(HeadcountEndYear, '') = ''
OR HeadcountEndYear = 0
THEN 13
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 = HeadcountEndYear
THEN CASE
WHEN HeadcountEndMonth < 10
THEN 0
ELSE HeadcountEndMonth + 1
END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 > HeadcountEndYear
THEN 0
END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 = HeadcountStartYear
THEN CASE
WHEN HeadcountStartMonth < 10
THEN 10
ELSE HeadcountStartMonth
END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 > HeadcountStartYear
THEN 1
END
), 0) AS Forecast
,ISNULL((EndSalary / 12) * (
CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) < HeadcountEndYear
OR ISNULL(HeadcountEndYear, '') = ''
OR HeadcountEndYear = 0
THEN 13
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) = HeadcountEndYear
THEN HeadcountEndMonth + 1
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) > HeadcountEndYear
THEN 0
END - CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) < HeadcountStartYear
THEN CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) < HeadcountEndYear
OR ISNULL(HeadcountEndYear, '') = ''
OR HeadcountEndYear = 0
THEN 13
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) = HeadcountEndYear
THEN HeadcountEndMonth + 1
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) > HeadcountEndYear
THEN 0
END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) = HeadcountStartYear
THEN HeadcountStartMonth
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) > HeadcountStartYear
THEN 1
END
), 0) AS BudgetValue
,0 AS ActualValue
FROM US_Planning_HR.dbo.BudgetHR_OutputPayRollTotal AS T00
WHERE (HRType <> 'Temp')
UNION ALL
SELECT (
SELECT ScenarioID
FROM US_Planning_HR.dbo.BudgetHR_InputScenario
) AS ScenarioID
,0 AS AnualSalary
,0 AS EndSalary
,0 AS AutoAllow
,0 AS FuelAllow
,0 AS Forecast
,SUM(BudgetValue) AS BudgetValue
,SUM(ActualValue) AS ActualValue
FROM (
SELECT t10.YearDate
,t10.AccountNum
,t10.AccountName
,0 AS BudgetValue
,SUM(t10.ActualValue) AS ActualValue
FROM dbo.BudgetOpExpense_ActualSpendings AS t10
WHERE (t10.MonthDate <= '9')
GROUP BY t10.YearDate
,t10.AccountNum
,t10.AccountName
) AS t00
WHERE (AccountNum = '63100300')
AND (YearDate = '2016')
GROUP BY YearDate
,AccountNum
,AccountName
) AS t00
LEFT JOIN dbo.wikaGLmapping AS t01 ON '63100300' = t01.accountnum
WHERE (
t00.ScenarioID = (
SELECT ScenarioID
FROM US_Planning_HR.dbo.BudgetHR_InputScenario
)
)
GROUP BY t00.ScenarioID
,t01.[GROUP]
,t01.companygroupaccount
,t01.accountnum
,t01.accountname
You have several queries here. The two inner queries are a UNION ALL. I would start by removing the outer query and just running the two inner ones. The second of your inner queries itself has some subselects. You have to first break this thing apart into its components, then you can start to isolate where the problem is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2015 at 10:57 am
First, your GROUP BY and SELECT clauses are intertwined. You cannot simply remove the GROUP BY clause without updating your SELECT clause.
Second, this line contains a GROUP BY clause and the end of a subquery:
GROUP BY t10.YearDate, t10.AccountNum, t10.AccountName) AS t00
If you are commenting out/removing that line, you are removing the end of the subquery, so you would get an error about expecting a closing ")", which I assume is the error that you are getting.
Drew
P.S.: If you are getting an error message, GIVE THE EXACT ERROR MESSAGE. The error message contains important information, summarizing the error message glosses over that important information.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 18, 2015 at 3:55 pm
Try this and see what you get:
SELECT
t00.ScenarioID,
t01.[GROUP],
'' AS GroupName,
t01.companygroupaccount,
t01.accountnum,
t01.accountname,
t00.ActualValue + t00.Forecast AS Forecast,
0 AS ForecastPercentage,
t00.BudgetValue AS Budget,
0 AS BudgetPercentage,
t00.BudgetValue - (t00.ActualValue + t00.Forecast) AS Variance,
0 AS VariancePercentage
FROM (
SELECT ScenarioID,
AnualSalary,
ISNULL(EndSalary, 0) AS EndSalary,
ISNULL(AutoAllow, 0) AS AutoAllow,
ISNULL(FuelAllow, 0) AS FuelAllow,
ISNULL(((ISNULL(AnualSalary, 0) + ISNULL(AutoAllow, 0) + ISNULL(FuelAllow, 0)) / 12) * (
CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 < HeadcountEndYear OR ISNULL(HeadcountEndYear, '') = '' OR HeadcountEndYear = 0 THEN 13
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 = HeadcountEndYear THEN CASE WHEN HeadcountEndMonth < 10 THEN 0 ELSE HeadcountEndMonth + 1 END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 > HeadcountEndYear THEN 0
END -
CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 < HeadcountStartYear THEN
CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 < HeadcountEndYear OR ISNULL(HeadcountEndYear, '') = '' OR HeadcountEndYear = 0 THEN 13
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 = HeadcountEndYear THEN CASE WHEN HeadcountEndMonth < 10 THEN 0 ELSE HeadcountEndMonth + 1 END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 > HeadcountEndYear THEN 0
END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 = HeadcountStartYear THEN CASE WHEN HeadcountStartMonth < 10 THEN 10 ELSE HeadcountStartMonth END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) - 1 > HeadcountStartYear THEN 1
END), 0) AS Forecast,
ISNULL((EndSalary / 12) * (
CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) < HeadcountEndYear OR ISNULL(HeadcountEndYear, '') = '' OR HeadcountEndYear = 0 THEN 13
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) = HeadcountEndYear THEN HeadcountEndMonth + 1
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) > HeadcountEndYear THEN 0
END -
CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) < HeadcountStartYear THEN
CASE
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) < HeadcountEndYear OR ISNULL(HeadcountEndYear, '') = '' OR HeadcountEndYear = 0 THEN 13
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) = HeadcountEndYear THEN HeadcountEndMonth + 1
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) > HeadcountEndYear THEN 0
END
WHEN CAST(RIGHT(ScenarioID, 4) AS INT) = HeadcountStartYear THEN HeadcountStartMonth WHEN CAST(RIGHT(ScenarioID, 4) AS INT) > HeadcountStartYear THEN 1
END), 0) AS BudgetValue,
0 AS ActualValue
FROM US_Planning_HR.dbo.BudgetHR_OutputPayRollTotal AS T00
WHERE (HRType <> 'Temp')
UNION ALL
SELECT (SELECT ScenarioID FROM US_Planning_HR.dbo.BudgetHR_InputScenario) AS ScenarioID,
0 AS AnualSalary,
0 AS EndSalary,
0 AS AutoAllow,
0 AS FuelAllow,
0 AS Forecast,
SUM(BudgetValue) AS BudgetValue,
SUM(ActualValue) AS ActualValue
FROM (
SELECT t10.YearDate,
t10.AccountNum,
t10.AccountName,
0 AS BudgetValue,
SUM(t10.ActualValue) AS ActualValue
FROM dbo.BudgetOpExpense_ActualSpendings AS t10
WHERE t10.MonthDate <= '9'
GROUP BY t10.YearDate, t10.AccountNum, t10.AccountName
) AS t00
WHERE (AccountNum = '63100300') AND (YearDate ='2016')
GROUP BY YearDate, AccountNum, AccountName
) AS t00
LEFT OUTER JOIN dbo.wikaGLmapping AS t01
ON '63100300' = t01.accountnum
WHERE t00.ScenarioID = (SELECT ScenarioID FROM US_Planning_HR.dbo.BudgetHR_InputScenario)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 19, 2015 at 2:34 pm
worked perfectly thank you. got 351 individual rows instead of 1 single row. I will compare to my original code, see your changes, and learn something new 🙂 thanks again.
November 19, 2015 at 4:30 pm
You're welcome.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply