See result for every 7 days

1

I'm trying to find invoices in an invoice table but I need only invoices done on saturdays. Is there a way to select specific days between date range. like if my starting date is 2019-01-05 which is a saturday, and to see only the next saturdays so basically every 7 days?

2 
WHERE
    DATEDIFF(DAY, '20190105',invoiceDate)%7 = 0
    AND invoiceDate >= '20190105'
3

I would build a table of dates - and join to that table. This keeps the query sargable where it can utilize an index if one exists.

Declare @startDate date = '2019-01-05'
      , @endDate date = '2020-01-10';

   With t(n)
     As (
 Select t.n 
   From (
 Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
      , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
      , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
      , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
      , dates (inputDate)
     As (
 Select Top ((datediff(day, @startDate, @endDate) + 1) / 7 + 1) 
        dateadd(day, checksum(row_number() over(Order By @@spid) - 1) * 7, @startDate) 
   From t t1, t t2, t t3, t t4
        )
 Select *
      , datename(weekday, inputDate)
   From dates                   dt
  Inner Join invoiceTable       it On it.InvoiceDate = dt.inputDate;

This is an example - you can build your list of Saturdays - but this requires the InvoiceDate column to be defined as a DATE data type. If it is defined as a datetime and contains a time component other than midnight - then you need to modify the join.

4

I'd also avoid using a function in the WHERE, and use a join instead, since SQL is optimized for joins and there is that small chance of being able to use an index seek (although I do think that 7,000 yrs worth of tally numbers is extreme overkill).

I also added logic to insure that the starting date is a Saturday, and if not adjust it, likewise for the ending date, while also allowing an ending date of null meaning "current date".

Finally, once you get the starting Saturday, you can just add 7 to get each next Saturday, you don't need a datediff calc every time.

Here's my corresponding code:

DECLARE @start_date date
DECLARE @end_date date /*NULL=thru current Saturday*/

SET @start_date = '20190105'
SET @end_date = NULL 

--------------------------------------------------------------------------------

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
),
cte_verify_adjust_dates AS (
    SELECT 
        /* if start_date is not a Saturday, back up to the immediately previous Saturday */
        DATEADD(DAY, -DATEDIFF(DAY, '19000106', @start_date) % 7, @start_date) AS start_date,
        /* if end_date is not a Saturday, move forward to the very next Saturday */
        DATEADD(DAY, -DATEDIFF(DAY, '19000106', end_date_base) % 7, end_date_base) AS end_date
    FROM (
        SELECT DATEADD(DAY, 6, ISNULL(@end_date, CAST(GETDATE() AS date))) AS end_date_base
    ) AS end_date_base
)
SELECT i.*
FROM dbo.invoices i
INNER JOIN (
    SELECT DATEADD(DAY, weeks.number * 7, start_date) AS saturday
    FROM cte_verify_adjust_dates
    INNER JOIN cte_tally10K weeks ON weeks.number BETWEEN 0 AND DATEDIFF(DAY, vad.start_date, vad.end_date) / 7
) AS saturdays ON saturdays.saturday = i.invoice_date
ORDER BY i.invoice_date
5

What data type is your invoice date column and you only want the next Saturday or all Saturdays?

6

Good day yosiasz

the type is date in that format : YYYY-MM-DD HH:MM:SS.****(milliseconds)

and it's all saturday withing a specifi range

thank you

7

This looks like the column is defined as a datetime data type - you can execute sp_help on the invoice table to get the actual data types for the columns.

If the values in that column contain times - for example: 2020-06-22 08:30:30.333 then you need to account for that in the join statement.

The way to do that is:

 Inner Join invoiceTable       it On it.InvoiceDate >= dt.inputDate
                                 And it.InvoideDate <  dateadd(day, 1, dt.inputDate

You would also modify the following:

Declare @startDate datetime = '2019-01-05'
      , @endDate datetime = '2020-01-10';
8

Just to be clear - my solution is not generating 7000 years worth of dates. It allows for generating that many - but will only generate the number of rows needed.

image