simple sql - 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

General SQL Server Forums

New to SQL Server Programming

simple sql

Author

Topic

ranganaath
Starting Member

18 Posts

Posted - 2011-02-13 : 15:22:53

i am having following tables

tblSales ------> party,qty,invoiceno etc
tblIncentive --> party,fromCBS,ToCBS,Amount (each party may have more than 1 rows )

i want following out put

party,sum(qty) from tblSales and Amount from tblIncentive where sum(qty) between fromcbs and tocbs and tblsales.party=tblincentive.party

thanks in advance

ranganath

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-13 : 23:43:56

come up with some sample data for tblSales and tblIncentive along with the desired output format in light of your two tables data. this will help us to help you out accordingly..

Cheers
MIK

ranganaath
Starting Member

18 Posts

Posted - 2011-02-14 : 02:24:35

tblSales
Party========Qty=========InvoiceNo=========
ABC==========10===========1================
ABC==========20===========2================
XYZ==========15===========101==============
XYZ==========10===========123==============
XYZ==========12===========134==============

tblIncentive
party========FromCBS=====ToCBS=======Amount
ABC==========10==========50==========100
ABC==========51==========1000========200
XYZ==========10==========50==========50
XYZ==========51==========1000========100

Out put
Party=====sum(QTY)======fromCBS====ToCBS====Amount
ABC=======30============10=========50=======100
XYZ=======37============10=========50=======50

thank you

ranganath

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-14 : 09:16:49

[code]
CREATE TABLE tblSales
(Party NVARCHAR(3)
,Qty INTEGER
,InvoiceNo INTEGER
)

CREATE TABLE tblIncentive
(Party NVARCHAR(3)
,FromCBS INTEGER
,ToCBS INTEGER
,Amount INTEGER
)

INSERT INTO tblSales
(Party, Qty, InvoiceNo)
SELECT 'ABC', 10, 1 UNION
SELECT 'ABC', 20, 2 UNION
SELECT 'XYZ', 15, 101 UNION
SELECT 'XYZ', 10, 123 UNION
SELECT 'XYZ', 12, 134

INSERT INTO tblIncentive
(Party, FromCBS, ToCBS, Amount)
SELECT 'ABC', 10, 50, 100 UNION
SELECT 'ABC', 51, 1000, 200 UNION
SELECT 'XYZ', 10, 50, 50 UNION
SELECT 'XYZ', 51, 1000, 100
go

SELECT TS1.Party
,TS1.Qty
,MIN(TI1.FromCBS) AS FromCBS
,MIN(TI1.ToCBS) AS ToCBS
,MIN(TI1.Amount) AS Amount
FROM (SELECT TS01.Party
,SUM(TS01.Qty) AS Qty
FROM tblSales AS TS01
GROUP BY TS01.Party) AS TS1
INNER JOIN
tblIncentive AS TI1
ON TS1.Party = TI1.Party
GROUP BY TS1.Party
,TS1.Qty

Party Qty FromCBS ToCBS Amount
ABC 30 10 50 100
XYZ 37 10 50 50
[/code]

ranganaath
Starting Member

18 Posts

Posted - 2011-02-14 : 13:17:29

thanks for reply here if i insert one more row in tblSales
INSERT INTO tblSales
(Party, Qty, InvoiceNo)
SELECT 'ABC', 90, 1

then my out put should be
Party====Qty====FromCBS====ToCBS====Amount
ABC======120====51=========1000=====200
XYX======37=====10=========50=======50

but in your query it is showing
fromcbs ===ToCBS=Amount
10=========50====100

i want the tblIncentive.Amount where sum(tblsales.qty) between tblIncentive.FromCBS and tblIncentive.ToCBS

ranganath

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 13:32:36

[code]
DECLARE @tblSales TABLE
(Party NVARCHAR(3)
,Qty INTEGER
,InvoiceNo INTEGER
)

DECLARE @tblIncentive TABLE
(Party NVARCHAR(3)
,FromCBS INTEGER
,ToCBS INTEGER
,Amount INTEGER
)

INSERT INTO @tblSales
(Party, Qty, InvoiceNo)
SELECT 'ABC', 10, 1 UNION
SELECT 'ABC', 20, 2 UNION
SELECT 'XYZ', 15, 101 UNION
SELECT 'XYZ', 10, 123 UNION
SELECT 'XYZ', 12, 134 UNION
SELECT 'ABC', 90, 1

INSERT INTO @tblIncentive
(Party, FromCBS, ToCBS, Amount)
SELECT 'ABC', 10, 50, 100 UNION
SELECT 'ABC', 51, 1000, 200 UNION
SELECT 'XYZ', 10, 50, 50 UNION
SELECT 'XYZ', 51, 1000, 100

SELECT ti.Party,SUM(Qty), ti.ToCBS , ti.FromCBS ,ti.Amount
FROM @tblincentive AS ti join @tblSales AS ts on ti.Party = ts.Party
GROUP BY ti.Party,ti.ToCBS, ti.FromCBS,ti.Amount
HAVING SUM(Qty)>=ti.FromCBS and SUM(Qty) <= ti.ToCBS
[/code]
/*results
Party (No column name) ToCBS FromCBS Amount
ABC 120 1000 51 200
XYZ 37 50 10 50
*/

Poor planning on your part does not constitute an emergency on my part.

ranganaath
Starting Member

18 Posts

Posted - 2011-02-14 : 13:43:14

thank You Sir
i got the result
"Happy valentine's Day"

ranganath

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 13:54:23

You are welcome.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources