August 22, 2010 at 4:38 pm
HI there,
I am trying to create a report which helps staff by showing the first and last jobs of their day.
Currently I am able to create a query which brings back all jobs for a particular date. Some staff have one job, some have eight - and anything in between. But I only want the first job and the last job for each staff member. I can do this using two subqueries - one selects the TOP 1 MIN of the job start time and the other query selects the TOP 1 MAX of the job finish time.
So I have this result;
Tom Smith 2010/09/01 08:00:00 2010/09/01 16:00:00
John Brown 2010/09/01 06:35:00 2010/09/01 13:50:00
This correctly gives me the start time of the first job and the end time of the last job - great.
But I also want to show the location of the first job and the location of the last job.
Subqueries only seem to be able to give me one column. The location field is contained in the same table as the start and finish time.
Before I start posting table definitions and lots of tSQL, can someone point me in the general area? If I can't work it out from that, I'll bombard you with SQL.
Thank you.
August 22, 2010 at 5:00 pm
Move the subqueries from the select clause (where you can only return one column per subquery) to the from clause (where there's no such limit)
If that doesn't help, please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2010 at 7:52 pm
Thanks for your help so far. I was avoiding posting everything in the hope that one line of advice might head me in the right direction and I might be able to figure it out for myself. Sadly, I've not been able to work it out. This is the SQL Server Newbies forum afterall.
As some background, I am using SQL Server 2008 Express with a Linked Server to Interbase. I can't change any of the Interbase tables as a 3rd party application relies on them - so I am restricted to what I can accomplish with the linked data. I use 7 different tables to get all the necessary data - some of them are quite big - hence my reluctance to post everything straight off.
Here is the script that was automatically generated when I right clicked and selected CREATE TO on a copy of the original table. In some cases irrelevant columns have been excluded.
CREATE TABLE [dbo].[EMPLOYEES](
[EMP_ID] [int] NOT NULL,
[EMP_HOME_REGION] [int] NOT NULL,
[EMP_FULL_NAME] [varchar](99) NULL,
[EMP_JOB_TITLE] [varchar](99) NULL,
[EMP_CELL_PHONE] [varchar](32) NULL,
[EMP_DRIVERS_LICENSE] [varchar](32) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHARTERS](
[CHA_ID] [int] NOT NULL,
[CHA_ORDER_TIMESTAMP] [datetime2](7) NOT NULL,
[CHA_ORDER_DATE] [date] NULL,
[CHA_BRANCH] [int] NOT NULL,
[CHA_QUOTING_COMPANY] [int] NOT NULL,
[CHA_CUSTOMER] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[SCHEDULED_CHARTER_INFO](
[SCI_ID] [int] NOT NULL,
[SCI_SCHEDULE] [int] NOT NULL,
[SCI_CHARTER] [int] NOT NULL,
[SCI_REQ_VEH_REQ_EMP] [smallint] NOT NULL,
[SCI_BUS_PARK_LOCATION] [int] NOT NULL,
[SCI_PRICE_ON_COMPLETION_ON] [smallint] NOT NULL,
[SCI_MANUAL_RPK] [numeric](17, 4) NOT NULL,
[SCI_MANUAL_RPH] [numeric](17, 4) NOT NULL,
[SCI_RUN_NUMBER] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SCHEDULE](
[SCH_ID] [int] NOT NULL,
[SCH_AST] [datetime2](7) NOT NULL,
[SCH_AFT] [datetime2](7) NOT NULL,
[SCH_EST] [datetime2](7) NOT NULL,
[SCH_ESL] [int] NOT NULL,
[SCH_EFL] [int] NOT NULL,
[SCH_EFT] [datetime2](7) NOT NULL,
[SCH_VST] [datetime2](7) NOT NULL,
[SCH_VSL] [int] NOT NULL,
[SCH_VFL] [int] NOT NULL,
[SCH_VFT] [datetime2](7) NOT NULL,
[SCH_CST] [datetime2](7) NOT NULL,
[SCH_CSL] [int] NOT NULL,
[SCH_CFL] [int] NOT NULL,
[SCH_CFT] [datetime2](7) NOT NULL,
[SCH_EMPLOYEE] [int] NOT NULL,
[SCH_VEHICLE] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CUSTOMERS](
[CUS_ID] [int] NOT NULL,
[CUS_ACC_REF] [varchar](64) NULL,
[CUS_NAME] [varchar](64) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[LOCATIONS](
[LOC_ID] [int] NOT NULL,
[LOC_NAME] [varchar](64) NOT NULL,
[LOC_CITY] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[LOCATIONS_CITIES](
[LCI_ID] [int] NOT NULL,
[LCI_NAME] [varchar](32) NOT NULL
) ON [PRIMARY]
And my query which gives me everything I want....infact too much. I just want the first and last record in each group by Driver for any given date(s). Most of this is done inside an OPENQUERY. I am also struggling to pass query parameters inside the OPENQUERY - hence the awful way of done it by selecting the entire database then applying a WHERE clause outside the OPENQUERY. If you can offer advice on that too, it would be great - but I am looking elsewhere for Interbase-specific syntax.
DECLARE
@WeekStarting DATETIME = '2010/08/26'
SELECT * FROM
OPENQUERY(OPSCENTRE6,'
SELECTEMPLOYEES.EMP_HOME_REGION AS BRANCH,
EMPLOYEES.EMP_FULL_NAME AS DRIVER,
CHARTERS.CHA_ID AS Charter,
SCHEDULED_CHARTER_INFO.SCI_RUN_NUMBER AS Run,
SCHEDULE.SCH_EST AS Start,
LOCATIONS.LOC_NAME AS EST_LOC,
LOCATIONS_CITIES.LCI_NAME AS EST_CITY,
CUSTOMERS.CUS_NAME,
SCHEDULE.SCH_EFT,
LOC2.LOC_NAME AS EFT_LOC,
LOCC2.LCI_NAME AS EFT_CITY
FROM
EMPLOYEES
INNER JOIN SCHEDULE ON EMPLOYEES.EMP_ID=SCHEDULE.SCH_EMPLOYEE
INNER JOIN SCHEDULED_CHARTER_INFO ON SCHEDULE.SCH_ID=SCHEDULED_CHARTER_INFO.SCI_SCHEDULE
INNER JOIN CHARTERS ON SCHEDULED_CHARTER_INFO.SCI_CHARTER=CHARTERS.CHA_ID
INNER JOIN CUSTOMERS ON CHARTERS.CHA_CUSTOMER = CUSTOMERS.CUS_ID
INNER JOIN LOCATIONS ON SCHEDULE.SCH_ESL = LOCATIONS.LOC_ID
INNER JOIN LOCATIONS_CITIES ON LOCATIONS.LOC_CITY=LOCATIONS_CITIES.LCI_ID
INNER JOIN LOCATIONS LOC2 ON SCHEDULE.SCH_EFL=LOC2.LOC_ID
INNER JOIN LOCATIONS_CITIES LOCC2 ON LOC2.LOC_CITY=LOCC2.LCI_ID
')
WHERE(Start>=@WeekStarting) AND (Start<DATEADD("d",8,@WeekStarting))
GROUP BY BRANCH, DRIVER, START, EST_LOC,EST_CITY,CUS_NAME,SCH_EFT,EFT_LOC,EFT_CITY, Charter, Run
ORDER BY Driver
Here is a sample of the data I get out (not showing all columns).
1Ian Nettleship204597212010-09-02 10:00:00.0000000Depot AAUCKLAND
1John Howe20450012010-08-27 20:34:00.0000000Depot AAUCKLAND
1John Howe20450032010-08-27 22:45:00.0000000Airport (International)AUCKLAND
1John Howe20457412010-08-28 03:00:00.0000000Depot AAUCKLAND
1John Howe20457442010-08-28 04:40:00.0000000Airport (International)AUCKLAND
1John Howe20452712010-08-28 06:35:00.0000000Hyatt HotelAUCKLAND
1John Howe20474022010-08-28 18:45:00.0000000Depot AAUCKLAND
1John Howe20450022010-08-28 20:40:00.0000000Depot AAUCKLAND
1John Howe20450042010-08-28 22:45:00.0000000Airport (International)AUCKLAND
1John Howe20457422010-08-29 03:45:00.0000000Crowne Plaza Hotel AKLAUCKLAND
1John Howe20457452010-08-29 04:40:00.0000000Airport (International)AUCKLAND
1John Howe20452722010-08-29 06:35:00.0000000Hyatt HotelAUCKLAND
1John Howe20450012010-08-29 20:34:00.0000000Depot AAUCKLAND
1John Howe20450032010-08-29 22:45:00.0000000Airport (International)AUCKLAND
1John Howe20457432010-08-30 03:00:00.0000000Depot AAUCKLAND
1John Howe20457462010-08-30 04:40:00.0000000Airport (International)AUCKLAND
1John Howe20452732010-08-30 06:35:00.0000000Hyatt HotelAUCKLAND
1John Howe20450022010-08-30 20:34:00.0000000Depot AAUCKLAND
1John Howe20450042010-08-30 22:45:00.0000000Airport (International)AUCKLAND
1John Howe20457412010-08-31 03:00:00.0000000Depot AAUCKLAND
1John Howe20457422010-08-31 04:40:00.0000000Airport (International)AUCKLAND
1John Howe20452712010-08-31 06:35:00.0000000Hyatt HotelAUCKLAND
1John Lancaster20593612010-08-28 08:25:00.0000000Depot AAUCKLAND
1John Lancaster20450052010-08-29 09:54:00.0000000Crowne Plaza Hotel AKLAUCKLAND
1John Lancaster20450082010-08-29 11:10:00.0000000Airport (International)AUCKLAND
1John Lancaster20459522010-08-31 07:55:00.0000000Depot AAUCKLAND
1John Lancaster204502612010-08-31 09:54:00.0000000Crowne Plaza Hotel AKLAUCKLAND
1John Lancaster204502862010-08-31 11:10:00.0000000Airport (International)AUCKLAND
1Kevin Watt20450072010-08-28 09:54:00.0000000Crowne Plaza Hotel AKLAUCKLAND
1Kevin Watt204500102010-08-28 11:10:00.0000000Airport (International)AUCKLAND
1Kevin Watt2045951462010-08-29 07:25:00.0000000Depot AAUCKLAND
You can see that John Howe has many jobs each date. I want to create a report which shows;
For each Driver, for each date, start time of first job (and all other columns in that row) and end time (EFT) of last job (and all other columns for that row).
I hope that makes sense.
As I said earlier, if you know how to pass parameters to Interbase (Linked with OLE) inside an OPENQUERY, please share.
Be gentle!
Thanks.
September 30, 2010 at 12:47 pm
"gentle" is not in CELKO's vocab. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply