Count data in rows? - 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 2005 Forums

Transact-SQL (2005)

Count data in rows?

Author

Topic

flipfyre
Starting Member

26 Posts

Posted - 2009-11-05 : 12:50:48

I have a kinda weird/advanced count situation for a work schedule project.

The table is like so:

ID LOC NAME JAN1 JAN2 JAN3 JAN4
1 US GEORGE SMITH OFF OFF [BLANK] OFF
2 US MARK SANCHEZ OFF [BLANK] [BLANK] OFF
3 US TOM DAVID [BLANK] [BLANK] [BLANK] [BLANK]
TOTAL: 1

So, to explain, if one person is OFF 2 days in a row, then add 1 to the grand total. If 2 people are OFF 2 days in a row, then you'd add 2 to the total. However, if a person is OFF one day (as in row 2) and not OFF again the following day, then don't add 1 to the total. By the way, this is a schedule for the whole year, and there is a total for each week

Thanks.

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-06 : 04:22:25

are jan1 jan2 jan3 and jan4 dates?

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

flipfyre
Starting Member

26 Posts

Posted - 2009-11-09 : 11:24:35

Asgast, yes, they are all dates. Actually, they are column headers representing the dates of actual days of the year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 11:29:27

are you telling about format of data displayed in some report? or is table itself contain data in posted format until dec31?

flipfyre
Starting Member

26 Posts

Posted - 2009-11-09 : 12:28:13

visakh16 - not sure what you are trying to say exactly. The data is in a SQL table just as it is listed above. Data is actually in the SQL table for the entire year (the employee's yearly schedule). I am also outputting data into an HTML table as a front end for users. Is that what you're referring to?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 12:32:16

do you mean you've data stored like above with jan1...dec31 values stored in columns (that will come to 365 columns itself)

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-10 : 06:07:06

:)
well you can have 1024 column :) its enough for two years.

flipfyre
if this is the case ou should consider redesigning our table.

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

flipfyre
Starting Member

26 Posts

Posted - 2009-11-10 : 09:24:04

The column names are jan1 - dec31. Not ideal, but I'll have to deal with it, as data is being passed from Oracle to SQL Server and it's built out for the entire year this way. Not sure how I could redesign my table (open to suggestions as I'm not great at table design - but have studied normalization and believe my table to be close to that), but would also like a solution to my current problem. Thanks guys.

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-10 : 10:25:53

I believe your table fails 1st normal form,
where is the primary key? your column grouping shares the same domain and the same meaning
but I never studied normalization, more then reading a couple of articles on it :) and skipped classes on database design at the university, I was planning on becoming a c/c++ programmer. Silly me :)

I'd go for the following design

Location nvarchar(*)
FirstName nvarchar(*)
LastName nvarchar(*)
date datetime
status int

date can be split into month and day int values, but i would go for datetime value

in this design by using ranking function i would easily calculate how many time a person was away for two days in a row

with your design i have the following idea:
run a cursor on columns inside a cursor i would need a variable to indicate to which day of the week my current cycle run corresponds
and I would insert my weekly results into a temp table

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-10 : 10:28:48

one silly question :)
Do you have feb28 and feb29 in your table?

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-10 : 13:01:11

its just a matter of keeping a calendar table and using it for comparing with attendance.

flipfyre
Starting Member

26 Posts

Posted - 2009-11-10 : 15:28:33

Asgast, there are more columns in my table than listed here, I just put the meat and potatoes for reference... of course I have a primary key. There is a feb29, to account for leap year - all of that was already thought out. Can you possibly provide example code on your "run a cursor inside a cursor" idea?

Visakh16 - can you elaborate on the "keeping a calendar table and compare with attendance" idea?

Thanks guys.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-11-10 : 15:53:50

What happens if they are off 3, 4 or 5 days in a row?

Looks like you will end up with something like this, if you want to keep that table design:

select
	case when JAN1 = 'OFF' and JAN2 = 'OFF' then 1 else 0 end +
	case when JAN2 = 'OFF' and JAN3 = 'OFF' then 1 else 0 end +
	case when JAN3 = 'OFF' and JAN4 = 'OFF' then 1 else 0 end +
	... and so on ...
	case when DEC30 = 'OFF' and dec31 = 'OFF' then 1 else 0 end
from
	MyTable

Have fun. I will let you figure out how to handle leap year.

CODO ERGO SUM

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-11 : 03:55:03

do you do it on a weekly bases?
at the end of the week you process the data for the previous week?

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

flipfyre
Starting Member

26 Posts

Posted - 2009-11-11 : 09:54:43

Michael, that does seem like it will work; although a super long process, but the logic is sound. I wish there were a loop of some sort, but I suppose that will work.

Asgast, well, the entire schedule for the whole year is pre-built with vacations and stuff already, as the employees worked that out before the year started. But, the times they are OFF for other reasons is what I'm calculating, and there has to be a weekly total.

I wonder... Can I take Michael's select statement for a week at a time and give it an alias name? For example:

select
(case when JAN1 = 'OFF' and JAN2 = 'OFF' then 1 else 0 end +
case when JAN2 = 'OFF' and JAN3 = 'OFF' then 1 else 0 end +
case when JAN3 = 'OFF' and JAN4 = 'OFF' then 1 else 0 end +
case when JAN4 = 'OFF' and JAN5 = 'OFF' then 1 else 0 end) as Week1Total
from
MyTable

What do you guys think? Should I go with this code, or do you have any other suggestions. Thanks again everyone.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-11-11 : 11:23:26

I think your queries would be much easier to write if you had one row for each location, person, and date. The query that you are forced to write for the current table design is a good illustration of the disadvantages of de-normalized design. Since you are importing the data from Oracle, you should be able to transform it as it is loaded, instead of doing it in every query. Think about the query you would have to write to get a simple list of days off for a single person using the current design.

Table ProjectWorkSchedule
ID
LocationID
PersonID
Date
ScheduleStatus

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources