Data Problem

Question

Data Problem

immad

Valued Member

Points: 67
More actions
March 21, 2023 at 1:14 pm
Go to Answer
#4163271

This is the Data
ID ----- Date From ------ Date To -------
2 ----- 2020-01-01 ----- 2020-01-31 -------
2 ----- 2020-02-01 ----- 2020-02-28 -------
2 ----- 2020-04-01 ----- 2020-04-30 -------
I give parameters in Query . Select * from TableName where Date From >= '2020-04-01' and Date To <= '2020-04-28'
I didn't get the data because date not available in that dates if data show null then its should give that date who is avaiable in that data
like 2020-04-01 ----- 2020-04-30
please help me out
Thank You.
Jeff Moden

SSC Guru

Points: 1004340
More actions
March 21, 2023 at 3:28 pm
Answer
#4163335
Ok... first off, your column naming structure flat out isn't going to work. It's a worst practice to include spaces in any object names including column names.
Second, if you want coded answers that actually work, take the little bit of extra time to make "Readily Consumable Data" to help those that would help you. Most folks jump on problems where such "Readily Consumable Data" is available... even if it's just 3 rows like what you posted.
Here's how it could have been posted. I've also removed the spaces from the column names. Change them to suit yourself. I hate using brackets on names. 😀
```
   DROP TABLE IF EXISTS #TestTable;
GO
 CREATE TABLE #TestTable
        (
         ID INT
        ,DateFrom DATE
        ,DateTo   DATE
        )
 SELECT v.ID
        ,DateFrom = CONVERT(DATE,v.DateFrom)
        ,DateTo   = CONVERT(DATE,v.Dateto)
   INTO #TestTable
   FROM (VALUES
         (1,'2020-01-01','2020-01-31')
        ,(2,'2020-02-01','2020-02-28')
        ,(3,'2020-04-01','2020-04-30')
        ) v (ID,DateFrom,DateTo)
;
```
Now that we have that, it's easy to demonstrate a working solution. Notice that things will seem entirely backwards where we compare a start date to an end date (DateTo) in the table and compare an end date to a start date (DateFrom).
```
 SELECT *
   FROM #TestTable
  WHERE '2020-04-01' <= DateTo
    AND '2020-04-28' >= DateFrom
;
```
Result:
Please see the following article for how and why the code above works. It's one of those things that every SQL Developer will run across.
https://www.sqlservercentral.com/articles/finding-%e2%80%9cactive%e2%80%9d-rows-for-the-previous-month-sql-spackle-1

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

where Date From >=  '2020-04-01'  and  Date To <= '2020-04-28'

There is no data in your sample that meets that requirement.

In

2  ----- 2020-04-01 ----- 2020-04-30

Date From is >= '2020-04-01', but Date To ('2020-04-30') is greater than '2020-04-28'

Ed B Hall of Fame Points: 3666 More actions · Answer 2

Edit. Solution above

This reply was modified 1 year, 8 months ago by Ed B.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

ratbak wrote:

where Date From >=  '2020-04-01'  and  Date To <= '2020-04-28'
There is no data in your sample that meets that requirement.
In
2  ----- 2020-04-01 ----- 2020-04-30
Date From is >= '2020-04-01', but Date To ('2020-04-30') is greater than '2020-04-28'

I think this is an "overlapping ranges" issue. See my post above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)