How to bring this result,without using dynamic que

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to bring this result,without using dynamic que

Author

Topic

urzsuresh
Starting Member

30 Posts

Posted - 2010-09-27 : 03:22:42

[code]
Table Structure
Location Year Qty1 Qty2 Qty3 Qty4 Qty5 Qty6 Qty7 Qty8 Qty9 Qty10 Qty11 Qty12
Atp 2010 6700 20770 19430 0 0 0 0 0 0 0 0 0
Atp 2010 4209 0 0 0 0 0 0 0 0 0 0 0 null null
[/code]

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

Suri

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-27 : 03:51:14

Try this -


DECLARE @Month AS INT
SET @Month = 6 

SELECT 
Location, 
Year,
CASE @Month
WHEN 0 THEN NULL
WHEN 1 THEN Qty1 
WHEN 2 THEN Qty1+Qty2
WHEN 3 THEN Qty1+Qty2+Qty3
WHEN 4 THEN Qty1+Qty2+Qty3+Qty4
WHEN 5 THEN Qty1+Qty2+Qty3+Qty4+Qty5
WHEN 6 THEN Qty1+Qty2+Qty3+Qty4+Qty5+Qty6
WHEN 7 THEN Qty1+Qty2+Qty3+Qty4+Qty5+Qty6+Qty7
WHEN 8 THEN Qty1+Qty2+Qty3+Qty4+Qty5+Qty6+Qty7+Qty8
WHEN 9 THEN Qty1+Qty2+Qty3+Qty4+Qty5+Qty6+Qty7+Qty8+Qty9
WHEN 10 THEN Qty1+Qty2+Qty3+Qty4+Qty5+Qty6+Qty7+Qty8+Qty9+Qty10
WHEN 11 THEN Qty1+Qty2+Qty3+Qty4+Qty5+Qty6+Qty7+Qty8+Qty9+Qty10+Qty11
WHEN 12 THEN Qty1+Qty2+Qty3+Qty4+Qty5+Qty6+Qty7+Qty8+Qty9+Qty10+Qty11+Qty12
END AS [Current], 
CASE @Month
WHEN 0 THEN Qty1+Qty2
WHEN 1 THEN Qty2+Qty3
WHEN 2 THEN Qty3+Qty4
WHEN 3 THEN Qty4+Qty5
WHEN 4 THEN Qty5+Qty6
WHEN 5 THEN Qty6+Qty7
WHEN 6 THEN Qty7+Qty8
WHEN 7 THEN Qty8+Qty9
WHEN 8 THEN Qty9+Qty10
WHEN 9 THEN Qty10+Qty11
WHEN 10 THEN Qty11+Qty12
WHEN 11 THEN Qty12
WHEN 12 THEN NULL
END AS ForeCast
FROM tbl

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources