Need Help Creating a View - 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

General SQL Server Forums

New to SQL Server Programming

Need Help Creating a View

Author

Topic

DaNuGai
Starting Member

10 Posts

Posted - 2011-10-23 : 20:47:38

I have a Table called UserAbsences with a couple of columns, UserID & DayID. Both of these columns are Integer Data Type and also a Foreign Key column. I would like to be able to create a view that provides a list of UserIDs for consecutive 10 integers from DayID. In other words, I'd like to create a view that gives me a list of users who are absent 10 consecutive days or more.

Please Help! Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-23 : 21:03:10

DayID doesn't make sense to me. It would need to be a date/time column as far as I can tell, unless it's some value from a starting point such as January 1st of some year. But it doesn't sound like it is. Could you some us a data example and EXACTLY what you want to see? I need to picture DayID better, and a data example might help. I think it might make me even more positive we need a different data type though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DaNuGai
Starting Member

10 Posts

Posted - 2011-10-23 : 21:41:00

First of all, thank you Tara for a quick reply. I appreciate you helping me on this.

I have a table where DayID is the primary key and WorkDay is the SmallDateTime column. As you may have guessed, this is a reference table with a list of possible work days. The reason for this is because dates listed in WorkDay column doesn't represent a "traditional" calendar.

I hope this helps ...


CREATE TABLE refWorkDay(
DayID Int NOT NULL,
WorkDay SmallDateTime NOT NULL,
PRIMARY KEY (DayID)
);

CREATE TABLE UserAbsences(
UserID Int NOT NULL,
DayID Int NOT NULL,
PRIMARY KEY(UserID, DayID)
);

INSERT INTO refWorkDAY (DayID, WorkDay) VALUES (1, '2/1/2011');
INSERT INTO refWorkDAY (DayID, WorkDay) VALUES (2, '2/5/2011');
INSERT INTO refWorkDAY (DayID, WorkDay) VALUES (3, '2/9/2011');
INSERT INTO refWorkDAY (DayID, WorkDay) VALUES (4, '2/16/2011');
INSERT INTO refWorkDAY (DayID, WorkDay) VALUES (5, '2/22/2011');
INSERT INTO refWorkDAY (DayID, WorkDay) VALUES (6, '3/3/2011');
INSERT INTO refWorkDAY (DayID, WorkDay) VALUES (7, '3/14/2011');
INSERT INTO refWorkDAY (DayID, WorkDay) VALUES (8, '3/21/2011');

INSERT INTO UserAbsences (UserID, DayID) VALUES(1, 1);
INSERT INTO UserAbsences (UserID, DayID) VALUES(1, 2);
INSERT INTO UserAbsences (UserID, DayID) VALUES(1, 3);
INSERT INTO UserAbsences (UserID, DayID) VALUES(1, 8);
INSERT INTO UserAbsences (UserID, DayID) VALUES(2, 5);
INSERT INTO UserAbsences (UserID, DayID) VALUES(2, 6);
INSERT INTO UserAbsences (UserID, DayID) VALUES(2, 7);
INSERT INTO UserAbsences (UserID, DayID) VALUES(2, 8);

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-23 : 21:44:36

"absent 10 consecutive days" this is refer to actual calendar day or WorkDay or DayID ?

And what is the expected result for the sample data that you have posted ?

KH
[spoiler]Time is always against us[/spoiler]

DaNuGai
Starting Member

10 Posts

Posted - 2011-10-23 : 21:45:33

Refers to DayID and I'd like to get a list of users who were absent 10 consecutive days or more preferably with a DayID when consecutive absences started. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-23 : 21:52:32

you have a consecutive running DayID in your refWorkDay table. The refWorkDay table is for all users right ? How about for different user ? It will also follow the same refWorkDay table ?

can you post the expected result for the sample data that you have posted ?

KH
[spoiler]Time is always against us[/spoiler]

DaNuGai
Starting Member

10 Posts

Posted - 2011-10-23 : 21:59:56

It will be for all users.

As far as sample result, suppose I'm looking for UserIDs with 4 consecutive absences from the sample code I provided above, then the result should return

UserID, DayID
2, 5

Suppose I'm looking for UserIDs with 3 consecutive absences, then the result should return

UserID, DayID
1, 1
2, 5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-23 : 23:57:51

try


select	u.UserID, w.DayID, w.WorkDay
from	Users u
	cross join refWorkDay w
where	exists
	(
		select	x.UserID
		from	UserAbsences x
		where	x.UserID	= u.UserID
		and	x.DayID		between w.DayID and w.DayID + 3 - 1
		group by x.UserID
		having count(*) >= 3
	)

KH
[spoiler]Time is always against us[/spoiler]

DaNuGai
Starting Member

10 Posts

Posted - 2011-10-24 : 00:43:25

Thank you so much...I appreciate all your help.

I'll try your code first thing tomorrow morning and let you know how it goes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources