Select statment

I have a table that records times of events, I want to create a view that retrieves these records and display new records for missing hours in original data.

Example of the table:

2011-01-01 08:00
2011-01-01 09:15
2011-01-01 11:30
2011-01-01 14:00

The required view should return the following records:

2011-01-01 08:00
2011-01-01 09:15
2011-01-01 10:00
2011-01-01 11:30
2011-01-01 12:00
2011-01-01 13:00
2011-01-01 14:00

smetechitAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Oh! My previous query have a bug - If works only with data from a single date, if there is another date, output goes wrong
Here is the corrected query - Please test it with different dates
with cte as
(
	select min(actiontime) mintime, max(actiontime) maxtime from #table
	union all
	select dateadd(hh, 1, cte.mintime) actiontime, cte.maxtime from cte
		where dateadd(hh, 1, cte.mintime) <= cte.maxtime
)
select mintime from cte 
	where CONVERT(varchar(14),mintime,113) 
			not in (select CONVERT(varchar(14),actiontime,113) from #table)
union
select actiontime from #table

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is this for a single date?

what version of sql server?
0
 
Rajkumar GsSoftware EngineerCommented:
How you are finding the missing hours ?
Is it each one hour - but your expected data is not so

Raj
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Aaron ShiloChief Database ArchitectCommented:
hi

create a temp table populate the table with all the timestamps
and user a outer join on the two tables.
0
 
Rajkumar GsSoftware EngineerCommented:
If you are working on SQL Server 2005 or higher, this should work.
This query will fill all the missing one-hours.
I am not sure whether you are asking for this

I used table name as 'YourTable' and column name as 'actiontime'
Replace it with your actual table name and column name

with cte as
(
	select min(actiontime) mintime, max(actiontime) maxtime from YourTable
	union all
	select dateadd(hh, 1, cte.mintime) actiontime, cte.maxtime from cte
		where dateadd(hh, 1, cte.mintime) <= cte.maxtime
)
select mintime from cte
union
select actiontime from YourTable

Open in new window

0
 
smetechitAuthor Commented:
Hi RajkumarGS,

Your solution works but it displays additional values that are marked BOLD below and need to be eliminated.

2011-01-01 08:00
2011-01-01 09:00
2011-01-01 09:15
2011-01-01 10:00
2011-01-01 11:00
2011-01-01 11:30
2011-01-01 12:00
2011-01-01 13:00
2011-01-01 14:00

0
 
Rajkumar GsSoftware EngineerCommented:
I asked doubt about that - http:#34937113

How you are calculating the missing hours ?

Raj
0
 
smetechitAuthor Commented:
If an hour is listed don't add it,  otherwise add it with minutes = 00
0
 
Rajkumar GsSoftware EngineerCommented:
Here is the corrected query - Replace #table with your actual table name and actiontime with your actual column name
with cte as
(
	select min(actiontime) mintime, max(actiontime) maxtime from #table
	union all
	select dateadd(hh, 1, cte.mintime) actiontime, cte.maxtime from cte
		where dateadd(hh, 1, cte.mintime) <= cte.maxtime
)
select mintime from cte where datepart(hh, mintime) not in (select datepart(hh,actiontime) from #table)
union
select actiontime from #table

Open in new window

0
 
smetechitAuthor Commented:
Hi RajkumarGS,

I think that actiontime in the 5th line shoulld be removed (select dateadd(hh, 1, cte.mintime) actiontime ...).

By replacing actiontime with my field and #table with my table the query works perfectly and your solution is 100% accepted.

Can I ask to send me the query if the origibal table contains second datetime field and need to be returned. for newly added records you can copy actiontime in place of field2.
0
 
Rajkumar GsSoftware EngineerCommented:
smetechit,
Please post it as a new question, as it is different from you ask here.
Raj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.