sql server 2008 - Create multiple rows from single row sql -


i have table structure like

atm         ticket open time    ticket closed time  m30g324202  17-02-2013 06:15    19-02-2013 20:54 m30g324202  28-02-2013 21:00    01-03-2013 11:18 m30g324203  27-02-2013 19:10    28-02-2013 07:14 m30g324203  28-02-2013 07:15    28-02-2013 11:18

if ticket open time or ticket closed time lies between '20:00:00' , '06:00:00' i.e. 8 pm & 6 new row should created not have time frame

e.g. first row in above table 

atm         ticket open time    ticket closed time  m30g324202  17-02-2013 06:15    17-02-2013 20:00 m30g324202  18-02-2013 06:00    18-02-2013 20:00 m30g324202  19-02-2013 06:00    19-02-2013 20:00  //above 1st row  //second row change follows  m30g324202  01-03-2013 06:00    01-03-2013 11:18     (time 28-02-2013 21:00 neglected till next day morning 6      after 8 pm )  //third row change follows  m30g324203  27-02-2013 19:10    27-02-2013 20:00 m30g324203  28-02-2013 06:00    28-02-2013 07:14  //fourth row change follows  m30g324203  28-02-2013 07:15    28-02-2013 11:18 (no change is)

i have written 20:00 instead of 20:54 because 54 min. after 8 pm 19th feb.

a great question! please check try:

declare @tbl table (atm nvarchar(20), ticketopentime datetime, ticketclosedtime datetime) insert @tbl values ('m30g324202', '02-17-2013 06:15', '02-19-2013 20:54'), ('m30g324202', '02-28-2013 21:00', '03-01-2013 11:18'), ('m30g324203', '02-27-2013 19:10', '02-28-2013 07:14'), ('m30g324203', '02-28-2013 07:15', '02-28-2013 11:18')  declare @min datetime, @max datetime select @min = min(ticketopentime), @max = max(ticketclosedtime) @tbl  ;with t as(     select convert(datetime, convert(numeric(20), @min, 101)) dt     union     select dt+1 t dt<@max )  select      a.atm,      case when a.ticketopentime>dt1 a.ticketopentime else dt1 end ticketopentime,     case when a.ticketclosedtime>dt2 dt2 else a.ticketclosedtime end ticketclosedtime @tbl cross apply(     select          dt,          dateadd(minute, 360, dt) dt1,          dateadd(minute, 1200, dt) dt2 t b               dt between cast(a.ticketopentime date) , cast(a.ticketclosedtime date) )x a.ticketopentime<=x.dt2 order a.atm, a. ticketopentime

Comments

Post a Comment

Popular posts from this blog

Change php variable from jquery value using ajax (same page) -

what i'm trying this: i've php global variable $currentid , simple jquery script check if there "id" (page) in hash of url , if yes wanted give value $currentid . i've been reading lot , notice shall use ajax i'm not managing it. my code is: <script type="text/javascript"> $(document).ready(function(){ if (parent.location.hash != '' ) { var thecurrentid = parent.location.hash.split('#')[1]; $.ajax({ url: 'my.php', //current page type: 'post', data: {theid: thecurrentid }, datatype: 'json', success: function () { } }); } // code here }); </script> could me please? thank in advance. php server side language, meaning once has sent data browser, php variables , data cannot changed... however, using ajax, can send request php page , change data in dom based on result. consider th...
Read more

Pull out data related to my apps from Android Play Store and iOS App Store -

i want pull out data related apps (ratings, reviews, etc) google play store , apple app store ? how can accomplish ? i have found open source apis google mentioned not reliable. also there paid apis (e.g. appfigures) available. has idea how able pull data these play stores ?? i looking direction on how can accomplish this. can suggest ? we (appfigures) have been working on developing infrastructure give ability reviews , bunch of other data points major app stores several years :] we make available via our api though may want check out.
Read more

How can I fetch data from a web server in an android application? -

i want retrieve data web server in android application, , don't know begin. should use web services? i recommend these tutorials: connect android php , mysql , json in android , php , mysqli i used these tutorials , managed trying working without difficulty. between them describe each step in how attempting @ each stage, android application, database , web server side , has information included can process , use received information the thing add connect android php , mysql tutorial makes use of mysql_ in php deprecated. better use mysqli why included third link. the basic outline of want this: 1) in android app make request server php script using class this: import java.io.bufferedreader; import java.io.ioexception; import java.io.inputstream; import java.io.inputstreamreader; import java.io.unsupportedencodingexception; import java.util.list; import org.apache.http.httpentity; import org.apache.http.httpresponse; import org.apache.http.namevaluepair...
Read more