| Author |
 Topic  |
|
|
jayram
Starting Member
27 Posts |
 Posted - 08/27/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/27/2012 : 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
27 Posts |
Posted - 08/27/2012 : 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
27 Posts |
Posted - 08/27/2012 : 10:36:42
|
| since the current fiscal year has not ended, i am choosing a future date which 12/31/2045 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/27/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 08/27/2012 : 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
27 Posts |
Posted - 08/27/2012 : 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
|
|
|
| |
Topic |
|
|
|
Calculate Avg for a group by for Fiscal year range
