Bussiness Logic Need in SQL Statement

Hello Experts,

I'm working on Reconciliation Report, where I'm missing Logic:

1. I need to Check the Record exists in given  Date Range, if no records exists. I need to search beyond another nearest date range from  the table datetime field.


 
ASPDEVAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
vdr1620Connect With a Mentor Commented:
Try using
 IF (SELECT COUNt(*) FROM TableName WHERE Date between '08-20-2010' and '08-21-2010')  > 1
BEGIN
SELECT Statement
END
ELSE
BEGIN
SELECT Statement
END

OR
 you can use IF EXISTS as in the Link
http://articles.techrepublic.com.com/5100-10878_11-6180272.html

OR
you Can use CASE WHEN in where Clause of select statement depending on the requirement
0
 
almanderCommented:
you could use the exists function in an If, or Join

Exists(      
SELECT X FROM X WHERE X)
0
 
ASPDEVAuthor Commented:
This is my requirement, I need to get the closet datetime value if the given date ranges doesn't get any results.

The datetime value from the table must be close eniough to get me the results.

e.g

Quantity

ID  datetime        Qty
1   08/02/2010    100
2   08/06/2010    300
3   08/21/2010    400


If user has give date = 08/07/2010  it should check the nearest datetime(Can be greater or lesser) so that it can find results.

0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
almanderCommented:
See snippet
declare @userDate datetime
select @userDate = GetDate()

select top 1 id 
from yourtablename 
order by abs(datediff(n, [datetime], @userDate))

Open in new window

0
 
almanderCommented:
Just noticed table name
declare @userDate datetime
select @userDate = GetDate()

select top 1 *
from Quantity
order by abs(datediff(n, [datetime], @userDate))

Open in new window

0
 
almanderCommented:
Provided resolution to question.
0
 
ASPDEVAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.