Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: conditional query

Show all headers | 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