Groups > comp.databases.ms-sqlserver > #620

Re: conditional query

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	"Bob Barrows" <reb01501@NOyahooSPAM.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: conditional query
Date	Tue, 23 Aug 2011 15:35:40 -0400
Organization	A noiseless patient Spider
Lines	68
Message-ID	<j30veg$eoe$1@dont-email.me> (permalink)
References	<6d06d2ab-012b-4e50-a734-9c71a7dc31c6@s7g2000yqd.googlegroups.com> <j30qjb$b7d$1@dont-email.me> <9d3c54c1-361f-41be-92cc-be2c95afa66b@p10g2000yqi.googlegroups.com>
Injection-Date	Tue, 23 Aug 2011 19:35:44 +0000 (UTC)
Injection-Info	mx04.eternal-september.org; posting-host="NUmboHSNYG4cX5Ys9j8TNw"; logging-data="15118"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+38IstQzz3BhOQSnIRNmXjAKU5dIJ2X3U="
X-Newsreader	Microsoft Outlook Express 6.00.2800.2001
Cancel-Lock	sha1:Nndi4Xqn0WLjY+CKQDBOaolwuIY=
X-Priority	3
X-MSMail-Priority	Normal
X-MIMEOLE	Produced By Microsoft MimeOLE V6.00.2800.2001
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:620

Show key headers only | View raw

Harry wrote:
> On Aug 23, 2:11 pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
>> Harry wrote:
>>> I have a table with three columns:
>>
>>> Name Date Action
>>> Fred Today this
>>> Fred Today this
>>> Fred Today that
>>> Wilma Today this
>>
>>> I want to sort by name and by date, but only if 'this' was done more
>>> than once on the same day, and not if 'that' was done on the same
>>> day,They have to perform this more than once, and they cannot have
>>> performed that on the same day.
>>
>> So the desired result from this sample data would be an empty
>> resultset? What do you want to display when the data actually
>> contains rows that meet your requirement? All the rows for that name
>> and date? Or a single row for that name and date?
>> Why don't you try again. Show us some sample data that includes data
>> that meets your requirements and then show us what you want the
>> desired query to return. The key being "show" - don't just describe
>> the desired result: show it to us.
>
> The raw data would be:
>
> Name     Date       Proc
> Fred         1            stent
> Fred         1            stent
> Fred         1            unstent
> Wilma       2           stent
> Wilma       2           stent
> Barney      2           stent
> Barnet       2           stent
> Barney       2          stent
> Barney       2          unstent
> ...
>
>
> The query would return
>
> Wilma       2           stent
> Wilma       2           stent
>
>
> Because Fred and Barney also had a stent pulled on the same day they
> had more than one stent inserted.
>
Assuming SQL 2005 or greater:

;WITH q AS (
SELECT Name,Date
,SUM(CASE WHEN Proc='stent' THEN 1 ELSE 0 END) stents
,SUM(CASE WHEN Proc='unstent' THEN 1 ELSE 0 END) unstents
FROM tablename
GROUP BY Name, Date)
,q2 AS (
SELECT Name,Date FROM q WHERE stents>1 AND unstents=0)
SELECT t.Name,t.Date,t.Proc FROM tablename t
JOIN q2 ON t.Name=q2.Name and t.Date=q2.Date

This is untested air code (obviously), solely intended to provide the
general idea.
Hopefully, you actually haven't used those reserved keywords for your column
names ...

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

conditional query Harry <hamackey@gmail.com> - 2011-08-23 06:49 -0700
  Re: conditional query Henk van den Berg <me@myplace.net> - 2011-08-23 19:41 +0200
    Re: conditional query Harry <hamackey@gmail.com> - 2011-08-23 10:56 -0700
      Re: conditional query Henk van den Berg <me@myplace.net> - 2011-08-23 23:04 +0200
  Re: conditional query "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-08-23 14:11 -0400
    Re: conditional query Harry <hamackey@gmail.com> - 2011-08-23 12:06 -0700
      Re: conditional query "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-08-23 15:35 -0400
      Re: conditional query Yonghang Wang <wyhang@gmail.com> - 2011-08-24 13:53 -0700
        Re: conditional query "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-08-24 17:34 -0400
          Re: conditional query Yonghang Wang <wyhang@gmail.com> - 2011-08-24 21:21 -0700

csiph-web