Date Range Problem - 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

General SQL Server Forums

New to SQL Server Programming

Date Range Problem

Author

Topic

cjsmile999
Starting Member

5 Posts

Posted - 2011-08-29 : 15:17:54

Hello Everyone,

I've done some searching and attempted to resolve this but I can't seem to find and answer that works for me. I'm trying to create a query to pull data from two serarate DB's. It works fine if I pull out the date range, but as soon as I add the date range it errors out.

What I've done is linked two separate DB's in an attempt to pull some data. No errors linking the two DB's Below is my script.

IF (
SELECT Count(amount)
FROM SQLServer02.Sales.dbo.CO AS firstdb
JOIN SQLServer01.Expenses.dbo.CO AS seconddb
ON firstdb.county = seconddb.county_1 where firstdb.city in (22,52)) > 0 and firstdb.date = Convert(Varchar(10),Dateadd(dd, -24, GETDATE()), 120) AS [YYYY-MM-DD]
BEGIN
SELECT county, county_1
FROM SQLServer02.Sales.dbo.CO AS firstdb
JOIN SQLServer01.Expenses.dbo.CO AS seconddb
ON firstdb.county = seconddb.county_1 where firstdb.ciy in (-22,-52)
END

Thanks you your help,
CJ

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-29 : 15:30:26

This line should cause an error
and firstdb.date = Convert(Varchar(10),Dateadd(dd, -24, GETDATE()), 120)

as sql doesn't know that firstdb is the same one you're referencing inside the ()

Would this work?
IF (
SELECT Count(amount)
FROM SQLServer02.Sales.dbo.CO AS firstdb
JOIN SQLServer01.Expenses.dbo.CO AS seconddb ON
firstdb.county = seconddb.county_1
where firstdb.city in (22,52)
and firstdb.date = Convert(Varchar(10),Dateadd(dd, -24, GETDATE()), 120)
) > 0

Jim

Everyday I learn something that somebody else already knew

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-29 : 17:24:06

A couple of other points:

You can use EXISTS instead of SELECT count(amount) to determine whether or not rows exist. However, I am not sure why you even need that - since you are not inserting or updating any data. All you really need to do is run the select - and the rows will either be returned or an empty set will be returned if nothing found.

If you really want to include the IF statement because there is other logic that you didn't include, then you can also remove the join to the second DB and replace that with an EXISTS also which might be faster (might not - so test).

And finally, you can change your calculation for the date to: DATEADD(day, DATEDIFF(day, 0, getdate()) - 24, 0)

That will return a datetime data type and eliminate an implicit conversion from varchar to datetime in the filter.

Jeff

cjsmile999
Starting Member

5 Posts

Posted - 2011-09-03 : 07:26:44

Thanks Jim it worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources