find max date - 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 2005 Forums

Transact-SQL (2005)

find max date

Author

Topic

sivaarc
Starting Member

9 Posts

Posted - 2009-03-06 : 01:59:02

i have a following table with this column

CustomerID DATE SalesItemCount
---------------------------------------------------------
1 2009-03-01 12:30:00 100
1 2009-03-01 15:30:00 150
1 2009-03-01 22:30:00 250 ***
-----------------------------------------------------------
2 2009-03-04 08:30:00 50
2 2009-03-04 18:30:00 75 ***

Using the above table i have to list records like below...

1 2009-03-01 250
2 2009-03-04 75

for a day i have to select only one record with max time ...
how can i achive this ???

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 02:14:11

select customerid , max(date) from tablename group by customerid

sivaarc
Starting Member

9 Posts

Posted - 2009-03-06 : 03:10:43

how can i use this query for the date range like from 01-jan-2009 to 02-Feb-2009 ?

sivaarc
Starting Member

9 Posts

Posted - 2009-03-06 : 03:13:18

i have to fileter only one record for a customer for a single day and that record time should be max time ....

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-03-06 : 04:03:31

declare @t table (CustomerID int,DATE datetime,SalesItemCount int)
Insert into @t
select 1,'2009-03-01 12:30:000',100 union all
select 1,'2009-03-01 15:30:000',150 union all
select 1,'2009-03-01 22:30:000',250 union all
select 2,'2009-03-04 08:30:000',50 union all
select 2,'2009-03-04 18:30:000',75 union all
select 1,'2009-03-02 12:30:000',100 union all
select 1,'2009-03-02 13:30:000',100 union all
select 1,'2009-03-02 13:50:000',100 union all
select 1,'2009-03-02 14:30:000',100 union all
select 1,'2009-03-04 12:30:000',100 union all
select 1,'2009-03-04 13:30:000',100 union all
select 1,'2009-03-03 13:50:000',100 union all
select 1,'2009-03-03 14:30:000',100 union all
select 2,'2009-03-03 18:30:000',75 union all
select 2,'2009-03-03 18:50:000',75 union all
select 2,'2009-03-02 19:30:000',75 union all
select 2,'2009-03-05 18:30:000',75 union all
select 2,'2009-03-05 18:50:000',75 union all
select 2,'2009-03-06 19:30:000',75

select CustomerID,max(date) from @t
group by CustomerID,convert(varchar(10),date)
order by CustomerID

isk

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-06 : 04:32:14

Try this once,


declare @temp table (CustomerID int, DATE varchar(32),SalesItemCount int)
insert into @temp 
select 1, '2009-03-01 12:30:00' ,100 union all
select 1, '2009-03-01 15:30:00', 150 union all
select 1, '2009-03-01 22:30:00', 250  union all
select 2 ,'2009-03-04 08:30:00', 50 union all
select 2, '2009-03-04 18:30:00', 75 

select customerid,date,salesitemcount from 
( select *,row_number() over ( partition by customerid order by date desc) as rn from @temp) t
where t.rn = 1

sivaarc
Starting Member

9 Posts

Posted - 2009-03-06 : 07:00:31

thank you.....

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 07:40:27

slight modification to nageswar, query for each day one record the use this one
select customerid,date,salesitemcount from
( select *,row_number() over ( partition by customerid,dateadd(d,datediff(d,0,date),0) order by date desc) as rn from @temp) t
where t.rn = 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-06 : 23:37:50

quote:
Originally posted by sivaarc

thank you.....

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources