derive previous dates from given table

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 2008 Forums

Transact-SQL (2008)

derive previous dates from given table

Author

Topic

psiri3
Starting Member

5 Posts

Posted - 2010-08-30 : 22:28:54

Hi all,
I have a requirement and I am newbie to TSQL.

I have a table, which is populated daily, except for weekends.
so, for august,I have a date entries like,

trade_date
__________

08/02/2010
08/03/2010
08/04/2010
08/05/2010
08/06/2010
08/09/2010
08/10/2010
08/11/2010
08/12/2010
08/13/2010
08/16/2010
08/17/2010

such a way.. note that, there are no entries for 08/07/2010, 08/08/2010,08/14/2010, 08/15/2010 as they are weekends.

Now I need a table derived from this table such that, when I Give a date to query, It should give me a table,
with the dates starting from that month, till that date in one column and corresponding previous dates in next column.

Suppose, if choose 08/20/2010,

I need to write an TSQL statement such that, I need a a table this way:

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-30 : 22:39:36

what's the logic to get previous date?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-30 : 22:40:21

to arrive at first column, You can have something like this-

select 
    trade_date as trade_date
from 
    tab 
where 
    trade_date<=@InputDate and month(trade_date)=month(@InputDate) and year(trade_date)=year(@InputDate)

psiri3
Starting Member

5 Posts

Posted - 2010-08-30 : 22:47:06

quote:
Originally posted by sakets_2000

to arrive at first column, You can have something like this-

select
trade_date as trade_date
from
tab
where
trade_date<=@InputDate and month(trade_date)=month(@InputDate) and year(trade_date)=year(@InputDate)

Hi, I could get the first column easily,
But, the problem is, for each entry in first column, I need to get that particular previous date.
I couldnt even start it.seems to be complex to me.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-30 : 22:52:44

what is the logic to arrive at 'particular previous date'? 30 days back?? 30 dates back in the table?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-30 : 22:54:08

I don't see your attachment as well in your first which displays your output..

sakepwr
Starting Member

3 Posts

Posted - 2010-08-31 : 00:46:32

DECLARE
@StartDate smalldatetime,
@EndDate smalldatetime

SET @EndDate = '2010/08/30'
SET @StartDate = CAST(CAST(DATEPART(yyyy,@EndDate) AS CHAR(4)) + '/' + CAST(DATEPART(mm,@EndDate) AS VARCHAR(2)) + '/01' AS smalldatetime);

WITH DateTbl (RowNum, trade_date) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY trade_date) AS 'RowNum',
trade_date
FROM dbo.trade_tbl)
SELECT
d1.trade_date,
d2.trade_date
FROM DateTbl d1
JOIN DateTbl d2 ON d2.RowNum = d1.RowNum - 1
WHERE d1.trade_date BETWEEN @StartDate AND @EndDate
ORDER BY d1.trade_date

sakepwr
Starting Member

3 Posts

Posted - 2010-08-31 : 00:47:35

Sorry about the bad formatting. I'm new to this forum. Apparently it doesn't respect tabs. Hope the query makes sense anyway

psiri3
Starting Member

5 Posts

Posted - 2010-08-31 : 07:44:36

Sakets_2000, i am unable to figure out the logic for previous dates fot the set of dates.
This is the image I posted.
http://img413.imageshack.us/img413/2328/52458957.jpg
however, I could write the logic for getting previous day fr one particular date.

sakepwr..Thanks.will try to execute it..
is that a pl/sql code?
I need a sql code

psiri3
Starting Member

5 Posts

Posted - 2010-08-31 : 09:48:34

I use this to get the previous date for any given date

Select trade_date from table1 where trade_date = (select max(trade_date) from table where trade_date < user_given_date)

If we give a date, this will retrieve the previous date in the table for that date

My database is in Sybase. I need to write a T-SQL code for it.

sakepwr
Starting Member

3 Posts

Posted - 2010-09-01 : 11:50:27

quote:
Originally posted by psiri3

sakepwr..Thanks.will try to execute it..
is that a pl/sql code?
I need a sql code

It's T-SQL. I tested it on my instance of SQL Server 2005, it should also work in 2008.

quote:
My database is in Sybase. I need to write a T-SQL code for it.

Maybe I'm confused. You're running Sybase and you're posting this question in a SQL Server 2008 forum?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 12:21:46

If you are using sql2005 or greater you could use OUTER APPLY.

Example


DECLARE @testTable TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [trade_date] DATETIME
	)

SET DATEFORMAT mdy
INSERT @testTable ([trade_date])
      SELECT '08/02/2010'
UNION SELECT '08/03/2010'
UNION SELECT '08/04/2010'
UNION SELECT '08/05/2010'
UNION SELECT '08/06/2010'
UNION SELECT '08/09/2010'
UNION SELECT '08/10/2010'
UNION SELECT '08/11/2010'
UNION SELECT '08/12/2010'
UNION SELECT '08/13/2010'
UNION SELECT '08/16/2010'
UNION SELECT '08/17/2010'



-- Previous Date for each row
SELECT
	tt.[ID]
	, tt.[trade_date]
	, prevtt.[trade_date]
FROM
	@testTable AS tt
	OUTER APPLY (
		SELECT TOP 1
			[trade_date] AS [trade_date]
		FROM
			@testTable AS tt2
		WHERE
			tt2.[trade_date] < tt.[trade_date]
		ORDER BY
			tt2.[trade_date] DESC
		)
		AS prevtt

Gives the results


ID	trade_date	trade_date
1	2010-08-02 00:00:00.000	NULL
2	2010-08-03 00:00:00.000	2010-08-02 00:00:00.000
3	2010-08-04 00:00:00.000	2010-08-03 00:00:00.000
4	2010-08-05 00:00:00.000	2010-08-04 00:00:00.000
5	2010-08-06 00:00:00.000	2010-08-05 00:00:00.000
6	2010-08-09 00:00:00.000	2010-08-06 00:00:00.000
7	2010-08-10 00:00:00.000	2010-08-09 00:00:00.000
8	2010-08-11 00:00:00.000	2010-08-10 00:00:00.000
9	2010-08-12 00:00:00.000	2010-08-11 00:00:00.000
10	2010-08-13 00:00:00.000	2010-08-12 00:00:00.000
11	2010-08-16 00:00:00.000	2010-08-13 00:00:00.000
12	2010-08-17 00:00:00.000	2010-08-16 00:00:00.000

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

PavanKK
Starting Member

32 Posts

Posted - 2010-09-02 : 06:33:43

To reduce the overhead of ORDER BY Clause you can try below.

SELECT T.ID
,T.trade_date
,(SELECT MAX(T1.trade_date) FROM @testTable T1 WHERE T1.trade_date < T.trade_date) As Previous_Date
FROM @testTable T

KK

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-03 : 18:52:41

[CODE]declare @EndDate date = '20100820'

;With DaysInMonth(day_of_month)
as (
select DateAdd(Month, DateDiff(Month, 0, @EndDate), 0) day_of_month

union all

select DateAdd(Day, 1, day_of_month)
from DaysInMonth
where day_of_month < @EndDate
)
,Weekdays(day_of_month, previous)
as (
select
day_of_month,
DateAdd(day, case
when datepart(weekday, day_of_month) = 2 then -3
else -1
end
,
day_of_month
)
from DaysInMonth
where datepart(weekday, day_of_month) between 2 and 6
)
select *
from Weekdays
order by day_of_month[/CODE]

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources