Help with select statement... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with select statement...

Author

Topic

criki
Starting Member

4 Posts

Posted - 2010-12-03 : 11:11:57

Hi all!
I have a select statement I'm trying to create in the most efficient way and the best I have come up with is creating 2 views, both with case statements and selecting on that. I was wondering if someone can give me some ideas on how to make this a little faster.
I come up with 45 results and it takes about 10 seconds to run.

The first view is:

SELECT     dbo.TEST.SAMPLE_NUMBER, 
   SUM(
     CASE analysis 
       WHEN 'test1' THEN 
         CASE test.status 
           WHEN 'i' THEN 1 
         END 
       WHEN 'test2' THEN 
         CASE test.status 
           WHEN 'i' THEN 1 
         END 
      ELSE
   0 END) AS NeededInComplete
FROM         dbo.TEST 
GROUP BY dbo.TEST.SAMPLE_NUMBER, dbo.TEST.STATUS

The second view is:

SELECT     dbo.TEST.SAMPLE_NUMBER, 
SUM(
   CASE analysis 
     WHEN 'test3' THEN 
       CASE test.status 
         WHEN 'c' THEN 1 
       END 
     WHEN 'test4' THEN 
       CASE test.status 
         WHEN 'c' THEN 1 
       END
     ELSE 0 
   END) AS NeededComplete
FROM         dbo.TEST 
GROUP BY dbo.TEST.SAMPLE_NUMBER, dbo.TEST.STATUS

And the final select statement:

SELECT c.SAMPLE_NUMBER
FROM dbo.vw_2 AS c INNER JOIN
dbo.vw_1 AS i ON c.SAMPLE_NUMBER = i.SAMPLE_NUMBER
WHERE     (c.NeededComplete > 0) AND (i.NeededInComplete > 0)

This works, but seems like it could be simplified. Any ideas?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 11:20:22

well the first view is
SELECT dbo.TEST.SAMPLE_NUMBER,
SUM (
case when analysis = 'test1' and test.status = 'i' then 1
when analysis = 'test2' and test.status = 'i' then 1
else 0
end
) AS NeededInComplete
FROM dbo.TEST
GROUP BY dbo.TEST.SAMPLE_NUMBER, dbo.TEST.STATUS

which groups by 2 column values but only returns one of them which sounds like a mistake - heading for wrong joins?

in fact it's
SELECT dbo.TEST.SAMPLE_NUMBER,
SUM (
case when test.status = 'i' and analysis in ('test1','test2') then 1
else 0
end
) AS NeededInComplete
FROM dbo.TEST
GROUP BY dbo.TEST.SAMPLE_NUMBER, dbo.TEST.STATUS

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

criki
Starting Member

4 Posts

Posted - 2010-12-03 : 11:24:57

I didn't see that, left over code from my first tries. :-)
It still takes some time to process, actually, it seems to take longer (by a second)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 11:30:58

Not sure what you're trying to do - and I think the lack of clear requirement is the issue, but this might be something like it

select a.SAMPLE_NUMBER
(
select distinct SAMPLE_NUMBER
FROM dbo.TEST
where test.status = 'i' and analysis in ('test1','test2')
) a
join
(
select distinct SAMPLE_NUMBER
FROM dbo.TEST
where test.status = 'c' and analysis in ('test3','test4')
) b
on a.SAMPLE_NUMBER = b.SAMPLE_NUMBER

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

criki
Starting Member

4 Posts

Posted - 2010-12-03 : 11:50:46

a little above me, I think.
couldn't get it to run, added From and it returned 1 record (should be 45).
But here's the jist of it.
I have a Test table that stores records for each test done on a sample. ie;


TestID  SampleNumber  TestName  Status
1       100            test1      i
2       100            test2      i
3       100            test3      c
4       100            test4      c
5       100            test5      i
6       200            test1      c
7       200            test2      c
8       200            test3      c
9       200            test4      c
10      200            test5      i

I need to pull all samples that have status='i' for test1 and test2
and a status='c' for test3 and test4.
Using the above data my result should be 100

Thanks for your help!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 12:11:13

hmm - that query should have given more than you wanted not less as it just gets any that have the test1 or test 2 and i not both
Try
select a.SAMPLE_NUMBER
from
(
select SAMPLE_NUMBER
FROM dbo.TEST
where test.status = 'i' and analysis in ('test1','test2')
group by SAMPLE_NUMBER having count(*) = 2
) a
join
(
select SAMPLE_NUMBER
FROM dbo.TEST
where test.status = 'c' and analysis in ('test3','test4')
group by SAMPLE_NUMBER having count(*) = 2
) b
on a.SAMPLE_NUMBER = b.SAMPLE_NUMBER

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

criki
Starting Member

4 Posts

Posted - 2010-12-03 : 12:16:51

Thank you! I changed it up a bit, but it worked:


select distinct a.SAMPLE_NUMBER
from
(SELECT sample_number
  FROM [T_LW_LIMS].[dbo].[TEST]
  where ((ANALYSIS = test3' and STATUS = 'c') or (ANALYSIS='test4' and STATUS = 'c'))) a
  join
  (SELECT sample_number
  FROM [T_LW_LIMS].[dbo].[TEST]
  where  ((ANALYSIS='test1' and STATUS = 'i') or (ANALYSIS='test2' and STATUS = 'i'))) b
  on a.SAMPLE_NUMBER = b.SAMPLE_NUMBER

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 12:21:58

that's doing an or instead of an and
i.e.
test1 i
test2 c
test3 c

will be included.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources