February 3, 2016 at 11:12 pm
Ok this one's got me scratching my head pretty good. I created a temp table with one field containing values 1 to 24 so I could left join it to a date field in another table. (The purpose of this was so I could add a timeseries field to my data that wouldn't have any hour gaps in a 24 hour period, so I could use it for forecasting.)
However as you can see in the attached QueryResults.xlsx, the query isn't returning all of the values from the #temphours table. It's only returning the matching values in the AdmitDateTime field in the listdetail table. So it's acting like an inner join instead of a left join.
I've also attached sample data from the listdetail table in the SampleData.xlsx file. Any help is greatly appreciated.
Query:
CREATE TABLE #temphours
(hournumber int)
INSERT INTO #temphours (hournumber)
VALUES
('1'),
('2' ),
('3'),
('4'),
('5'),
('6' ),
('7');
SELECT
hd.AdmitDateTime,
t.hournumber
FROM #temphours t
left join listDetail hd on t.hournumber = DATEPART(hh,hd.AdmitDateTime)
where AdmitDateTime between '2013-01-01' and '2013-01-02'
order by t.hournumber asc
February 3, 2016 at 11:39 pm
Put the WHERE clause into the join.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 4, 2016 at 2:02 am
Better still, write it in a way which is most likely to be performant as well as correct. It's very little extra work:
-- SARGable Solution
DECLARE @StartDate DATETIME = '20130101'; -- Unambiguous date format
-- A CTE with 24 rows, one for each hour, with the start datetime for each
-- hour precalculated
WITH TempHours AS (
SELECT hournumber = n, StartDate = DATEADD(HOUR,n-1,@StartDate)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) d (n)
)
SELECT t.hournumber, hd.AdmitDateTime
FROM TempHours t
LEFT JOIN #listDetail hd
ON hd.AdmitDateTime >= t.StartDate
AND hd.AdmitDateTime < DATEADD(HOUR,1,t.StartDate)
ORDER BY t.hournumber;
-- Sample data script
DROP TABLE #listDetail;
SELECT AdmitDateTime
INTO #listDetail
FROM (VALUES
(CAST('1/1/13 0:30' AS DATETIME)),
('1/1/13 0:41'),('1/1/13 2:00'),('1/1/13 2:01'),('1/1/13 2:37'),('1/1/13 3:59'),
('1/1/13 4:20'),('1/1/13 4:43'),('1/1/13 4:52'),('1/1/13 6:10'),('1/1/13 6:27'),
('1/1/13 6:45'),('1/1/13 7:59'),('1/1/13 8:34'),('1/1/13 8:37'),('1/1/13 9:03'),
('1/1/13 10:26'),('1/1/13 11:34'),('1/1/13 11:50'),('1/1/13 12:20'),('1/1/13 13:00'),
('1/1/13 14:30'),('1/1/13 15:03'),('1/1/13 16:42'),('1/1/13 17:32'),('1/1/13 17:40'),
('1/1/13 17:46'),('1/1/13 18:15'),('1/1/13 18:36'),('1/1/13 19:10'),('1/1/13 19:26'),
('1/1/13 19:59'),('1/1/13 20:00'),('1/1/13 20:25'),('1/1/13 20:45'),('1/1/13 21:00'),
('1/1/13 22:30'),('1/1/13 22:39'),('1/1/13 23:01'),('1/1/13 23:12'),('1/1/13 23:25'),
('2/1/13 0:41'),('2/1/13 2:00'),('2/1/13 2:01'),('2/1/13 2:37'),('2/1/13 3:59'),
('2/1/13 4:20'),('2/1/13 4:43'),('2/1/13 4:52'),('2/1/13 6:10'),('2/1/13 6:27'),
('2/1/13 6:45'),('2/1/13 7:59'),('2/1/13 8:34'),('2/1/13 8:37'),('2/1/13 9:03'),
('2/1/12 0:41'),('2/1/12 2:00'),('2/1/12 2:01'),('2/1/12 2:37'),('2/1/12 3:59'),
('2/1/12 4:20'),('2/1/12 4:43'),('2/1/12 4:52'),('2/1/12 6:10'),('2/1/12 6:27'),
('2/1/12 6:45'),('2/1/12 7:59'),('2/1/12 8:34'),('2/1/12 8:37'),('2/1/12 9:03')
) d (AdmitDateTime);
CREATE CLUSTERED INDEX cx_AdmitDateTime ON #listDetail (AdmitDateTime);
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 4, 2016 at 12:48 pm
You got your solution, but I like to explain why your solution failed.
The LEFT JOIN you used (in itself, without the selection on date) will produce at least one row for every row in the temp table. If there is no match, then that row will have NULL values for all columns originating from the ListDetail table.
Adding the WHERE clause will, for those rows, result in a comparison of a NULL value to the specified date range. Such a comparison for NULL always evaluates to Unknown, and an Unknown condition in a WHERE will result in the row not being included.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply