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|||This is a multi-part message in MIME format.
--=_NextPart_000_1494_01C6B485.0C0BFC80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
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 =3D 1: -- 500+1000=3D1500
SELECT c.PeopleID
, sum (Quantity )
FROM #People p
JOIN #Contract c
ON p.ID =3D c.PeopleID
WHERE c.PeopleID =3D 1
GROUP BY c.PeopleID
-- The quantities that have the same contract number. in this case 300 -- Total result for peopleid=3D1 ->1800
SELECT Totals =3D sum( Quantity )
FROM #People p
JOIN #Contract c
ON p.ID =3D c.PeopleID
WHERE ( c.PeopleID =3D 1
OR c.[Number] IN ( SELECT [Number]
FROM #Contract
WHERE PeopleID =3D 1
)
)
-- The expected result is in the case of the people id =3D 2: -- 300+the quantities that have the same contract number. In this case =500 -- Total result for peopleid=3D2 ->800
SELECT Totals =3D sum( Quantity )
FROM #People p
JOIN #Contract c
ON p.ID =3D c.PeopleID
WHERE ( c.PeopleID =3D 2
OR c.[Number] IN ( SELECT [Number]
FROM #Contract
WHERE PeopleID =3D 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@.microsoft.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 =3D 1: > > 500+1000=3D1500 > + > The quantities that have the same contract number. in this case 300 > > Total result for peopleid=3D1 ->1800 > > The expected result is in the case of the people id =3D 2: > > 300+the quantities that have the same contract number. In this case =500 > > Total result for peopleid=3D2 ->800 > > -- > Thanks
> Regards.
> Josema
--=_NextPart_000_1494_01C6B485.0C0BFC80
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 =3D 1: -- 500+1000=3D1500
SELECT c.PeopleID , =sum (Quantity )FROM #People p JOIN #Contract c ON p.ID =3D c.PeopleIDWHERE =c.PeopleID =3D 1GROUP BY c.PeopleID
-- The quantities that have the same contract number. in this case =300 -- Total result for peopleid=3D1 ->1800
SELECT Totals =3D sum( Quantity =)FROM #People p JOIN #Contract =c ON p.ID =3D c.PeopleIDWHERE ( c.PeopleID =3D 1 OR c.[Number] IN ( SELECT [Number] &=nbsp; &n=bsp; FROM #Contract = &=nbsp; WHERE PeopleID =3D 1 &n=bsp; ) )
-- The expected result is in the case of the people id =3D 2: =-- 300+the quantities that have the same contract number. In this case 500 -- =Total result for peopleid=3D2 ->800
SELECT Totals =3D sum( Quantity =)FROM #People p JOIN #Contract =c ON p.ID =3D c.PeopleIDWHERE ( c.PeopleID =3D 2 OR c.[Number] IN ( SELECT [Number] &=nbsp; &n=bsp; FROM #Contract = &=nbsp; WHERE PeopleID =3D 2 &n=bsp; ) )
DROP TABLE #PeopleDROP TABLE #Contract
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Josema"
--=_NextPart_000_1494_01C6B485.0C0BFC80--
No comments:
Post a Comment