NEED TO SELECT ONE RECORD - SQL Server Forums

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)

NEED TO SELECT ONE RECORD

Author

Topic

sqlbee
Starting Member

11 Posts

Posted - 2003-10-01 : 15:52:47

I have a table that has field like this.
id cost start_date recordNumber
1 20.00 1/1/2003 1
1 30.00 1/1/1950 2
2 23.00 1/1/1950 3
3 34.00 1/1/2003 4
4 40.00 1/1/1950 5
I need to pick record no. 1,3 and 4 how do i do it in where statement?
This table is joined to two tables.
this table has lot of columns and only start_date is different for given ID.The year(start_date) should match with current year or
It should get the other date.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-01 : 15:57:22

How come not row 5?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

sqlbee
Starting Member

11 Posts

Posted - 2003-10-01 : 16:02:29

sorry!!!!!!!!!! we need to pick number five too.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-01 : 16:14:27

I don't know if this is what you're looking for, but it gives you the results you want...


USE Northwind
GO

CREATE TABLE myTable99 ([id] int, cost money, start_date datetime, RecordNumber int)
GO

INSERT INTO myTable99 ([id], cost, start_date, recordNumber)
SELECT 1, 20.00, '1/1/2003', 1 UNION ALL
SELECT 1, 30.00, '1/1/1950', 2 UNION ALL
SELECT 2, 23.00, '1/1/1950', 3 UNION ALL
SELECT 3, 34.00, '1/1/2003', 4 UNION ALL
SELECT 4, 40.00, '1/1/1950', 5
GO

SELECT [Id], MAX(start_date) FROM myTable99 GROUP BY [Id] 
GO

DROP TABLE myTable99
GO

Also, if your post provide sample DDL, data, and maybe code...it's a big help...help us to help you

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-01 : 16:18:41

sqlbee, for DDL, sample data, and code, please see this thread:

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

You'll see a link to an threadin the first post. That thread shows you how we need to see the DDL, sample data, etc...

Tara

sqlbee
Starting Member

11 Posts

Posted - 2003-10-01 : 16:59:28

SELECT BNPOSTCODE.POSTAL_CODE,BNPOSTCODE.POST_CODE_TBL,BNPOSTCODE.INS_CARRIER,COVERAGE_OPT,EMP_CONT,
COVER_TYPE,BNPRMOPT.START_DATE
FROM BNPOSTCODE INNER JOIN R_PLAN ON
BNPOSTCODE.POST_CODE_TBL=R_PLAN.POST_CODE_TBL
AND BNPOSTCODE.INS_CARRIER=R_PLAN.INS_CARRIER INNER JOIN
BNPRMOPT ON R_PLAN.PLAN_CODE=BNPRMOPT.PLAN_CODE
WHERE YEAR(BNPRMOPT.START_DATE)=CASE WHEN YEAR(BNPRMOPT.START_DATE)=YEAR(GETDATE()) THEN YEAR(GETDATE())
ELSE YEAR(BNPRMOPT.START_DATE) END
AND BNPRMOPT.plan_code in('BG00')
ORDER BY BNPRMOPT.START_DATE DESC,POSTAL_CODE

In my table we actually have start date as 1/1/2004 not 1/1/2003 and if
we do max(date) it will not work because we need to check if it is current year or not. As soon as we hit 2004 we need to pick that record not with year 1950. This time we need to pick records that have only 1950 not 2004.
thanks for the help.
rita

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-01 : 17:03:47

You didn't provide the DDL nor the sample data.

Tara

sqlbee
Starting Member

11 Posts

Posted - 2003-10-01 : 17:35:07

CREATE TABLE [dbo].[MOPT] (
[ID] [smallint] NOT NULL ,
[AMOUNT] [MONEY] NOT NULL ,
[START_DATE] [DATETIME]NOT NULL,
COVERAGE_OPT CHAR(1) )

insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (1, 11111, '1/1/1950', 1)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (2, 22222, '1/1/1950', 2)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (3, 32566, '1/1/1950', 3)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (4, 1452, '1/1/1950', 4)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (5, 21458, '1/1/1950',5)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (6, 14785, '1/1/1950', 6)

insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (1, 11111, '1/1/2004', 1)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (2, 42566, '1/1/2004', 2)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (3, 22222, '1/1/2004', 3)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (4, 12566, '1/1/2004', 4)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (5, 22222, '1/1/2004', 5)
insert MOPT (ID,AMOUNT,START_DATE,COVERAGE_OPT) values (6, 54566, '1/1/2004', 6)

This time we need only those records that has a year(start_date) < year(getdate())
but as soon as we hit Jan 2004 we need only those records that has year(start_date)=year(getdate())

thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-02 : 13:12:39

and what happens in Feb 2004? Mar 2004 ? you need to more specifically state your requirements.

I hear your requirements as:

"if the current month (from getdate()) is January, return all rows from the current year. Otherwise, return all rows from all years less than the current year."

which too me doens't make much sense. Please be specific; remember, we have no idea about your problem or what you are trying to do other than what you specifically tell us.

(did I mention to be specific ??? )

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources