WHERE (docdate BETWEEN DATEADD(YY, - 1, GETDATE()) AND GETDATE())

SELECT     TOP (100) PERCENT SlsRep, SlsName, SUM(MonthToDate) AS MonthToDate, SUM(LastYearMonth) AS LastYearMonth, CASE WHEN SUM(Sales.LastYearMonth) 
                      = 0 AND SUM(Sales.MonthToDate) <> 0 THEN 1 WHEN SUM(Sales.LastYearMonth) <> 0 AND SUM(Sales.MonthToDate) = 0 THEN - 1 WHEN SUM(Sales.LastYearMonth)
                       = 0 AND SUM(Sales.MonthToDate) = 0 THEN 0 ELSE (SUM(Sales.MonthToDate) - SUM(Sales.LastYearMonth)) / SUM(Sales.LastYearMonth) 
                      END AS [Current Period Variance], 0 AS CPProfit, SUM(YearToDate) AS YearToDate, SUM(LastYear) AS LastYear, CASE WHEN SUM(Sales.LastYear) = 0 AND 
                      SUM(Sales.YearToDate) <> 0 THEN 1 WHEN SUM(Sales.LastYear) <> 0 AND SUM(Sales.YearToDate) = 0 THEN - 1 WHEN SUM(Sales.LastYear) = 0 AND 
                      SUM(Sales.YearToDate) = 0 THEN 0 ELSE (SUM(Sales.YearToDate) - SUM(Sales.LastYearMonth)) / SUM(Sales.LastYear) END AS [Last Year Variance], 0 AS LPProfit, 
                      GETDATE() AS FECHA
FROM         dbo.vwSalesbyRep AS Sales
WHERE     (docdate BETWEEN DATEADD(YY, - 1, GETDATE()) AND GETDATE())
GROUP BY SlsRep, SlsName
ORDER BY SlsName

SELECT     dbo.gbkmut.res_id AS SlsRep, reshumres.fullname AS SlsName, dbo.gbkmut.docdate, YEAR(dbo.gbkmut.docdate) AS year, MONTH(dbo.gbkmut.docdate) AS month, 
                      CASE WHEN YEAR(gbkmut.docdate) = YEAR(getdate()) THEN CASE WHEN month(gbkmut.docdate) = month(getdate()) 
                      THEN - bdr_hfl ELSE 0 END ELSE 0 END AS MonthToDate, CASE WHEN YEAR(gbkmut.docdate) = YEAR(getdate()) THEN CASE WHEN month(gbkmut.docdate) 
                      <> month(getdate()) THEN - bdr_hfl ELSE 0 END ELSE 0 END AS YearToDate, CASE WHEN YEAR(gbkmut.docdate) <> YEAR(getdate()) 
                      THEN CASE WHEN month(gbkmut.docdate) = month(getdate()) THEN - bdr_hfl ELSE 0 END ELSE 0 END AS LastYearMonth, CASE WHEN YEAR(gbkmut.docdate) 
                      <> YEAR(getdate()) THEN CASE WHEN month(gbkmut.docdate) <> month(getdate()) THEN - bdr_hfl ELSE 0 END ELSE 0 END AS LastYear, 
                      CASE WHEN YEAR(gbkmut.docdate) = YEAR(getdate()) THEN CASE WHEN month(gbkmut.docdate) = month(getdate()) 
                      THEN '1 MonthToDate' ELSE '1  YearToDate' END ELSE CASE WHEN month(gbkmut.docdate) = month(getdate()) 
                      THEN '2 LastMonthToDate' ELSE '2  LastYearToDate' END END AS Period, - dbo.gbkmut.bdr_hfl AS Total
FROM         dbo.gbkmut LEFT OUTER JOIN
                      dbo.humres AS reshumres ON dbo.gbkmut.res_id = reshumres.res_id INNER JOIN
                      dbo.grtbk ON dbo.grtbk.reknr = dbo.gbkmut.reknr LEFT OUTER JOIN
                      dbo.Items ON dbo.gbkmut.artcode = dbo.Items.ItemCode
WHERE     (dbo.gbkmut.transtype IN ('N', 'C', 'P')) AND (dbo.gbkmut.ReminderCount <= 15) AND (dbo.grtbk.omzrek IN ('J')) AND (dbo.gbkmut.transsubtype <> 'X')

quote:

---

Originally posted by mjimenezh

Hi everyone, I have a problem and I don't found the solution, I need your help with a line of code, I have a sql 2008 view of sales and I need to change the attached line of code because the code shows information of the current day and I need the information but from yesterday, can you please help me? I don't understand this line of code, I need change the "getdate" command to '10/31/2013' and in the dateadd I suppose that the date is '10/31/2012'

WHERE (docdate BETWEEN DATEADD(YY, - 1, GETDATE()) AND GETDATE())

Thanks

---

quote:

---

Originally posted by visakh16

see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

http://visakhm.blogspot.in/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---