November 8, 2008 at 7:57 am
I have the following query that returns to me the quotas and sales for sales reps. Currently, because of the way the query is written, it returns all quotas and results from July to October. However, I want it to return, in addition, the quota values for the remainder of the year - Novebmer and December. So, for those two months, or depending on the year, any months that haven't happened yet I want it to return either 0 or null values for the remaining months of the year that haven't happened yet for the sales values. I believe I need to make a change on the join to the dimQuota table but I've tried a couple of things without any success. If anyone has ideas on how I can accomplish this I would welcom the input. Thanks and here's the query:
ALTER PROCEDURE udsp_scorecard_report_revenueDrillDown
@year int,
@employeeNumber int
AS
SELECT dD.month_name,
dE.emp_last_first,
dE.tier,
fSP.employee_number,
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN (dQ.ytdBkQuota * dQ.percent_tier_quota) - (SELECT (ytdBKQuota * dQ.percent_tier_quota) FROM dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdBkQuota * dQ.percent_tier_quota END AS [YTD Booked Quota],
dQ.ytdminBkQuota *dQ.percent_tier_quota AS [YTD Min Booked Quota],
dQ.moBkQuota * dQ.percent_tier_quota AS [Mo Booked Quota],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.booked_revenue END) AS [Revenue Booked],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.gross_profit END) AS [Revenue Gross Profit],
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN dQ.ytdCRQuota - (SELECT ytdCRQuota FROM dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdCRQuota END AS [YTD Renewal Quota],
dQ.ytdminCRQuota AS [YTD Min Renewal Quota],
dQ.moCRQuota,
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.booked_revenue END) AS [Renewals Booked],
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.gross_profit END) AS [Renewals Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.booked_revenue END) AS [Allocations Booked],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.gross_profit END) AS [Allocations Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.booked_revenue END) AS [Exceptions Booked],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.gross_profit END) AS [Exceptions Gross Profit]
FROM factScorecard_performance_rev_measure_dd fSP JOIN dimDate dD ON fSP.begin_date = dD.ddid
JOIN dimQuotas dQ ON dD.month_of_year = dQ.month AND dD.calendar_year = dQ.year AND dQ.employee_number = fSP.employee_number
JOIN dimEmployee dE ON fsp.employee_id = dE.employee_dimid
WHERE fSP.employee_number = @employeeNumber
AND dD.calendar_year = @year
AND dE.is_current = 'True'
GROUP BY dD.month_name,
dD.month_of_year,
fSP.employee_number,
dE.emp_last_first,
dE.tier,
dQ.ytdBkQuota ,
dQ.ytdminBkQuota,
dQ.moBkQuota,
dQ.ytdCRQuota,
dQ.ytdminCRQuota,
dQ.moCRQuota,
dQ.[month],
dQ.percent_tier_quota
ORDER BY dD.month_of_year
November 8, 2008 at 11:20 am
Hello,
If you use a Right Outer Join on dimDate, that will get you all records from dimDate even when there aren’t any matching records in the (Alias) fSP Table.
As the Where clause limits results to the required Year only, then this change may be enough to get the results that you want.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 8, 2008 at 1:36 pm
I had tried that but it didn't make a difference. I also tried putting the right outer join on both dimDate and dimQuotas and that didn't work either. Could the multiple joins on the dimQuotas table be where I'm having an issue?
November 8, 2008 at 2:36 pm
Can you post the table definitions (as create scripts), some sample data (as insert statements) and your expected results please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2008 at 5:02 pm
Yes, I can. I'll prepare some and post them tomorrow.
November 9, 2008 at 6:04 am
Hello Again,
I would suspect that the Where clause is filtering out the required records.
Can you try commenting out the Where clause, except the check on the Year, and see if you then get the data that you want? (You’ll probably want to add in a temporary “Top 1000” or similar, when testing this).
If it is the Where clause then you can fix the issue by using the following technique:-
IsNull(dE.is_current, ‘True’) = 'True'
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 9, 2008 at 12:58 pm
I tried what you suggested with the WHERE clause but no luck.
Here is a script that builds the tables and loads them with sample data. What I want is what I am getting the way it is now along with the remaining months of the year (Nov and Dec) showing the quota numbers and then zeros for the revenue numbers.
IF OBJECT_ID('tempdb..#factScorecard_performance_rev_measure_dd') IS NOT NULL
DROP TABLE #factScorecard_performance_rev_measure_dd
CREATE TABLE #factScorecard_performance_rev_measure_dd
(
employee_id int NOT NULL,
employee_number int NOT NULL,
revenue_component varchar(20) NOT NULL,
booked_revenue money NOT NULL,
gross_profit money NOT NULL,
begin_date int NOT NULL,
end_date int NOT NULL
)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Booked Revenue', 18461.00, 6634.72, 20080701, 20080731)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Revenue Allocation', 616.97, 373.56, 20080701, 20080731)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Contract Renewal', 8130.00, 8130.00, 20080701, 20080731)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Booked Revenue', 49397.50, 30440.78, 20080701, 20080731)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Revenue Allocation', 1650.87, 1713.95, 20080701, 20080731)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Contract Renewal', 27460.00, 27460.00, 20080701, 20080731)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Booked Revenue', 22533.37, 17233.87, 20081001, 20081031)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Revenue Allocation', 232.26, 294.59, 20081001, 20081031)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Contract Renewal', 450.00, 450.00, 20081001, 20081031)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Booked Revenue', 56930.00, 30271.13, 20081001, 20081031)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Revenue Allocation', 586.80, 517.44, 20081001, 20081031)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Contract Renewal', 12495.00, 12495.00, 20081001, 20081031)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Booked Revenue', 16870.00, 12295.98, 20080801, 20080831)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Revenue Allocation', 1786.52, 1992.74, 20080801, 20080831)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Contract Renewal', 16605.00, 16605.00, 20080801, 20080831)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Booked Revenue', 22765.00, 11938.08, 20080801, 20080831)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Revenue Allocation', 2410.80, 1934.7, 20080801, 20080831)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Contract Renewal', 13350.00, 13350.00, 20080801, 20080831)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Booked Revenue', 1255.00, 1159.70, 20080901, 20080930)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Revenue Allocation', 113.51, 165.83, 20080901, 20080930)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (958, 773, 'Contract Renewal', 5176.20, 5176.20, 20080901, 20080930)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Booked Revenue', 13061.84, 16507.025, 20080901, 20080930)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Revenue Allocation', 1181.35, 2360.36, 20080901, 20080930)
INSERT into #factScorecard_performance_rev_measure_dd (employee_id, employee_number, revenue_component, booked_revenue, gross_profit, begin_date, end_date)
VALUES (972, 82, 'Contract Renewal', 18742.50, 18742.50, 20080901, 20080930)
IF OBJECT_ID('tempdb..#dimEmployee') IS NOT NULL
DROP TABLE #dimEmployee
CREATE TABLE #dimEmployee
(
employee_dimid int NOT NULL,
employee_number int NOT NULL,
emp_last_first varchar(42) NULL,
tier int NULL,
is_current char(5) NULL
)
INSERT into #dimEmployee (employee_dimid, employee_number, emp_last_first, tier, is_current)
VALUES (958, 773, 'Smith, Sally', 4, 'True')
INSERT into #dimEmployee (employee_dimid, employee_number, emp_last_first, tier, is_current)
VALUES (972, 82, 'Clayton, Bob', 5, 'True ')
IF OBJECT_ID('tempdb..#dimDate') IS NOT NULL
DROP TABLE #dimDate
CREATE TABLE #dimDate
(
ddid int NOT NULL,
month_name char(3) NULL,
month_of_year tinyint NULL,
calendar_year smallint NULL
)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20080701, 'Jul', 7, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20080731, 'Jul', 7, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20080801, 'Aug', 8, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20080831, 'Aug', 8, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20080901, 'Sep', 9, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20080930, 'Sep', 9, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20081001, 'Oct', 10, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20081031, 'Oct', 10, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20081101, 'Nov', 11, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20081130, 'Nov', 11, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20081201, 'Dec', 12, 2008)
INSERT into #dimDate (ddid, month_name, month_of_year, calendar_year)
Values(20081231, 'Dec', 12, 2008)
IF OBJECT_ID('tempdb..#dimQuotas') IS NOT NULL
DROP TABLE #dimQuotas
CREATE TABLE #dimQuotas
(
month int NOT NULL,
percent_tier_quota decimal(3, 2) NOT NULL,
ytdBkQuota money NOT NULL,
year int NOT NULL,
ytdminBkQuota money NOT NULL,
moBkQuota money NOT NULL,
ytdCRQuota money NOT NULL,
ytdminCRQuota money NOT NULL,
moCRQuota money NOT NULL,
employee_number int NOT NULL
)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (7, 1.00, 314000.00, 2008, 21200.00, 26500.00, 131371.00, 16040.00, 20050.00, 773)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (8, 1.00, 340500.00, 2008, 42400.00, 26500.00, 139192.00, 22296.80, 7821.00, 773)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (9, 1.00, 367000.00, 2008, 63600.00, 26500.00, 157292.50, 36777.20, 18100.50, 773)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (10, 1.00, 393500.00, 2008, 84800.00, 26500.00, 157742.50, 37137.20, 450.00, 773)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (11, 1.00, 420000.00, 2008, 106000.00, 26500.00, 157742.50, 37137.20, 0.00, 773)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (12, 1.00, 446500.00, 2008, 127200.00, 26500.00, 174417.50, 50477.20, 16675.00, 773)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (7, 1.00, 193959.00, 2008, 26000.00, 32500.00, 69085.00, 5400.00, 6750.00, 82)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (8, 1.00, 226459.00, 2008, 52000.00, 32500.00, 101635.00, 31440.00, 32550.00, 82)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (9, 1.00, 258959.00, 2008, 78000.00, 32500.00, 118345.00, 44808.00, 16710.00, 82)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (10, 1.00, 291459.00, 2008, 104000.00, 32500.00, 133615.00, 57024.00, 15270.00, 82)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (11, 1.00, 323959.00, 2008, 130000.00, 32500.00, 141000.00, 62932.00, 7385.00, 82)
INSERT into #dimQuotas ([month], percent_tier_quota, ytdBkQuota, [year], ytdminBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota, employee_number)
VALUES (12, 1.00, 356459.00, 2008, 156000.00, 32500.00, 160380.00, 78436.00, 19380.00, 82)
DECLARE@year int
DECLARE @employeeNumber int
SET @year = 2008
SET @employeeNumber = 82
SELECT dD.month_name,
dE.emp_last_first,
dE.tier,
fSP.employee_number,
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN (dQ.ytdBkQuota * dQ.percent_tier_quota) - (SELECT (ytdBKQuota * dQ.percent_tier_quota) FROM dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdBkQuota * dQ.percent_tier_quota END AS [YTD Booked Quota],
dQ.ytdminBkQuota *dQ.percent_tier_quota AS [YTD Min Booked Quota],
dQ.moBkQuota * dQ.percent_tier_quota AS [Mo Booked Quota],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.booked_revenue END) AS [Revenue Booked],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.gross_profit END) AS [Revenue Gross Profit],
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN dQ.ytdCRQuota - (SELECT ytdCRQuota FROM dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdCRQuota END AS [YTD Renewal Quota],
dQ.ytdminCRQuota AS [YTD Min Renewal Quota],
dQ.moCRQuota,
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.booked_revenue END) AS [Renewals Booked],
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.gross_profit END) AS [Renewals Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.booked_revenue END) AS [Allocations Booked],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.gross_profit END) AS [Allocations Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.booked_revenue END) AS [Exceptions Booked],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.gross_profit END) AS [Exceptions Gross Profit]
FROM #factScorecard_performance_rev_measure_dd fSP JOIN #dimDate dD ON fSP.begin_date = dD.ddid
JOIN #dimQuotas dQ ON dD.month_of_year = dQ.month AND dD.calendar_year = dQ.year AND dQ.employee_number = fSP.employee_number
JOIN #dimEmployee dE ON fsp.employee_id = dE.employee_dimid
WHERE fSP.employee_number = @employeeNumber
AND dD.calendar_year = @year
AND dE.is_current = 'True'
GROUP BY dD.month_name,
dD.month_of_year,
fSP.employee_number,
dE.emp_last_first,
dE.tier,
dQ.ytdBkQuota ,
dQ.ytdminBkQuota,
dQ.moBkQuota,
dQ.ytdCRQuota,
dQ.ytdminCRQuota,
dQ.moCRQuota,
dQ.[month],
dQ.percent_tier_quota
November 10, 2008 at 7:08 am
Hello,
Try changing your query as below and see if that is closer to what you need.
It would still need a bit of work to get the Month and Employee Names in for future months.
Regards.
John Marsh
SELECT
dQ.month,
--fSP.month_name,
dE.emp_last_first,
dE.tier,
-- fSP.employee_number,
dQ.employee_number,
-- CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN (dQ.ytdBkQuota * dQ.percent_tier_quota) - (SELECT (ytdBKQuota * dQ.percent_tier_quota) FROM #dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdBkQuota * dQ.percent_tier_quota END AS [YTD Booked Quota],
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN (dQ.ytdBkQuota * dQ.percent_tier_quota) - (SELECT (ytdBKQuota * dQ.percent_tier_quota) FROM #dimQuotas WHERE [month] = 6 AND [year] = 2008 AND employee_number = @employeeNumber) ELSE dQ.ytdBkQuota * dQ.percent_tier_quota END AS [YTD Booked Quota],
dQ.ytdminBkQuota *dQ.percent_tier_quota AS [YTD Min Booked Quota],
dQ.moBkQuota * dQ.percent_tier_quota AS [Mo Booked Quota],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.booked_revenue END) AS [Revenue Booked],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.gross_profit END) AS [Revenue Gross Profit],
-- CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN dQ.ytdCRQuota - (SELECT ytdCRQuota FROM #dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdCRQuota END AS [YTD Renewal Quota],
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN dQ.ytdCRQuota - (SELECT ytdCRQuota FROM #dimQuotas WHERE [month] = 6 AND [year] = 2008 AND employee_number = @employeeNumber) ELSE dQ.ytdCRQuota END AS [YTD Renewal Quota],
dQ.ytdminCRQuota AS [YTD Min Renewal Quota],
dQ.moCRQuota,
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.booked_revenue END) AS [Renewals Booked],
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.gross_profit END) AS [Renewals Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.booked_revenue END) AS [Allocations Booked],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.gross_profit END) AS [Allocations Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.booked_revenue END) AS [Exceptions Booked],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.gross_profit END) AS [Exceptions Gross Profit]
FROM
#dimQuotas dQ
Left Outer Join
(Select * From #factScorecard_performance_rev_measure_dd Inner Join #dimDate On begin_date = ddid) fSP
ON fSP.month_of_year = dQ.month AND fSP.calendar_year = dQ.year AND dQ.employee_number = fSP.employee_number
Left Outer JOIN #dimEmployee dE
ON fsp.employee_id = dE.employee_dimid
WHERE
dQ.employee_number = @employeeNumber And
dQ.year = @year And
IsNull(dE.is_current, 'True') = 'True'
GROUP BY
--fSP.month_name,
-- fSP.month_of_year,
-- fSP.employee_number,
dQ.year,
dQ.month,
dQ.employee_number,
dE.emp_last_first,
dE.tier,
dQ.ytdBkQuota ,
dQ.ytdminBkQuota,
dQ.moBkQuota,
dQ.ytdCRQuota,
dQ.ytdminCRQuota,
dQ.moCRQuota,
dQ.[month],
dQ.percent_tier_quota
www.sql.lu
SQL Server Luxembourg User Group
November 10, 2008 at 11:35 am
Thanks, but for the months that have already happened it returns two rows. One with the data and one wtih all null values. For future months it just returns all null values.
If you run the sample that I posted with the sample data I want it to return the rows like it does for those months that have happened and then for the remaining months of the year. In this case, Nov and Dec. I want it to return a row with the month, employee name, tier and employee number and all the quota columns populated accordingly. The non quota columns would be either null or 0 since they haven't happened yet.
November 10, 2008 at 11:42 am
Sorry John, but when I looked earlier there was a message that just said to change the FROM and WHERE clauses to something other than what you have listed here. I'll give this new one a try and see how it works. Thanks for your help and I'll let you know.
November 10, 2008 at 3:08 pm
Thanks for all your help John! Would have probably taken a lot longer and been less efficient if not for your input. Here is what I ended up with:
IF @year = 2008
BEGIN
SELECT
dQ.month,
dQ.emp_last_first,
dQ.tier,
dQ.employee_number,
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN (dQ.ytdBkQuota * dQ.percent_tier_quota) - (SELECT (ytdBKQuota * dQ.percent_tier_quota) FROM #dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdBkQuota * dQ.percent_tier_quota END AS [YTD Booked Quota],
dQ.ytdminBkQuota *dQ.percent_tier_quota AS [YTD Min Booked Quota],
dQ.moBkQuota * dQ.percent_tier_quota AS [Mo Booked Quota],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.booked_revenue END) AS [Revenue Booked],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.gross_profit END) AS [Revenue Gross Profit],
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN dQ.ytdCRQuota - (SELECT ytdCRQuota FROM #dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdCRQuota END AS [YTD Renewal Quota],
dQ.ytdminCRQuota AS [YTD Min Renewal Quota],
dQ.moCRQuota,
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.booked_revenue END) AS [Renewals Booked],
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.gross_profit END) AS [Renewals Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.booked_revenue END) AS [Allocations Booked],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.gross_profit END) AS [Allocations Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.booked_revenue END) AS [Exceptions Booked],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.gross_profit END) AS [Exceptions Gross Profit]
FROM (SELECT dQi.*, dE.emp_last_first, dE.tier FROM #dimQuotas dQi JOIN #dimEmployee dE ON dQi.employee_number = dE.employee_number) dQ LEFT JOIN
(SELECT fPSi.employee_id, fPSi.employee_number, fPSi.revenue_component, fPSi.booked_revenue, fPSi.gross_profit, fPSi.begin_date, dDi.month_of_year, dDi.calendar_year FROM #factScorecard_performance_rev_measure_dd fPSi JOIN #dimDate dDi ON fPSi.begin_date = dDi.ddid) fSP
ON fSP.month_of_year = dQ.[month] AND fSP.calendar_year = dQ.[year] AND dQ.employee_number = fSP.employee_number
LEFT JOIN #dimEmployee dE ON fsp.employee_id = dE.employee_dimid
WHERE dQ.employee_number = @employeeNumber
AND dQ.year = @year
AND ISNULL(dE.is_current, 'True') = 'True'
AND dQ.[month] > 6
GROUP BY dQ.[year],
dQ.[month],
dQ.employee_number,
dQ.emp_last_first,
dQ.tier,
dQ.ytdBkQuota ,
dQ.ytdminBkQuota,
dQ.moBkQuota,
dQ.ytdCRQuota,
dQ.ytdminCRQuota,
dQ.moCRQuota,
dQ.percent_tier_quota,
dQ.is_current
END
ELSE
SELECT
dQ.month,
dQ.emp_last_first,
dQ.tier,
dQ.employee_number,
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN (dQ.ytdBkQuota * dQ.percent_tier_quota) - (SELECT (ytdBKQuota * dQ.percent_tier_quota) FROM #dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdBkQuota * dQ.percent_tier_quota END AS [YTD Booked Quota],
dQ.ytdminBkQuota *dQ.percent_tier_quota AS [YTD Min Booked Quota],
dQ.moBkQuota * dQ.percent_tier_quota AS [Mo Booked Quota],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.booked_revenue END) AS [Revenue Booked],
SUM(CASE WHEN fSP.revenue_component = 'Booked Revenue' THEN fSP.gross_profit END) AS [Revenue Gross Profit],
CASE WHEN @year = 2008 AND dQ.[month] >=7 THEN dQ.ytdCRQuota - (SELECT ytdCRQuota FROM #dimQuotas WHERE [month] = 6 AND [year] = 2008 AND is_current = 'True' AND employee_number = @employeeNumber) ELSE dQ.ytdCRQuota END AS [YTD Renewal Quota],
dQ.ytdminCRQuota AS [YTD Min Renewal Quota],
dQ.moCRQuota,
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.booked_revenue END) AS [Renewals Booked],
SUM(CASE WHEN fSP.revenue_component = 'Contract Renewal' THEN fsp.gross_profit END) AS [Renewals Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.booked_revenue END) AS [Allocations Booked],
SUM(CASE WHEN fSP.revenue_component = 'Revenue Allocation' THEN fsp.gross_profit END) AS [Allocations Gross Profit],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.booked_revenue END) AS [Exceptions Booked],
SUM(CASE WHEN fSP.revenue_component = 'Exception' THEN fsp.gross_profit END) AS [Exceptions Gross Profit]
FROM (SELECT dQi.*, dE.emp_last_first, dE.tier FROM #dimQuotas dQi JOIN #dimEmployee dE ON dQi.employee_number = dE.employee_number) dQ LEFT JOIN
(SELECT fPSi.employee_id, fPSi.employee_number, fPSi.revenue_component, fPSi.booked_revenue, fPSi.gross_profit, fPSi.begin_date, dDi.month_of_year, dDi.calendar_year FROM #factScorecard_performance_rev_measure_dd fPSi JOIN #dimDate dDi ON fPSi.begin_date = dDi.ddid) fSP
ON fSP.month_of_year = dQ.[month] AND fSP.calendar_year = dQ.[year] AND dQ.employee_number = fSP.employee_number
LEFT JOIN #dimEmployee dE ON fsp.employee_id = dE.employee_dimid
WHERE dQ.employee_number = @employeeNumber
AND dQ.year = @year
AND ISNULL(dE.is_current, 'True') = 'True'
GROUP BY dQ.[year],
dQ.[month],
dQ.employee_number,
dQ.emp_last_first,
dQ.tier,
dQ.ytdBkQuota ,
dQ.ytdminBkQuota,
dQ.moBkQuota,
dQ.ytdCRQuota,
dQ.ytdminCRQuota,
dQ.moCRQuota,
dQ.percent_tier_quota,
dQ.is_current
November 11, 2008 at 2:29 am
Your table modeling and query design are terrible! Your method of handling dates is terrible, your method of handling running totals is terrible and there are several places where the normalization...needs work. You should get someone in there for a month or two that knows a bit about data modeling. I only hope this was something you inherited when the original designer resigned in shame.
I've included some partial improvements below, but there are more improvements that are needed. For example, your penchant for keeping YTD running totals in consecutive rows creates what I call a Row-Spanning Dependency. What happens when a revenue amount for July changes for an employee? Perhaps because of an unreported sale or a sale is canceled -- such things can and do happen. The YTD values for August rely on that value and so that must also be updated, which changes the YTD value for Sept and so on. You want to design your data so that if a past sales figure is modified, all the YTD values will automatically reflect that change the next time you run your query. As it is, your design is, in a word, extremely fragile. 😀
I don't mean to be insulting (not, that is, just for the pleasure of being insulting), but there is obviously money at stake here and Stable Accuracy should be at the top of your company's (and your) considerations. (Accuracy is where the query returns correct results. Stable Accuracy is where your query continues to return correct results no matter what changes are made to the underlying data.)
The code below doesn't give you everything you were asking for, but maybe it will start you in that direction. Not everything I have done should be considered a final solution. In particular, the Year2008 table is strictly part of an interim solution that would NOT be part of a final design.
declare @ScoreCard table(
emp_number int NOT NULL,
rev_component varchar(20) NOT NULL,
booked_rev money NOT NULL,
gross_profit money NOT NULL,
RevMonth datetime NOT NULL
);
INSERT into @ScoreCard
(emp_number, rev_component, booked_rev, gross_profit, RevMonth)
select 773, 'Booked rev', 18461.00, 6634.72, '2008-07-01'union all
select 773, 'rev Allocation', 616.97, 373.56, '2008-07-01'union all
select 773, 'Contract Renewal', 8130.00, 8130.00, '2008-07-01'union all
select 773, 'Booked rev', 16870.00, 12295.98, '2008-08-01'union all
select 773, 'rev Allocation', 1786.52, 1992.74, '2008-08-01'union all
select 773, 'Contract Renewal', 16605.00, 16605.00, '2008-08-01'union all
select 773, 'Booked rev', 1255.00, 1159.70, '2008-09-01'union all
select 773, 'rev Allocation', 113.51, 165.83, '2008-09-01'union all
select 773, 'Contract Renewal', 5176.20, 5176.20, '2008-09-01'union all
select 773, 'Booked rev', 22533.37, 17233.87, '2008-10-01'union all
select 773, 'rev Allocation', 232.26, 294.59, '2008-10-01'union all
select 773, 'Contract Renewal', 450.00, 450.00, '2008-10-01'union all
select 82, 'Booked rev', 49397.50, 30440.78, '2008-07-01'union all
select 82, 'rev Allocation', 1650.87, 1713.95, '2008-07-01'union all
select 82, 'Contract Renewal', 27460.00, 27460.00, '2008-07-01'union all
select 82, 'Booked rev', 22765.00, 11938.08, '2008-08-01'union all
select 82, 'rev Allocation', 2410.80, 1934.7, '2008-08-01'union all
select 82, 'Contract Renewal', 13350.00, 13350.00, '2008-08-01'union all
select 82, 'Booked rev', 13061.84, 16507.025, '2008-09-01'union all
select 82, 'rev Allocation', 1181.35, 2360.36, '2008-09-01'union all
select 82, 'Contract Renewal', 18742.50, 18742.50, '2008-09-01'union all
select 82, 'Booked rev', 56930.00, 30271.13, '2008-10-01'union all
select 82, 'rev Allocation', 586.80, 517.44, '2008-10-01'union all
select 82, 'Contract Renewal', 12495.00, 12495.00, '2008-10-01';
declare @Employees TABLE(
emp_number int NOT NULL,
emp_name varchar(42) NULL,
tier int NULL,
is_current char(5) NULL
);
INSERT into @Employees
(emp_number, emp_name, tier, is_current)
select 773, 'Smith, Sally', 4, 'True'union all
select 82, 'Clayton, Bob', 5, 'True ';
declare @Year2008 TABLE(
MonthFirst smalldatetime not NULL,
MonthLast as DateAdd( month, 1, MonthFirst) - 1,
MonthNum as DatePart( month, MonthFirst ),
MonthName as Left( DateName( mm, MonthFirst ), 3)
);
INSERT into @Year2008
(MonthFirst)
select '2008-07-01'union all
select '2008-08-01'union all
select '2008-09-01'union all
select '2008-10-01'union all
select '2008-11-01'union all
select '2008-12-01';
declare @Quotas TABLE(
emp_number int NOT NULL,
[Month] smalldatetime NOT NULL,
pct_quota decimal(3, 2) NOT NULL,
ytdBkQuota money NOT NULL,
[YTD Booked Quota] as ytdBkQuota * pct_quota,
ytdminBkQuota money NOT NULL,
[YTD Min Booked Quota] as ytdminBkQuota * pct_quota,
moBkQuota money NOT NULL,
[Mo Booked Quota] as moBkQuota * pct_quota,
ytdCRQuota money NOT NULL,
ytdMinCRQuota money NOT NULL,
moCRQuota money NOT NULL
);
INSERT into @Quotas
(emp_number, [Month], pct_quota, ytdBkQuota, ytdMinBkQuota, moBkQuota, ytdCRQuota, ytdminCRQuota, moCRQuota)
select 773, '2008-07-01', 1.00, 314000.00, 21200.00, 26500.00, 131371.00, 16040.00, 20050.00union all
select 773, '2008-08-01', 1.00, 340500.00, 42400.00, 26500.00, 139192.00, 22296.80, 7821.00union all
select 773, '2008-09-01', 1.00, 367000.00, 63600.00, 26500.00, 157292.50, 36777.20, 18100.50union all
select 773, '2008-10-01', 1.00, 393500.00, 84800.00, 26500.00, 157742.50, 37137.20, 450.00union all
select 773, '2008-11-01', 1.00, 420000.00, 106000.00, 26500.00, 157742.50, 37137.20, 0.00union all
select 773, '2008-12-01', 1.00, 446500.00, 127200.00, 26500.00, 174417.50, 50477.20, 16675.00union all
select 82, '2008-07-01', 1.00, 193959.00, 26000.00, 32500.00, 69085.00, 5400.00, 6750.00union all
select 82, '2008-08-01', 1.00, 226459.00, 52000.00, 32500.00, 101635.00, 31440.00, 32550.00union all
select 82, '2008-09-01', 1.00, 258959.00, 78000.00, 32500.00, 118345.00, 44808.00, 16710.00union all
select 82, '2008-10-01', 1.00, 291459.00, 104000.00, 32500.00, 133615.00, 57024.00, 15270.00union all
select 82, '2008-11-01', 1.00, 323959.00, 130000.00, 32500.00, 141000.00, 62932.00, 7385.00union all
select 82, '2008-12-01', 1.00, 356459.00, 156000.00, 32500.00, 160380.00, 78436.00, 19380.00;
DECLARE @year int,
@employeeNumber int;
select @year = DatePart( year, GetDate() ),
@employeeNumber = 82;
select y.MonthFirst as [Month], y.MonthNum, y.MonthName,
e.emp_number, e.emp_name, e.tier, x.*
from @Year2008 y
cross join @Employees e
left join(
select Q.emp_number,
Q.[Month],
Q.[YTD Booked Quota],
Q.[YTD Min Booked Quota],
Q.[Mo Booked Quota],
Q.ytdCRQuota as [YTD Renewal Quota],
Q.ytdminCRQuota AS [YTD Min Renewal Quota],
Q.moCRQuota,
SUM(CASE WHEN sc.rev_component = 'Booked rev' THEN sc.booked_rev else 0 END) AS [rev Booked],
SUM(CASE WHEN sc.rev_component = 'Booked rev' THEN sc.gross_profit else 0 END) AS [rev Gross Profit],
SUM(CASE WHEN sc.rev_component = 'Contract Renewal' THEN sc.booked_rev else 0 END) AS [Renewals Booked],
SUM(CASE WHEN sc.rev_component = 'Contract Renewal' THEN sc.gross_profit else 0 END) AS [Renewals Gross Profit],
SUM(CASE WHEN sc.rev_component = 'rev Allocation' THEN sc.booked_rev else 0 END) AS [Allocations Booked],
SUM(CASE WHEN sc.rev_component = 'rev Allocation' THEN sc.gross_profit else 0 END) AS [Allocations Gross Profit],
SUM(CASE WHEN sc.rev_component = 'Exception' THEN sc.booked_rev else 0 END) AS [Exceptions Booked],
SUM(CASE WHEN sc.rev_component = 'Exception' THEN sc.gross_profit else 0 END) AS [Exceptions Gross Profit]
from @ScoreCard sc
join @Quotas Q
on Q.[Month] = sc.RevMonth
and Q.emp_number = sc.emp_number
group by Q.emp_number,
Q.[Month],
Q.[YTD Booked Quota],
Q.[YTD Min Booked Quota],
Q.[Mo Booked Quota],
Q.ytdCRQuota,
Q.ytdminCRQuota,
Q.moCRQuota
) x
on e.emp_number = x.emp_number
and y.MonthFirst = x.[Month]
order by e.emp_number, y.MonthFirst
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
November 11, 2008 at 2:54 am
Hello,
Glad to hear that you are now getting the results that you need.
Best regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 12, 2008 at 9:11 am
Tomm,
Thanks for your input and I understand your points. One thing that I did not mention is that this is all being pulled from a data warehouse. Thus the "strange" way of handling dates. This actually points back to a date dimension that gives great flexiblity in being able to get different date ranges. For the purpose of this example I paired the date dimension down considerably to keep it simple. It also explains why normilization isn't what you would expect, because in a warehouse environment it doesn't follow the same rules as a transactional system.
The YTD totals you refer to are all specific to quotas, not actual sales. These are updated through slowly changing dimensions from the transactional system. So if something changes it, the YTD totals for the quotas update in the dimension. As far as actual sales are concerned we have methods to handle late arriving or changing facts to address what you refer to here.
If this were a transactional system then, yes, it would look vastly different. However, being we are pulling data from a warehouse we have checks in place to handle the issues you had brought up.
November 12, 2008 at 3:01 pm
OK, that explains a lot. In retrospect, I probably should have noticed the warehouse-like structure, but I have not worked a lot with data warehouses and, unfortunately, I have seen way too many atrociously designed databases. Please pardon me if I overreacted.
In the meantime, you can probably still get something useful out of my code. In particular, separate the results set where you will be calculating aggregate totals into a derived table and join the non-aggregate data to it. This makes your query a lot simpler and makes possible the left join where you can get rows for future months.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply