Post reply

Record not showing up for same day or previous day

  • July 31, 2015 at 3:57 am

    #319425

    Hi,

    I have got a query and it works ok so far. I am stuck when someone asks me to provide for last week or yesterday date entered for forecast. Could please someone help me?

    It works fine without Where but when I add

    Where SalesForecastLine.DateEntered = dateadd(dd, datediff(dd, 0, getdate()), 0) it shows nothing.

    Thanks

    SELECT CustomerAccount.AccountNumber, Company.CompanyName,

    SalesForecast.ForecastDescription,Product.ProductName, Product.ProductDescription,

    SalesForecastLine.ForecastQuantity,

    SalesForecastLine.ProductValue,

    SalesForecastLine.DateEntered

    , SalesForecast.ExchangeRate

    FROM Company INNER JOIN

    CustomerAccount ON Company.CompanyID = CustomerAccount.CompanyID INNER JOIN

    SalesForecast ON CustomerAccount.CustomerAccountID = SalesForecast.CustomerAccountID INNER JOIN

    SalesForecastLine ON SalesForecast.SalesForecastID = SalesForecastLine.SalesForecastID INNER JOIN

    SalesLine ON SalesForecastLine.SalesForecastLineID = SalesLine.SalesForecastLineID INNER JOIN

    Product ON SalesLine.ProductID = Product.ProductID

    Where SalesForecastLine.DateEntered = dateadd(dd, datediff(dd, 0, getdate()), 0)

  • July 31, 2015 at 4:15 am

    #1816383

    this should help explain

    http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 31, 2015 at 4:26 am

    #1816384

    J Livingston SQL (7/31/2015)

    this should help explain

    http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

    Thanks Mr Livingston. I got the link working now.

    My query runs ok without any error but it does not show any data.

  • July 31, 2015 at 4:31 am

    #1816385

    gazy007 (7/31/2015)

    J Livingston SQL (7/31/2015)

    this should help explain

    http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

    Thanks Mr Livingston. I got the link working now.

    My query runs ok without any error but it does not show any data.

    sorry...have edited the link in original post

    select dateadd(dd, datediff(dd, 0, getdate()), 0) ...this code returns today...alter it accordingly for the date you want

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 31, 2015 at 4:34 am

    #1816386

    I have used this

    dateadd(dd, datediff(dd, 0, getdate()), 0) and other but somehow it does not display the data but it shows the column.

    when I use

    DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), - 30)) i get three lines.

    it is me .

  • July 31, 2015 at 4:41 am

    #1816387

    gazy007 (7/31/2015)

    I have used this

    dateadd(dd, datediff(dd, 0, getdate()), 0) and other but somehow it does not display the data but it shows the column.

    when I use

    DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), - 30)) i get three lines.

    not sure if this is another question or that you have now solved the problem?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 31, 2015 at 4:53 am

    #1816389

    You have solved my problem Sir. The user gave me half information as well to compile the report but thanks to you I have got my answer I needed.

    Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply