Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    2

    Question Unanswered: Create A Stored Procedure to get total last 7 day sales for each day

    Table name: ONIV
    Columns:
    Date: DocDate
    Sales: DocTotal

    I want to populate a line graph which would show the 7 days of the week on X-Axis and the sale on the Y-Axis.Click image for larger version. 

Name:	IMG_20150227_101700.jpg 
Views:	2 
Size:	83.9 KB 
ID:	16227
    kindly help me with that.

    Thanking you in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What have you tried so far?
    What reporting tool are you using for your graph?

    Query to give the last 7 full days:
    Code:
    ; WITH last_7_days (the_date) AS (
      SELECT DateAdd(dd, -1, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -2, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -3, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -4, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -5, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -6, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -7, Cast(Current_Timestamp As date))
    )
    SELECT the_date
    FROM   last_7_days
    ORDER
        BY the_date
    ;
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2015
    Posts
    2
    Quote Originally Posted by gvee View Post
    What have you tried so far?
    What reporting tool are you using for your graph?

    Query to give the last 7 full days:
    Code:
    ; WITH last_7_days (the_date) AS (
      SELECT DateAdd(dd, -1, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -2, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -3, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -4, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -5, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -6, Cast(Current_Timestamp As date))
      UNION ALL SELECT DateAdd(dd, -7, Cast(Current_Timestamp As date))
    )
    SELECT the_date
    FROM   last_7_days
    ORDER
        BY the_date
    ;
    I have tried this code
    [
    select convert(char(10), DocDate, 120), sum(DocTotal)
    from ONIV
    where datediff(day, DocDate, getdate()) < 7
    group by convert(char(10), DocDate, 120)
    ]

    But I am not getting what i want.
    Also I am using Crystal reports for reporting

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •