Getting the closest date available for each record

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

SQL Server Development (2000)

Getting the closest date available for each record

Author

Topic

orcities
Starting Member

8 Posts

Posted - 2004-07-29 : 15:56:16

I am trying to query 2 tables and return the nearest date to todays date for each record.

Table Status has dates, and id (multiple dates per id)
Table Bills has id

Looking at the date functions I can't seem to find the best solution.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 16:02:23

close date above??
Select bills.*, id = (Select min(statusDate) From Status where id = bills.id and StatusDate > getdate()) From bills

close date below??
Select bills.*, id = (Select max(statusDate) From Status where id = bills.id and StatusDate < getdate()) From bills

That work??

Corey

orcities
Starting Member

8 Posts

Posted - 2004-07-29 : 16:15:28

That doesn't work. Basically I don't know when the date is I just need the most recent action for that billid.

orcities
Starting Member

8 Posts

Posted - 2004-07-29 : 16:25:51

Sorry, it works but I need the action not the date

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 16:47:47

what is the action called?

I assume actionId...

Select
A.*,
B.*
From
(
Select
bills.*,
actionId = (Select actionId From Status where id = bills.id Order By StatusDate Desc)
From bills
) as A
Inner Join Status as B
On A.actionId = B.actionId

Corey

orcities
Starting Member

8 Posts

Posted - 2004-07-29 : 16:55:58

Didn't work got this error:
Server: Msg 1033, Level 15, State 1, Line 8
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Table structure is as follows: i need the newest [action] column for each billid

CREATE TABLE [dbo].[BillStatus_Bills] (
[BillID] [int] IDENTITY (1, 1) NOT NULL ,
[LegislatureID] [int] NULL ,
[BillNumber] [nvarchar] (10) NULL ,
[Title] [nvarchar] (100) NULL ,
[Description] [text] NULL ,
[RelatingTo] [text] NULL ,
[SessionIdentifier] [numeric](1, 0) NULL
)

CREATE TABLE [dbo].[BillStatus_Status] (
[StatusID] [int] IDENTITY (1, 1) NOT NULL ,
[BillID] [int] NOT NULL ,
[Legislature] [char] (1) NULL ,
[DateUpdated] [smalldatetime] NULL ,
[Action] [nvarchar] (256) NULL
)

orcities
Starting Member

8 Posts

Posted - 2004-07-29 : 16:56:48

QUERY:
Select A.*,B.*
From
(
Select
billstatus_bills.*,
[action]= (Select [action] From billstatus_Status where billid = billstatus_bills.id Order By billstatus_dateupdated Desc)
From billstatus_bills
) as A
Inner Join billstatus_Status as B
On A.actionId = B.actionId

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-29 : 17:00:34

How's about



SELECT * FROM BillStatus_Status o
WHERE EXISTS (SELECT BillId FROM BillStatus i 
WHERE i.BillId = o.BillId
GROUP BY BillId
HAVING o.StatusId = MAX(i.StatusId))

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 17:03:00

I was close... i forgot the top 1

Select A.*,B.*
From
(
Select
billstatus_bills.*,
[action]= (Select top 1 [action] From billstatus_Status where billid = billstatus_bills.id Order By billstatus_dateupdated Desc)
From billstatus_bills
) as A
Inner Join billstatus_Status as B
On A.actionId = B.actionId

But i think bretts probably got a better solution...

Corey

orcities
Starting Member

8 Posts

Posted - 2004-07-29 : 17:04:57

Brett had a good solution. It isn't the quickest thing in the world but it gets the job done.

orcities
Starting Member

8 Posts

Posted - 2004-07-29 : 17:16:53

OK brett that gives me the latest now how do i combine the two tables
everyone i go slows it down even more or doesn't allow the statement because it has two many arguments

orcities
Starting Member

8 Posts

Posted - 2004-07-29 : 17:35:35

OK, using the statusid will not work i have some entries where they aren't in order. So i have to go by date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources