Selective SELECT on data

Question

Post reply

Selective SELECT on data

Digs

SSCertifiable

Points: 7333
More actions
February 13, 2014 at 11:33 am

#289534

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1;
CREATE TABLE #temp1(
Symbol VARCHAR(10),
ExpDate VARCHAR(10),
Tag BIT,
)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('A','2014-03',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('A','2014-04',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('A','2014-05',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('B','2014-03',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('C','2014-03',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('C','2014-04',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('D','2014-03',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('D','2014-04',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('D','2014-05',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('E','2014-04',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('E','2014-05',0)
INSERT INTO #temp1 (Symbol,ExpDate,Tag) VALUES ('F','2014-05',0)
SELECT *
FROM #temp1
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1;
Hi,
I wish to tag = 1 where the Symbol has more than one ExpDate while it has 2014-03 in the data.
I only wish to tag the 2014-03 only when there is more than on ExpDate while 2014-03 is the data for each symbol, this is the desired result.
A2014-031
A2014-040
A2014-050
B2014-030
C2014-031
C2014-040
D2014-031
D2014-040
D2014-050
E2014-040
E2014-050
F2014-050
Any ideas??:-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Here's a possible solution for you. Be sure to understand it and ask any questions you have.

UPDATE t SET

Tag = 1

FROM #temp1 t

WHERE ExpDate = '2014-03'

AND Symbol IN( SELECT x.Symbol FROM #temp1 x GROUP BY x.Symbol HAVING COUNT(*) > 1)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mickyT SSChampion Points: 10360 More actions · Answer 2

Hi

If I have understood your requirements correctly, this should do what you want

SELECT Symbol,

ExpDate,

CASE

WHEN expdate = '2014-03'

AND COUNT(*) OVER (PARTITION BY Symbol) > 1

THEN 1

ELSE 0

END tag

from #temp1

Digs SSCertifiable Points: 7333 More actions · Answer 3

Digs

SSCertifiable

Points: 7333

February 13, 2014 at 2:16 pm

#1688924

Thanks

Will work with the above!

Dwain Camps SSC Guru Points: 86978 More actions · Answer 4

Here are 2 more ways:

WITH PreAggregate AS

(

SELECT Symbol, ExpDate

FROM #temp1

WHERE ExpDate > '2014-03'

)

UPDATE a

SET Tag = 1

FROM #temp1 a

JOIN PreAggregate b ON a.Symbol = b.Symbol

WHERE a.ExpDate = '2014-03';

UPDATE a

SET Tag = 1

FROM #temp1 a

WHERE a.ExpDate = '2013-03' AND EXISTS

(

SELECT 1

FROM #temp1 b

WHERE a.Symbol = b.Symbol AND ExpDate > '2013-03'

);

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St