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 : 11:12:35

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:
Date, (F_inspections.end_date or F_OCCURRENCES.OCCURRENCE_DATE)
Regulation: (F_INSPECTIONS.REG_SURR_ID or F_OCCURRENCES.REG_SURR_ID)
Activity: Weather its an inspection or occurance (i can do this in code)
Inspector: if its an inspection (i can do this in code probably)
Outcome: if its an inspection and there was a problem then get what was done from another table....

However, even if i can just get the Inspection_ids and Occurence ids and the type of id i can requery the data using the ids now that i know which ones are the latest (since I would have to requery anyways to get Regulation description rather than reg_surr_id and do a join to get Inspector_name as well as outcome.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-08 : 12:18:14

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

Edit:
Wrong post - this one has more detail

BCrowe
Starting Member

23 Posts

Posted - 2005-02-08 : 13:23:18

SELECT inspection_id, end_date, reg_surr_id, 'Inspection'
FROM f_inspections
UNION
SELECT occurrence_id, occurrence_date, reg_surr_id, 'Occurrence'
FROM f_occurrences

without more information can't go much farther than this

BCrowe

If you can dodge a wrench, you can dodge a ball.

--Patches O'Houlihan

dbenoit64
Starting Member

36 Posts

Posted - 2005-02-09 : 13:32:50

okay so how would i get the top 5 dates:
ie (end_date or occurance_date) they are both different dates. how do we treat them as the same thing. that is my real issue....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 13:35:10

something like:

select top 5 *
from (the union select)
order by YourDateColumn desc

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources