February 13, 2014 at 11:33 am
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??:-)
February 13, 2014 at 11:48 am
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)
February 13, 2014 at 11:49 am
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
February 13, 2014 at 2:16 pm
Thanks
Will work with the above!
February 16, 2014 at 7:00 pm
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 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply