Solved

Select statment

Posted on 2011-02-20
11
290 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:smetechit
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34937104
is this for a single date?

what version of sql server?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34937113
How you are finding the missing hours ?
Is it each one hour - but your expected data is not so

Raj
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34937143
hi

create a temp table populate the table with all the timestamps
and user a outer join on the two tables.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34937599
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
 

Author Comment

by:smetechit
ID: 34937705
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34937710
I asked doubt about that - http:#34937113

How you are calculating the missing hours ?

Raj
0
 

Author Comment

by:smetechit
ID: 34937727
If an hour is listed don't add it,  otherwise add it with minutes = 00
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34937738
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
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 34937849
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
 

Author Comment

by:smetechit
ID: 34938126
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34938249
smetechit,
Please post it as a new question, as it is different from you ask here.
Raj
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question