Group By Query? can I combine this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Group By Query? can I combine this?

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-07-31 : 13:35:54

For some reason a seamingly simple query has become quite a hassle.

What I am looking to do is the following:

I have 1 table, and in this table there are 2 columns, 1 containts a statusID and the other contains a Date.

What I want to do is the following, I want to supply a @date, and get the Max(Date) < @Date

so for example

COL1(Date),COL2(STATUSID)
01/01/2005, 5
01/05/2005, 1
01/09/2005, 7
01/22/2005, 22

So if I were to pass the variable of today 07/31/2006 I would get the StatusID value of 22

but if I were to pass the variable of 01/12/2005 I would get the statusID of value 7

I am currently doing this with 2 queries

I pass the @Date variable

Select StatusID
From TMI_EmployerStatus b
Where b.EffectiveDate =
(Select Max(a.EffectiveDate)
From TMI_EmployerStatus a
Where a.EffectiveDate <= @Date
group by a.EmployerID)

Is there a way to do this with 1 query?

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-31 : 14:07:47

[code]create table #t (c1 datetime, c2 int)

insert into #t values ('01/01/2005',5)
insert into #t values ('01/05/2005',1)
insert into #t values ('01/09/2005',7)
insert into #t values ('01/22/2005',22)

declare @pc datetime

Set @pc = '07/31/2006'
Select top 1 c2 from #t where c1 < @pc order by c1 desc

Set @pc = '01/12/2005'
Select top 1 c2 from #t where c1 < @pc order by c1 desc

drop table #t [/code]

Srinika

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-07-31 : 14:58:10

Thanks, Very nicley done.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-01 : 02:25:05

If u know the StatusId, u can:

SELECT CONVERT(DATETIME, Col1, 103) FROM <Table Name> WHERE Col1 < '1/12/2005' AND Col2 = 7

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources