Member Avatar for saj_amo

Hi someone help in distinct command i have created 2 tables department and balance,


1. Department

dep_id, dep_code, dep_name

2 001 Bloom Room
3 002 Tahmeco Th
4 003 Kniting

2. Balance

dep_id, Date, Amount

2 01/Mar/2011 10000
3 02/Mar/2011 15000
4 20/Mar/2011 25000
3 22/Mar/2011 10000
2 26/Apr/2011 27000
4 28/Mar/2011 5000
3 29/Mar/2011 20000

Now i want to use the distinct command that gets all departments latests entered amount,
On latest date, date between 1st to 29th eg: >= 01/Mar/2011 AND <=29/Mar/2011

this is what i want from above table

2 26/Apr/2011 27000
4 28/Mar/2011 5000
3 29/Mar/2011 20000

Help me in this...

Edited by saj_amo because: n/a
  • 3 Contributors
  • 2 Replies
  • 82 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Knvn
Member Avatar for abelLazm

Check this Query

select distinct dep.dep_id,do.dep_name dep.Date, dep.Amount from Balance as dep join Department as do on dep.dep_id=do.dep_id where dep.Date>='01/03/2011' and  dep.Date>='29/03/2011'
Edited by abelLazm because: n/a
Member Avatar for Knvn

From the Balance table you can take the latest row using the query:

select b1.* from @Balance b1 inner join
(
select dep_id,MAX(Date) [Date] from @Balance
where Date between '2011-03-01' and '2011-03-29' group by dep_id
) as b2 on b1.dep_id=b2.dep_id and b1.Date=b2.Date

In order to get the department name, just join it with the Department table on dep_id

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.