To get the next record after getdate()

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)

To get the next record after getdate()

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-03-14 : 15:29:35

[code]
CREATE TABLE [dbo].[payment_plans] (
[emp_num] [varchar] (13) NOT NULL ,
[Plan_Number] [smallint] NULL ,
[Plan_Create_Dt] [datetime] NULL ,
[Plan_Creator_ID] [varchar] (3) NULL ,
[Plan_Status_Cd] [varchar] (1) NULL ,
[Plan_Amount] [money] NULL ,

) ON [PRIMARY]
GO

INSERT INTO payment_plans
SELECT 'E005',1,'01/16/2007','LMT','A',1300.00 UNION
SELECT 'E005',2,'02/16/2007','LMT','A',1300.00 UNION
SELECT 'E005',3,'03/16/2007','LMT','A',1300.00 UNION
SELECT 'E005',4,'04/16/2007','LMT','A',1300.00 UNION
SELECT 'E005',5,'05/16/2007','LMT','A',1300.00 UNION
SELECT 'E005',6,'06/16/2007','LMT','A',1300.00 UNION
SELECT 'E006',1,'04/16/2007','LMT','A',1600.00 UNION
SELECT 'E006',2,'05/16/2007','LMT','A',1600.00 UNION
SELECT 'E006',3,'06/16/2007','LMT','A',1600.00 UNION
SELECT 'E006',4,'07/16/2007','LMT','A',1600.00 UNION
SELECT 'E006',5,'08/16/2007','LMT','A',1600.00 UNION
SELECT 'E006',6,'09/16/2007','LMT','A',1600.00 UNION
SELECT 'E006',7,'10/16/2007','LMT','A',1600.00 UNION
SELECT 'E007',1,'02/16/2007','LMT','A',1900.00 UNION
SELECT 'E007',2,'03/16/2007','LMT','A',1900.00 UNION
SELECT 'E007',3,'04/16/2007','LMT','A',1900.00 UNION
SELECT 'E007',4,'05/16/2007','LMT','A',1900.00 UNION
SELECT 'E007',5,'06/16/2007','LMT','A',1900.00 UNION
SELECT 'E007',6,'07/16/2007','LMT','A',1900.00 UNION
SELECT 'E007',7,'08/16/2007','LMT','A',1900.00
SELECT 'E005',4,'04/16/2007','LMT','A',1300.00 UNION
SELECT 'E006',1,'04/16/2007','LMT','A',1600.00 UNION
SELECT 'E007',3,'04/16/2007','LMT','A',1900.00 UNION

Results :

emp_num Plan_Number Plan_Create_Dt Plan_Creator_ID Plan_Status_Cd Plan_Amount
E005 4 2007-04-16 00:00:00.000 LMT A 1300.0000
E006 1 2007-04-16 00:00:00.000 LMT A 1600.0000
E007 3 2007-04-16 00:00:00.000 LMT A 1900.0000

The above shown should be the results.Basically the logic is to get the next records which is after the current date for each emp_number

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 15:43:18

[code]-- Prepare sample data
DECLARE @Payments TABLE (
Emp_Num VARCHAR(13),
Plan_Number SMALLINT,
Plan_Create_Dt DATETIME,
Plan_Creator_ID VARCHAR(3),
Plan_Status_Cd VARCHAR(1),
Plan_Amount MONEY
)

INSERT @Payments
SELECT 'E005', 1, '01/16/2007', 'LMT', 'A', 1300.00 UNION ALL
SELECT 'E005', 2, '02/16/2007', 'LMT', 'A', 1300.00 UNION ALL
SELECT 'E005', 3, '03/16/2007', 'LMT', 'A', 1300.00 UNION ALL
SELECT 'E005', 4, '04/16/2007', 'LMT', 'A', 1300.00 UNION ALL
SELECT 'E005', 5, '05/16/2007', 'LMT', 'A', 1300.00 UNION ALL
SELECT 'E005', 6, '06/16/2007', 'LMT', 'A', 1300.00 UNION ALL
SELECT 'E006', 1, '04/16/2007', 'LMT', 'A', 1600.00 UNION ALL
SELECT 'E006', 2, '05/16/2007', 'LMT', 'A', 1600.00 UNION ALL
SELECT 'E006', 3, '06/16/2007', 'LMT', 'A', 1600.00 UNION ALL
SELECT 'E006', 4, '07/16/2007', 'LMT', 'A', 1600.00 UNION ALL
SELECT 'E006', 5, '08/16/2007', 'LMT', 'A', 1600.00 UNION ALL
SELECT 'E006', 6, '09/16/2007', 'LMT', 'A', 1600.00 UNION ALL
SELECT 'E006', 7, '10/16/2007', 'LMT', 'A', 1600.00 UNION ALL
SELECT 'E007', 1, '02/16/2007', 'LMT', 'A', 1900.00 UNION ALL
SELECT 'E007', 2, '03/16/2007', 'LMT', 'A', 1900.00 UNION ALL
SELECT 'E007', 3, '04/16/2007', 'LMT', 'A', 1900.00 UNION ALL
SELECT 'E007', 4, '05/16/2007', 'LMT', 'A', 1900.00 UNION ALL
SELECT 'E007', 5, '06/16/2007', 'LMT', 'A', 1900.00 UNION ALL
SELECT 'E007', 6, '07/16/2007', 'LMT', 'A', 1900.00 UNION ALL
SELECT 'E007', 7, '08/16/2007', 'LMT', 'A', 1900.00 UNION ALL
SELECT 'E005', 4, '04/16/2007', 'LMT', 'A', 1300.00 UNION ALL
SELECT 'E006', 1, '04/16/2007', 'LMT', 'A', 1600.00 UNION ALL
SELECT 'E007', 3, '04/16/2007', 'LMT', 'A', 1900.00

-- Show the expected result
SELECT DISTINCT p.Emp_Num,
p.Plan_Number,
p.Plan_Create_Dt,
p.Plan_Creator_ID,
p.Plan_Status_Cd,
p.Plan_Amount
FROM @Payments AS p
INNER JOIN (
SELECT Emp_Num,
MIN(Plan_Create_Dt) as Beer
FROM @Payments
WHERE Plan_Create_Dt > CURRENT_TIMESTAMP
GROUP BY Emp_Num
) AS x ON x.Emp_Num = p.Emp_Num AND x.Beer = p.Plan_Create_Dt
ORDER BY p.Emp_Num,
p.Plan_Number,
p.Plan_Create_Dt[/code]

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-03-14 : 17:08:39

This is working.Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 17:28:34

You're welcome.
See you soon!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources