Variables in views

richarda · December 22nd, 2004, 12:50 PM

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.

jemacc · December 23rd, 2004, 12:31 AM

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

Jaime E. Maccou

richarda · December 23rd, 2004, 05:24 AM

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.

jemacc · December 23rd, 2004, 10:19 AM

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

richarda · December 23rd, 2004, 11:25 AM

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.

jemacc · December 23rd, 2004, 11:33 AM

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

richarda · December 23rd, 2004, 11:55 AM

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

Thanks,
Richard.

jemacc · December 23rd, 2004, 11:59 AM

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

Jaime E. Maccou

richarda · December 23rd, 2004, 12:11 PM

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.

jemacc · December 23rd, 2004, 12:29 PM

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