September 27, 2010 at 1:20 am
Table Structure
Location Year Qty1 Qty2 Qty3 Qty4 Qty5 Qty6 Qty7 Qty8 Qty9 Qty10 Qty11 Qty12
Atp 2010 6700 20770 9430000000000
Atp 2010 420900000000000 null null
in the above table qty1,qty2 represent as month.
If i send month 4
Here i need to calculate (qty1+ qty2+qty3+qty4) current, (qty5+qty6) Forcast. in another column
If i send month 6
Here i need to calculate (qty1+ qty2+qty3+qty4+qty5+qty6) current, (qty7+qty8) Forcast. in another column
How can i bring this result can u please guide me through sample code
September 27, 2010 at 1:37 am
It is possible that you can change the design of the table ?
A table with the structure of
Location
Year
Month
Quantity
Would be easier to process.
Whenever I see a table with repeating column names appended with numbers, I cringe in horror.
http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/
September 27, 2010 at 1:45 am
Here we cant change the table structure. b'coz already running in production server.so that only
September 27, 2010 at 1:54 am
Well , you could make the best of a bad job by unpivoting (either by using UNPIVOT or using a case statement) the data first to fake a correct structure.
September 27, 2010 at 5:06 am
It does look like the table in your eample is populated by some PIVOTING query. I would recommend to have a look it and see where data is comming from originaly. Then, you can, most likley, easely implement what you want making your quiry on the original source table(s).
September 28, 2010 at 10:41 am
I'm not suggesting this is a good idea, but if avoiding dynamic sql is your only consideration then this might work.
Edit: I assumed you wanted the rest of the year in the forecast, if its only the next two months then you would need additional logic.
DECLARE @input_month INT
SET @input_month = 4
DECLARE @factor1 INT, @factor2 INT, @factor3 INT, @factor4 INT, @factor5 INT, @factor6 INT,
@factor7 INT, @factor8 INT, @factor9 INT, @factor10 INT, @factor11 INT, @factor12 INT
SELECT @factor1 = CASE WHEN @input_month >= 1 THEN 1 ELSE 0 END,
@factor2 = CASE WHEN @input_month >= 2 THEN 1 ELSE 0 END,
@factor3 = CASE WHEN @input_month >= 3 THEN 1 ELSE 0 END,
@factor4 = CASE WHEN @input_month >= 4 THEN 1 ELSE 0 END,
@factor5 = CASE WHEN @input_month >= 5 THEN 1 ELSE 0 END,
@factor6 = CASE WHEN @input_month >= 6 THEN 1 ELSE 0 END,
@factor7 = CASE WHEN @input_month >= 7 THEN 1 ELSE 0 END,
@factor8 = CASE WHEN @input_month >= 8 THEN 1 ELSE 0 END,
@factor9 = CASE WHEN @input_month >= 9 THEN 1 ELSE 0 END,
@factor10 = CASE WHEN @input_month >= 10 THEN 1 ELSE 0 END,
@factor11 = CASE WHEN @input_month >= 11 THEN 1 ELSE 0 END,
@factor12 = CASE WHEN @input_month >= 12 THEN 1 ELSE 0 END
SELECT (Qty1*@factor1) + (Qty2*@factor2) + (Qty3*@factor3) + (Qty4*@factor4) +
(Qty5*@factor5) + (Qty6*@factor6) + (Qty7*@factor7) + (Qty8*@factor8) +
(Qty9*@factor9) + (Qty10*@factor10) + (Qty11*@factor11) + (Qty12*@factor12) as current_value,
(Qty1*(1-@factor1)) + (Qty2*(1-@factor2)) + (Qty3*(1-@factor3)) + (Qty4*(1-@factor4)) +
(Qty5*(1-@factor5)) + (Qty6*(1-@factor6)) + (Qty7*(1-@factor7)) + (Qty8*(1-@factor8)) +
(Qty9*(1-@factor9)) + (Qty10*(1-@factor10)) + (Qty11*(1-@factor11)) + (Qty12*(1-@factor12)) as forcast_value
September 28, 2010 at 12:51 pm
Hello friend,
Really nice. i got exact result for current value. but forecast value is getting wrong result.
my requirement is
If month is 4
Current value- (Qty1+Qty2+Qty3+Qty4)
forecast value- (Qty5+Qty6)
If month is 5
Current value- (Qty1+Qty2+Qty3+Qty4+Qty5)
forecast value- (Qty6+Qty7)
suppose if its month is 11
Current value- (Qty1+Qty2+Qty3+Qty4+Qty5....+Qty11)
forecast value- (Qty12+0)
if its month is 12
Current value- (Qty1+Qty2+Qty3+Qty4+Qty5....+Qty11+Qty12)
forecast value- (0+0)
How can i bring this result. can you please guide me.
September 29, 2010 at 7:53 am
This is even more convoluted, but it might work.
I would be inclined to unpivot the data, but that might be equally clumsy.
DECLARE @input_month INT
SET @input_month = 4
DECLARE @factor1 INT, @factor2 INT, @factor3 INT, @factor4 INT, @factor5 INT, @factor6 INT,
@factor7 INT, @factor8 INT, @factor9 INT, @factor10 INT, @factor11 INT, @factor12 INT,
@fc_factor1 INT, @fc_factor2 INT, @fc_factor3 INT, @fc_factor4 INT, @fc_factor5 INT, @fc_factor6 INT,
@fc_factor7 INT, @fc_factor8 INT, @fc_factor9 INT, @fc_factor10 INT, @fc_factor11 INT, @fc_factor12 INT
SELECT @factor1 = CASE WHEN @input_month >= 1 THEN 1 ELSE 0 END,
@factor2 = CASE WHEN @input_month >= 2 THEN 1 ELSE 0 END,
@factor3 = CASE WHEN @input_month >= 3 THEN 1 ELSE 0 END,
@factor4 = CASE WHEN @input_month >= 4 THEN 1 ELSE 0 END,
@factor5 = CASE WHEN @input_month >= 5 THEN 1 ELSE 0 END,
@factor6 = CASE WHEN @input_month >= 6 THEN 1 ELSE 0 END,
@factor7 = CASE WHEN @input_month >= 7 THEN 1 ELSE 0 END,
@factor8 = CASE WHEN @input_month >= 8 THEN 1 ELSE 0 END,
@factor9 = CASE WHEN @input_month >= 9 THEN 1 ELSE 0 END,
@factor10 = CASE WHEN @input_month >= 10 THEN 1 ELSE 0 END,
@factor11 = CASE WHEN @input_month >= 11 THEN 1 ELSE 0 END,
@factor12 = CASE WHEN @input_month >= 12 THEN 1 ELSE 0 END
SELECT @fc_factor1 = CASE WHEN @input_month IN (0,0) THEN 1 ELSE 0 END,
@fc_factor2 = CASE WHEN @input_month IN (0,1) THEN 1 ELSE 0 END,
@fc_factor3 = CASE WHEN @input_month IN (2,2) THEN 1 ELSE 0 END,
@fc_factor4 = CASE WHEN @input_month IN (2,3) THEN 1 ELSE 0 END,
@fc_factor5 = CASE WHEN @input_month IN (3,4) THEN 1 ELSE 0 END,
@fc_factor6 = CASE WHEN @input_month IN (4,5) THEN 1 ELSE 0 END,
@fc_factor7 = CASE WHEN @input_month IN (5,6) THEN 1 ELSE 0 END,
@fc_factor8 = CASE WHEN @input_month IN (6,7) THEN 1 ELSE 0 END,
@fc_factor9 = CASE WHEN @input_month IN (7,8) THEN 1 ELSE 0 END,
@fc_factor10 = CASE WHEN @input_month IN (8,9) THEN 1 ELSE 0 END,
@fc_factor11 = CASE WHEN @input_month IN (9,10) THEN 1 ELSE 0 END,
@fc_factor12 = CASE WHEN @input_month IN (10,11) THEN 1 ELSE 0 END
SELECT (Qty1*@factor1) + (Qty2*@factor2) + (Qty3*@factor3) + (Qty4*@factor4) +
(Qty5*@factor5) + (Qty6*@factor6) + (Qty7*@factor7) + (Qty8*@factor8) +
(Qty9*@factor9) + (Qty10*@factor10) + (Qty11*@factor11) + (Qty12*@factor12) as current_value,
(Qty1*(@fc_factor1)) + (Qty2*(@fc_factor2)) + (Qty3*(@fc_factor3)) + (Qty4*(@fc_factor4)) +
(Qty5*(@fc_factor5)) + (Qty6*(@fc_factor6)) + (Qty7*(@fc_factor7)) + (Qty8*(@fc_factor8)) +
(Qty9*(@fc_factor9)) + (Qty10*(@fc_factor10)) + (Qty11*(@fc_factor11)) + (Qty12*(@fc_factor12)) as forcast_value
September 29, 2010 at 9:31 am
Hi,
Really fantastic.Thanx a lot . I have small doubt in this.
(ie.) eg.
If month is 4
Current value- (Qty1+Qty2+Qty3+Qty4)
forecast1 value- (Qty5)
forecast2 value- (Qty6)
If month is 5
Current value- (Qty1+Qty2+Qty3+Qty4+Qty5)
forecast1 value- (Qty6)
forecast2 value- (Qty7)
suppose if its month is 11
Current value- (Qty1+Qty2+Qty3+Qty4+Qty5....+Qty11)
forecast1 value- (Qty12)
forecast2 value- (0)
if its month is 12
Current value- (Qty1+Qty2+Qty3+Qty4+Qty5....+Qty11+Qty12)
forecast1 value- (0)
forecast2 value- (0)
Here i need to forecast two value separtly like forecast 1 and forecast2. How can i make changes in above query. can u please guide me.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply