February 29, 2008 at 8:07 am
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate
,OutEndDate,OutStartTime,OutEndTime,OutID
FROM vwOutOfOffice
LEFT OUTER JOIN vwStaffList ON OutUser=StaffID
WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example
Current date=29/02/2008
Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear
27/02/2008--28/02/2008--09:00------17:00-----No (because of date)
28/02/2008--03/03/2008--08:00------17:00-----Yes
28/02/2008--29/02/2008--17:00------17:00-----Yes
29/02/2008--29/02/2008--12:00------14:00-----Yes
29/02/2008--29/02/2008--14:30------17:00-----No (because of time)
02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
February 29, 2008 at 8:13 am
First off, it looks like you need to swap the Staff List and out of office views around.
select ... FROM StafList LEFT JOIN outOfOffice ...
Secondly, can you post the DDL for the tables/views?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 29, 2008 at 8:19 am
Martin
Do you have the option to change the view so that it gives the date and time in one value? If you can do that then a simple BETWEEN operator will do the job for you (although purists will point out that BETWEEN is inclusive of the start AND end points of the range).
John
February 29, 2008 at 8:22 am
I can do, not help much they are just simple SELECT statements, they are only in views for security reasons.
tblStaffList
CREATE VIEW [dbo].[vwStaffList]
AS
SELECT StaffID, StaffName, StaffInitials, StaffEmail, StaffLineManager, StaffWhichLM, StaffHolidayTotal, StaffCompany, StaffExt, StaffSuspend,
StaffRoomSpecial, StaffPassword, StaffDept, StaffSecurity, StaffHide, StaffFlexiDays
FROM dbo.tblStaffList
CREATE TABLE [dbo].[tblStaffList](
[StaffID] [int] IDENTITY(1,1) NOT NULL,
[StaffName] [varchar](40) NOT NULL,
[StaffInitials] [varchar](4) NOT NULL,
[StaffJobTitle] [varchar](60) NULL,
[StaffExt] [varchar](6) NOT NULL,
[StaffTelWork] [varchar](20) NOT NULL,
[StaffTelHome] [varchar](20) NULL,
[StaffMobile] [varchar](20) NULL,
[StaffFax] [varchar](20) NULL,
[StaffEmail] [varchar](120) NULL,
[StaffCompany] [int] NOT NULL,
[StaffDept] [int] NOT NULL,
[StaffLocation] [int] NOT NULL,
[StaffDOB] [datetime] NULL,
[StaffDateStarted] [datetime] NULL,
[StaffPhoto] [varchar](120) NULL,
[StaffAddress] [varchar](500) NULL,
[StaffPostcode] [varchar](9) NULL,
[StaffLineManager] [bit] NOT NULL,
[StaffRoomSpecial] [bit] NOT NULL,
[StaffSuspend] [bit] NOT NULL,
[StaffWhichLM] [int] NOT NULL,
[StaffDateAdded] [datetime] NOT NULL CONSTRAINT [DF_tblStaffList_StaffDateAdded] DEFAULT (getdate()),
[StaffPassword] [varchar](10) NOT NULL,
[StaffBirthday] [bit] NULL,
[StaffEmergency] [varchar](500) NULL,
[StaffUpdates] [bit] NOT NULL,
[StaffManagement] [bit] NULL,
[StaffHolidayTotal] [numeric](19, 2) NULL CONSTRAINT [DF_tblStaffList_StaffHolidayTotal] DEFAULT (25),
[StaffHide] [bit] NULL,
[StaffRemember] [bit] NULL,
[StaffHours] [varchar](100) NULL,
[StaffSecurity] [int] NULL,
[StaffBlackBerry] [varchar](20) NULL,
[StaffFlexiDays] [float] NULL,
CONSTRAINT [PK_tblStaffList] PRIMARY KEY CLUSTERED
(
[StaffID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Out of Office
CREATE VIEW [dbo].[vwOutOfOffice]
AS
SELECT dbo.tblOutOfOffice.*
FROM dbo.tblOutOfOffice
CREATE TABLE [dbo].[tblOutOfOffice](
[OutID] [int] IDENTITY(1,1) NOT NULL,
[OutAdded] [datetime] NOT NULL CONSTRAINT [DF_tblOutOfOffice_OutAdded] DEFAULT (getdate()),
[OutUser] [int] NOT NULL,
[OutStartDate] [datetime] NOT NULL,
[OutEndDate] [datetime] NOT NULL,
[OutStartTime] [varchar](15) NULL,
[OutEndTime] [varchar](15) NULL,
[OutReason] [varchar](500) NOT NULL,
[OutContact] [varchar](200) NULL,
[OutComplete] [bit] NOT NULL CONSTRAINT [DF_tblOutOfOffice_OutComplete] DEFAULT (0),
[OutCompany] [int] NULL,
[OutSection] [char](2) NULL CONSTRAINT [DF_tblOutOfOffice_OutSection] DEFAULT ('fp'),
[OutInternal] [bit] NULL,
[OutRecur] [bit] NULL,
[OutReminder] [bit] NULL,
[OutReminderTime] [int] NULL CONSTRAINT [DF_tblOutOfOffice_OutReminderTime] DEFAULT (0.00)
) ON [PRIMARY]
February 29, 2008 at 8:26 am
Do you have the option to change the view so that it gives
Yeah I have the option to do this, not something I've ever to do before.
February 29, 2008 at 8:26 am
Martin
Now I'm confused. If you're storing OutStartDate and OutEndDate as datetime, why do you need OutStartTime and OutEndTime?
John
February 29, 2008 at 8:30 am
John...
Its an old system I inherited and for one reason or another cannot be changed much, but working on that. It was so a user can enter their own dates and times on a web form but now they want the option so people can view others data based on the current date and time.
February 29, 2008 at 8:34 am
Like John said ...
SELECT DISTINCT
REPLACE(StaffName,'''''',' ') AS StaffName
,OutReason
,OutStartDate
,OutEndDate
,OutStartTime
,OutEndTime
,OutID
FROM vwStaffList
INNER JOIN vwOutOfOffice
ON OutUser = StaffID
WHERE
OutComplete = 0
AND GETDATE() BETWEEN OutStartDate + OutStartTime AND OutEndDate + OutEndTime
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 29, 2008 at 8:50 am
Thanks so far, given me some ideas, though that last one didn't work only brought out records that have the startdate as today, but I need to get records whose startdate is older than todays date but the end date is >= todays date, if that makes sense
Start date---End Date----Start Time--End Time--Should Appear
27/02/2008--28/02/2008--09:00------17:00-----No (because of dates)
28/02/2008--03/03/2008--08:00------17:00-----Yes
28/02/2008--29/02/2008--17:00------17:00-----Yes
29/02/2008--29/02/2008--12:00------14:00-----Yes
29/02/2008--29/02/2008--14:30------17:00-----No (because of time)
02/03/2008--02/03/2008--08:00------10:00-----No (because of dates)
February 29, 2008 at 8:55 am
Martin
It's so much more complicated when you separate out date and time. Please will you post the results of this query:
SELECT TOP 20 OutStartDate, OutEndDate
FROM vwOutofOffice
Thanks
John
February 29, 2008 at 8:57 am
Martin (2/29/2008)
Thanks so far, given me some ideas, though that last one didn't work only brought out records that have the startdate as today, but I need to get records whose startdate is older than todays date but the end date is >= todays date, if that makes sense
That doesn't make any sense. It would return that info.
How is OutComplete set? What happens if you remove that from the WHERE clause?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 29, 2008 at 8:58 am
Thanks John
I know what you mean am going to try and merge the data, without breaking the current system
2006-05-18 00:00:00.0002006-05-22 00:00:00.000
2006-04-13 00:00:00.0002006-04-13 00:00:00.000
2006-05-19 00:00:00.0002006-05-19 00:00:00.000
2006-09-20 00:00:00.0002006-09-20 00:00:00.000
2006-04-12 00:00:00.0002006-04-13 00:00:00.000
2006-04-19 00:00:00.0002006-04-19 00:00:00.000
2006-04-18 00:00:00.0002006-04-18 00:00:00.000
2006-04-19 00:00:00.0002006-04-19 00:00:00.000
2006-05-05 00:00:00.0002006-05-05 00:00:00.000
2006-05-03 00:00:00.0002006-05-03 00:00:00.000
2006-04-19 00:00:00.0002006-04-20 00:00:00.000
2006-04-20 00:00:00.0002006-04-20 00:00:00.000
2007-09-07 00:00:00.0002007-09-07 00:00:00.000
2006-04-20 00:00:00.0002006-04-20 00:00:00.000
2006-05-03 00:00:00.0002006-05-03 00:00:00.000
2006-04-27 00:00:00.0002006-04-27 00:00:00.000
2006-05-02 00:00:00.0002006-05-02 00:00:00.000
2006-05-02 00:00:00.0002006-05-02 00:00:00.000
2006-05-25 00:00:00.0002006-05-25 00:00:00.000
2007-09-06 00:00:00.0002007-09-06 00:00:00.000
February 29, 2008 at 9:02 am
Then Jason's query should work. How about the results of this?
SELECT TOP 20 OutStartDate + OutStartTime, OutEndDate + OutStartTime
FROM vwOutofOffice
Have a look at the results and see if they correspond to the actual start and end times.
John
March 3, 2008 at 4:32 am
Thanks again for your help.
I get the following error with the SQL statement:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
I know how to sort it, but I'm going to merge the Start and End dates and times into Start and End fields, which will just be a DateTime field and see how things go from there.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply