March 3, 2010 at 2:10 am
Hi,
I've been scratching my head with this one for a while. I hope someone can point me in the right direction.
I have a table, lets call it tableA that contains a list of people.
I have another tableB that contains a list of items which each contains a foreign key from tableA
In this table is also a date and a field (to state where this item is open or closed) with true or false.
What I need is a result whereby it shows the rows from tableB where there are non unique items from tableA and where the true column is not the latest date.
for example, I would like a result of...
tableB.col1 tableA.col1 tableB.Date tableB.Open
------------ ------------ ------------- -------------
tableBID 2 tableAID 1 2009/10/25 false
tableBID 3 tableAID 1 2009/11/01 false
tableBID 4 tableAID 1 2009/11/08 true
tableBID 5 tableAID 1 2009/11/15 false
I would like this tableAID1 as the last date for this ID is false but there is a true earlier on.
If the tableBID5 was true and tableBID 4 was false I would not want this.
I've got to the point where I have a result table of over 1600 rows containing the above details but at the moment it lists All rows regardless of whether the last date is true or false.
Hope this makes sense.
Any help grwatly appreciated.
March 3, 2010 at 2:21 am
Will you provide your sample script with temp table or drieve table?. So it will hepfull to analyze yout problem.
March 3, 2010 at 2:31 am
sure.this is the query I've got that gets me to a certain point.
I've a feeling I'm going about it the wrong way though 🙂
select
tableB.tableid,
a.person ,
tableB.date,
case when tableB.ClosedReason is null then 'open' else 'closed' end as 'status'
from (
Select
person,
count(*) as counter
from tableB
group by person
) a
left join tableB on tableB.person = a.person
where
a.counter > 1
order by person, date
March 3, 2010 at 2:43 am
Will you provide semple script as Mentioned below and your Expected Output from the script.
Create Table #tableA
(
id int,
name varchar(100)
)
DROP TABLE #tableb
Create Table #tableb
(
id int,
f_id int,
ItemName varchar(100),
Date Datetime,
[Status] char(1)
)
Insert into #tableA
Select 1, 'Vijay'
Union
Select 2, 'Kedar'
Select * from #tableb
Insert into #tableb
Select 1, 1, 'ABCD', GETDATE()-1, 'C'
Union
Select 1, 1, 'ABCD', GETDATE(), 'O'
March 3, 2010 at 8:21 am
As Vijay said, sample data / table structure is helpful for things like this. (See the article in my signature on providing sample data for tips on how to do this.)
In the meantime, this may point you in the right direction:
;WITH MD AS (SELECT Col1, MAX(Date) MDate FROM TableB GROUP BY Col1), -- Get max date from B
C AS (SELECT Col1, COUNT(*) CountC FROM TableA GROUP BY Col1) -- Count of rows from A grouped by whatever you want.
SELECT *
FROM TableB B
INNER JOIN MD ON B.Date = MD.MDate
INNER JOIN C ON B.Col1 = C.Col1 AND C.CountC > 1
WHERE [B.OPEN] = 'False'
March 3, 2010 at 8:47 am
Hi,
Thanks for that. I've managed to get it working in a very similar way to which you suggest.
I will make sure I produce sample code as you guys recommend in future!
Thanks again
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply