Pretty Patterns - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Pretty Patterns

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-27 : 04:03:15

I'm not sure that SQL is the best tool for this or even that it can be done in SQL but would appreciate any comments.

I have some data that I am looking for patterns in but there are a number of possible patterns

The data is about events that have occured over the last two years

What I know about events is this

Events last all day
An event can be for more than one day
Events can only happen on working days but if they happen on a friday and the following monday they count as one event
Events have a code (either S or US)

I need some ordinary summaries e.g. total events, total days (not counting weekends etc), average days per event, events by code etc.

However, and this is the more challenging part, I also need to try to identify patterns in the events

e.g. How many events were on a particular day (e.g. friday or the 3rd wednesday in the month)
There may also be a bi-monthly pattern e.g. every second month on the third week
Finally (and this is a bit more odd) there is a possibility that the events are related to the cycles of the moon but I have no idea how I could work this out with SQL.

Any suggestions (including don't do it in SQL) would be appreciated

thanks

steve

A sarcasm detector, what a great idea.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-27 : 04:22:02

Steve
Dont do it in SQL!!!

Whats the DDL of your events table?

Andy

Beauty is in the eyes of the beerholder

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-27 : 05:04:17

Some sample data could be useful...

rockmoose

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-27 : 09:14:44

OK, at the moment there is no DDL for the events table but the data is of the form (using ISO dates to save confusion)
date, code
'20050412', 'US'
'20050413', 'US' -- these would collectively form a single event
'20031112', 'S'
'20041119', 'S'

It would also be relatively straightforward to have it in the following form if it was easier to handle

date , duration , code
'20050412', 2 'US' -- this would indicate two consecutive days which would collectively form a single event
'20031112', 1 'S'
'20041119', 1 'S'

At the moment the data is on bits of paper scattered all over the place which is why I am trying to make sense of it

Does this help?

steve

A sarcasm detector, what a great idea.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-27 : 09:40:24

simple parent-child:


create table Event
(EventId int --PK
--, other stuff...
)

create table EventPart
(
EventPartId int, -- PK
EventId int,     -- FK to event table
EventDate datetime,
EventCode varchar(10)
--, other stuff...
)

then you can specify what counts as an event in the Event table and how many parts it has in the EventPart table.
or is there something more i'm not seeing??

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-27 : 11:33:13

>>At the moment the data is on bits of paper scattered all over the place which is why I am trying to make sense of it
You haven't been in my office have You ???

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-27 : 12:16:57

I would collect all the data in a (fact) table, just like You did:
(group the events if they can be grouped in various categories)
date, code
'20050412', 'US'
'20050413', 'US' -- these would collectively form a single event (ok this can be tricky, but I don't know enough)
'20031112', 'S'
'20041119', 'S'

Then create a time (dimension) table.
In the time table create columns for all the dimensions that You would like to examine
eg.
1st,2nd,3rd,4th,,, day of week
moonphase

workday/holiday/weekend
vacation month
1,2,3,,, 12 month
1,2,3,4,,,31 day
1,2,3,4,5 week in month
1,2,3,4,,,53 week in year
... ?

Then do histograms (group by), plotting the event (or eventgroup)
against the various aspects of time that are interesting.

You could for example plot the occurrence of an event against month + weekinmonth in order to see if
this event has a pattern of occurring 2nd weekinmonth every 2 months.

Ideally You will need something to visualize the data (makes analysis quicker).
Try Analysis Server

Or maybe SQL Server's data mining (which I have not had any success with)

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-27 : 12:20:52

are you having trouble identifying patterns? or is it the table structure thats the biggest problem? i think mladens right about the basic structure...

can you give a little bit more info on the patterns...?

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-05-27 : 20:25:28

Go Analysis Services or use more sophisticated analysis tools like SAS
[url]http://www.sas.com/technologies/analytics/statistics/index.html[/url]

You can't teach an old mouse new clicks.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-01 : 03:43:52

It's the patterns I'm having problems with. I agree that the structure is simple.

The problem is that I suspect that there is some pattern to these events and I can't see what it is.

I suspect that it is connected to the phases of the moon (I know it sounds bizarre) but there is a possibility that there is some other sort of pattern, e.g. that these events occur every two months

steve

A sarcasm detector, what a great idea.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-01 : 03:59:13

I think You need a continuous datetable with the phases of the moon in it

( could be great script resource...
I did statistical analysis on patients with mental disorders a few years ago,
but the customer was not as keen as I on analysis against zodiac and moonphases )

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-01 : 04:10:16

hey steve so what is your consern with patterns here??
why do you need to recognize them?

Go with the flow & have fun! Else fight the flow

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-03 : 03:18:23

OK, to be honest I'm looking at sick leave. There are some people here who have taken a lot of sick leave and I'm trying to work out if there is any regular pattern for any of these people i.e. I would be looking at one person at a time. One of these people has a bit of a thing about phases of the moon (I think for religious reasons). We have forms that allow us to look for some patterns but they are tedious and time consuming, I'm a computer geek, I want a simple solution (even if it takes me months to write) However, if I can get this working I will probably put it into the script forum.

I have been wary of saying this as it can be a sensitive topic but this is NOT a finger pointing excercise, it's more about finding explanations and then talking to the individual concerned as often this can reduce the 'problem' to more normal levels which is all I'm looking for.

Having thought about it, what Rockmoose says does of course make sense, I must be able to create a function or something

steve

A sarcasm detector, what a great idea.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-03 : 05:01:13

then you definitly need a date table and then you can do histograms.
why do i get the feeling you and rockmoose are working on the same stuff...

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-06-03 : 05:26:55

Might be worthwhile tying it to Sporting Event fixtures, Concerts and the like

And the standard Mother-in-Law visting/JustVisited calendar of course

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-06 : 09:06:49

Let me get this right spirit. Are you suggesting a table with ALL dates for the last two years or just the ones of interest?

Actually Kristen you may have a point. There could be a familial factor.

thanks

steve

A sarcasm detector, what a great idea.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-06 : 09:18:46

Yes, You would want all the dates in the table.

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-06 : 09:18:58

well if you want histograms than why not....

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-06 : 09:21:41

12 seconds ...

rockmoose