SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 FITER DATE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/01/2013 :  20:16:49  Show Profile  Reply with Quote
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

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/01/2013 :  20:22:00  Show Profile  Reply with Quote
This is the full code :

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
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/01/2013 :  20:30:10  Show Profile  Reply with Quote
I don't know if the problem comes from the original view that i'm accessing, this is the code :
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')
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 11/02/2013 :  02:21:45  Show Profile  Reply with Quote
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


For fixed date, try this:
where docdate>='20121101' and docdate<'20131101'

Using getdate, try this:
where docdate>=dateadd(yy,-1,dateadd(dd,-1,getdate()))
and docdate<dateadd(dd,-1,getdate())

This will get you same period, as the fixed date
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/02/2013 :  13:30:39  Show Profile  Reply with Quote
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
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/04/2013 :  11:58:28  Show Profile  Reply with Quote
Thank you for your valuable help


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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000