April 25, 2012 at 9:38 am
Hi all,
HELP! I’m a newbie to SQL reporting and I’m stuck on how to generate the output for a new “dashboard” report my GM is requesting. It involves several sections: production, sales, scrap, inventories, etc, but the main premise is he wants a snapshot of the day’s activities and MTD values compared to a Forecast/Budget and what the Forecast is for the entire month. Getting the actuals for a given day or MTD I understand how to get and calculate; where I run into a problem is with the Forecast data. For Sales and Production that data is stored by week, for scrap it’s a Monthly value. Add on top of that he wants to see some products individually on the report but others summarized by a product line. Each product can have multiple transactions in a day. I’ve devised a way to group the items, I called that field “Class”. I can also calculate the date into the week it falls. I understand that for the Daily Forecast value I can take the weekly value/5 and just show that value (No totaling). What I’m not sure is how to calculate the MTD forecast and how to Total the Forecast for the Month.
As for some Sample data, here’s a quick scenario: (Weeks 14-17 fall into the Month of April)
Sales detail
DateCustomerItemClassQtyPrice
4/2/120001AB1ABC51.00
4/2/120007AB1ABC41.00
4/9/120001AB1ABC71.00
4/9/120007AB1ABC61.00
4/2/120001XYZ1XYZ52.00
4/2/120001XYZ2XYZ62.00
4/2/120003XYZ2XYZ52.00
4/7/120001XYZ1XYZ32.00
4/7/120003XYZ1XYZ52.00
4/9/120001XYZ1XYZ72.00
4/9/120003XYZ2XYZ52.00
4/9/120007XYZ2XYZ82.00
Forecast Data
YearWeekClassWeeklyForecast
201214ABC20
201215ABC40
201216ABC50
201217ABC50
201214XYZ50
201215XYZ50
201216XYZ75
201217XYZ100
Output Date Range 04/2/12 thru 04/09/12 (I’ll use the end date as my “Daily values” assume 5 day/wk)
DailyMTDMonth
ClassActualForecast VarianceActualForecast VarianceForecast Variance
ABC13852228-6160 -132
XYZ2010 104660 -14225 -179
April 25, 2012 at 9:58 am
I think you need to rewrite this to explain it a little better. You are rambling a bit in the explanation of what you want to occur and it's not clear.
Also, sample results are nice, but they don't make it easy to read or understand on the forums. You can format them as plain code (to the left of the text box when typing), but really we want to see DDL.
CREATE TABLE xxxx (...)
insert into xxxx select a, b, c, d
This helps us to recreate an environment and better understand what data you have available.
April 25, 2012 at 1:12 pm
OK, like I said I'm a newbie, so here's my 2nd attempt:
I have to create a report that compares actuals vs Forecast at 3 date intervals grouped by Item Class (FClass) which is how the forecast data is stored:
Curent Date, MTD and Total Month (Forecast Value for entire month compared to Actual Sales MTD)
My forecast data is stored on a weekly level.
Report parameters:
StartDate
EndDate (I assume this to be my Current Date)
Year (Hidden Parameter that calculates the year)
FirstDay (Hidden Parameter for first day the year)
I also assume there are 5 Sales day in a week
I've simplied my scenario as there are mutiple tables to get the data from, but the test data below will provide enough for what I'm not sure how to calculate:
Which is the MTD Forecast Amount and Total Month Forecast Amount
---- Clear the current target table, If it exists, drop it
IF OBJECT_ID('TempDB..dbo.bdt_Forecast','U') IS NOT NULL
DROP TABLE dbo.bdt_Forecast
CREATE TABLE [dbo].[bdt_Forecast](
[FID] [bigint] IDENTITY(1,1) NOT NULL,
[FYear] [bigint] NULL,
[FWeekNumber] [bigint] NULL,
[FCLass] [nvarchar](50) NOT NULL DEFAULT (''),
[WeeklyForecastQty] [float] NULL,
CONSTRAINT [PK_bdt_Forecast] PRIMARY KEY CLUSTERED
(
[FID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
--===== Insert the test data into the test table
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT bdt_Forecast ON
INSERT INTO bdt_Forecast (FID, FYear, FWeekNumber, FCLass, WeeklyForecastQty)
SELECT '1','2012','14','ABC','20' UNION ALL
SELECT '2','2012','15','ABC','40' UNION ALL
SELECT '3','2012','16','ABC','50' UNION ALL
SELECT '4','2012','17','ABC','50' UNION ALL
SELECT '5','2012','14','XYZ','50' UNION ALL
SELECT '6','2012','15','XYZ','50' UNION ALL
SELECT '7','2012','16','XYZ','75' UNION ALL
SELECT '8','2012','17','XYZ','100'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT bdt_Forecast OFF
---- Clear the current target table, If it exists, drop it
IF OBJECT_ID('TempDB..dbo.bdt_Sales','U') IS NOT NULL DROP TABLE dbo.bdt_Sales
CREATE TABLE [dbo].[bdt_Sales](
[SID] [bigint] IDENTITY(1,1) NOT NULL,
[SDate] [datetime],
[SCusNumber] [bigint] NULL,
[SItemNumber] [nvarchar](50) NOT NULL DEFAULT (''),
[SCLass] [nvarchar](50) NOT NULL DEFAULT (''),
[SQty] [float] NULL,
[SPrice] [float] NULL,
CONSTRAINT [PK_bdt_Sales] PRIMARY KEY CLUSTERED
(
[SID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
--===== Insert the test data into the test table
SET IDENTITY_INSERT bdt_Sales ON
INSERT INTO bdt_Sales (SID, SDate, SCusNumber, SItemNumber, SCLass, SQty, SPrice)
SELECT '1','Apr 2 2012 12:00AM','0001','AB1','ABC','5', '1.00' UNION ALL
SELECT '2','Apr 2 2012 12:00AM','0007','AB1','ABC','4', '1.00' UNION ALL
SELECT '3','Apr 2 2012 12:00AM','0001','XYZ1','XYZ','5', '2.00' UNION ALL
SELECT '4','Apr 2 2012 12:00AM','0001','XYZ2','XYZ','6', '2.00' UNION ALL
SELECT '5','Apr 2 2012 12:00AM','0003','XYZ2','XYZ','5', '2.00' UNION ALL
SELECT '6','Apr 9 2012 12:00AM','0001','XYZ1','XYZ','3', '2.00' UNION ALL
SELECT '7','Apr 9 2012 12:00AM','0003','XYZ1','XYZ','5', '2.00' UNION ALL
SELECT '8','Apr 10 2012 12:00AM','0001','AB1','ABC','7', '1.00' UNION ALL
SELECT '9','Apr 10 2012 12:00AM','0007','AB1','ABC','6', '1.00' UNION ALL
SELECT '10','Apr 10 2012 12:00AM','0001','XYZ1','XYZ','7', '2.00' UNION ALL
SELECT '11','Apr 10 2012 12:00AM','0003','XYZ2','XYZ','5', '2.00' UNION ALL
SELECT '12','Apr 10 2012 12:00AM','0007','XYZ2','XYZ','8', '2.00'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT bdt_Sales OFF
--- Report code
SELECT
SDate,
SItemNumber,
SQty,
FClass,
WeeklyForecastQty,
SFWeekNumber
FROM bdt_Sales
LEFT OUTER JOIN bdt_Forecast ON FYear = @Year
AND (Floor(DateDiff(day,@FirstDay,SDate)/7) + 1) = FWeekNumber
AND bdt_Forecast.FCLASS = bdt_Sales.SClass
WHERE SDate >= dbo.wfn_GetSimpleDate(@StartDate)
AND SDate < DATEADDd,1,dbo.wfn_GetSimpleDate(@EndDate))
ORDER BY
FClass
April 25, 2012 at 2:30 pm
Better, though I've formatted the code. There are formatting items to the side of the edit box that make it much easier to read.
FirstDay - First day of the year? Not sure what you're doing here. Are you trying to calculate a week number here? Not sure what you mean.
For the range of the dates (start -> end), what are the three date intervals? You mention that in the description, but then don't explain it. I get that you are looking for a return of data by week and by fclass, so for each week you want a comparison of forecast v sales for each FClass/SClass (I assume these are equal since you have it that way in the WHERE clause.
In terms of the forecast, this is by week, right? So does this need to be summed up for the MTD? Or is it by week?
It's not quite clear what you want to return for the results. It would help if you showed the results for this sample data, but show it in a formatted way (Use the code = plain formatting when you enter it. Then describe what goes into each result row. What is the calculation you're expecting?
I think this is fairly complex, and if you haven't worked with these windows of data, it can be hard to write the T-SQL. However it's not clear exactly what you've been asked for. Once you get that, it will be easier to sort out the T-SQL that's needed.
You might read about this a bit more as well:
April 25, 2012 at 7:37 pm
I'll focus on converting a weekly forecast to a monthly forecast. The normal method for doing this is 4-4-5 (first 2 months of a quarter absorb 4 weeks each of the forecast, last month gets 5).
You can calculate this forecast this way (note that I extended your data to the full quarter:
DECLARE @fcst TABLE (Year INT, Week INT, Class VARCHAR(10), Qty INT)
INSERT INTO @fcst
SELECT 2012,14,'ABC',20
UNION ALL SELECT 2012,15,'ABC',40
UNION ALL SELECT 2012,16,'ABC',50
UNION ALL SELECT 2012,17,'ABC',50
UNION ALL SELECT 2012,18,'ABC',40
UNION ALL SELECT 2012,19,'ABC',50
UNION ALL SELECT 2012,20,'ABC',50
UNION ALL SELECT 2012,21,'ABC',40
UNION ALL SELECT 2012,22,'ABC',50
UNION ALL SELECT 2012,23,'ABC',50
UNION ALL SELECT 2012,24,'ABC',40
UNION ALL SELECT 2012,25,'ABC',50
UNION ALL SELECT 2012,26,'ABC',50
UNION ALL SELECT 2012,14,'XYZ',50
UNION ALL SELECT 2012,15,'XYZ',50
UNION ALL SELECT 2012,16,'XYZ',75
UNION ALL SELECT 2012,17,'XYZ',100
UNION ALL SELECT 2012,18,'XYZ',50
UNION ALL SELECT 2012,19,'XYZ',50
UNION ALL SELECT 2012,20,'XYZ',75
UNION ALL SELECT 2012,21,'XYZ',100
UNION ALL SELECT 2012,22,'XYZ',50
UNION ALL SELECT 2012,23,'XYZ',50
UNION ALL SELECT 2012,24,'XYZ',75
UNION ALL SELECT 2012,25,'XYZ',100
UNION ALL SELECT 2012,26,'XYZ',50
;WITH wmo (w,m) AS (
SELECT 14,4 UNION ALL SELECT 15,4 UNION ALL SELECT 16,4 UNION ALL SELECT 17,4 UNION ALL SELECT 18,5
UNION ALL SELECT 19,5 UNION ALL SELECT 20,5 UNION ALL SELECT 21,5 UNION ALL SELECT 22,6
UNION ALL SELECT 23,6 UNION ALL SELECT 24,6 UNION ALL SELECT 25,6 UNION ALL SELECT 26,6)
,f445 AS (
SELECT Year, m as Mo, Class, SUM(Qty) AS Qty
FROM @fcst
INNER JOIN wmo ON Week = w
GROUP BY Year, m, Class)
SELECT * FROM f445
Results of 4-4-5 forecast:
YearMoClassQty
20124ABC160
20124XYZ275
20125ABC180
20125XYZ275
20126ABC240
20126XYZ325
Note that if your forecast has a week 53, the last quarter may need to be 5-4-5.
The other way would be to take weeks 13-26, add them up and divide by 3 to get the monthly forecasts. However looking at your data, you'll see that doing it this way loses the seasonality component of the forecast.
Also dividing by 3 will result in fractional quantities (unlikely assuming this is a discrete manufacturing case). You'll lose the fractional amounts by keeping the forecast quantity as an integer, however you could use a technique such as I proposed in my fudge rounding article (http://www.sqlservercentral.com/articles/Financial+Rounding/88067/) to retain the full forecast amount while pushing it to one of the months. Might even be possible to adjust it to the month with the most number of days (or work days).
Incidentally, you didn't mention anything about your actuals needing to be adjusted based on work days, but this may not be required if your plant always operates 5 days a week.
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
April 26, 2012 at 7:02 am
Thanks Steve and dwain.c for the help and I apologize for all the improper formatting.
I'll get right to the point and try to answer your questions Steve (in order)
FirstDay is essentially the FirstDay of our financial year, which for 2012 it's 01/02/2012. As you saw in my code I use that to calculate the week we are in for access the data in the Forecast table.
(Floor(DateDiff(day,@FirstDay,SDate)/7) + 1) = FWeekNumber
Yes the grouping will be by FClass and as for the 3 date intervals, the GM wants to see a Daily value, a MTD value and a Total Month for the Forecast. So with the test data in mind, If I ran the report as:
StartDate: 04/02/2012
End Date: 04/10/2012
My output would be:
Daily MTD Month
Class Act Fcast Var Act Fcast Var Fcast Var
ABC 13 8 5 22 36 -14 160 -132
XYZ 20 10 10 46 70 -24 225 -179
The Daily Values are calculated for actual by totaling the sales for the EndDate (04/10/12); the Forecast would take the week value for Week 15 and divide by 5 to get the daily value. Var = act-fcast Getting these 2 values I think should be easy. I'm thinking my code should be something like (again forgive the syntax):
Case When Sdate = EndDate
THEN SQTY
ELSE 0
END AS DailyActQty
The MTD value for actual sum all sales from 04/02-04/10/12; mtd forecast is the value of Week 14 + (Week 15/5 days * 2( 2nd day of week). Var = act - fcast. Accumulating the actual sales should again be relatively easy. Not quite sure how I would calculate the Forecast value
The Month Total value would be an accumulation of all the forecast weeks for the month of April, which for our test data is weeks 14-17. The variance would be the MTD actual - Total Month Fcast. How i get that total month fcast is the challenge. I'm still trying to understand dwain.c code.
Hope that helps to explain it further.
Thanks! Nick
April 26, 2012 at 8:19 am
As a side note, the forecast values for a given class typically won't fluctuate much week to week within a given month. My understanding from our sales folks is initially they come up with a month forecast and break it down by the number of weeks per month. Occasionally during the month they will adjust those numbers for the remaining weeks in a month. For example if the forecast was initially 600 for line ABC for the month of April then weeks 14-17 would have an initial value of 150. Say during week 15 they decide to adjust that number to 500, then the values for week 16 & 17 would drop to 100. It's the old 80/20 rule.... 80% of the time it will stay unchanged.
With that in mind, if it would make calculating the MTD forecast easier, using my test data, I think I could get away with calculating the number of work days between the start and end date (subtract weekends). Then ((add the forecast value for week 14 + week 15) / 10 (number of days in those 2 weeks)) / (datediff of start & end dates less weekends)
Creating a calculation as such, the MTD fcast for the ABC class using my test data = ((20+40)/10)*7 = 42
For XYZ class ((50+50)/10)*7 = 70
April 26, 2012 at 9:28 am
This fiscal year start thing is going to make your life interesting. I've got in mind a way to do what you want, but it depends on some assumptions I'd like you to confirm before I dive in.
1. If Fiscal Year starts on 02-Jan, the 01 Fiscal Month should start on the same day, thus 02 Fiscal month should start on 02-Feb (one month later). Is that correct?
2. Likewise Forecasting week 01 starts on the first day of the fiscal year. Is that correct?
3. When you talk about month to date, you're talking about fiscal month's day 01 + fiscal month's day 02 + ... + current day. Is that right?
4. The first set of columns always reports a single sales day, right?
My thinking is along the lines that you can build yourself a 12 fiscal month calendar table, listing these columns:
- First date of fiscal month
- Last date of fiscal month
- Fiscal month number
With this you can do the join on my f445 forecasting calendar to calculate a fiscal month forecast (like I did before except I used calendar month).
Once you have the month's forecast, it's easy enough to create additional columns for day's actual and mtd (sales BETWEEN first date of fiscal month AND last date of fiscal month) . Variances are no issue.
Then all you need to do is add a WHERE that selects which fiscal month you're running for.
One other question. You mentioned that you might want to run this by product instead of class. How do you break down the forecast by product, given that your table seems to be by class?
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
April 26, 2012 at 9:59 am
dwain.c (4/26/2012)
This fiscal year start thing is going to make your life interesting. I've got in mind a way to do what you want, but it depends on some assumptions I'd like you to confirm before I dive in.1. If Fiscal Year starts on 02-Jan, the 01 Fiscal Month should start on the same day, thus 02 Fiscal month should start on 02-Feb (one month later). Is that correct?
2. Likewise Forecasting week 01 starts on the first day of the fiscal year. Is that correct?
3. When you talk about month to date, you're talking about fiscal month's day 01 + fiscal month's day 02 + ... + current day. Is that right?
4. The first set of columns always reports a single sales day, right?
My thinking is along the lines that you can build yourself a 12 fiscal month calendar table, listing these columns:
- First date of fiscal month
- Last date of fiscal month
- Fiscal month number
With this you can do the join on my f445 forecasting calendar to calculate a fiscal month forecast (like I did before except I used calendar month).
Once you have the month's forecast, it's easy enough to create additional columns for day's actual and mtd (sales BETWEEN first date of fiscal month AND last date of fiscal month) . Variances are no issue.
Then all you need to do is add a WHERE that selects which fiscal month you're running for.
One other question. You mentioned that you might want to run this by product instead of class. How do you break down the forecast by product, given that your table seems to be by class?
I agree on the fiscal calendar, although you might consider some additional columns.
Fiscal Year
Fiscal Month
Fiscal Month Name
Fiscal Week of Year
Fiscal Week of Month
Sales Day Indicator
Shop Day indicator
Then you have some easy counters to sum things up.
i.e. - how many days of a certain type are in the current month?
And with these types of counters, you can account for Holidays too.
We have a Fiscal 4 4 5 calendar which starts Jan 1 every year, and the first and last week adjust so we show 52 weeks in every year.
I think as soon as you start talking fiscal, a calendar like this can be extended as needed.
Like if you decided later you need Day of Year to do some yearly calculation.
I also like the idea of a simple table to join vs. custom code.
Just an idea.
April 26, 2012 at 10:40 am
dwain.c to answer you questions:
1. Unfortunately that is not the case, but I do have access to an AccountingCalendar table, which I will discuss shortly.
2. Correct
3. MTD would be the range entered in the StartDate & EndDate Parameters
4. Correct
If it would simplfy things, I could add a field to the Forecast table for the Period that Week is included.
As for the AccountingCalendar I can access, here are the pertinent fields:
AccountingDate (1 record for every day of the year)
AccountingYear
AccountingPeriod
AvailableForSales (currently every Mon-Fri, no holidays accounted for)
AvailableForManufacturing
PeriodClosed
Your last question: in essense I already took care of this with the FClass field in the forecast table. A brief company background: We have roughly 7 products lines of which 1 accounts for 75%+ of sales (old 80/20 rule), so the forecast table has forecasts for each of the items in that 80% product line and the balance are total amounts for each of the remaining product lines.
April 26, 2012 at 6:46 pm
OK then. I'll need information on your Accounting Calendar table to do this right.
For the third question, where you say MTD is BETWEEN start date AND end date. A sensible MTD would require that start date then be the first date of the fiscal month. Right?
Finally, from your test data I expect AB1 is your 80% product, not that it matters to the solution. My question about this though was how did you expect to report on XYZ1 in terms of forecast? Just use the class forecast?
Greg - Certainly some of the fields you recommend may have utility in various scenarios, however I don't think they're needed for this particular query (I could be wrong). We'll see after I get the information above and craft a solution. The point is, I tend to be a minimalist as far as my SQL goes. I believe in only calculating what is needed to solve the problem. I realize that if you're trying to create a VIEW or something that has use across extended cases, you need to include everything that will be used across the case domain. For now, my focus is on a single case. Like I said though, I may need some other information as I progress towards a solution, but the list that I provided fits my intial solution visualization.
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
April 26, 2012 at 6:48 pm
Nick - Sorry I just realized you provided the information on your accounting calendar in the prior point. However what I need is DDL and some readily consumable data to populate that table.
Better I should get it from you than make it up myself and get it wrong.
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
April 27, 2012 at 6:36 am
dwain.c (4/26/2012)
OK then. I'll need information on your Accounting Calendar table to do this right.For the third question, where you say MTD is BETWEEN start date AND end date. A sensible MTD would require that start date then be the first date of the fiscal month. Right?
Finally, from your test data I expect AB1 is your 80% product, not that it matters to the solution. My question about this though was how did you expect to report on XYZ1 in terms of forecast? Just use the class forecast?
Greg - Certainly some of the fields you recommend may have utility in various scenarios, however I don't think they're needed for this particular query (I could be wrong). We'll see after I get the information above and craft a solution. The point is, I tend to be a minimalist as far as my SQL goes. I believe in only calculating what is needed to solve the problem. I realize that if you're trying to create a VIEW or something that has use across extended cases, you need to include everything that will be used across the case domain. For now, my focus is on a single case. Like I said though, I may need some other information as I progress towards a solution, but the list that I provided fits my intial solution visualization.
I work with our Data Warehouse group. So I tend to see things are rarely just solve it once. This leads me to look at more of a design for reusability. We do just as you describe - plan vs. actual in our cube - with a calendar shared by the SQL base and the Cube. As you can see, in my environment, since we have a number of plans, it is a bit different. We have multiple uses for a fiscal calendar, and know that it is easier to maintain 1 calendar than many queries.
Depending on if you expose any tables and views to your users, a fiscal calendar could add to some consistency / ease of use for them.
Many ways to solve some of this, but much depends on if it is a one time query, or something you see as coming up more often.
April 27, 2012 at 6:59 am
Greg - I definitely see your point and mostly I agree.
I'm just saying don't try initially to build for the universe of possibilities until you've fully explored the galaxy.
Sorry. Seem to have the Star Wars bug today. 😛
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
April 27, 2012 at 8:02 am
dwain.c (4/26/2012)
For the third question, where you say MTD is BETWEEN start date AND end date. A sensible MTD would require that start date then be the first date of the fiscal month. Right?
Finally, from your test data I expect AB1 is your 80% product, not that it matters to the solution. My question about this though was how did you expect to report on XYZ1 in terms of forecast? Just use the class forecast?
As to the 3rd question, I would say technically that is true, but I'm also still a newbie to this company as well so I could see some folks running the report as the 1st Monday of the new Period and neglecting the previous 2 day weekend. Period always end on a Friday from what I've seen except for the Month of December. For purposes of this section of the report it shouldn't matter as sales only occur Mon-Fri, but when I start working on the production section of this report it will be a concern.
Yes, those XYZ items fall into those 20% of sales product lines. Typically there are only a few items per product line and the forecast is based on all the items in that line (class).
---- Clear the current target table, If it exists, drop it
IF OBJECT_ID('TempDB..dbo.AccountingCalendar','U') IS NOT NULL DROP TABLE dbo.AccountingCalendar
CREATE TABLE [dbo].[AccountingCalendar](
[AccountingDate] [datetime] IDENTITY(1,1) NOT NULL,
[AccountingYear] [int] NOT NULL,
[AccountingPeriod] [int] NOT NULL,
[AvailableForSales] [int] NOT NULL,
[AvailableForManufacturing] [int] NOT NULL,
[PeriodClosed] [bit] NOT NULL,
CONSTRAINT [PK_AccontingCalendar] PRIMARY KEY CLUSTERED
(
[AccountingDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
--===== Insert the test data into the test table
SET IDENTITY_INSERT AccountingCalendar ON
INSERT INTO AccountingCalendar (AccountingDate, AccountingYear, AccountingPeriod, AvailableForSales, AvailableForManufacturing, PeriodClosed)
SELECT 'Mar 31 2012 12:00AM','2012','4','0','0', '0' UNION ALL
SELECT 'Apr 1 2012 12:00AM','2012','4','0','0', '0' UNION ALL
SELECT 'Apr 2 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 3 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 4 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 5 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 6 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 7 2012 12:00AM','2012','4','0','0', '0' UNION ALL
SELECT 'Apr 8 2012 12:00AM','2012','4','0','0', '0' UNION ALL
SELECT 'Apr 9 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 10 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 11 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 12 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 13 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 14 2012 12:00AM','2012','4','0','0', '0' UNION ALL
SELECT 'Apr 15 2012 12:00AM','2012','4','0','0', '0' UNION ALL
SELECT 'Apr 16 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 17 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 18 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 19 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 20 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 21 2012 12:00AM','2012','4','0','0', '0' UNION ALL
SELECT 'Apr 22 2012 12:00AM','2012','4','0','0', '0' UNION ALL
SELECT 'Apr 23 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 24 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 25 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 26 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 27 2012 12:00AM','2012','4','100','100', '0' UNION ALL
SELECT 'Apr 28 2012 12:00AM','2012','5','0','0', '0' UNION ALL
SELECT 'Apr 29 2012 12:00AM','2012','5','0','0', '0' UNION ALL
SELECT 'Apr 30 2012 12:00AM','2012','5','100','100', '0'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT AccountingCalendar OFF
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply