Psst! Did you know DevelopmentNow is a mobile web site design agency?

Contact us for help mobilizing your site, or to sign up for our beta Mobile Web SDK!
all groups > sqlserver server > february 2006 >

sqlserver server : How to do this SQL analysis...



Rick Sawtell
2/27/2006 11:14:54 AM

[quoted text, click to view]

How about doing a DATEADD on the seconds for completed tasks as a time2 in
the query, then select BETWEEN time1 and time2.

Rick Sawtell
MCT, MCSD, MCDBA





Scott Morris
2/27/2006 1:42:40 PM
[quoted text, click to view]

I assume you realize why this task is so difficult. For those who don't,
this is what happens when the schema is not properly normalized. In this
case, I am assuming that there is a 1-1 relationship between the "start" row
of a given task and the "end" row of a given task - both are related by a
common value for ID_KEY. Comments below are predicated on these
assumptions.

[quoted text, click to view]

The basic query that provides the data you seek is:

select t1.ID_KEY, t1.DATETIME as STARTDT,
DATEADD(mi, t2.DURATION, t1.DATETIME) as ENDDT,
t2.DURATION
from mytable as t1
left outer join mytable as t2
on t1.ID_KEY = t2.ID_KEY
and t1.DATETIME is null
and t2.DATETIME is not null
order by ????

Using the above as a derived table, the logic you need is something along
the lines of:

declare @target datetime

set @target = '20060227 13:45:00.000'
select .....
from [this is where the derived table goes, assume an alias of durdata]
where STARTDT <= @target
and coalesce (ENDDT, @target) >= @target
order by ????

This gives you information as of a particular moment in time. You should be
able to expand that logic to get information for a particular time period.

[quoted text, click to view]

If you want hour-by-hour information, just create (and populate) a
"calendar" table. This can be done in different ways - one way is
dynamically with a table-valued function. With that table, you join to the
derived table and substitute the variable boundary values with the
associated columns from the calendar table.

Scott Morris
2/27/2006 4:02:30 PM
[quoted text, click to view]

Perhaps. There are many uses for a calendar table (or, more simply, a table
of sequential numbers). You can search the newsgroup for reasons and
examples of their usage. Whether there is value in making this information
permanent is something you must determine based on its useful as well as
other factors that may not be apparent to outsiders like us. Most people
discover that there are special ways of handling information based on
whether a date is a "work day" (as opposed to a holiday, snow day, or other
non-working day)

Assume a temp table contains this info. At its simplest, it is just a table
of dates. Simplying your request some more, lets assume that we want hourly
numbers for the current date. Given the previously posted queries, what
specifically do we need in order to generate the appropriate information?

Answer - we need the boundaries of our timeperiods. Back to the example, we
need a table of two columns that define our boundary values for each period
of interest. The rows would look like the following:

'20060227 00:00:00.000' , '20060227 01:00:00.000'
'20060227 01:00:00.000' , '20060227 02:00:00.000'
'20060227 02:00:00.000' , '20060227 03:00:00.000'

Did you notice the overlap of time from the end of one period to the start
of the next. By usage / convention, we'll prevent the overlap from altering
our numbers. Why overlap - for clarification / presentation purposes only.

To get your data, join this table using the previous derived table example -
replace the variable usage with the appropriate columns and add the group by
information. Focus particularly on the coalesce usage. Note that a task
applies to a period if the startdate of the task precedes the end date of
the period (not inclusive) and the end date of the task falls on/after the
start date of the period (inclusive - this is the "usage convention"
mentioned earlier). Note that a task that has not completed applies to all
periods that end after the task start date.

There are tricks to generating the timeperiod information. For now, it is
probably easiest to focus on the logic and ignore whatever tricks might be
used for generation. It probably makes most sense, from a developing
perspective, to create a temp table and populate it with insert statements
in a script. It's only 24 insert statements and you can copy/paste the
information quickly.

Play with the concept a bit. I'll work up an example if you still have
questions.

HK
2/27/2006 5:06:12 PM
With SQL 2005 Standard: I have records in a table that show how long it
takes to complete a task...

ID_KEY DATETIME (nearest minute) DURATION

When a task is started, a record is added with a "null" duration, and when
the task is finished, the duration is updated to represent the number of
seconds the task took to complete. So, here's sample values:

133541 2006-02-27 16:51:00 NULL
133540 2006-02-27 16:51:00 60
133539 2006-02-27 16:50:00 NULL
133538 2006-02-27 16:50:00 153
133537 2006-02-27 16:50:00 30

The above data shows that 2 tasks are not complete and 3 tasks finished in
those respective #seconds.

My question: I would like to know how to determine the number of
simulataneous tasks in process for any given period of time, or by the hour
of the day. Right this moment, I can determine that by doing SELECT
COUNT(*) FROM TABLE WHERE DURATION IS NULL, but once you get into the
historical perspective (where the nulls are all real duration values), you
have to figure our which duration values overlap with other duration values.
Yeah, I know my full-minute rounding will screw up the analysis, but coming
close would be nice right now. For example, the 153 second task was
simultaneous with the two tasks that started one minute later. But how do
you run some kind of query against the table for periods of time where the
durations are all now complete (no nulls for yesterday, for example, because
those tasks are complete). How do I determine those "max simultaneous tasks
were in progress" during hour by hour windows of time? Or something like
that? I really need this to determine staffing, because if we had 100
simulatenous tasks between 8 and 8:10 AM yesterday morning, I know we need
to hire more people.

HK
2/27/2006 6:32:38 PM

[quoted text, click to view]


Although I understand the concept and how to use date functions, I still
don't know how to put this into production in a real query (the selecting
count between time1 and time2 is still not clear when I try), and also how
to group by time intervals. Sample syntax would be much appreciated.
Thanks.

HK
2/27/2006 8:36:45 PM

[quoted text, click to view]


Thanks for the "join to itself" example. I'm not understanding the
calendar table, sorry. Are you able to give a quick example of that? Are
you meaning to create a temp table?

HK
2/27/2006 11:02:58 PM

[quoted text, click to view]


So you were talking about creating a truly separate table, with each row
being a period of time, like hourly. I understand now, thanks.

But one thing still escapes me. So you have the thousands of raw records
(the example records I gave in the beginning of this post) in each day. If
I said tell me the busiest time of day yesterday, and how many simultaneous
tasks were being executed at that moment, how would you determine the
answer? Would you create 24*60 rows in your calendar table, one for each
minute? And if so, how would you do this in an automated way, to both
create the calendar table rows AND insert the count value into each row? I
certainly don't want to hand-type 24*60 entries for each day. I can
imagine writing code that looks through a recordset, row by row, with
counters as it goes. But I would also think that there should be a way to
do this from query analyzer.

Scott Morris
2/28/2006 12:00:00 AM
[quoted text, click to view]

yes - based on your examples and questions only. Obviously, the solution
depends on what "answer" you seek.

[quoted text, click to view]

Good requirements often lead you to a good solution. Define "busiest time
of the day". This raises the issue of how accurate an answer you seek (and
how accurate your data is). If the data is only accurate to the nearest
minute, then the answer is equally limited. Knowing that, we need to
calculate the outstanding task counts on a minute basis throughout the
period of interest (yesterday) and get the largest count. This involves 1
day x 24 hours x 60 minutes invidividual calculation periods; this then
drives the content of the temp table discussed earlier. BTW - a table of
1440 (24 x 60) rows is a very, VERY, small table.

[quoted text, click to view]

You said "automated", followed by "hand-type". Obviously, anything that you
do on a regular basis can be automated and there is no reason why you would
need to "hand-type" anything more than once. Is it possible to write a
script to populate a temp table with the hourly intervals for a specified
date? Of course. There is a looping construct in tsql. There are also
tricks, as mentioned earlier. Steve Kass has posted a UDF that generates a
table of sequential numbers in the .programming NG, if you want to see one
such technique.

[quoted text, click to view]

Yes - you do this because you are most familiar with conventional
programming. Good relational solutions involve set manipulations. Very
rarely is a "row-by-row" solution appropriate; often such a solution is far
from "best". Search the newsgroups for the term "cursor" to see the oft
repeated discussions about such things.

[quoted text, click to view]

tsql code is tsql code. How you execute it is your decision. QA is but one
tool that can do this.

Scott Morris
2/28/2006 12:00:00 AM
[quoted text, click to view]

This is wrong. Replace the datetime column in the join conditions with the
duration column. I should point out that there is an assumption regarding
the datetime column (nice name, btw) - specifically that all rows for a task
(identified by ID_KEY) have the same value for the DATETIME column.

Scott Morris
2/28/2006 9:42:35 AM
A working example

set nocount on
go

if object_id('tempdb..#rawdata') is not null
drop table #rawdata
go
if object_id('tempdb..#period') is not null
drop table #period
go



create table #rawdata (
id_key int not null
, tstamp datetime not null
, duration int null
)


insert #rawdata (id_key, tstamp, duration)
select 31, '20060227 16:51:00', NULL
union all
select 31, '20060227 16:51:00', 181
union all
select 28, '20060227 08:03:00', null
union all
select 28, '20060227 08:03:00', 600
union all
select 12, '20060227 00:00:00', null
union all
select 900, '20060227 00:00:00', null
union all
select 901, '20060227 12:00:00', null
union all
select 88, '20060227 23:00:00', null
union all
select 88, '20060227 23:00:00', 60

select id_key, tstamp, duration from #rawdata order by id_key, tstamp,
duration

select t1.id_key, t1.tstamp as STARTDT,
dateadd (mi, t2.duration, t1.tstamp) as ENDDT,
t2.DURATION
from #rawdata as t1
left outer join #rawdata as t2
on t1.id_key = t2.id_key
and t1.duration is null
and t2.duration is not null
where t1.duration is null

create table #period (
pstart datetime not null
, pend datetime not null
)

declare @temp int, @start datetime
set @start = '20060227'
while @start < '20060228'
begin
insert #period (pstart, pend) values (@start, dateadd (hour, 1, @start))
set @start = dateadd (hour, 1, @start)
end

select * from #period order by pstart

select *
from (
select t1.id_key, t1.tstamp as startdt,
dateadd (mi, t2.duration, t1.tstamp) as enddt,
t2.duration
from #rawdata as t1
left outer join #rawdata as t2
on t1.id_key = t2.id_key
and t1.duration is null
and t2.duration is not null
where t1.duration is null
) as normal

select #period.*, normal.*
from (
select t1.id_key, t1.tstamp as startdt,
dateadd (mi, t2.duration, t1.tstamp) as enddt,
t2.duration
from #rawdata as t1
left outer join #rawdata as t2
on t1.id_key = t2.id_key
and t1.duration is null
and t2.duration is not null
where t1.duration is null
) as normal
inner join #period
on normal.startdt < #period.pend
and coalesce (normal.enddt, #period.pend) > #period.pstart
order by normal.id_key, #period.pstart


select #period.pstart, #period.pend, count(*)
from (
select t1.id_key, t1.tstamp as startdt,
dateadd (mi, t2.duration, t1.tstamp) as enddt,
t2.duration
from #rawdata as t1
left outer join #rawdata as t2
on t1.id_key = t2.id_key
and t1.duration is null
and t2.duration is not null
where t1.duration is null
) as normal
inner join #period
on normal.startdt < #period.pend
and coalesce (normal.enddt, #period.pend) > #period.pstart
group by #period.pstart, #period.pend
order by #period.pstart

HK
2/28/2006 11:08:56 PM
Thank you for spending the time on the examples.
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.
Don't see what you're looking for? Try a search.
View other messages about sqlserver server
AddThis Social Bookmark Button
View Other Months
March 2003
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007