grouping by problem - SQL Server Forums

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

Transact-SQL (2000)

grouping by problem

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-06-14 : 14:06:30

Hi there I seem to have become unstuck with a query I am running and require some help please,
one of my tables containig loads of duplicate codes has the following fields and data :


 
code    year     week    value    effectivedate

454584 	  2007	  5	5.9700	  2007-03-01 00:00:00.000
454584 	  2007	  2	6.5800	  2007-02-08 00:00:00.000
454584 	  2007	  20	5.2800	  2007-06-14 00:00:00.000
454584 	  2007	  6	5.9700	  2007-03-04 00:00:00.000
454584 	  2007	  8	5.9700	  2007-03-23 00:00:00.000
454584 	  2006	  24	6.5800	  2006-07-09 00:00:00.000
454584 	  2006	  23	6.5800	  2006-07-07 00:00:00.000
454584 	  2007	  9	6.5800	  2007-03-25 00:00:00.000
454584 	  2007	  5	6.5800	  2007-02-27 00:00:00.000
454584 	  2006	  20	5.2300	  2006-06-13 00:00:00.000
454584 	  2006	  21	5.2300	  2006-06-18 00:00:00.000
454584 	  2006	  8	6.5800	  2006-03-20 00:00:00.000
454585 	  2006	  16	3.5000	  2006-05-19 00:00:00.000
454585 	  2006	  20	3.5000	  2006-06-15 00:00:00.000
454585 	  2006	  14	3.5000	  2006-05-05 00:00:00.000
454585 	  2006	  8	3.3300	  2006-03-20 00:00:00.000
454585 	  2006	  8	3.3300	  2006-03-20 00:00:00.000

Now I need to write a query that will only return one distinct code
having a max effective date ie for the above extract of data I would require the following informaton back :


code        year      week      value           effectivedate
454584 	  2007	   20	   5.2800	2007-06-14 00:00:00.000
454585 	  2006	   20	   3.5000	2006-06-15 00:00:00.000

I cant believe I cant work this out so really would appreciatte some help

cheers

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-14 : 17:32:43

This is one way:

DECLARE @T TABLE (code INT,  [year] int, week int,  [value] DECIMAL(6, 4), effectivedate DATETIME)

INSERT @T
SELECT 454584, 	  2007,	  5,	5.9700,	  '2007-03-01 00:00:00.000'
UNION SELECT 454584, 	  2007,	  2,	6.5800,	  '2007-02-08 00:00:00.000'
UNION SELECT 454584, 	  2007,	  20,	5.2800,	  '2007-06-14 00:00:00.000'
UNION SELECT 454584, 	  2007,	  6,	5.9700,	  '2007-03-04 00:00:00.000'
UNION SELECT 454584, 	  2007,	  8,	5.9700,	  '2007-03-23 00:00:00.000'
UNION SELECT 454584, 	  2006,	  24,	6.5800,	  '2006-07-09 00:00:00.000'
UNION SELECT 454584, 	  2006,	  23,	6.5800,	  '2006-07-07 00:00:00.000'
UNION SELECT 454584, 	  2007,	  9,	6.5800,	  '2007-03-25 00:00:00.000'
UNION SELECT 454584, 	  2007,	  5,	6.5800,	  '2007-02-27 00:00:00.000'
UNION SELECT 454584, 	  2006,	  20,	5.2300,	  '2006-06-13 00:00:00.000'
UNION SELECT 454584, 	  2006,	  21,	5.2300,	  '2006-06-18 00:00:00.000'
UNION SELECT 454584, 	  2006,	  8,	6.5800,	  '2006-03-20 00:00:00.000'
UNION SELECT 454585, 	  2006,	  16,	3.5000,	  '2006-05-19 00:00:00.000'
UNION SELECT 454585, 	  2006,	  20,	3.5000,	  '2006-06-15 00:00:00.000'
UNION SELECT 454585, 	  2006,	  14,	3.5000,	  '2006-05-05 00:00:00.000'
UNION SELECT 454585, 	  2006,	  8,	3.3300,	  '2006-03-20 00:00:00.000'
UNION SELECT 454585, 	  2006,	  8,	3.3300,	  '2006-03-20 00:00:00.000'


SELECT *
FROM @T t
WHERE
	(SELECT COUNT(1) FROM @T t1 WHERE t1.code = t.code AND t1.effectivedate >= t.effectivedate) = 1

-Ryan

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-14 : 23:44:38

Using join

Select a.* from @t a join
(Select code,
max(effectivedate) as eff_date
from @t
group by code) b
on a.code = b.code and a.effectivedate = b.eff_date
order by a.code

--------------------------------------------------
S.Ahamed

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-06-15 : 04:39:12

cheers Ahmed that works great I wish something like this worked, it would be much easier:

select code,[year],[week],value,max(effectivedate)
from @t
group by productcode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources