Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select statment

Posted on 2011-02-20
11
Medium Priority
?
297 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

885 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