SQL Select statement with a Sub select question

CREATE TABLE [dbo].[MY_DATA](
	[TS_DT] [datetime] NULL,
	[TS_HRS] [int] NULL
) ON [PRIMARY]
GO
|2020-04-09 00:00:00.000|2|
|2020-04-03 00:00:00.000|4|
|2020-04-10 00:00:00.000|6|


CREATE TABLE [dbo].[MY_SCHEDULE](
	[TS_PD_CD] [nchar](10) NULL,
	[END_DT] [datetime] NULL,
	[START_DT] [datetime] NULL
) ON [PRIMARY]
GO

Data
|WKLY      |2020-03-31 00:00:00.000|2020-03-28 00:00:00.000|
|WKLY      |2020-04-03 00:00:00.000|2020-04-01 00:00:00.000|
|WKLY      |2020-04-10 00:00:00.000|2020-04-04 00:00:00.000|


--The code was only designed to get a start and end date from MY_SCHEDULE for one row
i.e start    |2020-03-31 00:00:00.000|
     end     2020-03-28 00:00:00.000|

Now the users want to put in a range of dates and i have changed the code for this.

The question i have is when you have a select statement with a select (distinct) and this
pulls three rows does the select row in the first table, go check all three values returned from the distinct).

It works but i just want to make sure this is valid.

I would have looped three times, i would read from MY_SCHEDULE do the select then read the next row.


DECLARE @TS_START_FROM DATETIME, @TS_END_TO DATETIME

SET @TS_START_FROM = '03/28/2020'
SET @TS_END_TO = '04/10/2020'
 ---This pulls in 04/03/2020 (4 hrs) and 04/09/2020 (2 hours)  
SELECT T.TS_DT, T.TS_HRS 
               FROM MY_DATA T 
              WHERE  T.TS_DT BETWEEN @TS_START_FROM AND @TS_END_TO
		      AND (  
					 T.TS_DT             NOT IN (SELECT DISTINCT END_DT FROM MY_SCHEDULE WHERE START_DT = @TS_START_FROM  OR END_DT = @TS_END_TO AND TS_PD_CD = 'WKLY') 
			
					 )
 It should only bring in data between the periods i.e  04/09/2020            


 --Change code to use >=
 DECLARE @TS_START_FROM DATETIME, @TS_END_TO DATETIME

SET @TS_START_FROM = '03/28/2020'
SET @TS_END_TO = '04/10/2020'
 
SELECT T.TS_DT, T.TS_HRS 
               FROM MY_DATA T 
              WHERE  T.TS_DT BETWEEN @TS_START_FROM AND @TS_END_TO
		      AND (  
				
				 T.TS_DT             NOT IN (SELECT DISTINCT END_DT FROM MY_SCHEDULE WHERE START_DT >= @TS_START_FROM  OR END_DT <= @TS_END_TO AND TS_PD_CD = 'WKLY') 
				)

It's not really clear what you are requesting. It seems like you want to get data from MyData that has ranges in MySchedule based on a start and end date. I'm only guessing below, but let me know if this is close or way off.

CREATE TABLE #MY_DATA(
	[TS_DT] [datetime] NULL,
	[TS_HRS] [int] NULL
) ON [PRIMARY]
GO
insert into #MY_DATA values
('2020-04-09 00:00:00.000',2),
('2020-04-03 00:00:00.000',4),
('2020-04-10 00:00:00.000',6)


CREATE TABLE #MY_SCHEDULE(
	[TS_PD_CD] [nchar](10) NULL,
	[END_DT] [datetime] NULL,
	[START_DT] [datetime] NULL
) ON [PRIMARY]
GO

insert into #MY_SCHEDULE values
('WKLY','2020-03-31 00:00:00.000','2020-03-28 00:00:00.000'),
('WKLY','2020-04-03 00:00:00.000','2020-04-01 00:00:00.000'),
('WKLY','2020-04-10 00:00:00.000','2020-04-04 00:00:00.000')


DECLARE @TS_START_FROM DATETIME, @TS_END_TO DATETIME

SET @TS_START_FROM = '03/28/2020'
SET @TS_END_TO = '04/10/2020'
 ---This pulls in 04/03/2020 (4 hrs) and 04/09/2020 (2 hours)  
SELECT T.TS_DT, T.TS_HRS 
 FROM #MY_DATA T 
	join #MY_SCHEDULE s
		on t.ts_dt between s.Start_Dt and s.end_dt
		and s.TS_PD_CD = 'WKLY'
 where t.TS_DT between @TS_START_FROM and @TS_END_TO

hi

hope this helps :slight_smile:

I tried to do this .. Please see if this is OK

Please click arrow to the left for .. Create Data

Create Data
CREATE TABLE [dbo].[#MY_DATA](
	[TS_DT] [datetime] NULL,
	[TS_HRS] [int] NULL
) ON [PRIMARY]
GO
insert into #My_Data select '2020-04-09 00:00:00.000',2
insert into #My_Data select '2020-04-03 00:00:00.000',4
insert into #My_Data select '2020-04-10 00:00:00.000',6
go 

select 'My_data',* from #My_Data 


CREATE TABLE [dbo].[#MY_SCHEDULE](
	[TS_PD_CD] [nchar](10) NULL,
	[END_DT] [datetime] NULL,
	[START_DT] [datetime] NULL
) ON [PRIMARY]
GO

insert into #My_Schedule select 'WKLY','2020-03-31 00:00:00.000','2020-03-28 00:00:00.000'
insert into #My_Schedule select 'WKLY','2020-04-03 00:00:00.000','2020-04-01 00:00:00.000'
insert into #My_Schedule select 'WKLY','2020-04-10 00:00:00.000','2020-04-04 00:00:00.000'
go 

select 'My_Schedule',* from #My_Schedule 
go
; with cte as 
( 
select min([START_DT]) as min_dt,  max([END_DT]) as max_dt 
    from #My_Schedule
)
select * 
  from #My_Data , cte 
      where [TS_DT] between min_dt and max_dt 
go 

image

The data to be returned shouldn't include 4/3/2020.

The question is when the (select distinct runs and it contains more than one row of data).
Is both of the records being checked) against the outer sql statement I am used to a distinct statement only giving you run one row.

hi Tracey

Not able to understand ..
Could you please help us !! :slight_smile:

can you please explain !! EXAMPLE ..
image

This is the data
|2020-04-09 00:00:00.000|2|
|2020-04-03 00:00:00.000|4|
|2020-04-10 00:00:00.000|6|

Schedule Data
|WKLY |2020-03-31 00:00:00.000|2020-03-28 00:00:00.000|
|WKLY |2020-04-03 00:00:00.000|2020-04-01 00:00:00.000|
|WKLY |2020-04-10 00:00:00.000|2020-04-04 00:00:00.000|

This line
SELECT DISTINCT END_DT FROM MY_SCHEDULE WHERE START_DT = @TS_START_FROM OR END_DT = @TS_END_TO AND TS_PD_CD = 'WKLY')
pulls only
|WKLY |2020-03-31 00:00:00.000|2020-03-28 00:00:00.000|
|WKLY |2020-04-10 00:00:00.000|2020-04-04 00:00:00.000|

New code pulls all the three dates
SELECT DISTINCT END_DT FROM MY_SCHEDULE WHERE START_DT >= @TS_START_FROM OR END_DT <= @TS_END_TO AND TS_PD_CD = 'WKLY')
|WKLY |2020-03-31 00:00:00.000|2020-03-28 00:00:00.000|
|WKLY |2020-04-03 00:00:00.000|2020-04-01 00:00:00.000|
|WKLY |2020-04-10 00:00:00.000|2020-04-04 00:00:00.000|

So when it gets to the first AND It says is not in the dates, but it is so that record is not pulled to be updated.

So the question is for every select record in the MY DATA does it check every condition pulled from the sub select

|2020-04-09 00:00:00.000|2| When i read this record from MY DATA does it check
|WKLY |2020-03-31 00:00:00.000|2020-03-28 00:00:00.000|
and check
|WKLY |2020-04-03 00:00:00.000|2020-04-01 00:00:00.000|
and check
|WKLY |2020-04-10 00:00:00.000|2020-04-04 00:00:00.000|

|2020-04-03 00:00:00.000|4|
|WKLY |2020-03-31 00:00:00.000|2020-03-28 00:00:00.000|
and check
|WKLY |2020-04-03 00:00:00.000|2020-04-01 00:00:00.000|
and check
|WKLY |2020-04-10 00:00:00.000|2020-04-04 00:00:00.000|

|2020-04-10 00:00:00.000|6|
|WKLY |2020-03-31 00:00:00.000|2020-03-28 00:00:00.000|
and check
|WKLY |2020-04-03 00:00:00.000|2020-04-01 00:00:00.000|
and check
|WKLY |2020-04-10 00:00:00.000|2020-04-04 00:00:00.000|

It does appear to work with my new code, but i wanted to determine if this is what the sql really does when you have a sub select in the statement