T-SQL Stumper

Question

Post reply

T-SQL Stumper

John Magnabosco

Ten Centuries

Points: 1195
More actions
September 10, 2009 at 2:59 pm

#133940

Here is a real stumper:

I have a single row coming out of a table:

Name Date Hours Sec

--------------------------------------------------------

Sam 09/10/2009 15:20:39.000 3 11024

I need to represent this row in the following format:

Name Date Sec

-------------------------------------------------

Sam 09/10/2009 12:00:00.000 2585

Sam 09/10/2009 13:00:00.000 3600

Sam 09/10/2009 14:00:00.000 3600

Sam 09/10/2009 15:00:00.000 1239

Does anyone have ideas of how I can achieve this without a cursor or while loop?

This only reflects a single original row; but the original row can return many names with their data.

Thanks!

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1



use testing

select 'Sam' name1, '09/10/2009 15:20:39.000' endts, 3 unk, 11024 duration

into #matt

go

;with MattCTE as (

select datediff(second,dateadd(hour,datediff(hour,0,endts),0),endts) basesecs,

dateadd(hour,datediff(hour,0,endts),0) basehour,

*

from #matt)

select

case when (duration-basesecs-(n-1)*3600)>3600 then 3600 else (duration-basesecs-(n-1)*3600) end secsspent,

dateadd(hour,(-1*n),basehour) timeslice,

endts,unk,duration from MattCTE

inner join tally on duration-basesecs-(n-1)*3600>0

where n<10

UNION ALL

select case when duration<basesecs then duration else basesecs end secsspent,

basehour timeslice,

endts,unk,duration

from mattCTE

ORDER by timeslice desc

Try this on....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Garadin One Orange Chip Points: 29613 More actions · Answer 2

My immensely more confusing (but 2000 compliant) solution. I'm sure I could make this much less confusing and likely more efficient.

DECLARE @a TABLE(



NAME	VARCHAR(20),

date	DATETIME,

hours	INT,

seconds	BIGINT)

insert INTO @a

SELECT 'Sam', '09/10/2009 15:20:39.000', 3, 11024 UNION ALL

SELECT 'Mary', '09/10/2009 13:26:19.000', 3, 18439 UNION ALL

SELECT 'Beth', '09/10/2009 4:16:22.000', 2, 8439 

SELECT NAME,

		DATEADD(hh,-(N-1),DATEADD(hh, DATEDIFF(hh,0,[date]),0)),

		CASE WHEN N = 1 	THEN DATEDIFF(s,(DATEADD(hh, DATEDIFF(hh,0,[date]), 0)),[date])

			WHEN N > 1 AND DATEDIFF(s,(DATEADD(hh, DATEDIFF(hh,0,[date]), 0)),[date]) + ((N-1) * 3600) < seconds

						THEN 3600

			ELSE seconds - (DATEDIFF(s,(DATEADD(hh, DATEDIFF(hh,0,[date]), 0)),[date]) + ((N-2) * 3600))

			END

		Seconds

FROM @a, tally

WHERE (N-1)*3600  1 AND DATEDIFF(s,basehour,[date]) + ((N-1) * 3600) < seconds

						THEN 3600

			ELSE seconds - (DATEDIFF(s,basehour,[date]) + ((N-2) * 3600))

			END

		Seconds

FROM @a, tally

WHERE (N-1)*3600 < seconds

ORDER BY NAME,

DATEADD(hh,-(N-1),Basehour) DESC

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

John,

Both solutions above use a wonderful little tool known as a "Tally" or "Numbers" table. To see how to build one as well as a detailed explanation as to how they work and how they can be used to replace a While loop in a high speed fashion, please see the following article...

http://qa.sqlservercentral.com/articles/T-SQL/62867/

--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)
Intro to Tally Tables and Functions