sql script - sql server 2008

B0b_shiska
B0b_shiska used Ask the Experts™
on
Hi. From the below, how can i calculate the total of col 1 + col2 + col4 and put results in new column named 'newcol5'?

Also, calculate the following: 12 mth average based on recent calendar quarter and put results as 'total12avg'? In this case it would be the average of all rows from column 'month' 2010-04 to 2011-03. Thanks.

Month       col 1      col2 2       col3 x      col4        'newcol 5'
2011-05      x                   x            x               x             x      
2011-04      x      
2011-03      x      
.......-....    x
.......-.....      x             x               x               x             x
2010-04   x             x               x               x             x

First quarter: from the beginning of January to the end of March                        
Second quarter: from the beginning of April to the end of June                        
Third quarter: from the beginning of July to the end of September                        
Fourth quarter: from the beginning of October to the end of December      
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Select *, (Col1_col2 _ col4) As cal from Table Where month >= Convert(varchar,Year(GEtdate))+'-01' and month <= Convert(varchar,Year(GEtdate))+'-03'
This could be of your use

select col1,col2,col3, (col1+col2+col3) as newcol,
      (case when month(Tab1.dateCol) <= 3 then
                  (select sum(col1+col2+col3)/12 from YOURTABLENAME as Tab2
                        where Tab2.dateCol between convert(datetime, cast((year(dateCol)-1) as varchar(4))+'-04-01 00:00:00.000', 121)
                                    and convert(datetime, cast((year(dateCol)) as varchar(4))+'-03-31 23:59:59.99', 121))
            when month(Tab1.dateCol) > 3 and month(Tab1.dateCol) <= 6 then
                  (select sum(col1+col2+col3)/12 from YOURTABLENAME as Tab2
                        where Tab2.dateCol between convert(datetime, cast((year(dateCol)-1) as varchar(4))+'-07-01 00:00:00.000', 121)
                                    and convert(datetime, cast((year(dateCol)) as varchar(4))+'-06-30 23:59:59.99', 121))
            when month(Tab1.dateCol) > 6 and month(Tab1.dateCol) <= 9 then
                  (select sum(col1+col2+col3)/12 from YOURTABLENAME as Tab2
                        where Tab2.dateCol between convert(datetime, cast((year(dateCol)-1) as varchar(4))+'-10-01 00:00:00.000', 121)
                                    and convert(datetime, cast((year(dateCol)) as varchar(4))+'-12-31 23:59:59.99', 121))
            else
                  (select sum(col1+col2+col3)/12 from YOURTABLENAME as Tab2
                        where Tab2.dateCol between convert(datetime, cast((year(dateCol)-1) as varchar(4))+'-01-01 00:00:00.000', 121)
                                    and convert(datetime, cast((year(dateCol)) as varchar(4))+'-03-31 23:59:59.99', 121)) end) as '12mthAvg'
from YOURTABLENAME As Tab1
Bit correction

select col1,col2,col3, (col1+col2+col3) as newcol,
      (case when month(Tab1.dateCol) <= 3 then
                  (select sum(col1+col2+col3)/12 from YOURTABLENAME as Tab2
                        where Tab2.dateCol between convert(datetime, cast((year(dateCol)-1) as varchar(4))+'-04-01 00:00:00.000', 121)
                                    and convert(datetime, cast((year(dateCol)) as varchar(4))+'-03-31 23:59:59.99', 121))
            when month(Tab1.dateCol) > 3 and month(Tab1.dateCol) <= 6 then
                  (select sum(col1+col2+col3)/12 from YOURTABLENAME as Tab2
                        where Tab2.dateCol between convert(datetime, cast((year(dateCol)-1) as varchar(4))+'-07-01 00:00:00.000', 121)
                                    and convert(datetime, cast((year(dateCol)) as varchar(4))+'-06-30 23:59:59.99', 121))
            when month(Tab1.dateCol) > 6 and month(Tab1.dateCol) <= 9 then
                  (select sum(col1+col2+col3)/12 from YOURTABLENAME as Tab2
                        where Tab2.dateCol between convert(datetime, cast((year(dateCol)-1) as varchar(4))+'-10-01 00:00:00.000', 121)
                                    and convert(datetime, cast((year(dateCol)) as varchar(4))+'-09-30 23:59:59.99', 121))
            else
                  (select sum(col1+col2+col3)/12 from YOURTABLENAME as Tab2
                        where Tab2.dateCol between convert(datetime, cast((year(dateCol)) as varchar(4))+'-01-01 00:00:00.000', 121)
                                    and convert(datetime, cast((year(dateCol)) as varchar(4))+'-12-31 23:59:59.99', 121)) end) as '12mthAvg'
from YOURTABLENAME As Tab1
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Hi, The below query might helps you. Please let me know if i misunderstood.
WITH CTEmonthcal
AS
(
	SELECT 
		[Month],
		[col1], 
		[col2],
		[col3 ],
		[Col4], 
		([col1] + [col2] + [Col4]) [Col5]
	FROM tblMonth
)

SELECT 
	CAST([Month] AS VARCHAR(40)),
	[col1], 
	[col2],
	[col3 ],
	[col4], 
	[Col5]
FROM CTEmonthcal
UNION ALL
SELECT 
	'Total 12Avg',
	AVG([col1]),
	AVG([col2]),
	AVG([col3 ]),
	AVG([Col4]),
	AVG([Col5])
FROM CTEmonthcal
WHERE DATENAME(q,[Month]) IN (1,2,3,4)

Open in new window

Can you give sample input and sample output so that it will be clear for us to understand?
G Trurab KhanSnr. Development Manager

Commented:
Try this

 sql1.sql

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial