Date Dimension gives date one day later than start date

Transact-SQL
1

Hi

I have a query that uses an inline view to pull a particular episode of data, that has MIN Date and then a join from main section of query to give a range of weekly dates from that point. For some reason the outer query is returning the day after date from the date dimension table but I want it to start at the Min Date for that episode.

select	q.PersonID
	, dd.date AS [Date]
	, q.min_date
	, q.Data1
	, q.data2
	
FROM ( SELECT T1.P_ID
		, T1.Data1
		, t2.Data2
		, MIN(T1.WeekComm2) min_date
FROM table1 T1 INNER JOIN table2 T2 ON T1.ID = T2.ID 
group by T1.P_ID, T1.Data1, T2.Data2
) q 
CROSS JOIN DateDimension dd
INNER JOIN TablePerson tp ON tp.ID = q.P_ID
WHERE dd.date BETWEEN q.min_date AND GETDATE()

--Output


Person    Date         Min_Date          Data1     Data2
======   ==========    ==========        =====     ======
 1001	 2020-03-10		2020-03-09			x		y
1001	 2020-03-17		2020-03-09			x		y

Should be like this:

Person    Date         Min_Date          Data1     Data2
======   ==========    ==========        =====     ======
 1001	 2020-03-09		2020-03-09			x		y
1001	 2020-03-16		2020-03-09			x		y

So the Date should be the same as Min-Date to start, it will iterate one week at a time as the Min-Date is one week apart for each episode, so determining every week start date is done, but is one day ahead of the actual start date.

Min_date is a varchar if that might be the issue, have tried cast as date but makes so difference.

When I look at the full range of dates in my query it starts one day later than min date and finishes one day early from today's date.

Any ideas how to fix?

Thanks

Andrew

2

I suspect the behavior has to do with the join conditions on the inner query. But, you have not shown those join conditions (table1 T1 INNER JOIN table2 T2 ON ????)

3

I have included the mock up join for the inner query.

4

hi

is it possible to get any data for this !!!

data is very complex ( there can be many many scenarios )

but
data for this particular scenario
.. would help a lot of get to the bottom of the issue!!!

:slight_smile:
:+1:

5

It's nothing more complex than the above, a range of dates per week starting at min-date? So if starting at 09 Mar 2020 up to end of march I'd be looking for above, but instead I am getting:

It's one day ahead of where I want to be.

6

i was asking for sample data from the source tables
table1 , table2 ,DateDimension , TablePerson

somewhere in the joins "data" does not match ...
you can start debugging !!! .. by using very small data !!!

7

Sorry I found the issue, was using dd.day of week wrong, it was set on day 3 not 2 and never included this.

WHERE dd.date BETWEEN q.min_date AND GETDATE()
**and dd.DayOfWeek = 3**
8

oh ok

njoy

has happened to me .. a million times !!

i mean .. somethings wrong .. and it can be so many things !!
i have to figure it out