How to get data between two dates

Transact-SQL
1

hello i have a query as below

SELECT dbo.Teile_Bedarf.Teil_Nummer, dbo.Teile_Bedarf.Bedarfs_Typ, dbo.Teile_Bedarf.Menge_Bedarf, dbo.Teile_Bedarf.Datum_Bedarf,
dbo.JISSUPP_Kalender.Kalender_Flag
FROM dbo.Teile_Bedarf LEFT OUTER JOIN
dbo.JISSUPP_Kalender ON dbo.Teile_Bedarf.Datum_Bedarf = dbo.JISSUPP_Kalender.Kalender_Tag
WHERE (dbo.Teile_Bedarf.Bedarfs_Typ = '10') AND (dbo.Teile_Bedarf.Teil_Nummer LIKE '[^0]%') AND (dbo.JISSUPP_Kalender.Kalender_Flag <> 'F')

and i want all of the data between yesterday, today and tomorrow, so i wrote it like below.
SELECT Teil_Nummer, Bedarfs_Typ, Menge_Bedarf, Datum_Bedarf, Kalender_Flag
FROM dbo.[v_Bedarf_D+2]
WHERE (Datum_Bedarf <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) + 1) AND (Datum_Bedarf >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) - 1)

but when tomorrow is saturday and the day after tomorrow is sunday then DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) + 1) is tomorrow and there is no data for tomorrow in my table instead of tomorrow i want the data for monday
i want the data from yesterday, today and tomorrow and when tomorrow is holiday i want the data from the day after tomorrow
do you know how to write this
i have all of the dates in datum_bedarf excludes holidays
thank you

2

If you want to take care of just weekends, the following will be sufficient.

Datum_Bedarf <= 
  DATEADD(dd, CASE WHEN DATEDIFF(dd,'19000101',GETDATE())%7=4 THEN 3 ELSE 1 END, GETDATE())
AND Datum_Bedarf >= 
  DATEADD(dd, CASE WHEN DATEDIFF(dd,'19000101',GETDATE())%7=0 THEN -3 ELSE -1 END, GETDATE())

If you also want to consider holidays that are not weekends, then you will need a calendar table. You said "i have all of the dates in datum_bedarf excludes holidays". Is datum_bedarf a calendar table, or isit a column in v_Bedarf_D+2 ? If it is a calendar table you can make use of that table.

3

hello

dbo.JISSUPP_Kalender is a calendar table
do you kn ow how i can work with this table now?
thank you

4

Kalender_Flag <> 'F' is all of the dates those are holidays

5

F=holidays in this table and L is for normal dates

6

Kalender_Tag is the date of my calendar table

7 
SELECT  Teil_Nummer ,
        Bedarfs_Typ ,
        Menge_Bedarf ,
        Datum_Bedarf ,
        Kalender_Flag
FROM    dbo.[v_Bedarf_D+2]
WHERE 
			v.Datum_Bedarf <= 
			(
				SELECT TOP (1) Kalender_Tag FROM dbo.JISSUPP_Kalender
				WHERE Kalender_Flag <> 'F'
				AND Kalender_Tag > GETDATE()
				ORDER BY Kalender_Tag ASC 
			)
			AND v.Datum_Bedarf >= 
			(
				SELECT TOP (1) Kalender_Tag FROM dbo.JISSUPP_Kalender
				WHERE Kalender_Flag <> 'F'
				AND Kalender_Tag < CAST(GETDATE() AS DATE)
				ORDER BY Kalender_Tag DESC 
			)
8

Hello
Thank you very much
And this code is for today, tomorrow and yesterday or i am wrong?

9

Yes, it will pick up rows for which v.Datum_Bedarf is yesterday, today or tomorrow, not counting holidays.

If Datum_Bedarf has a time portion to it, this may not work as you expect it. If that is the case change the two instances of v.Datum_Bedarf in the WHERE clause to CAST(v.Datum_Bedarf as DATE).

Adding the CAST, while simple, is not the most efficient in terms of query performance. So if Datum_Bedarf is already of DATE data type, or if it is guaranteed to have no time portion, don't cast it. If Datum_Bedarf does have time portion, and if the performance is poor after you include the cast, reply back - the WHERE clause can be rewritten to be more efficient.

10

thank you very much
it works great
i want also to get data for yesterday,today, tomorrow and the day after tomorrow
again yesterday,today, tomorrow and two days after tomorrow
yesterday,today, tomorrow and three days after tomorrow
which change should i do to get data between these dates?
thank you in advance

11

hello

i wrote the following code but the result is not correct
i don't know why can you help please?
and also this view doesn't work in design
thank you

SELECT Teil_Nummer, Bedarfs_Typ, Menge_Bedarf, Datum_Bedarf, Kalender_Flag
FROM dbo.v_Bedarf
WHERE (Datum_Bedarf >=
(SELECT TOP (1) Kalender_Tag
FROM dbo.JISSUPP_Kalender AS JISSUPP_Kalender_1
WHERE (Kalender_Flag <> 'F') AND (Kalender_Tag < CAST(GETDATE() AS DATE))
ORDER BY Kalender_Tag DESC)) AND (Datum_Bedarf <=
(SELECT Kalender_Tag
FROM (SELECT Kalender_Tag,Kalender_Flag,ROW_NUMBER()
OVER(ORDER BY Kalender_Tag asc) as row from
dbo.JISSUPP_Kalender where (Kalender_Flag <> 'F') AND (Kalender_Tag > CAST(GETDATE() AS DATE))) t1
WHERE (t1.row=2)))

12

I don't see anything obviously wrong with your query. If you can run the subqueries by themselves and see what dates they return, that would help you debug the issue. For example, what is the date returned from this query?

SELECT  Kalender_Tag
FROM    ( SELECT    Kalender_Tag ,
                    Kalender_Flag ,
                    ROW_NUMBER() OVER ( ORDER BY Kalender_Tag ASC ) AS row
          FROM      dbo.JISSUPP_Kalender
          WHERE     ( Kalender_Flag <> 'F' )
                    AND ( Kalender_Tag > CAST(GETDATE() AS DATE) )
        ) t1
WHERE   ( t1.row = 2 )