Can you make this more efficient? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can you make this more efficient?

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2010-01-06 : 15:29:05

Kudos to anyone who can do this mess in fewer steps . . .

DECLARE @EventName_A NVARCHAR(100)
DECLARE @EventName_B NVARCHAR(100)

SELECT @EventName_A = 'Non-Foul' ,
@EventName_B = '2nd Base'

DECLARE @ClosedEvents TABLE
(
HistoryID INT ,
Player NVARCHAR(14) ,
EventType NVARCHAR(100) ,
FirstEvent BIT ,
LastEvent BIT,
Event_A BIT,
Event_B BIT
)
INSERT INTO @ClosedEvents
VALUES ( 1, 'JONES', 'Foul One', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 2, 'JONES', 'Non-Foul', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 3, 'JONES', '1st Base', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 4, 'SMITH', 'Non-Foul', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 5, 'JONES', '2nd Base', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 6, 'SMITH', '1st Base', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 7, 'MAY', 'Non-Foul', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 8, 'MAY', '1st Base', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 9, 'SMITH', 'Out Two', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 10, 'JONES', '3rd Base', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 11, 'JAMES', 'Non-Foul', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 12, 'JAMES', '1st Base', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 13, 'JONES', 'Home Plate', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 14, 'JAMES', '2nd Base', 0, 0 , 0, 0 )
INSERT INTO @ClosedEvents
VALUES ( 15, 'SMITH', 'Out three', 0, 0 , 0, 0 )

IF OBJECT_ID('tempdb..#FirstAndLast') IS NOT NULL
DROP TABLE #FirstAndLast

SELECT Player ,
MIN(HistoryID) AS [FirstHist] ,
MAX(HistoryID) AS [LastHist]
INTO #FirstAndLast
FROM @ClosedEvents
GROUP BY Player

UPDATE C
SET C.FirstEvent = ( CASE HistoryID
WHEN FirstHist THEN 1
ELSE 0
END ) ,
C.LastEvent = ( CASE HistoryID
WHEN LastHist THEN 1
ELSE 0
END ) ,
C.Event_A = (CASE EventType WHEN @EventName_A THEN 1 ELSE 0 END ) ,
C.Event_B = (CASE EventType WHEN @EventName_B THEN 1 ELSE 0 END )
FROM @ClosedEvents C
INNER JOIN #FirstAndLast F ON F.Player = C.Player

SELECT HistoryID ,
Player ,
EventType ,
FirstEvent ,
LastEvent ,
Event_A ,
Event_B
FROM @ClosedEvents

~ Shaun Merrill
Seattle area

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 01:13:28

[code]UPDATE C
SET C.FirstEvent = C1.StartEvent ,
C.LastEvent = C1.EndEvent,
C.Event_A = C1.Event_A,
C.Event_B = C1.Event_B
FROM @ClosedEvents C
INNER JOIN (SELECT Player, MIN(HistoryID) AS StartEvent,
MAX(HistoryID) AS EndEvent,
MAX(CASE WHEN EventType=@EventName_A THEN 1 ELSE 0 END) AS Event_A,
MAX(CASE WHEN EventType=@EventName_B THEN 1 ELSE 0 END) AS Event_B
FROM @ClosedEvents
GROUP BY Player)C1
ON C1.Player=C2.Player
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources