check if table contains yesterdays date

Hi guys
  I have a table CUSTOMER_DETAIL with ID and BusinessDate columns. I will run a sql at 11.00 AM in the morning where  i have to check if BusinessDate column has yesterday's date. if so pass it to my sql. If BusinessDate column doesnt have
 yesterday's date then pass the MAX(BusinessDate) to the sql.
             
  trying to figure how i can do that.
             
  My sql is
             
  select  [ID],[BusinessDate] FROM CUSTOMER_DETAIL
   where BusinessDate = (Here check if BusinessDate column has yesterday's date, if yes pass yesterdays date, it not  then pass the MAX(BusinessDate)) .
  Any idea how i can do that?
             
 I know yesterdays date can be got using   DATEADD(day, -1, convert(date, GETDATE()))

 Any help will be greatly appreciated
 Thanks.
royjaydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Can there be more than one record per [ID] ??

If so, you'll probably need to UNION two queries. This also assumes you'll have no records with a BusinessDate of today.

SELECT [ID], BusinessDate
FROM CUSTOMER_DETAIL
WHERE CONVERT(date, BusinessDate) = CONVERT(date, GETDATE() - 1)
UNION ALL
SELECT [ID], MAX(BusinessDate) AS BusinessDate
FROM CUSTOMER_DETAIL
WHERE [ID] NOT IN (SELECT [ID] FROM CUSTOMER_DETAIL
                   WHERE CONVERT(date, BusinessDate) <> CONVERT(date, GETDATE() - 1))
GROUP BY [ID]

Open in new window

The second query after the UNION ALL selects only those [ID]'s where there is absolutely no previous day's BusinessDate. It assumes you will not have any BusinessDate records for today's date.
0
SharathData EngineerCommented:
try this.
select [ID],[BusinessDate]
  from (select  [ID],[BusinessDate],
                dense_rank() over (order by convert(date,BusinessDate) desc) rn
          FROM CUSTOMER_DETAIL) t1
 where rn = case when convert(date,BusinessDate) = DATEADD(day, -1, convert(date, GETDATE())) then 2 else 1 end

Open in new window

0
PortletPaulfreelancerCommented:
when you say "pass to sql" I'm assuming you only need one value, either the max(BusinessDate) or DATEADD(day, -1, convert(date, GETDATE()))

I'm not sure if you need to convert BusinessDate to date or not, but that can be removed from this if unnecessary:

SELECT
        CASE 
          WHEN MAX(convert(DATE, BusinessDate)) < DATEADD(day, - 1, convert(DATE, GETDATE()))
               THEN MAX(convert(DATE, BusinessDate))
               ELSE DATEADD(day, -1, convert(date, GETDATE()))
        END
FROM CUSTOMER_DETAIL

Open in new window

{+ edit} I should add that if the table might also contain today's date - and you do NOT want that passed to your sql, then the above makes sense. If however you would need today's date if that is present in the table, then all you would need is:

select MAX(convert(DATE, BusinessDate)) FROM CUSTOMER_DETAIL

In other words I have used less than in my suggestion on the assumption you do not want today's date (or any future dates)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
& just in case you do want a list of dates with ID, (and still assuming you don't want today's date or any future ate) I'd suggest this:
SELECT
        ID
      , CASE 
          WHEN MAX(convert(DATE, BusinessDate)) < DATEADD(day, - 1, convert(DATE, GETDATE()))
               THEN MAX(convert(DATE, BusinessDate))
               ELSE DATEADD(day, -1, convert(date, GETDATE()))
        END
FROM CUSTOMER_DETAIL
GROUP BY
        ID
;

Open in new window

0
SharathData EngineerCommented:
try this.
SELECT ID, 
       CASE 
         WHEN max_past_BusinessDate = DATEADD(day, -1, CONVERT(DATE, GETDATE())) THEN max_past_BusinessDate
         ELSE max_BusinessDate 
       END BusinessDate 
  FROM (SELECT ID, 
               MAX(CONVERT(DATE, BusinessDate)) max_BusinessDate, 
               MAX(CASE 
                     WHEN CONVERT(DATE, BusinessDate) < CONVERT(DATE, GETDATE()) THEN CONVERT(DATE, BusinessDate)
                   END)                         max_past_BusinessDate 
          FROM CUSTOMER_DETAIL 
         GROUP BY ID) t1 

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.