select / create a default row when none exists? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

select / create a default row when none exists?

Author

Topic

henderson.marty
Starting Member

6 Posts

Posted - 2008-08-06 : 10:32:54

Hi folks,

I'm having a problem with some sql.
I have a table setup in this structure

create table working_days
(
user_id varchar(255),
day_of_week int,
hours int
)
go

insert into working_days values ('default', 1, 7)
insert into working_days values ('default', 2, 7)
insert into working_days values ('default', 3, 7)
insert into working_days values ('default', 4, 7)
insert into working_days values ('default', 5, 7)
insert into working_days values ('default', 6, 0)
insert into working_days values ('default', 7, 0)
insert into working_days values ('marty', 1, 4)
insert into working_days values ('marty', 2, 4)
insert into working_days values ('marty', 3, 4)

where the 1-7 represent days of the week and the last value is working hours for that day.

What I need is when I select a user from this table to return the working hours for each day of the week for them.
If a user has no entry in this table, then they would get the default values.
If they a user has 3 entries then for those three days they would get their own values and for the rest they would get the values associated with the default. So for the above if I selected 'marty' I would want to get
marty 1 4
marty 2 4
marty 3 4
marty 4 7
marty 5 7
marty 6 0
marty 7 0

i've been tinkering with unions, left outer joins and derived tables to the table itself, but can't quite get it right and it feels like there's something simple i'm missing but I can't quite get it!

Hope you can help,
marty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 10:46:47

[code]SELECT u.user_id, w.day_of_week, hours = ISNULL(d.hours, 0)
FROM ( -- REPLACE this WITH your user TABLE IF there IS one
SELECT user_id
FROM working_days
GROUP BY user_id
) u
CROSS JOIN
(
SELECT day_of_week = 1 UNION ALL
SELECT day_of_week = 2 UNION ALL
SELECT day_of_week = 3 UNION ALL
SELECT day_of_week = 4 UNION ALL
SELECT day_of_week = 5 UNION ALL
SELECT day_of_week = 6 UNION ALL
SELECT day_of_week = 7
) w
left JOIN working_days d ON u.user_id = d.user_id
AND w.day_of_week = d.day_of_week
[/code]

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

henderson.marty
Starting Member

6 Posts

Posted - 2008-08-06 : 11:12:12

almost there...
the output i would expect would be
for days 4, 5, 6, 7 for user 'marty' to be the hours for the default user, not 0 if you know what i mean?

also, i'd like the default user to not show up too.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 11:58:45

Yes I know. But you did not provide the default hours for the user and nor any table definition. Just change the isnull() part to your required default value or reference from other source if there is any.

"also, i'd like the default user to not show up too."
Just add the WHERE user_id = 'marty'

or user_id <> 'default'

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-06 : 12:22:50

I made some tweaks to Khtan's query to do what I think you are trying to acomplish:

DECLARE @working_days table 
(
user_id varchar(255),
day_of_week int,
hours int
)


insert into @working_days values ('default', 1, 7)
insert into @working_days values ('default', 2, 7)
insert into @working_days values ('default', 3, 7)
insert into @working_days values ('default', 4, 7)
insert into @working_days values ('default', 5, 7)
insert into @working_days values ('default', 6, 0)
insert into @working_days values ('default', 7, 0)
insert into @working_days values ('marty', 1, 4)
insert into @working_days values ('marty', 2, 4)
insert into @working_days values ('marty', 3, 4)


SELECT
	U.user_id,
	D.day_of_week,
	COALESCE(W.hours, D.hours) -- EDIT: forgot the COALESCE
FROM
	(
		SELECT DISTINCT user_id
		FROM @working_days
		WHERE user_id <> 'default'
	) AS U
CROSS JOIN
	(
		SELECT *
		FROM @working_days
		WHERE user_id = 'default'
	) AS D
LEFT OUTER JOIN
	@working_days AS W
	ON U.user_id = W.user_id
	AND D.day_of_week = W.day_of_week

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 21:54:14

Oh . . ok. So that's what OP wants

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

henderson.marty
Starting Member

6 Posts

Posted - 2008-08-07 : 05:08:42

guys, thanks!
you are amazing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources