Only data for Yesterday

How do i get only the data for the last date in the query. I just want the most current date. Which is yesterdays information. But on monday, its fridays information. So is there a code for getting the most Current Data. If not whats the code for getting the last business day such as friday for today. But for tommorow it will be monday. My time_log field has the date converted into mm-dd-yyyy. Any help?

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_6_Daily]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Status,

dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],

B.VisitsTotal

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status

FROM dbo.Qry_Visits

WHERE Qry_Visits.Status=2

GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status

HAVING SUM(Visits) < 6)B

ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND

CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate

WHERE REGION_KEY=@.Region_Key and Qry_Visits.Status=2 and CONVERT(varchar, dbo.Qry_Visits.time_log, 110)=GETDATE()

ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)

END

SET NOCOUNT OFF

Can you use something like:

Code Snippet

declare @.date datetime

if DATENAME(dw, getdate()) = 'MONDAY'
SET @.date = getdate()-3
else
SET @.date = getdate()-1

SELECT ....
FROM ......
WHERE DATEDIFF(D,Time_Log, @.Date) = 0

HTH!

|||

Do i have to make the date a parameter. I tried to just doing this:

Code Snippet

WHERE REGION_KEY=@.Region_Key and Qry_Visits.Status=2 and

(CONVERT(varchar, dbo.Qry_Visits.time_log, 110))=getdate()-3

but nothing shows up.

|||

Or, you might try this one:

Code Snippet

where REGION_KEY=@.Region_Key and Qry_Visits.Status=2

and DateDiff(dd,0,dbo.Qry_Visits.time_log) =

case

when DATENAME(dw, getdate()) = 'MONDAY' then datediff(dd,0,GetDate()) - 3

else datediff(dd,0,GetDate()) - 1

end

ORDER BY dbo.Qry_Sales_Group.SR_Name, dbo.Qry_Date_Dim.Date_Dimension_Date

|||that works perfectly THANKS!!