July 15, 2013 at 8:40 am
Hi all
Apologies if this is posted in the wrong section but I didnt really know where it belonged.
I have a set of data (snippet of matrix below)
Period 1
Asset CategoryNo Of JobsSum Of SpendAv Of Spend
36 £13,972.49£388.12
A 1044 £266,798.16£255.55
B 911 £187,905.17£206.26
C 185 £44,863.45£242.51
D 195 £106,987.62£548.65
New Equipment25 £61,087.37£2,443.49
Planned Only41 £24,576.29£599.42
Total 2437 £706,190.55
I actually have 8 periods worth of info, what I want to be able to do is forecast the remaining 4 periods based on the 8 periods I do have. So ideally I would take Asset Cat "A" and say for arguments sake I have 1000 jobs £10,000 Spend with an average of £10 per job. I want to apply that average to the average number of jobs so the average would be 1000 jobs divided by 8 periods = 125 jobs a period. so I want Periods 9 - 12 to show as 125*£10 - does that make any sense ???
I could do it easily in excel but how can I do it in SSRS?
Thanks
Carl.
July 15, 2013 at 8:53 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
July 15, 2013 at 9:08 am
I'm sorry but I do not have those things you are asking, I basically have a sql query that gives me;
Period
AssetCategory
Spend
No Jobs
Average (£)
I have 8 periods worth of data, and want to use that to forecast the last 4.
Regards
Carl.
July 15, 2013 at 9:13 am
Here is the SQl that gives my base data;
SELECT
[Cost] = Sum(Case
WHEN JobInvoiceCostQuery.InvoiceCost >0 THEN JobInvoiceCostQuery.InvoiceCost
WHEN Jobs.JobStatus = 'X' THEN 0
WHEN Jobs.JobStatus = 'B' THEN 0
WHEN Jobs.JobStatus = 'D' THEN 0
WHEN Jobs.ReferredCost >0 THEN Jobs.ReferredCost
WHEN Jobs.EstimatedCost <>200 THEN Jobs.EstimatedCost
ELSE assets.AccuralValue
END),
[Category] = case
WHEN jobs.SubAssetID IS NOT NULL THEN SubAssets.Category
ELSE assets.Category
END,
SGP_AT1ClientGroupPeriod.PeriodNumber,
SGP_AT1ClientGroupPeriod.PeriodYear
FROM
Occupants
JOIN Jobs ON Occupants.ID = Jobs.OccupantID
JOIN BuildingUnits ON BuildingUnits.ID = Occupants.BuildingUnitID
JOIN Buildings ON Buildings.ID = BuildingUnits.BuildingID
JOIN Districts ON Buildings.DistrictID = Districts.ID
JOIN SupplierAreasDistrictLinks ON Districts.ID = SupplierAreasDistrictLinks.DistrictID
JOIN SupplierAreas ON SupplierAreasDistrictLinks.SupplierAreaID = SupplierAreas.ID
JOIN assets ON Jobs.Asset = assets.ID
LEFT JOIN SubAssets ON Jobs.SubAssetID = SubAssets.ID
LEFT JOIN JobStatusCodes ON Jobs.JobStatus = JobStatusCodes.Code
LEFT JOIN Priorities ON Jobs.PriorityCode = Priorities.ID
LEFT JOIN JobCausations ON Jobs.CausationID = JobCausations.ID
JOIN Contractors ON Jobs.Contractor = Contractors.ID
LEFT JOIN JobInvoiceCostQuery ON Jobs.JobCode = JobInvoiceCostQuery.JobCode
INNER JOIN SGP_AT1Clients ON Occupants.TradingGroup = SGP_AT1Clients.TradingGroup
LEFT JOIN SGP_AT1ClientGroupPeriod ON SGP_AT1Clients.MasterClientGroupID = SGP_AT1ClientGroupPeriod.ClientGroupID
WHERE
Jobs.Reactive = 1
AND Occupants.TradingGroup = 'KFC'
AND (Jobs.RequestedAt >= SGP_AT1ClientGroupPeriod.PeriodStart AND Jobs.RequestedAt <= SGP_AT1ClientGroupPeriod.PeriodEnd)
AND Jobs.JobStatus NOT IN ('X', 'D')
AND SGP_AT1ClientGroupPeriod.PeriodYear = 2013
Group By
SGP_AT1ClientGroupPeriod.PeriodNumber,
SGP_AT1ClientGroupPeriod.PeriodYear,
case WHEN jobs.SubAssetID IS NOT NULL THEN SubAssets.Category
ELSE assets.Category
END
July 15, 2013 at 9:28 am
carl.meads (7/15/2013)
I'm sorry but I do not have those things you are asking, I basically have a sql query that gives me;Period
AssetCategory
Spend
No Jobs
Average (£)
I have 8 periods worth of data, and want to use that to forecast the last 4.
Regards
Carl.
Without something to work with there is little anybody can do. About all I can say is that you can use some of the aggregate functions to get your averages and total and then use those to calculate the remaining 4 periods.
_______________________________________________________________
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/
July 15, 2013 at 6:59 pm
I agree with Sean that there's little to go on, but I'll give it a shot even though my suggestion will probably be wrong. All the 2 CTEs do is set up 8 periods of data. It is the code below that you need.
WITH Period1 ([Asset Category],[No Of Jobs],[Sum Of Spend],[Av Of Spend]) AS (
-- Only Period 1 provided by OP and sample data was missing asset category XX
SELECT 'XX', 36,£13972.49,£388.12
UNION ALL SELECT 'A', 1044, £266798.16, £255.55
UNION ALL SELECT 'B', 911, £187905.17, £206.26
UNION ALL SELECT 'C', 185, £44863.45, £242.51
UNION ALL SELECT 'D', 195, £106987.62, £548.65
UNION ALL SELECT 'New Equipment', 25,£61087.37, £2443.49
UNION ALL SELECT 'Planned Only', 41,£24576.29, £599.42
UNION ALL SELECT 'Total', 2437, £706190.55, NULL
)
-- All this does is convert 1 period to 8 periods
,AllPeriods AS (
SELECT Period, [Asset Category],[No Of Jobs]=SUM([No Of Jobs])
,[Sum Of Spend]=SUM([Sum Of Spend])
,[Av Of Spend]=SUM([Sum Of Spend])/SUM([No Of Jobs])
FROM (
SELECT Period=n, [Asset Category], [No Of Jobs], [Sum Of Spend], [Av Of Spend]
FROM Period1
CROSS APPLY (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
) Tally(n)
) a
GROUP BY Period, [Asset Category])
SELECT Period=CASE n WHEN 0 THEN Period ELSE CAST(n AS VARCHAR(4)) END
,[Asset Category],[No Of Jobs],[Sum Of Spend],[Av Of Spend]
FROM (
-- Calculate the averages over the 8 periods of data we now have
SELECT Period='1-8',[Asset Category],[No Of Jobs]=SUM([No Of Jobs])
,[Sum Of Spend]=SUM([Sum Of Spend])
,[Av Of Spend]=SUM([Sum Of Spend])/SUM([No Of Jobs])
FROM AllPeriods
GROUP BY [Asset Category]) a
CROSS APPLY (
SELECT 0 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) Tally (n)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply