query parameter based on columnna,e...

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

query parameter based on columnna,e...

Author

Topic

celeron
Starting Member

11 Posts

Posted - 2008-06-24 : 04:10:53

good day dudes! here's my 2nd questione for ye'all...

my databases:

workshift
- shiftid
- shiftname
- timestart
- timeend
- flexwindow
- status

employeeplottedsched
- employeeid
- month
- year
- day1shiftid
- day2shiftid
- day3shiftid
...
...
...
- day29shiftid
- day30shiftid
- day31shiftid

how can I query the plotted shift sched of employees for a particular day? like I would like to know what shift the employees were scheduled
from day1 to 15 of May 2008? the days that I would like to query would be dynamic so it can be day1 to 15 or day3 to day5 or just day20...

any of you guys nice enough to enlighten me?

U + U + D + D + L + R + L + R + Sel + Start...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 04:13:03

Yes. Normalize your table.
http://www.datamodel.org/NormalizationRules.html

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 04:14:16

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 04:16:57

normalize your employeeplottedsched table into


employeeplottedsched
- employeeid
- month
- year
- day
- shiftid

then it will be extremely easy to do what you want

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 04:24:15

And at last, how do you plan to tell the stored procedure which "columns" you want? Ie dates.

E 12°55'05.25"
N 56°04'39.16"

celeron
Starting Member

11 Posts

Posted - 2008-06-24 : 04:24:43

dudes, yeah i would really love to have them tables normalized but the problem is i don't have the right to do it since the table has tons of data in it already...

U + U + D + D + L + R + L + R + Sel + Start...

celeron
Starting Member

11 Posts

Posted - 2008-06-24 : 04:36:11

quote:
Originally posted by Peso

And at last, how do you plan to tell the stored procedure which "columns" you want? Ie dates.

E 12°55'05.25"
N 56°04'39.16"

im using SQL Server 2005, thats what i would also want to know... if that can be done...

U + U + D + D + L + R + L + R + Sel + Start...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 04:39:01

create a view out of it


select employeeid, year, month, day = 1, shiftid = day1shiftid
from   employeeplottedsched
union all
select employeeid, year, month, day = 2, shiftid = day2shiftid
from   employeeplottedsched
. . .

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

celeron
Starting Member

11 Posts

Posted - 2008-06-24 : 04:49:58

ok sir, i'll try that out. i'll post back the result once im done... thanks once again dudes!

U + U + D + D + L + R + L + R + Sel + Start...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 05:10:27

Here is a more efficient way to create thw view in SQL Server 2005

SELECT	u.EmployeeID,
	u.[Month],
	u.[Year],
	u.ShiftID,
	CONVERT(INT, REPLACE(REPLACE(u.theDay, 'day', ''), 'shiftid', '')) AS [Day]
FROM	EmployeePlottedSched AS eps
UNPIVOT	(
		ShiftID
		FOR theDay IN	(eps.day1shiftid, eps.day2shiftid, eps.day3shiftid,
				eps.day4shiftid, eps.day5shiftid, eps.day6shiftid,
				eps.day7shiftid, eps.day8shiftid, eps.day9shiftid,
				eps.day10shiftid, eps.day11shiftid, eps.day12shiftid,
				eps.day13shiftid, eps.day14shiftid, eps.day15shiftid,
				eps.day16shiftid, eps.day17shiftid, eps.day18shiftid,
				eps.day19shiftid, eps.day20shiftid, eps.day21shiftid,
				eps.day22shiftid, eps.day23shiftid, eps.day24shiftid,
				eps.day25shiftid, eps.day26shiftid, eps.day27shiftid,
				eps.day28shiftid, eps.day29shiftid, eps.day30shiftid,
				eps.day31shiftid)
	) AS u

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources