locked
Filter control based on selected datetime variables RRS feed

  • Question

  • User1909155429 posted

    I want to have a list of values that represent the previous : week,fortnight,month plus all rows  pass each variable to SQL procedure then populate a control with the returned dataset for that particular variable. The select statement suppose to sum the totals for a particular datetime period between current datetime and last week,fortnight,month or all columns where vendorid is present as follows.

    SELECT UserID, SUM(Total) AS TOTAL
    FROM Invoices
    WHERE (UserID = @VENDORID) and (ORDERDATE   expression) GROUP BY UserID

    Database data

                                               VENDORID                                                                                                                                              ORDERDATE

    1551 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 03/05/2020 23:37:00                             
    1552 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 03/05/2020 23:39:00
    1553 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 03/05/2020 23:47:00
    1554 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 04/05/2020 19:40:00
    1555 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 04/05/2020 19:45:00
    1556 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 04/05/2020 19:54:00
    1557 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 04/05/2020 20:20:00
    1558 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 04/05/2020 20:48:00
    1559 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 04/05/2020 21:01:00
    1560 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 04/05/2020 21:13:00
    1561 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 05/05/2020 12:45:00
    1562 55a3b0a3-b994-4f2e-9b67-279fdf8b8092 05/05/2020 20:26:00
    1563 1440ac1a-95cd-4f3a-8d93-42c72cc2e254 21/05/2020 20:21:00
    Friday, May 22, 2020 9:36 PM

Answers

All replies

  • User475983607 posted

    I think you are looking for BETWEEN;https://www.w3schools.com/sql/sql_between.asp.

    SELECT UserID, SUM(Total) AS TOTAL
    FROM Invoices
    WHERE (UserID = @VENDORID) 
    	AND ORDERDATE BETWEEN @startDate AND @endDate 
    GROUP BY UserID

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 22, 2020 9:43 PM
  • User1909155429 posted

    I have had a job trying to make it work with the right data types? . i wanted to add the Orderdate column to the select and sort on it also but it would not allow me? is there another method that will allow you to show other columns. it seems restrictive using the group clause.

    Thanks

    Sunday, May 24, 2020 5:18 PM
  • User475983607 posted

    I have had a job trying to make it work with the right data types? . i wanted to add the Orderdate column to the select and sort on it also but it would not allow me? is there another method that will allow you to show other columns. it seems restrictive using the group clause.

    I don't understand what you are asking.  You do not want a SUM?

    Sunday, May 24, 2020 6:00 PM
  • User1909155429 posted

    Yes a sum of the Total column values using date parameter in the where clause

    Monday, May 25, 2020 10:22 PM