identify 2 separate months in this SQL

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

identify 2 separate months in this SQL

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-12 : 09:33:55

In this SQL, I am able to determine a field that will hold the month and year that can then be used to display as a heading in a report.

The question arises as to what if the user wants to see sales figures for a month, a 30 day period but spread into 2 different months, i.e 11/15/08 - 12/15/08. In this case, I would want the headings to be something like 'Months of November-December 2008'. Would it be possible to hold an extra month like that in the SQL?

SELECT sop10200.xtndprce price,
sop10200.quantity quantity,
Dateadd(mm,Datediff(mm,0,sop10100.docdate),0) id,
datename(month, sop10100.docDate) mo,
year(sop10100.docdate) yr,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-12 : 09:42:48

Like this?

DECLARE	@Sample TABLE
	(
		a DATETIME,
		b DATETIME
	)

INSERT	@Sample
SELECT	'20090814', '20090815' UNION ALL
SELECT	'20090607', '20090707' UNION ALL
SELECT	'20091207', '20100107'

-- Peso 1
SELECT	a,
	b,
	CASE DATEDIFF(MONTH, a, b)
		WHEN 0 THEN 'Month of ' + DATENAME(MONTH, a) + ' ' + DATENAME(YEAR, a)
		ELSE 'Months of ' + DATENAME(MONTH, a) + COALESCE(' ' + NULLIF(DATENAME(YEAR, a), DATENAME(YEAR, b)), '') + ' - ' + DATENAME(MONTH, b) + ' ' + DATENAME(YEAR, b)
	END AS Yak
FROM	@Sample

-- Peso 2
SELECT	a,
	b,
	CASE 
		WHEN DATEDIFF(MONTH, a, b) = 0 THEN 'Month of ' + DATENAME(MONTH, a) + ' ' + DATENAME(YEAR, a)
		WHEN DATEDIFF(YEAR, a, b) = 0 THEN 'Months of ' + DATENAME(MONTH, a) + ' - ' + DATENAME(MONTH, b) + ' ' + DATENAME(YEAR, a)
		ELSE 'Months of ' + DATENAME(MONTH, a) + ' ' + DATENAME(YEAR, a) + ' - ' + DATENAME(MONTH, b) + ' ' + DATENAME(YEAR, b)
	END AS Yak
FROM	@Sample

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

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-12 : 10:03:52

Thank you Peso, this is a very interesting SQL that I can learn a lot from it.

my only question is when you load the sample database, in my case it is random, the user is looking at 4 years of data. Should I load each 2 months possible combo first or can this be done more dynamically?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources