Joining 2 views 1

[Schnadi]

This might sound a bit of an odd question to all of you experts but i have the requirement to join 2 views.

View 1:

StaffID StartDate
1 12/12/2003
2 03/02/2002
2 20/04/2004
2 etc
3

View 2:

StaffID EndDate
1 14/12/2003
2 15/08/2003
2 22/02/2005
2 etc
3

I would like to merge these views into another view or table and join by staffid but this is not unique and would cause errors. Any ideas??

Thanks,
Paul

 

[donutman]

What sort of ideas are you looking for? {Your choice of sarcastic remarks here.}
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[miq]

Thats easy

create view  Shift_Timings

As

select a.staffid, a.startdate, b.enddate from View1 a
join View2 b
on a.staffid = b.staffid

select * from shift_timings

things to remember views can call other views, column names should be specified / no duplicate column naes are allowed.

B.R,
miq

 

[donutman]

How do you know which EndDate goes with which StartDate?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[miq]

Man i miss it
"I would like to merge these views into another view or table and join by staffid but this is not unique and would cause errors. Any ideas??"

Ok, so this the problem. But how come staffid has duplicate values?? Ok, if you don't want to change this behaviour then use a table with identity column, staffid, startdate, enddate. You still have to change DB Schema for this. Or you can use temp table but i don't recommand it.

B.R,
miq

 

[johnherman]

check out this site for ideas -

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[Schnadi]

Thanks guys...

You've given me food for thought!! I am creating a recruitment database and need to create some kind of allocation function. I would like something like this:

StaffID StartDate EndDate
1 12/12/2003 14/12/2003
2 03/02/2002 15/08/2003
2 20/04/2004 22/02/2005
3 etc

As you can see, a member of staff can be allocated to one or more jobs.

Thanks,
Paul

 

[donutman]

You need to add a JobID to each table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Schnadi]

Thanks guys...

I cracked it - used a stored procedure and its working great!! So far so good!!

Paul

 

[donutman]

Darn, I should have thought of that.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Schnadi]

No worries, you brought to light a good few issues and got me looking at cursors! Oh how exciting are cursors!! Ha ha.

Thnaks again