Embedding a query within another query? - 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

General SQL Server Forums

New to SQL Server Programming

Embedding a query within another query?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-03 : 10:19:04

Hi all

I need to get a range of data from a table which would be based from the current month to the beginning of the financial year.

Something like:-


SELECT name,age,postcode,sdate FROM table1
WHERE  sdate BETWEEN   
--now and start of this financial year, so i have to find what the
--current month is and then go back to 01/04 which would be the 
--start of the current financial year!

If i was just going back three months I could use something like

BETWEEN DATEADD(m,DATEDIFF(m,0,GETDATE()-3),0)
AND DATEADD(m,DATEDIFF(m,0,GETDATE()-1),0)

but that would not work in this instance as I would need to know how far to go back from this month!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 10:30:31

01/04 is 1st of April?

E 12°55'05.63"
N 56°04'39.26"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-03 : 10:33:24

Yes, sorry should have been more clear, our financial year runs from 1st April 2008 to 31st March 2009 (currently)

So I would need to look back to the beginning of the current financial year fro all the data that falls within those dates (now - back to 1st April)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 10:37:45

[code]DECLARE @Sample TABLE
(
dt DATETIME
)

INSERT @Sample
SELECT '20090303' UNION ALL
SELECT '20090402' UNION ALL
SELECT '20090615' UNION ALL
SELECT '20090831' UNION ALL
SELECT '20100501'

SELECT dt,
DATEADD(MONTH, DATEDIFF(MONTH, 0, dt) - DATEDIFF(MONTH,90, dt) % 12, 0) AS Peso
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 10:40:46

[code]DECLARE @Sample TABLE
(
dt DATETIME
)

INSERT @Sample
SELECT '20090303' UNION ALL
SELECT '20090402' UNION ALL
SELECT '20090615' UNION ALL
SELECT '20090831' UNION ALL
SELECT '20100501'

SELECT dt,
DATEADD(YEAR, DATEDIFF(MONTH, 90, dt) / 12, 90) AS Previous,
DATEADD(YEAR, DATEDIFF(MONTH, 90, dt) / 12, 455) AS [Next]
FROM @Sample

DECLARE @FromDate DATETIME,
@ToDate DATETIME

SELECT @FromDate = DATEADD(YEAR, DATEDIFF(MONTH, 90, GETDATE()) / 12, 90),
@ToDate = DATEADD(YEAR, DATEDIFF(MONTH, 90, GETDATE()) / 12, 455)

SELECT *
FROM Table1
WHERE Col1 >= @FromDate
AND Col1 < @ToDate[/code]

E 12°55'05.63"
N 56°04'39.26"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-03 : 10:41:22

Nice pointer, thanks Peso.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-03 : 10:50:41

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - DATEDIFF(MONTH,90, GETDATE()) % 12, 0)

cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 10:53:07

Faster...

SELECT	DATEADD(YEAR, DATEDIFF(MONTH, 90, GETDATE()) / 12, 90)

and, in my opinion, more readable.

E 12°55'05.63"
N 56°04'39.26"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-04 : 01:00:03

Nice one Peso,
this is one forum that I'm really glad I joined, I've picked up a lot of tips and had a lot of help here!

Cheers all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources