Query the data for month,year range

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

Transact-SQL (2005)

Query the data for month,year range

Author

Topic

vibha
Starting Member

7 Posts

Posted - 2008-04-30 : 05:07:07

Hi,

I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.

(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month >= 6 AND R.Year = 2006)
Group By R.Year , R.Month )
Union
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month <=3 AND R.Year = 2008)
Group By R.Year , R.Month )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 05:12:19

This is one query.
What happened to the year 2007?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 05:15:26

[code]SELECT SUM(Amt),
[Month],
[Year]
FROM Receipt
WHERE 100 * [Year] + [Month] BETWEEN 200606 AND 200803
GROUP BY [Year],
[Month]
ORDER BY [Year],
[Month][/code]

E 12°55'05.25"
N 56°04'39.16"

vibha
Starting Member

7 Posts

Posted - 2008-04-30 : 05:18:37

Using this query i can get only two consecutive years data.ex. 2006 and 2007.It works fine if i give Jun-2006 and Mar-2007.But it doesn't work for Jun-2006 and Mar-2008.
I want from Jun-2006 to Mar-2008

quote:
Originally posted by Peso

This is one query.
What happened to the year 2007?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 05:25:24

And I did provide a query for you doing exactly what you want.

E 12°55'05.25"
N 56°04'39.16"

vibha
Starting Member

7 Posts

Posted - 2008-04-30 : 05:30:21

Thanx,
Its working fine.Exactly give me the result that i want.
but it takes 9 secs.

quote:
Originally posted by Peso

And I did provide a query for you doing exactly what you want.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 05:35:04

Of course!

Even if there is a present index over [Year] and/or [Month] you can't use them.
Is there an original column with a complete date for Receipt?

E 12°55'05.25"
N 56°04'39.16"

vibha
Starting Member

7 Posts

Posted - 2008-04-30 : 05:44:53

No there is not such a column.
Thanx.

quote:
Originally posted by Peso

Of course!

Even if there is a present index over [Year] and/or [Month] you can't use them.
Is there an original column with a complete date for Receipt?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 06:03:22

Run this code

ALTER TABLE Receipt
ADD YearMonth INT
GO

CREATE NONCLUSTERED INDEX IX_YearMonth ON Receipt (YearMonth)
GO

CREATE TRIGGER trgReceipt ON Receipt
AFTER UPDATE, INSERT
AS

UPDATE		r
SET		r.YearMonth = 100 * r.[Year] + r.[Month]
FROM		Receipt AS r
INNER JOIN	inserted AS i ON i.[Year] = r.[Year]
WHERE		i.[Month] = r.[Month]
GO

And now your query will be VERY fast.

SELECT		SUM(Amt),
		[Month],
		[Year]
FROM		Receipt
WHERE		YearMonth BETWEEN 200606 AND 200803
GROUP BY	[Year],
		[Month]
ORDER BY	[Year],
		[Month]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 06:17:22

If you don't want a trigger, you can make the calculated column persisted like this

ALTER TABLE	Receipt
ADD		YearMonth AS (100 * [Year] + [Month]) PERSISTED
GO

CREATE NONCLUSTERED INDEX IX_YearMonth ON #Receipt (YearMonth)
GO

And now your query will be equally fast

SELECT		SUM(Amt),
		[Month],
		[Year]
FROM		Receipt
WHERE		YearMonth BETWEEN 200606 AND 200803
GROUP BY	[Year],
		[Month]
ORDER BY	[Year],
		[Month]
GO

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources