how to get moth data on month differece basis

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)

how to get moth data on month differece basis

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-07 : 05:24:21

hi,

i have to extract data with moth difference. i have data like this.

1 01/jan/2003
2 01/feb/2003
3 01/march/2003
4 01/jun/2003
5 01/aug/2003
6 01/Dec/2003

i have to extract data less than last two month for exp result should be like that.

1 01/jan/2003
2 01/feb/2003
3 01/march/2003
4 01/jun/2003

i have to check data available on max date then keep last two month data and display the rest.

kindly tell me how to get this.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-07 : 06:01:54

Try joining your table to a derived table of the max date and subtract two months, something like this:

SELECT t.*
FROM tablename t
JOIN (SELECT thedate = MAX(thedate) FROM tablename) x ON t.thedate >= DATEADD(mm, -5, x.thedate)

Raymond

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-07 : 07:55:59

i am not sure abt the difference between months, difference can be of 3 or 4 or 5 there is no fix date difference.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-07 : 08:04:44

So where I put -5, you can have -3, -4 or -5. How do you detrmine which difference you need?

Raymond

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-07 : 08:07:07

Make the mm and -5 in the DATEADD variables. You can then set them to be whatever you need when you call the stored procedure.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-07 : 08:10:23

Nice idea, assuming a stored procedure is being used. If not, then it should be!

Raymond

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-08 : 00:04:50

my dear friends i can not fix the difference either 5,4,or 3. the difference is not predictable it can be 1 ,7 12 or any.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-08 : 00:25:18

DECLARE @diff INT
SELECT @diff = -5 --(This can be set to whatever the user chooses.)

SELECT t.*
FROM tablename t
JOIN (SELECT thedate = MAX(thedate) FROM tablename) x ON t.thedate >= DATEADD(mm, @diff, x.thedate)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources