July 24, 2012 at 3:27 am
I have two tables:
TXN has primary key of jid
RI has primary key of cid
TXN
jid (varchar 5) cid varchar(5) Dt (datetime) Amt(int) Opt(boolean)
abc 123 2012-03-01 110 0
def 456 2011-11-11 120 1
ghi 789 2011-12-15 130 0
jkl 234 2012-01-08 125 1
mno 123 2012-01-15 120 1
RI
cid (VARCHAR 5) bal (INT)
123 50
456 120
789 45
234 200
PROBLEM:
Find all values of CID where the TXN.dt falls between 2011-07-24 and 2012-04-24
AND the SUM of all TXN.amt values for that CID is>75 AND the opt value is 1
and the RI.bal value is <100 for that CID
How do I write the query
July 24, 2012 at 3:47 am
Hi
Thanks for the information provided, but if you could spend a few minutes looking through the second link in my signature on how to post code and data for the best help it would be appreciated.
To help you out, based on what you have provided it should look something like this
DECLARE @TXN TABLE (jid varchar (5), cid varchar(5), Dt datetime, Amt int, Opt bit)
insert into @TXN values ('abc','123','2012-03-01',10,0),
('def','456','2011-11-11',20,1),
('ghi','789','2011-12-15',30,0),
('jkl','234','2012-01-08',25,1),
('mno','123','2012-01-15',20,1)
DECLARE @ri TABLE (cid VARCHAR (5), bal INT)
INSERT INTO @ri VALUES ('123',50),
('456',120),
('789',45),
('234',200)
select * from @TXN
select * from @ri
Now based on this sample data what should the output look like? As from what I can see it will return 0 rows, due to the TXN.amt never being above 75 for any of the sample data provided.
Based on the logic, you would want something like this, but cannot test it as the sample data is incomplete
SELECT
RI.CID
FROM
@ri RI
INNER JOIN
(
SELECT
TXN.CID
FROM
@TXN TXN
INNER JOIN
(
SELECT
CID
FROM
@TXN
GROUP BY
CID
HAVING
SUM(amt) > 75
) AS Dev1
ON
TXN.cid = Dev1.cid
WHERE
TXN.Dt BETWEEN '2011-07-24' AND '2012-04-24'
AND
TXN.Opt = 1
) AS Dev2
ON
Ri.cid = Dev2.cid
AND
RI.bal < 100
July 24, 2012 at 5:30 am
Hi,
2 Things:
1. As Anthony said, please do have a create, insert or the required statements for your requirements. This will help the people to reply back you fastly.
2. Again values which you provided is not matching your criteria as not greater than 75. So please provide the values that matches your requirement.
Please try the below code for your req.
SELECT b.cid,r.cid,r.bal FROM (
SELECT A.CID FROM (
SELECT cid,Amt FROM txn
WHERE Dt BETWEEN '2011-07-24' and '2012-04-24' and Opt = 1) A
GROUP BY cid
HAVING SUM(AMT)>75)b
inner join RI r
on r.cid = b.cid
WHERE r.bal<100
Regards,
Karthik.
SQL Developer.
July 24, 2012 at 6:43 am
Try this....
create table #txn
(
jid varchar(5) primary key
,cid varchar(5)
,Dt datetime
,Amt int
,Opt bit
)
GO
insert into #txn values ('abc','123','2012-03-01',10,0),
('def','456','2011-11-11',20,1),
('ghi','789','2011-12-15',30,0),
('jkl','234','2012-01-08',25,1),
('mno','123','2012-01-15',20,1)
GO
create table #ri
(
cid VARCHAR(5)
,bal INT
)
go
INSERT INTO #ri VALUES ('123',50),
('456',120),
('789',45),
('234',200)
GO
SELECT *
FROM #txn
SELECT *
FROM #ri
SELECT t.cid,
t.jid,
t.dt,
Sum(t.amt)AS Amt
FROM #txn AS t
INNER JOIN #ri AS r
ON t.cid = r.cid
WHERE t.dt BETWEEN '2011-07-24' AND '2012-04-24'
AND opt = 1
AND r.bal < 100
AND t.cid > 75
GROUP BY t.cid,
t.jid,
t.dt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply