QUERY - SQL Server Forums

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

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-20 : 08:49:40

Hi

I need current quarter all months.

like '7,8,9'

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-20 : 09:01:24

SELECT CASE DATEPART(QUARTER, GETDATE())
WHEN 1 THEN '1,2,3'
WHEN 2 THEN '4,5,6'
WHEN 3 THEN '7,8,9'
ELSE '10,11,12'
END AS QuarterText

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

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-20 : 09:40:02

Hi

SELECT *
FROM SALES_COMP_RAWDATA
WHERE CONVERT(VARCHAR(2),DATEPART(MONTH,ORDER_DATE))
IN (SELECT DBO.FN_GETMONTHSOFQUARTER())

Am using above query to fetch previous quarter months data but it's not working
pls help out

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-20 : 13:16:58

FN_GETMONTHSOFQUARTER is not a function given by sql server.
It is your own function.
So how should we know to help you.

No, you're never too old to Yak'n'Roll if you're too young to die.

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-20 : 15:01:02

Hi

Ya it's my own function...
SELECT DBO.FN_GETMONTHSOFQUARTER() this will return 4,5,6

If am using below code it will work
SELECT *
FROM SALES_COMP_RAWDATA
WHERE CONVERT(VARCHAR(2),DATEPART(MONTH,ORDER_DATE))
IN (4,5,6)

Instead of this..

SELECT *
FROM SALES_COMP_RAWDATA
WHERE CONVERT(VARCHAR(2),DATEPART(MONTH,ORDER_DATE))
IN (SELECT DBO.FN_GETMONTHSOFQUARTER())

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-20 : 15:13:48

You cannot do it like that. There are several options: One is dynamic sql, which might be fastest depending on your data set. Another option is to convert the IN to a LIKE:

DECLARE @Yak TABLE (MyMonth INT)

INSERT @Yak
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9

DECLARE @MonthsOfQuarter VARCHAR(20)
SET @MonthsOfQuarter = '4,5,6'

-- Your query is like this.. doen't work
SELECT *
FROM @Yak
WHERE CAST(MyMonth AS VARCHAR(2)) IN (@MonthsOfQuarter)

-- Convert IN to a LIKE
SELECT *
FROM @Yak
WHERE '%,' + @MonthsOfQuarter + ',%' LIKE '%,' + CAST(MyMonth AS VARCHAR(2)) + ',%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-20 : 16:08:55

You can write a VIEW like this

CREATE VIEW dbo.vwCurrentQuarter
AS

SELECT	DATEADD(DAY, Number, FromDate) AS OrderDate
FROM	(
		SELECT	Number,
			DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) AS FromDate,
			DATEADD(QUARTER, DATEDIFF(QUARTER, -1, GETDATE()), -1) AS ToDate
		FROM	master..spt_values
		WHERE	Type = 'P'
	) AS d
WHERE	Number <= DATEDIFF(DAY, FromDate, ToDate)

And then your query can look like this

SELECT	*
FROM	SALES_COMP_RAWDATA 
WHERE	ORDER_DATE IN (SELECT OrderDate FROM dbo.vwCurrentQuarter)

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

Shaj
Starting Member

3 Posts

Posted - 2009-08-20 : 16:37:25

am also new to SQL server..but looking at your function, if it returns values..will the below work??

Hi

Ya it's my own function...
SELECT DBO.FN_GETMONTHSOFQUARTER() this will return 4,5,6

If am using below code it will work
SELECT *
FROM SALES_COMP_RAWDATA
WHERE CONVERT(VARCHAR(2),DATEPART(MONTH,ORDER_DATE))
IN (4,5,6)

Instead of this..

SELECT *
FROM SALES_COMP_RAWDATA
WHERE CONVERT(VARCHAR(2),DATEPART(MONTH,ORDER_DATE))
IN (DBO.FN_GETMONTHSOFQUARTER())

*** Just removed the select before dbo.fn_getmonthsofquarter()

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-20 : 18:41:58

quote:
Originally posted by Shaj

am also new to SQL server..but looking at your function, if it returns values..will the below work??

Hi

Ya it's my own function...
SELECT DBO.FN_GETMONTHSOFQUARTER() this will return 4,5,6

If am using below code it will work
SELECT *
FROM SALES_COMP_RAWDATA
WHERE CONVERT(VARCHAR(2),DATEPART(MONTH,ORDER_DATE))
IN (4,5,6)

Instead of this..

SELECT *
FROM SALES_COMP_RAWDATA
WHERE CONVERT(VARCHAR(2),DATEPART(MONTH,ORDER_DATE))
IN (DBO.FN_GETMONTHSOFQUARTER())

*** Just removed the select before dbo.fn_getmonthsofquarter()

Nope.. If you look closer at my example it might help you to understand the different. It seems subtle, but it is significant.

The issue is that when you hard code the IN clause the 4,5,6 are INTs when you run the function you are returning a VARCAHR.

Imagine that you wanted to check for CHAR data. If you do:
IN ('1', '2', '3')
is not the same as
IN ('1,2,3')

Hopefully, that makes some sense. Perhaps someone can explain it better if you want more detail.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-20 : 18:54:27

Or why not simply this?

SELECT	*
FROM	SALES_COMP_RAWDATA 
WHERE	ORDER_DATE >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)
	AND ORDER_DATE < DATEADD(QUARTER, DATEDIFF(QUARTER, -1, GETDATE()), 0)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources