September 11, 2013 at 5:41 am
Not sure how to explain this but here goes...
I have a forecast of volume for a product for the year.
A product costing can change through the year.
I need to calculate a forecast value based on a costing table.
so something like...
SELECT fcast.Year, fcast.Customer, fcast.StockCode, fcast.Type, fcast.Measure, fcast.MonthNo, fcast.Volume,
cost.Cost
FROM #forecast_unpivot fcast
LEFT JOIN(
SELECT Customer, StockCode, Year, MonthNo, Cost
FROM #costing) cost
ON fcast.Year= cost.Year
AND fcast.Customer = cost.Customer
AND fcast.StockCode = cost.StockCode
AND fcast.Year = cost.Year
AND fcast.MonthNo = cost.MonthNo
but i only get cost matches on the exact month (i've attached the results in a text file (sqcl.txt), so the NULLS need to be the previous costing)
i need to get jan cost to appear in feb then when the cost change in mar apply the new cost to all future months.
if i use >= on month i get duplication.
thanks
** edit - i've add the full sql code, creates tables (temp), data etc. (full_sql.txt)
*** edit - i've added another text file (monthNm_to_monthNo.txt) which is a function to convert date name to date number
September 11, 2013 at 8:53 am
maybe slightly over complicated things a bit :ermm:
is it possible to write a query which will 'copy' the cost forward to future months in the following table?
Year.........MonthNo.....Volume.....Cost
2013........1................5000........1
2013........2................4000........NULL
2013........3................5000........1.5
2013........4................2000........NULL
2013........5................5000........NULL
2013........6................3000........NULL
2013........7................7000........NULL
.........
so that it looks like this...
Year.........MonthNo.....Volume.....Cost
2013........1................5000........1
2013........2................4000........1
2013........3................5000........1.5
2013........4................2000........1.5
2013........5................5000........1.5
2013........6................3000........1.5
2013........7................7000........1.5
.........
September 11, 2013 at 10:10 am
Sure that is pretty simple with a correlated subquery.
Btw, it would be really helpful if you would post ddl and sample data.
create table #SomeTable
(
MyYear int,
MonthNo int,
Volume int,
Cost numeric(9,2)
)
insert #SomeTable
select 2013, 1, 5000, 1 union all
select 2013, 2, 4000, NULL union all
select 2013, 3, 5000, 1.5 union all
select 2013, 4, 2000, NULL union all
select 2013, 5, 5000, NULL union all
select 2013, 6, 3000, NULL union all
select 2013, 7, 7000, NULL
Now for the query.
select MyYear, MonthNo, Volume, case when Cost IS NULL then (select top 1 Cost from #SomeTable s2 where s2.MonthNo < s.MonthNo order by s.MonthNo) else Cost end as Cost
from #SomeTable s
_______________________________________________________________
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/
September 11, 2013 at 12:52 pm
thanks, i'm nearly there. It's always using the earliest cost though. it now shows....
Year.........MonthNo.....Volume.....Cost
2013........1................5000........1
2013........2................4000........1
2013........3................5000........1.5
2013........4................2000........1
2013........5................5000........1
2013........6................3000........1
2013........7................7000........1
2013........8................5000........1.6
2013........9................3000........1
2013........10................7000........1
.....
and here's the ddl (query is at the end)....
--drop tables
IF exists (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_volumes')
DROP TABLE tbl_volumes
GO
IF exists (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_costing')
DROP TABLE tbl_costing
GO
--create volume table
CREATE TABLE tbl_volumes(
[Year] [int] NOT NULL,
[Customer] [varchar](7) NOT NULL,
[StockCode] [varchar](35) NOT NULL,
[MonthNo] [int] NOT NULL,
[Volume] [decimal](18, 4) NULL,
[Cost] [decimal](18, 4) NULL,
[Value] [decimal](37, 8) NULL
) ON [PRIMARY]
--create volume data
INSERT INTO tbl_volumes
SELECT 2013, 'CUST01', 'STK0001', 1, 5000,1, 5000 UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 2, 4000,NULL, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 3, 5000,1.5, 7500 UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 4, 2000,NULL, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 5, 5000,NULL, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 6, 3000,NULL, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 7, 7000,NULL, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 8, 2500,NULL, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 9, 8000,NULL, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 10, 2500, 1.62, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 11, 5000, NULL, NULL UNION ALL
SELECT 2013, 'CUST01', 'STK0001', 12, 5000, NULL, NULL
--create costing table
CREATE TABLE tbl_costing
(
Customervarchar(7) NOT NULL
,StockCodevarchar(35) NOT NULL
,Yearint NOT NULL
,MonthNoint NOT NULL
,Costdecimal(18,4) NOT NULL
)
--create costing data
INSERT INTO tbl_costing
SELECT 'CUST01', 'STK0001', 2013, 1, 1 UNION ALL
SELECT 'CUST01', 'STK0001', 2013, 3, 1.5 UNION ALL
SELECT 'CUST01', 'STK0002', 2013, 1, 2 UNION ALL
SELECT 'CUST01', 'STK0003', 2013, 1, 1.1 UNION ALL
SELECT 'CUST01', 'STK0004', 2013, 1, .5
--the query
SELECT s.Year, s.Customer, s.StockCode, s.MonthNo, s.Volume,
CASE
WHEN Cost IS NULL THEN (SELECT TOP 1 Cost FROM tbl_costing s2 WHERE s2.Year = s.Year AND s2.Customer = s.Customer AND s2.StockCode = s.StockCode AND s2.MonthNo < s.MonthNo ORDER BY s2.MonthNo)
ELSE Cost END AS Cost
FROM [dbo].[tbl_volumes] s
September 11, 2013 at 1:14 pm
Think you just need to specify the sort order.
ORDER BY s2.MonthNo DESC
_______________________________________________________________
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/
September 11, 2013 at 1:25 pm
getting closer. sorry, if i add a 3rd costing i get the second cost carried forward. so...
Year.........MonthNo.....Volume.....Cost
2013........1................5000........1
2013........2................4000........1
2013........3................5000........1.5
2013........4................2000........1.5
2013........5................5000........1.5
2013........6................3000........1.5
2013........7................7000........1.5
2013........8................5000........1.5
2013........9................3000........1.5
2013........10................7000........1.62
2013........11................5000........1.5
2013........12................9000........1.5
--the query
SELECT s.Year, s.Customer, s.StockCode, s.MonthNo, s.Volume,
CASE
WHEN Cost IS NULL THEN (SELECT TOP 1 Cost FROM tbl_costing s2 WHERE s2.Year = s.Year AND s2.Customer = s.Customer AND s2.StockCode = s.StockCode AND s2.MonthNo < s.MonthNo ORDER BY s2.MonthNo DESC)
ELSE Cost END AS Cost
FROM [dbo].[tbl_volumes] s
September 11, 2013 at 2:06 pm
spin (9/11/2013)
getting closer. sorry, if i add a 3rd costing i get the second cost carried forward. so...Year.........MonthNo.....Volume.....Cost
2013........1................5000........1
2013........2................4000........1
2013........3................5000........1.5
2013........4................2000........1.5
2013........5................5000........1.5
2013........6................3000........1.5
2013........7................7000........1.5
2013........8................5000........1.5
2013........9................3000........1.5
2013........10................7000........1.62
2013........11................5000........1.5
2013........12................9000........1.5
--the querySELECT s.Year, s.Customer, s.StockCode, s.MonthNo, s.Volume,
CASE
WHEN Cost IS NULL THEN (SELECT TOP 1 Cost FROM tbl_costing s2 WHERE s2.Year = s.Year AND s2.Customer = s.Customer AND s2.StockCode = s.StockCode AND s2.MonthNo < s.MonthNo ORDER BY s2.MonthNo DESC)
ELSE Cost END AS Cost
FROM [dbo].[tbl_volumes] s
Your query is doing exactly what you told it, which is to find the most recent value in tbl_costing where the month is less than the current month. I think what you want instead is to get the value from tbl_volumes when there is not previous in tbl_costing???
_______________________________________________________________
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/
September 12, 2013 at 1:42 am
thanks again
the reason i need this is so that i can have a cost for a product now (say, Jan) which i know is going to have a cost change in a couple of months, and again a couple of months past that.
I need to be able to create a forecast which uses the current cost then applies any future costing for the relevant year/month.
Is what i'm doing able to achieve this?
thanks
September 12, 2013 at 2:32 am
cracked it!!!
i never wanted to use the cost in the volume table. it should always come from the costing table.
so....
--the query
SELECT s.Year, s.Customer, s.StockCode, s.MonthNo, s.Volume,
CASE
WHEN Cost IS NULL THEN (SELECT TOP 1 Cost FROM tbl_costing s2 WHERE s2.Year = s.Year AND s2.Customer = s.Customer AND s2.StockCode = s.StockCode AND s2.MonthNo <= s.MonthNo ORDER BY s2.MonthNo DESC)
ELSE (SELECT TOP 1 Cost FROM tbl_costing s2 WHERE s2.Year = s.Year AND s2.Customer = s.Customer AND s2.StockCode = s.StockCode AND s2.MonthNo = s.MonthNo ORDER BY s2.MonthNo DESC)
END AS Cost
FROM [dbo].[tbl_volumes] s
i'm sure there's an easier way to write the case part but it definitely works.
Thanks for all your help.
September 12, 2013 at 7:20 am
Given the changes here I don't think you need a case expression at all.
Pretty sure this would give you the same results.
SELECT s.Year, s.Customer, s.StockCode, s.MonthNo, s.Volume, x.Cost
FROM [dbo].[tbl_volumes] s
cross apply (select top 1 Cost from tbl_costing s2 WHERE s2.Year = s.Year AND s2.Customer = s.Customer AND s2.StockCode = s.StockCode AND s2.MonthNo <= s.MonthNo ORDER BY s2.MonthNo DESC) x
_______________________________________________________________
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/
September 12, 2013 at 8:13 am
well thats 2 new things i've learnt, correlated query and cross apply. both of which i can see myself using again.
thanks again 🙂
September 12, 2013 at 8:21 am
Happy to help. You might want to read up on APPLY.
Paul White has a 2 part series that is the best I have read on the subject.
http://www.sqlservercentral.com/articles/69953/[/url]
http://www.sqlservercentral.com/articles/69954/[/url]
_______________________________________________________________
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/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply