Complicated sql query.... need help badly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complicated sql query.... need help badly

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2005-02-08 : 10:47:23

I am trying to write a query that queries 2 tables and gets back 5 rows. These two tables arent really related and the data I get back is different for either table.
The 5 rows that I want to get back are for the LATEST 5 DATES. The date field for one table (F_INSPECTIONS) is END_DATE and the date field for the other table (F_OCCURRENCES) is OCCURRRENCE_DATE.

I am writing a program to do this so if its absolutely impossible to implement this with sql code then a suggestion of how I might be able to go about doing it antoher way would help.

Table descriptions:

/****** Object: Table [dbo].[F_INSPECTIONS] Script Date: 2/8/2005 10:59:41 AM ******/
CREATE TABLE [dbo].[F_INSPECTIONS] (
[INSPECTION_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
[INSPECTION_NAME] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[CAP_FACILITY_ID] [int] NOT NULL ,
[REG_SURR_ID] [smallint] NOT NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[INSP_UPDATED_ON] [datetime] NULL ,
[INSP_ORIGIN_ID] [tinyint] NULL ,
[INSP_TYPE_ID] [tinyint] NULL ,
[DAYS_SINCE_LAST] [smallint] NULL ,
[VIOLATION_COUNT] [smallint] NULL ,
[NON_COMPLIANCE_IND] [tinyint] NULL ,
[INSPECTION_COUNT] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[F_OCCURRENCES] (
[OCCURRENCE_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
[CAP_FACILITY_ID] [int] NOT NULL ,
[OCCURRENCE_NM] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[OCCURRENCE_DATE] [datetime] NULL ,
[REG_SURR_ID] [smallint] NOT NULL ,
[REPORTED_DATE] [datetime] NULL ,
[ASSESSMENT_DATE] [datetime] NULL ,
[UPDATED_ON] [datetime] NULL ,
[ORIGIN_ID] [tinyint] NULL ,
[CATEGORY_ID] [tinyint] NULL ,
[OUTCOME_ID] [tinyint] NULL
) ON [PRIMARY]

I need to query from these 2 tables and get these columns back:

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-08 : 12:20:53

Duplicate post
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45661[/url]

Right one this time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources