# 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
Alpesh Patel

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
sachitjain

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)``````
Can you give sample input and sample output so that it will be clear for us to understand?
Try this

sql1.sql