August 22, 2007 at 7:16 am
Can one of you guys who is better with T-SQL than me help me out?
Here is kind of what my data looks like
Pole_ID Attachment_ID Date
1 101 1/1/2008
1 102 1/1/2008
2 103 NULL
2 104 1/1/2008
3 105 1/1/2008
I need to write a query that returns a list of the distinct Pole_ID's that do not have any NULL Dates. e.g. for this data it would return 1, 3 but not 2. Thanks for any help.
August 22, 2007 at 7:38 am
DECLARE @tbl TABLE (Pole_ID int, Attachment_ID int, dt datetime)
INSERT INTO @tbl
SELECT 1, 101, '2008-01-01'
UNION
SELECT 1, 102, '2008-01-01'
UNION
SELECT 2, 103, NULL
UNION
SELECT 2, 104, '2008-01-01'
UNION
SELECT 3, 105, '2008-01-01'
SELECT Pole_ID
FROM @tbl
WHERE Pole_ID NOT IN (SELECT Pole_ID FROM @tbl WHERE dt IS NULL)
GROUP BY Pole_ID
August 22, 2007 at 7:57 am
Thanks alot.
August 22, 2007 at 10:05 am
The following code will work also and perhaps give you a different way of looking at things. The first part of the query just builds and populates the test table. You will not need this but I included it so you could see what I was doing. Having said that, I like using temp tables as I feel it gives me a little better control over data expecially when I am changing data. I used a column named "flag" as a null indicator for the Pole_ID.
You will be interested in the highlighted code at the bottom. You will find that this type of approach is very flexable and useful.
Good Luck,
John
if exists (select 1 from tempdb.dbo.sysobjects where name like '%test%' and type = 'u')drop table #test
create table #test (
Pole_ID int,
dt varchar(12),
flag int default 0)
insert #test(Pole_ID,dt)
values(1,'1/1/2008')
insert #test(Pole_ID,dt)
values(1,'1/1/2008')
insert #test(Pole_ID)
values(2)
insert #test(Pole_ID,dt)
values(2,'1/1/2008')
insert #test(Pole_ID,dt)
values(3,'1/1/2008')
-- The following query will work for your needs as defined. Simply
-- subsistute your table name for #test
declare @poleid int
set @poleid = (select distinct pole_id from #test where dt is null)
print @poleid
update #test set flag = 1 where pole_id = @poleid
select distinct pole_id from #test where flag = 0
drop table #test
go
August 22, 2007 at 12:52 pm
Mmmm... One of those having tricks can solve this.
SELECT
Pole_ID
FROM
XX
GROUP BY
Pole_ID
HAVING
COUNT(*) = SUM(CASE WHEN Date IS NULL THEN 0 ELSE 1 END)
Note that John's code will fail if more than 1 Pole_ID has a NULL date (subqueries are only allowed to return 1 value)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply