Seperating quarter years

Question

Seperating quarter years

Mike-238404

Old Hand

Points: 353
More actions
September 23, 2005 at 9:55 am

#91324

SELECT TYPE,vendor,discontinueddate, datepart(quarter,[discontinueddate]) AS 'Quarter'
from dbo.EOL
WHERE DISCONTINUEDDATE > DateAdd(M,-24,GETDATE()) and DISCONTINUEDDATE < DATEADD(D, 1, DATEDIFF(D, 0, GetDate()))
Order by Discontinueddate
Works good but I need to seperate Quarters with Years. Sorting all quarters in 2005 / 2006 etc.

Thanks

Mike

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I think your DISCONTINUEDDATE < DATEADD(D, 1, DATEDIFF(D, 0, GetDate())) constriant is not correct. Basically it gives you tomorrow, hence, 2006 records will not be included.

I threw this together for testing.

DECLARE @EOL TABLE( Type char(3),

Vendor varchar(25),

DiscountDueDate smalldatetime)

INSERT INTO @EOL

SELECT 'A', 'VendorA', '01/01/2005' UNION ALL

SELECT 'A', 'VendorA', '07/15/2005' UNION ALL

SELECT 'A', 'VendorA', '12/30/2005' UNION ALL

SELECT 'A', 'VendorA', '02/01/2006' UNION ALL

SELECT 'A', 'VendorA', '08/01/2006' UNION ALL

SELECT 'A', 'VendorA', '11/15/2006'

SELECT Type, Vendor, DiscountDueDate, DATEPART( quarter, DiscountDueDate) AS 'Quarter'

FROM @EOL

WHERE DiscountDueDate > DATEADD( m, -24, GETDATE())

-- AND DiscountDueDate < DATEADD( d, 1, DATEDIFF( d, 0, GETDATE())) -- this is 09/24/2005 so it will not capture 2006 records...

AND DiscountDueDate < DATEADD( m, 24, GETDATE()) -- this is 09/24/2005 so it will not capture 2006 records...

ORDER BY DiscountDueDate

I wasn't born stupid - I had to study.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

Why not add DATEPART(YEAR, DiscoDate) AS 'YrDiscod'

And then ORDER BY YrDiscod, [Quarter]?

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

That will work too. But since he already displaying DiscoDate, it seems to me to six of one, half a dozen of the other...

I wasn't born stupid - I had to study.

Mike-238404 Old Hand Points: 353 More actions · Answer 4

SELECT Type, Vendor, discontinueddate, DATEPART( YEAR, discontinueddate) AS 'Quarter Year',datepart(quarter,[discontinueddate]) AS 'Quarter'

FROM EOL

WHERE DISCONTINUEDDATE > DateAdd(M,-0,GETDATE())

Order by Discontinueddate

Seems to work ok here.