Help with query

Two tables:

TBL1
HID NAME
-- ---
1 C1
2 C2
3 C3
4 C4

TBL2
CID HID CStartdate CEndDate
-- -- ---- ----
1 1 01 Jan 02 31 Dec 02
2 1 01 Jan 03 31 Dec 03
3 1 01 Jan 04 31 Dec 04
4 2 01 Jan 03 31 Dec 03
5 3 01 Jan 00 31 Dec 00
6 3 01 Jan 01 31 Dec 01
7 3 01 Jan 02 31 Dec 02
8 4 01 Jan 03 31 Dec 03

I'm looking for the query that will bring back the rows with the latest enddates for each of the HIDs, i.e

HID Name CStartdate CEndDate
-- --- ---- ----
1 C1 01 Jan 04 31 Dec 04
2 C2 01 Jan 03 31 Dec 03
3 C3 01 Jan 02 31 Dec 02
4 C4 01 Jan 03 31 Dec 03

Obviously a JOIN but also somewhere a group by on HID with max(Cenddate)? I'm having no luck, would appreciate some help...

Thanks
GregSomething along these lines?

USE Northwind
GO
SELECT *
FROM Orders o
INNER JOIN [Order Details] d
ON o.OrderId = d.OrderId
WHERE EXISTS (SELECT *
FROM Orders x
GROUP BY OrderId
HAVING MAX(x.OrderDate) = o.OrderDate
AND x.OrderId = o.OrderId)
GO|||Try something like this:

select t1.HID,t1.NAME,max(t2.CStartdate), max(t2.CEndDate)
from TBL1 t1
join TBL2 t2 on t2.HID=t1.HID
group by t1.HID,t1.NAME

If CStartdate<>EndDate for record - it needs to change logic...|||I think the second query is nearly there. However, Startdate is not equal to enddate, and hypothetically for a particular HID there may be an endate entry which isn't the latest but has a startdate later than the startdate for the entry with the latest enddate. This is extremely unlikely, but I want to make sure the query is robust.

Basically the first table is a list of individual hardware items, each referenced by a single HID.

The second table is a list of support contracts for the hardware. Over a period of time there will be multiple support contracts for each item, each covering a specific period of time (startdate/enddate).

I want the query to return me information about the hardware (from the first table) with information on the latest support contract. I thought a table join querying on the Max(enddate) and grouping by HID would work, but can't quite get there.......my logic generates errors :-)

g.|||Something like this maybe?

select tbl2.HID, tbl1.Name, tbl2.CStartDate, tbl2.CEndDate
from tbl2 inner join (select HID, Max(CEndDate) as EndDate from tbl2 group by HID) as Temp1
on tbl2.HID = Temp1.HID
inner join tbl1 on tbl2.HID = tbl1.HID
where tbl2.CEndDate = Temp1.EndDate|||That looks to have done the trick.
Cheers!

G.

help with query

Hi to all,
I would like some help with a query, taking in count this two tables.
Table people:
ID Name
-- --
1 P1
2 P2
3 P3
Table Contract
Number Quantity PeopleID
-- -- --
000000 500 1
000000 300 2
111111 1000 1
545454 100 3
The expected result is in the case of the people id = 1:
500+1000=1500
+
The quantities that have the same contract number. in this case 300
Total result for peopleid=1 ->1800
The expected result is in the case of the people id = 2:
300+the quantities that have the same contract number. In this case 500
Total result for peopleid=2 ->800
Thanks
Regards.
JosemaHi Josema,
Perhaps the following query might help you:
DECLARE @.ID INT
SET @.ID = 1
SELECT SUM(QUANTITY) FROM CONTRACT WHERE NUMBER IN
(SELECT DISTINCT NUMBER FROM CONTRACT WHERE PEOPLEID = @.ID)
Haven't tested above code thorough, but it seemed to work, as described by
you.
Good luck!
Regards,
Van
"Josema" wrote:

> Hi to all,
> I would like some help with a query, taking in count this two tables.
> Table people:
> ID Name
> -- --
> 1 P1
> 2 P2
> 3 P3
> Table Contract
> Number Quantity PeopleID
> -- -- --
> 000000 500 1
> 000000 300 2
> 111111 1000 1
> 545454 100 3
> The expected result is in the case of the people id = 1:
> 500+1000=1500
> +
> The quantities that have the same contract number. in this case 300
> Total result for peopleid=1 ->1800
> The expected result is in the case of the people id = 2:
> 300+the quantities that have the same contract number. In this case 500
> Total result for peopleid=2 ->800
> --
> Thanks
> Regards.
> Josema|||Here are some ideas that should point you in the correct direction.
CREATE TABLE #People
( ID int
, [Name] varchar(25)
)
CREATE Table #Contract
( [Number] varchar(20)
, Quantity int
, PeopleID int
)
SET NOCOUNT ON
INSERT INTO #People VALUES ( 1, 'P1' )
INSERT INTO #People VALUES ( 2, 'P2' )
INSERT INTO #People VALUES ( 3, 'P3' )
INSERT INTO #Contract VALUES ( '000000', 500, 1 )
INSERT INTO #Contract VALUES ( '000000', 300, 2 )
INSERT INTO #Contract VALUES ( '111111', 1000, 1 )
INSERT INTO #Contract VALUES ( '545454', 100, 3 )
-- The expected result is in the case of the people id = 1:
-- 500+1000=1500
SELECT
c.PeopleID
, sum (Quantity )
FROM #People p
JOIN #Contract c
ON p.ID = c.PeopleID
WHERE c.PeopleID = 1
GROUP BY c.PeopleID
-- The quantities that have the same contract number. in this case 300
-- Total result for peopleid=1 ->1800
SELECT
Totals = sum( Quantity )
FROM #People p
JOIN #Contract c
ON p.ID = c.PeopleID
WHERE ( c.PeopleID = 1
OR c.[Number] IN ( SELECT [Number]
FROM #Contract
WHERE PeopleID = 1
)
)
-- The expected result is in the case of the people id = 2:
-- 300+the quantities that have the same contract number. In this case 500
-- Total result for peopleid=2 ->800
SELECT
Totals = sum( Quantity )
FROM #People p
JOIN #Contract c
ON p.ID = c.PeopleID
WHERE ( c.PeopleID = 2
OR c.[Number] IN ( SELECT [Number]
FROM #Contract
WHERE PeopleID = 2
)
)
DROP TABLE #People
DROP TABLE #Contract
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Josema" <Jestrade@.ocu.org> wrote in message news:D6703F12-39E9-422A-940C-CE443F9347AD@.micro
soft.com...
> Hi to all,
>
> I would like some help with a query, taking in count this two tables.
>
> Table people:
>
> ID Name
> -- --
> 1 P1
> 2 P2
> 3 P3
>
> Table Contract
>
> Number Quantity PeopleID
> -- -- --
> 000000 500 1
> 000000 300 2
> 111111 1000 1
> 545454 100 3
>
> The expected result is in the case of the people id = 1:
>
> 500+1000=1500
> +
> The quantities that have the same contract number. in this case 300
>
> Total result for peopleid=1 ->1800
>
> The expected result is in the case of the people id = 2:
>
> 300+the quantities that have the same contract number. In this case 500
>
> Total result for peopleid=2 ->800
>
> --
> Thanks
> Regards.
> Josema