Help with a query

Hello,

I need some assistance with a query that i am trying to build.

A table contains records which consitute an employee's shift.

There are 7 'Default' records for each employee, along with any number of additional records which will override the default record if the date in this record equals the date the form is displaying.

For instance: today is Friday 7/28 and the employee name is Joe; if there are no additional records out in the table for Joe for 7/28, then we will grab his default record. If there is a record for Joe for 7/28, then we will use this record to get his shift start and end times.

Here are the fields in the two records and what they may contain:

Name: Joe, XDate:"7/28/2006", XDay:Fri, StartTime:xxxx, EndTime:xxxx

Name: Joe, XDate:"Default", XDay:Fri, StartTime:xxxxx, EndTime:xxxxx

and this situation can occur for serveral employees.

So again, I need to grab the record with a XDate that matches todays date, if that does not exist then I need to grab the record with the XDate that has the word "Default" in it.

By the way, XDate is a text field and not a Date datatype field.

*****oh and one other thing i forgot to mention. If the Date of 7/28 is not found, then i would use the day value of that date "Fri" to grab the default start and end times.

Thanks for your help!

StrangeMike:

oh and one other thing i forgot to mention. If the Date of 7/28 is not found, then i would use the day value of that date "Fri" to grab the default start and end times.

Sorry I'm not very clear with this point: how will you use the XDay date? Previously you said 'Default will be used if no match XDate is found, now how will you use the XDay value? Anyways despite the use of XDay data, you may try the code below:

declare @.d smalldatetime
set @.d='2006-07-29'

select * from test
where name='Joe'
and XDate= CASE WHEN (SELECT count(*) FROM test
WHERE XDate=CONVERT(varchar(12),DATEPART(mm,@.d))+'/'+
CONVERT(varchar(12),DATEPART(dd,@.d))+'/'+
CONVERT(varchar(12),DATEPART(yy,@.d)))>0
THEN CONVERT(varchar(12),DATEPART(mm,@.d))+'/'+
CONVERT(varchar(12),DATEPART(dd,@.d))+'/'+
CONVERT(varchar(12),DATEPART(yy,@.d))
ELSE 'Default'
END

|||

Hi Lori_Jay,

The xDay is the clue to tell me which 'Default' record to take Mon-Sun.

xDate can contain the word "Default" or an actual Date "7/28/2006".

There are 7 'Default' records. So if there is no record with an actual date..like "7/28/2006",

then I need to go after the default record for the day I am currently displaying, and that is how xDay come into play. If today is a Friday, then I grab the default record where xDay = "Fri".

I know it's a bit confusing.

Thank you for your reply.

|||

You'll need to change "MyTable" to your table name, and set @.date to the textbox's .Text property.

SELECT t1.Name,ISNULL(t1.StartTime,(SELECT StartTime FROM MyTable t2 WHERE t2.Name=e.Name and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM MyTable t2 WHERE t2.Name=e.Name and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime

(SELECT DISTINCT Name,@.date AS XDate,CASE DATEPART(dw,CAST(@.date as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM MyTable) e

LEFT JOIN MyTable t1 ON (t1.Name=e.Name and t1.XDate=e.XDate)

|||

Wow nice query. I'm a little confused by the "set @.date to the textbox's .Text property." though.

I am passing two parameters to this stored query that populates a datagrid, those being..Date and Day. Here is the current query which returns the record for the date, but it also includes the default record for the same day, I removed some unrealted fields for simplicity: Using your query how can I modify this one? Thanks

SELECT tblPhotographerShifts.RecId, tblPhotographerShifts.Photographer, tblPhotographerShifts.StartTime, tblPhotographerShift.EndTime, tblPhotographerShifts.xDate, tblPhotographerShifts.Day
FROM tblPhotographerShifts
WHERE (((tblPhotographerShifts.Photographer)<>"TBA") AND ((tblPhotographerShifts.xDate)=[@.ByDate])) OR (((tblPhotographerShifts.xDate)="Default") AND ((tblPhotographerShifts.Day)=[@.Day]))
ORDER BY tblPhotographerShifts.Photographer, tblPhotographerShifts.xDate;

|||

SELECT t1.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime

(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e

LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)

WHERE t1.Photographer<>'TBA'

ORDER BY t1.Photographer

I don't use @.Day, since I calculate it in the query from @.ByDate already.

|||

Motley thanks for your help with this, I've gotta say I don't know how you thought of something like this. I basically just tried cutting and pasting your query into the Sql View of the query. When I tried to go to design I got this error:

"The Select includes a reserve word or argument name that is misspelled or missing, or the puncuation is incorrect"

I gave it a shot looking at it, but to be honest there are parts of this query I have never even seen before. Do you know what may be incorrect?

Thank you.

|||

Heh, I forgot the word FROM, try this:

SELECT

t1.Photographer,ISNULL(t1.StartTime,(SELECT StartTimeFROM tblPhotographerShifts t2WHERE t2.Photographer=e.Photographerand t2.XDate='Default'AND t2.XDay=e.Dow))AS StartTime,ISNULL(t1.EndTime,(SELECT EndTimeFROM tblPhotographerShifts t2WHERE t2.Photographer=e.Photographerand t2.XDate='Default'AND t2.XDay=e.Dow))AS EndTime

FROM

(

SELECTDISTINCT Photographer,@.ByDateAS XDate,CASEDATEPART(dw,CAST(@.ByDateasdatetime))WHEN 1THEN'Sun'WHEN 2THEN'Mon'WHEN 3THEN'Tue'WHEN 4THEN'Wed'WHEN 5THEN'Thu'WHEN 6THEN'Fri'WHEN 7THEN'Sat'ENDAS DowFROM tblPhotographerShifts) e

LEFT

JOIN tblPhotographerShifts t1ON(t1.Photographer=e.Photographerand t1.XDate=e.XDate)

WHERE

t1.Photographer<>'TBA'

ORDER

BY t1.Photographer|||

Getting closer... Here is a new error:

"Wrong number of arguments used with function in query expression ISNULL(t1.StartTime, (Select StartTime FROM tblPhotogpraherShifts t2 where t2.Photographer=e.Phtographer and t2.xDate='Default' and t2.Xday=e.dow))"

|||

You have a typo somewhere, although I did fix one other bug, here is my test script:

USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--DROP TABLE tblPhotographerShifts
GO
CREATE TABLE [dbo].[tblPhotographerShifts](
[Photographer] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XDate] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XDay] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartTime] varchar(10) NULL,
[EndTime] varchar(10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('Me','8/1/2006',NULL,'9:30','10:30')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('Me','Default','Wed','9:00','10:00')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','Default','Tue','10:00','11:00')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','Default','Wed','10:30','11:30')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','8/1/2006',NULL,'11:30','12:30')

DECLARE @.ByDate varchar(20)

SET @.ByDate='8/1/2006'
SELECT e.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime ,e.dow
FROM
(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e
LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)
WHERE e.Photographer<>'TBA'
ORDER BY t1.Photographer

SET @.ByDate='8/2/2006'
SELECT e.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime ,e.dow
FROM
(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e
LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)
WHERE e.Photographer<>'TBA'
ORDER BY t1.Photographer

Results:

Me 9:30 10:30 Tue
You 11:30 12:30 Tue

Me 9:00 10:00 Wed
You 10:30 11:30 Wed

|||

It could be an MS Access restriction. I am just cutting and pasting into an SQL View and it is giving me the error. I'll keep trying some different things to see if I can get Access to accept your query.

Thanks

|||Motley, thanks for your assistance on this, between you and another site I have a query that is working for me.|||Heh, it would have helped if you mentioned that you were using Access. Or posted it in the AccessDataSource forums.