How to get the id's of those who have a specific price for every date ....

Question

How to get the id's of those who have a specific price for every date ....

tzanouch

SSC Rookie

Points: 34
More actions
May 27, 2011 at 2:29 am

#241155

I want to get the id's of everyone who has a specific value for every date.
ex. From the following table I want all those who have value='y' for every date
thus i want only the ID=1.
PS: The dates are not only those 4 🙂
ID's Dates Values
1 2011-01-01 y
1 2011-01-02 y
1 2011-01-03 y
1 2011-01-04 y
2 2011-01-02 n
2 2011-02-01 y
3 2011-01-01 y
3 2011-01-02 n
...
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

tzanouch (5/27/2011)
I want to get the id's of everyone who has a specific value for every date.
ex. From the following table I want all those who have value='y' for every date
thus i want only the ID=1.
PS: The dates are not only those 4 🙂
ID's Dates Values
1 2011-01-01 y
1 2011-01-02 y
1 2011-01-03 y
1 2011-01-04 y
2 2011-01-02 n
2 2011-02-01 y
3 2011-01-01 y
3 2011-01-02 n
...

SELECT ID

FROM [Table]

GROUP BY ID

HAVING MAX(Value)='y' AND MIN(Value)='y'

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 2

SELECT * FROM [Table]

Where

id=1 and value='y'

What about this?

tzanouch SSC Rookie Points: 34 More actions · Answer 3

To:SSC Veteran

I don't already know the id's.

So ....

tzanouch SSC Rookie Points: 34 More actions · Answer 4

To:mister.magoo

Thanks very much ....

This is a very clever way and it works fine and faster than my query which was like

ACCOUNT_NO IN (

select distinct ACCOUNT_NO from t1.ALL_ACCOUNTS where STATUS_DORMANT = 'Y'

and ACCOUNT_NO NOT in

(select distinct account_no from t1.ALL_ACCOUNTS where STATUS_DORMANT='N'))

group by ACCOUNT_NO

Thanks again

tzanouch SSC Rookie Points: 34 More actions · Answer 5

TO: MM

Actually my way was 2 times faster than yours

(my execution was 8sec and yours 16s).

How can this be??

I was sure that yours would be faster!!!!!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

tzanouch (5/27/2011)
TO: MM
Actually my way was 2 times faster than yours
(my execution was 8sec and yours 16s).
How can this be??
I was sure that yours would be faster!!!!!

Probably down to indexing - my simple query would be a table scan, yours can utilise an index.

Try this, especially if STATUS_DORMANT can only hold 'Y' or 'N':

SELECT ACCOUNT_NO

FROM t1.ALL_ACCOUNTS As A1

WHERE A1.STATUS_DORMANT = 'Y'

AND NOT EXISTS (SELECT 1 FROM t1.ALL_ACCOUNTS A2 where A2.ACCOUNT_NO = A1.ACCOUNT_NO AND A2.STATUS_DORMANT = 'N')

GROUP BY ACCOUNT_NO

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

David Burrows SSC Guru Points: 65144 More actions · Answer 7

tzanouch (5/27/2011)
I want to get the id's of everyone who has a specific value for every date.

Is that every date in the table or every date for each ID ?

eg.

ID's Dates Values

1 2011-01-01 y

1 2011-01-02 y

1 2011-01-03 y

1 2011-01-04 y

2 2011-01-02 y

2 2011-02-01 y

3 2011-01-01 y

3 2011-01-02 y

What would be the output ?

1

or

1

2

3

Far away is close at hand in the images of elsewhere.
Anon.

tzanouch SSC Rookie Points: 34 More actions · Answer 8

To: SSCertifiable

On your example

The wanted output should be 1,2,3,

thus for every date that there is a record for a specific id.