Solved

Select statment

Posted on 2011-02-20
11
291 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 143

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

838 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