asked
How do I create a row for each hour between a time range
I need to create a census table where I have a row for each hour between start and end times. I created a temporary table to get the start and end time and then a cte with a union to get each hour between those times. but I get separate queries as the result. please help below is my script: USE CLARITY GO /******* GET THE LIST OF ACCOUNTS WITH ADMIT AND DISCHARGE TIME**************/ DECLARE @i INT; DECLARE @NUMROWS AS INT; DECLARE @ACCOUNTLST AS TABLE ( IDX INT PRIMARY KEY IDENTITY(1,1) ,HSP_ACCOUNT_ID INT ,HSP_ADMSN_TIME DATETIME ,HSP_DISCHRG_TIME DATETIME ); INSERT @ACCOUNTLST SELECT HSP_ACCOUNT_ID ,HOSP_ADMSN_TIME ,HOSP_DISCHRG_TIME FROM PAT_ENC WHERE HOSP_ADMSN_TIME IS NOT NULL AND HSP_ACCOUNT_ID IS NOT NULL AND HOSP_DISCHRG_TIME IS NOT NULL --SELECT * --FROM @ACCOUNTLST SET @i =1 SET @NUMROWS =( SELECT COUNT(*) FROM @ACCOUNTLST ) IF @NUMROWS> 0 WHILE (@i <= ( SELECT MAX(IDX) FROM @ACCOUNTLST ) ) BEGIN ;WITH CTE AS ( SELECT HSP_ACCOUNT_ID ,HSP_ADMSN_TIME 'CENSUSTIME' ,HSP_DISCHRG_TIME 'ENDTIME' FROM @ACCOUNTLST WHERE IDX=@i UNION ALL --HSP_ACCOUNT_ID SELECT HSP_ACCOUNT_ID ,DATEADD(HH,1,CENSUSTIME) ,ENDTIME FROM CTE WHERE CENSUSTIME <=ENDTIME ) SELECT * FROM CTE OPTION (MAXRECURSION 0) SET @i=@i+1 END
It is not at all clear what you are trying to do here. You really should try to avoid looping in t-sql as it is horribly inefficient. In the code you posted you have an infinite loop. Perhaps if you can some table definitions, sample data and what you are trying to do we can help. I suspect however that the answer to your problem lies with needing to use a numbers or tally table instead of looping like this.
There are several objects that are handy for lots of different tasks, that you should consider adding as permanent fixtures to your databases. Two of these include a "calendar" table (called a "date dimension" in data warehousing) and a closely-related concept called an "auxiliary table of numbers" (also known as a tally table). A calendar table has one row per date and maybe ten or twenty years worth of data depending on what your local needs are. You can have columns for things like month, day of week, public holiday, week number of year, financial year, fiscal year, lunar cycles... really, the sky's the limit and you don't have to try and hack these sometimes complex concepts into your code (try writing code to pick the start of Easter for example, versus simply having it identified in a Calendar table). The [tally table][1] is even simpler and serves even more purposes. Click the link for Moden's seminal article. Now, if you create your calendar and tally tables it becomes a hellishly simple task. And fast. Boy is it fast. The following code gives you a list of every hour between 1 July 2015 and midnight last night. DECLARE @start_date as datetime = '20150701'; DECLARE @end_date as datetime = dateadd(dd,0,datediff(dd,0,GETDATE()));--midnight last night SELECT dateadd(hh,t.N - 1,cal.census_dttm) as census_hour FROM dbo.CALENDAR cal cross join dbo.TALLY t WHERE t.N < 25 and cal.census_dttm >= @start_date and cal.census_dttm < @end_date ORDER BY census_hour The output looks something like this: 2015-07-01 00:00:00.000 2015-07-01 01:00:00.000 2015-07-01 02:00:00.000 2015-07-01 03:00:00.000 2015-07-01 04:00:00.000 2015-07-01 05:00:00.000 2015-07-01 06:00:00.000 . . . [1]:
http://www.sqlservercentral.com/articles/T-SQL/62867/
20 People are following this question.
