New column with multiplication & division

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

General SQL Server Forums

New to SQL Server Programming

New column with multiplication & division

Author

Topic

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-27 : 01:22:24

I have a table containing values of
every month every year:
say

month___year___sales
JAN_____2007___10000
FEB_____2007___20000
JAN_____2008___5000
FEB_____2008___15000

now i want to find the % variance in sales FORMULA =
((OLD VALUE-NEW VALUE)/OLD VALUE)*100
=>>((JAN2007 SALES-JAN2008 SALES)/JAN2007 SALES)*100

HOW CAN I CREATE A NEW COLUMN HERE WHICH CAN GIVE ME VARIANCE AUTOMATICALLY FOR ALL THE MONTHS COMPARED WITH PREVIOUS SALES???..Please Help

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-27 : 02:44:06

[code]
select t.month, t.year, t.sales, variance = (p.sales - t.sales) / p.sales * 100.0
from a_table t
left join a_table p on t.month = p.month and t.year = p.year + 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-27 : 03:28:41

I gt your point..thanks ....but could not insert the same method in this type of Query...

please help:

SELECT
COUNT(InvoiceNumber) AS [Cancelled Bookings],
DATENAME(Month,BookingDate) as Month,
DATENAME(Year,BookingDate) as Year

FROM tblInvoice
WHERE (fk_StatusID = 5) AND
(BookingDate between CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2020-01-31 00:00:00', 102))

GROUP BY DATENAME(Month,BookingDate) , DATENAME(year,BookingDate),
DATEPART(MM,BookingDate),DATEPART(YYYY,BookingDate)
ORDER BY DATEPART(YYYY,BookingDate),DATEPART(MM,BookingDate)

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-27 : 06:13:38

use a CTE


; with a_table as
(
SELECT
COUNT(InvoiceNumber) AS [Cancelled Bookings],
DATENAME(Month,BookingDate) as Month,
DATENAME(Year,BookingDate) as Year
[aggregate of the sales column] as sales
FROM tblInvoice
WHERE (fk_StatusID = 5) AND
(BookingDate between CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2020-01-31 00:00:00', 102))

GROUP BY DATENAME(Month,BookingDate) , DATENAME(year,BookingDate),
DATEPART(MM,BookingDate),DATEPART(YYYY,BookingDate)
)
select t.Month, t.Year, t.sales, variance = (p.sales - t.sales) / p.sales * 100.0
from   a_table t
       left join a_table p on t.Month = p.Month and t.Year = p.Year + 1

KH
[spoiler]Time is always against us[/spoiler]

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-27 : 08:55:46

I tried . but could not,,,,get this in sql management sudio query designer. i have tried in report builder and go it..

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources