Link to home
Start Free TrialLog in
Avatar of B0b_shiska
B0b_shiska

asked on

sql script - sql server 2008

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      
Avatar of Alpesh Patel
Alpesh Patel
Flag of India image

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
ASKER CERTIFIED SOLUTION
Avatar of sachitjain
sachitjain
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?