Consider yourself a SQL Server Guru? - Solve this!

Question

Post reply

Consider yourself a SQL Server Guru? - Solve this!

a1011

SSC Enthusiast

Points: 180
More actions
April 29, 2010 at 11:36 am

#232221

I have a unique situation and I am having trouble writing a query to deal with it. This is what I have:
RowIDDatecreated Event
112010-04-01 06:16:16.6230
212010-04-01 12:25:18.9400
312010-04-01 20:03:07.1101
412010-04-01 20:40:27.2170
522010-04-01 09:48:42.4500
622010-04-01 09:51:24.8900
722010-04-01 20:37:29.9531
822010-04-01 21:48:40.8900
922010-04-01 22:13:56.6330
1022010-04-01 23:52:41.6530
This is what I am trying to achieve:
RowIDDatecreated Event Value
112010-04-01 06:16:16.6230 2
212010-04-01 12:25:18.9400 2
312010-04-01 20:03:07.1101 0
412010-04-01 20:40:27.2170 1
522010-04-01 09:48:42.4500 2
622010-04-01 09:51:24.8900 2
722010-04-01 20:37:29.9531 0
822010-04-01 21:48:40.8900 3
922010-04-01 22:13:56.6330 3
1022010-04-01 23:52:41.6530 3
Therefore basically I want the value column. This is how the value column should work:
It should sum up the number of rows where there is no event until an event occurs for a specific ID. Therefore for the first two rows the value is 2 because no event occurs in those rows but an event occurs in the third row.
For the Forth row the Value is 1 because no event occurs in that row and the ID changes from 1 to 2 in the fifth row.
For 5th and 6th rows the value is 2 because no events occurs in those rows for ID =2 but an event occurs in the 7th row.
Whenever there is an event the Value = 0
(when an event occurs Event = 1; When there is no event Event = 0)
Any help will be much appreciated
(I have attached the table that I need since the tables I have posted here are not very legible)
Thank you

Viewing 6 posts - 1 through 6 (of 6 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'm by far no SQL Server Guru, but that issue seems to be solvable.

The question is: What have YOU tried so far?

Also, if you want some of the heavy hitters having a look at it you might want to provide data in a ready to use format as described in the first article I referenced in my signature. (side note: the person who wrote that article is known to be one of the SQL experts not only on this site... 😉 )

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95461 More actions · Answer 2

If I can solve it, then it definitely doesn't take a guru to solve this. Though they can probably make it more efficient.

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

if OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test

CREATE TABLE #Test (

Row int,

ID int,

Datecreated datetime,

[Event] bit

)

INSERT INTO #Test (Row, ID, Datecreated, [Event])

SELECT 1, 1, '2010-04-01 06:16:16.623', 0 UNION ALL

SELECT 2, 1, '2010-04-01 12:25:18.940', 0 UNION ALL

SELECT 3, 1, '2010-04-01 20:03:07.110', 1 UNION ALL

SELECT 4, 1, '2010-04-01 20:40:27.217', 0 UNION ALL

SELECT 5, 2, '2010-04-01 09:48:42.450', 0 UNION ALL

SELECT 6, 2, '2010-04-01 09:51:24.890', 0 UNION ALL

SELECT 7, 2, '2010-04-01 20:37:29.953', 1 UNION ALL

SELECT 8, 2, '2010-04-01 21:48:40.890', 0 UNION ALL

SELECT 9, 2, '2010-04-01 22:13:56.633', 0 UNION ALL

SELECT 10, 2, '2010-04-01 23:52:41.653', 0

;WITH CTE AS

(

SELECT * ,

RowNbr = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Datecreated)

FROM #Test t1

), CTE2 AS

(

SELECT *,

Value = CASE WHEN [Event] = 1 THEN 0

ELSE (SELECT MIN(t2.RowNbr)

FROM CTE t2

WHERE t2.ID = t1.ID

AND t2.RowNbr > t1.RowNbr

AND t2.[Event] = 1)

END

FROM CTE t1

)

SELECT Row,

ID,

Datecreated,

[Event],

Value = CASE WHEN Value = 0 THEN 0

WHEN Value > 0 THEN Value - 1

ELSE (SELECT COUNT(*)

FROM CTE2 t2

WHERE t1.ID = t2.ID

AND Value IS NULL)

END

FROM CTE2 t1

ORDER BY Row

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

a1011 SSC Enthusiast Points: 180 More actions · Answer 3

You guys are awesome. Thank you for helping me out with this. Also, next time I want any help I will post a table with data in it so as to make it easier for people to help, as you suggested.

Thank you very much!!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

a1011 (4/29/2010)
I have a unique situation and I am having trouble writing a query to deal with it. This is what I have:
RowIDDatecreated Event
112010-04-01 06:16:16.6230
212010-04-01 12:25:18.9400
312010-04-01 20:03:07.1101
412010-04-01 20:40:27.2170
522010-04-01 09:48:42.4500
622010-04-01 09:51:24.8900
722010-04-01 20:37:29.9531
822010-04-01 21:48:40.8900
922010-04-01 22:13:56.6330
1022010-04-01 23:52:41.6530
This is what I am trying to achieve:
RowIDDatecreated Event Value
112010-04-01 06:16:16.6230 2
212010-04-01 12:25:18.9400 2
312010-04-01 20:03:07.1101 0
412010-04-01 20:40:27.2170 1
522010-04-01 09:48:42.4500 2
622010-04-01 09:51:24.8900 2
722010-04-01 20:37:29.9531 0
822010-04-01 21:48:40.8900 3
922010-04-01 22:13:56.6330 3
1022010-04-01 23:52:41.6530 3
Therefore basically I want the value column. This is how the value column should work:
It should sum up the number of rows where there is no event until an event occurs for a specific ID. Therefore for the first two rows the value is 2 because no event occurs in those rows but an event occurs in the third row.
For the Forth row the Value is 1 because no event occurs in that row and the ID changes from 1 to 2 in the fifth row.
For 5th and 6th rows the value is 2 because no events occurs in those rows for ID =2 but an event occurs in the 7th row.
Whenever there is an event the Value = 0
(when an event occurs Event = 1; When there is no event Event = 0)
Any help will be much appreciated
(I have attached the table that I need since the tables I have posted here are not very legible)
Thank you

Heh... [font="Arial Black"]"CONSIDER YOURSELF TO BE AN SQL SERVER NEWBIE? READ THIS!!!"[/font]

[font="Arial Black"]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/font][/url]

😉

P.S. I love it when people post attachments in WORD 2007 format... I don't have it installed so I don't need to read them. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 5

Like I said before, I'm not considering myself as a guru, but here's how I would do it:

CREATE TABLE #tbl

(

RowINT,

IDINT,

DatecreatedDATETIME,

EventINT,

Value INT

)

INSERT INTO #tbl

SELECT 1,1,'2010-04-01 06:16:16.623',0, NULL UNION ALL

SELECT 2,1,'2010-04-01 12:25:18.940',0, NULL UNION ALL

SELECT 3,1,'2010-04-01 20:03:07.110',1, NULL UNION ALL

SELECT 4,1,'2010-04-01 20:40:27.217',0, NULL UNION ALL

SELECT 5,2,'2010-04-01 09:48:42.450',0, NULL UNION ALL

SELECT 6,2,'2010-04-01 09:51:24.890',0, NULL UNION ALL

SELECT 7,2,'2010-04-01 20:37:29.953',1, NULL UNION ALL

SELECT 8,2,'2010-04-01 21:48:40.890',0, NULL UNION ALL

SELECT 9,2,'2010-04-01 22:13:56.633',0, NULL UNION ALL

SELECT 10,2,'2010-04-01 23:52:41.653',0, NULL

;WITH cte AS

(

SELECT

row,

ROW_NUMBER() OVER(ORDER BY row)

- ROW_NUMBER() OVER(PARTITION BY id,EVENT ORDER BY row ) AS sub

FROM #tbl

),

cte2 AS

(

SELECT

row,

COUNT(*) OVER (PARTITION BY sub) AS val

FROM cte

)

UPDATE t

SET

[Value] = CASE WHEN t.event = 0 THEN val ELSE 0 END

FROM cte2

INNER JOIN #tbl t

ON cte2.row = t.row

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]