Calculate Avg for a group by for Fiscal year range

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 2008 Forums

Transact-SQL (2008)

Calculate Avg for a group by for Fiscal year range

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2012-08-27 : 09:50:45

hi,

i am riposting this here from 2005 forum since no body has any suggestions

i have a table like follows

CREATE TABLE [dbo].[ID](KEY [varchar](6) NOT NULL,
[ID] [varchar](5) NOT NULL,
[START] [smalldatetime] NOT NULL,
[END] [smalldatetime] NULL,
[SSI] [float] NULL,
[MC] [float] NULL,
[IN] [float] NULL,
[IM] [float] NULL,
[VR] [float] NULL,
[CR] [float] NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[KEY] ASC,
[ID] ASC,
[START] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

i want to insert into another table from IDby calculating certain averages

records for ID table are follows

insert into ID select '010001', '36301', '2011-10-01 00:00:00', '2011-10-30 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020
insert into ID select '010001', '36301', '2011-10-31 00:00:00', '2012-06-17 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020
insert into ID select '010001', '36301', '2012-06-18 00:00:00', '2045-12-31 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020
insert into ID select '010002', '36301', '2011-10-01 00:00:00', '2012-06-24 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020
insert into ID select '010002', '36301', '2012-06-25 00:00:00', '2012-07-05 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020
insert into ID select '010002', '36301', '2012-07-06 00:00:00', '2045-12-31 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020
insert into ID select '010003', '36302', '2011-10-01 00:00:00', '2012-05-22 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020
insert into ID select '010003', '36302', '2012-05-23 00:00:00', '2012-07-05 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020
insert into ID select '010003', '36302', '2012-07-06 00:00:00', '2045-12-31 00:00:00', 0.1327, 0.1714, 0.0000, 0.0000, 0.237, 0.020

i want to select ID, START, END, SSI, MC, IN, IM, VR, CR like follows

1. the date ranges should be broken by Fiscal year ranges (10/01 to 09/30)
2. average should be calculated by grouping by ID so if there is more than one key in a ID, it should be grouped in that

so i am expecting to have for the above

36301 2011-10-01 00:00:00 2012-09-30 00:00:00 0.1327 0.1714 0.0000 0.0000 0.237 0.020

36302 2011-10-01 00:00:00 2012-09-30 00:00:00 0.1327 0.1714 0.0000 0.0000 0.237 0.020

i am using AVG function and group by ID, START and END but there are multiple rows for the same ID, start and END

Any help??

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-27 : 10:14:11

Since all the float values are the same, hard to make out which columns you are averaging and what the right values should be. Can you show your current query? My guess is that you need to group by this:

GROUP BY
	ID,
	DATEADD(MONTH,-10,[start])

jayram
Starting Member

47 Posts

Posted - 2012-08-27 : 10:35:12

i am approaching this a little differently and i think this works

but here it goes

i select the variables i need and put in a temp table

select key, id,
case when start >= '10/01/2011' and start < '09/30/2012' then '10/01/2011' else start end AS start ,
case when END > '10/01/2011' then '12/31/2045' else END end as END
, SSI, MC, IN
, IM, VR, CR
into Temp
from ID
where id <> ''
and Start >= '10/01/2011'
group by key, id, start, end, SSI, MC, IN
, IM, VR, CR
order by id, start

and then i calculate the averages like follows

select id,
start,
end
, avg(ssi) as ssi, avg(mc) as mc, avg(in) as in
, avg(im) as im, AVG(cr) as cr, AVG(vr) as vr
from temp
group by id, start, end
order by id, start

and i insert the result into the final table.......

do you think i m missing somethin!!!

jayram
Starting Member

47 Posts

Posted - 2012-08-27 : 10:36:42

since the current fiscal year has not ended, i am choosing a future date which 12/31/2045

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-27 : 10:54:32

My concern about what you showed is that the dates are hard-coded. When the current fiscal year ends and a new one starts in October of this year, won't you have to rewrite the query again? Or is this a one-time thing that you are doing and won't be required going foward?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-08-27 : 12:29:54

When in doubt, copy Peso.

DECLARE @FiscalStart datetime
DECLARE @ThisDate datetime

SET @FiscalStart = '19001001'
SET @Thisdate = current_timestamp

SELECT DATEADD(YEAR, DATEDIFF(MONTH, @FiscalStart, @Thisdate) / 12, @FiscalStart) AS FiscalYearStart

Jim

Everyday I learn something that somebody else already knew

jayram
Starting Member

47 Posts

Posted - 2012-08-27 : 14:54:04

Thanks Sunita and Jim

your concerns are correct, i would have to re-write the query.

I would probably use Jim's suggestion

SELECT DATEADD(YEAR, DATEDIFF(MONTH, '10/01/2011', start) / 12, '10/01/2011') AS start

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources