Solved

Select statment

Posted on 2011-02-20
11
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
efficient backup report for SQL Server 13 81
Please explain the difference between EXCLUDE, INTERSECT and JOIN 7 52
Powershell error using sql agent job 24 41
t-sql left join 2 34
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

752 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