query help - 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 2000 Forums

Transact-SQL (2000)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-01 : 12:16:12

I need help with the following.

In my db I have a field newslettermonth and newsletteryear which are integers such as for today 3 and 2005

Now I want to write a query where I pass in a start date and end date and it finds all the records that are within those dates.

Can someone help me with it. It's a bit confusing as in the tables it is not in a date field.

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-01 : 12:21:11

Depends on your business rules and requirements. Does a value of 3 and 2005 imply March 1st to March 31st ? What if it's a quarterly, not monthly publication ? What does it *mean* if I pass a date range of, say Feb 20th 2005 to March 5th 2005 ? Does that match month=3 year=2005 because there's an *overlap* ? Or does it not match because there's only a partial, but not full overlap ?

The solution can only come when you explain the business rules & requirements.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-01 : 12:29:03

3 and 2005 means march 2005
People get subscribed always from the first of the month to the 30th.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 12:52:11

Post the DDL

Like CREATE TABLE....

Post Some sample Data like

INSERT INTO myTable
SELECT data,data,...ect UNION ALL
SELECT data,data,...ect UNION ALL
ect

Then what the expected results are suppose to look like

Should get an answer in seconds after you post that

Brett

8-)

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-03-01 : 13:08:46

If I understand you correctly ...

If you can query your target table directly, you can use this:

SELECT * FROM TargetTable
WHERE MONTH(TargetColumn) = 3 AND YEAR(TargetColumn) = 2005

However, if you can only pass in a starting and ending date, then try this:

DECLARE @StartDate datetime,
@EndDate datetime,
@newslettermonth int,
@newsletteryear int

SET @newslettermonth = 3
SET @newsletteryear = 2005

SET @StartDate = CONVERT(datetime, CONVERT(char(2), @newslettermonth) + '/01/' + CONVERT(char(4), @newsletteryear))
SET @EndDate = DATEADD(MONTH, DATEDIFF(MONTH, 30, @StartDate), 30)

SELECT * FROM YourTable
WHERE TargetColumn BETWEEN @StartDate AND @EndDate

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-01 : 14:06:42

no unless I don't understand this is not what I need.

Let me give an exapmple -- Let's say i'm passing in 1/1/2005 -4/30/2005
I want to do a query to select all enries with the newsletters between 1/2/ 2005 and 4/30/2005
which will return all entries where newsletter month and year is jan 2005,feb 2004 , march 2005, and april 2005. plus of cousrse this must work with dates 11/1/2004 - 4/30/2005

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-01 : 14:22:10

It still goes back to requirements.

Are you always passing in dates that represent month-end ranges ?
i.e. is StartDate *always* the 1st of the month ? Is EndDate *always* the last day of the month ? If no to either of those, what does it mean when a mid-month date is passed ?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-01 : 14:32:55

ok I have just changes it. start date is always the first of the month.
Inclduing both so 1/12005 - 1.4.2005 would include jan,feb,march,april

Thanks for your help

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-01 : 14:37:49

quote:
Originally posted by PW

It still goes back to requirements.

Are you always passing in dates that represent month-end ranges ?
i.e. is StartDate *always* the 1st of the month ? Is EndDate *always* the last day of the month ? If no to either of those, what does it mean when a mid-month date is passed ?

His example tells you the requirements. It is always from the beginning of the month.



Declare @myTable table (id int identity(1,1), mth int, yr int)
Insert Into @myTable (mth, yr)
Select 10, 2004 Union All Select 10, 2004 Union All Select 11, 2004 Union All Select 12, 2004 Union All Select 12, 2004 Union All
Select 12, 2004 Union All Select 1, 2005 Union All Select 1, 2005 Union All Select 2, 2005 Union All Select 3, 2005 Union All
Select 3, 2005 Union All Select 3, 2005 Union All Select 3, 2005

Declare @startDate datetime,
	@endDate datetime

Select @startDate = '11/10/2004', @endDate = '1/1/2005'

Select * From @myTable 
Where yr*100+mth between (year(@startDate)*100 + month(@startDate)) and (year(@endDate)*100 + month(@endDate))

Select @startDate = '1/31/2005', @endDate = '4/25/2005'

Select * From @myTable 
Where yr*100+mth between (year(@startDate)*100 + month(@startDate)) and (year(@endDate)*100 + month(@endDate))

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-03-01 : 14:51:04

Will this work for you?

SELECT * FROM YourTable
WHERE CONVERT(char(4), newsletteryear) + REPLICATE('0', 2 - LEN(newslettermonth)) + CONVERT(char(2), newslettermonth) BETWEEN
LEFT(CONVERT(char(8), CONVERT(datetime, '11/1/2004'), 112), 6) AND LEFT(CONVERT(char(8), CONVERT(datetime, '4/12/2005'), 112), 6)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources