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 2005 Forums  Transact-SQL (2005)  Calculate average of Group by for fiscal years

Author  Topic  jayram Starting Member 47 Posts Posted - 2012-08-24 : 17:51:56 hi, 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