problem with finding record between to yearseries

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)

problem with finding record between to yearseries

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2009-01-15 : 00:01:02

Dear All,
I am using sql 2005
I want to find out no of record between to frmonth , from year and tomoth , toyear .
In table table are saving as
Article Month Year Other detail Other detail
1233 1 2008 . .
45654 8 2009 . .
46456 7 2008 . .

I write below query

SELECT * FROM SALES_DETAIL WHERE ((SALD_MM BETWEEN @frommonth AND @tomonth)) AND ((SALD_YYYY BETWEEN @fromyear AND @toyear))

It gives me perfect result if year series same. But when it differ I do not get correct result
For example if I select February 2008 to Jan 2009 it deos not return anything where it has to return data of feb 2008 to Jan 2009

Please help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-15 : 02:35:02

Try

SELECT * FROM SALES_DETAIL
WHERE
dateadd(month,SALD_MM-1,dateadd(year,SALD_YYYY-1900,0))>=dateadd(month,@frommonth-1,dateadd(year,@fromyear-1900,0))
and
dateadd(month,SALD_MM-1,dateadd(year,SALD_YYYY-1900,0))<dateadd(month,@tomonth-1,dateadd(year,@toyear-1900,0))+1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 02:53:29

[code]-- These are the user supplied parameters for stored procedure
DECLARE @FromYear SMALLINT,
@FromMonth TINYINT,
@ToYear SMALLINT,
@ToMonth TINYINT

SELECT @FromYear = 2008,
@FromMonth = 9,
@ToYear = 2009,
@ToMonth = 3

-- Now convert them into dates for utilizing any present index over sald_mm column
DECLARE @From DATETIME,
@Next DATETIME

-- This way we also prevent parameter sniffing
SELECT @From = DATEADD(MONTH, 12 * @FromYear - 22801 + @FromMonth, 0),
@Next = DATEADD(MONTH, 12 * @ToYear - 22800 + @ToMonth, 0)

-- Display the data
SELECT *
FROM Sales_Detail
WHERE Sald_mm >= @From
AND Sald_mm < @Next[/code]

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

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-15 : 05:24:02

Hi Dear,

I hope the below query will help you to get the expected result.

Query With ExampleDECLARE @FromYear INT, @ToYear INT
DECLARE @FromMonth varchar(40), @ToMonth varchar(40)
DECLARE @FromDate DATETIME, @ToDate DATETIME
DECLARE @NoOfDaysToMonth int
SELECT @FromYear = 2007, @ToYear= 2008, @FromMonth ='January', @ToMonth = 'March'

SELECT @NoOfDaysToMonth = datepart(dd,
dateadd(dd,-1,
dateadd(mm,1,CAST(CAST(@ToYear as varchar)+'-'+@ToMonth+'-01' as datetime))))

SELECT @FromDate = CAST(CAST(@FromYear as varchar)+'-'+@FromMonth+'-01' as datetime)
,@ToDate = CAST(CAST(@ToYear as varchar)+'-'+@ToMonth+'-01' as datetime) + @NoOfDaysToMonth-1

SELECT * FROM SALES_DETAIL
WHERE CAST(CAST(SALD_YYYY as varchar)+'-'+SALD_MM+'-01' as datetime)
BETWEEN @FromDate AND @ToDate

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:22:32

quote:
Originally posted by thiyagu_rind

Hi Dear,

I hope the below query will help you to get the expected result.

Query With ExampleDECLARE @FromYear INT, @ToYear INT
DECLARE @FromMonth varchar(40), @ToMonth varchar(40)
DECLARE @FromDate DATETIME, @ToDate DATETIME
DECLARE @NoOfDaysToMonth int
SELECT @FromYear = 2007, @ToYear= 2008, @FromMonth ='January', @ToMonth = 'March'

SELECT @NoOfDaysToMonth = datepart(dd,
dateadd(dd,-1,
dateadd(mm,1,CAST(CAST(@ToYear as varchar)+'-'+@ToMonth+'-01' as datetime))))

SELECT @FromDate = CAST(CAST(@FromYear as varchar)+'-'+@FromMonth+'-01' as datetime)
,@ToDate = CAST(CAST(@ToYear as varchar)+'-'+@ToMonth+'-01' as datetime) + @NoOfDaysToMonth-1

SELECT * FROM SALES_DETAIL
WHERE CAST(CAST(SALD_YYYY as varchar)+'-'+SALD_MM+'-01' as datetime)
BETWEEN @FromDate AND @ToDate

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

How is this better than what Peso posted?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 08:57:34

It is at least different...

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-15 : 10:25:54

There isn't much chance to use an index for this, so this should work as well as anything else.


SELECT
	*
FROM
	SALES_DETAIL 
WHERE
	(SALD_YYYY*100)+SALD_MM between 
		(@fromyear*100)+@frommonth and 
		(@toyear*100)+@tomonth

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources