Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 22nd, 2004, 12:50 PM
Registered User
 
Join Date: Dec 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Variables in views

Hi,

I am quite new to SQL. I have two tables TBL_TECHNICIANS which contains a list of technicians, and TBL_SCHEDULE which contains a list of jobs to do, which technician is assigned to the job, and the date the job is to be done.

I am trying to get a view to show ALL of the technicians is one column, and the job that the technician is going to do in another column for a specific date which is variable. If there is not a job to be done for a technician on the specified date i would like the job column to be <NULL>

Can anyone offer any assistance? Any suggestions would be greatly appreciated!!

Thanks
Richard.

 
Old December 23rd, 2004, 12:31 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Please post your table structure. You cannot pass a variable to a view. using a store procedure will be more useful




Jaime E. Maccou
 
Old December 23rd, 2004, 05:24 AM
Registered User
 
Join Date: Dec 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, thanks for that suggestion - could you show me how i might be able to create a stored procedure to do that??

The table structure is as follows :

TBL_TECHNICIANS
nTechnicianPK int
sName varchar
bArchived bit

TBL_SCHEDULE
nSchedulePK int
dDate datetime
sDescription varchar
nTechnicianFK int

Thanks again for your help and if you can show me how to do a stored procedure to do this it would be great!

Thanks very much,

Richard.

 
Old December 23rd, 2004, 10:19 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

start with something like this

Code:
create proc tech_schedule (@dates datetime = Null)
as
If @dates Is Null 
    Set @dates = getdate()-- your value

SELECT     dbo.tbl_technicians.nTechnician, dbo.tbl_technicians.sName, dbo.TBL_SCHEDULE.nSchedule, dbo.TBL_SCHEDULE.dDate, 
                      dbo.TBL_SCHEDULE.sDescription, dbo.tbl_technicians.bArchived
FROM         dbo.TBL_SCHEDULE INNER JOIN
                      dbo.tbl_technicians ON dbo.TBL_SCHEDULE.nTechnician = dbo.tbl_technicians.nTechnician
where       ddate=@dates
exec tech_schedule '2004-12-23'

Jaime E. Maccou
 
Old December 23rd, 2004, 11:25 AM
Registered User
 
Join Date: Dec 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That's a massive help - thanks.

Do you know how i would be able to get it to display all of the technecians even if they don't have a job on the specified day?

Thanks,
Richard.

 
Old December 23rd, 2004, 11:33 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Use the select statement without the variable

Code:
SELECT     dbo.tbl_technicians.nTechnician, dbo.tbl_technicians.sName, dbo.TBL_SCHEDULE.nSchedule, dbo.TBL_SCHEDULE.dDate, 
                      dbo.TBL_SCHEDULE.sDescription, dbo.tbl_technicians.bArchived
FROM         dbo.TBL_SCHEDULE INNER JOIN
                      dbo.tbl_technicians ON dbo.TBL_SCHEDULE.nTechnician = dbo.tbl_technicians.nTechnician


Jaime E. Maccou
 
Old December 23rd, 2004, 11:55 AM
Registered User
 
Join Date: Dec 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, is there any way that you know of that allows me to filter by date, and display all of the technicians?

Thanks,
Richard.

 
Old December 23rd, 2004, 11:59 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

when you say filter by date, do you mean order by or group by? give an example

Jaime E. Maccou
 
Old December 23rd, 2004, 12:11 PM
Registered User
 
Join Date: Dec 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I would like to run the procedure and specify a date ie 18/12/2004.

I would like the results to show in column 1 a list of all of the technicians names, and in column 2 the job that technician has to do on that specific day. If there is a technician with no job to do on that specific day i still would like their name to be listed in column 1.

Eg. the date i would specify would be 18/12/04 the result would look like :

sName sDescription dDate
Richard Pipe freezing on site 18/12/04
James <NULL>
Oliver Joint work in workshop 18/12/04

However the date needs to be variable.

I hope this is clearer.

Thank you for your help.
Richard.

 
Old December 23rd, 2004, 12:29 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Just change the select statement to how you want the columns to appear
Code:
SELECT    dbo.tbl_technicians.sName, dbo.TBL_SCHEDULE.sDescription, dbo.TBL_SCHEDULE.dDate
FROM         dbo.TBL_SCHEDULE INNER JOIN
dbo.tbl_technicians ON dbo.TBL_SCHEDULE.nTechnician = dbo.tbl_technicians.nTechnician
Jaime E. Maccou





Similar Threads
Thread Thread Starter Forum Replies Last Post
Using variables in Views Jimbocephus SQL Server 2005 8 March 14th, 2008 02:30 PM
Views prashar SQL Language 1 December 13th, 2005 02:10 AM
converting Access 2000 views to Sql views matta Classic ASP Professional 1 January 26th, 2005 03:37 PM
Views or not ?? stagedancer SQL Server 2000 1 November 24th, 2004 03:08 PM
Views SAM GORDON SQL Server 2000 5 July 19th, 2003 08:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.