convert date timeline in rows to columnar format in sql server -

-

my data :

sku      startdatetime  enddatetime es311335    6/30/2013   5/24/2015 es311355    6/30/2013   6/28/2015 es311470    6/30/2013   12/14/2014 es311478    6/30/2013   6/28/2015 es311501    6/30/2013   6/28/2015 es311574    6/30/2013   6/28/2015 es311632    6/30/2013   6/22/2014

i want in format:

sku         date es311335    6/30/2013 es311335    7/7/2013 es311335    7/14/2013 es311335    7/21/2013 es311335    7/28/2013 es311335    8/4/2013 ------      ------ es311335    24/05/2014

i have used below query not giving me correct result.

declare @interval nvarchar(10), @i datetime,  @intervalmin datetime, @intervalmax datetime  --- set interval day or month or year -----------------  set @interval = 'day'   -------------------------------------------------------  declare @records table (startdatetime datetime  ,enddatetime datetime)  insert @records select startdatetime, enddatetime records  declare @results table (startdate datetime  ,enddate datetime)  while exists(select * @records) begin   select top 1 @intervalmin = startdatetime, @intervalmax = enddatetime   @records    set @i = @intervalmin    while (@i <= @intervalmax)   begin     if @interval = 'day'     begin       insert @results       select @i, case when convert(date, dateadd(day, 7, @i)) > @intervalmax            @intervalmax else convert(date, dateadd(day, 7, @i)) end        set @i = convert(date, dateadd(day, 7, @i))     end end    delete @records    startdatetime = @intervalmin    , enddatetime = @intervalmax end  select * @results

i mentioned in comment should use tally table. here version of tally table. keep view in system don't have store permanently , generates 10,000 rows 0 reads.

create view [dbo].[ctetally]      e1(n) (select 1 (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),     e2(n) (select 1 e1 a, e1 b), --10e+2 or 100 rows     e4(n) (select 1 e2 a, e2 b), --10e+4 or 10,000 rows max     ctetally(n)      (         select  row_number() on (order (select null)) e4     ) select n ctetally

now how apply trying do? can date math incorporate tally table solution set based instead of rbar (row agonizing row) looping construct. before can solution have build data in format consumable others.

here 1 way that.

if object_id('tempdb..#something') not null     drop table #something  create table #something (     sku char(9)     , startdatetime datetime     , enddatetime datetime )  insert #something select 'es311335', '6/30/2013', '5/24/2015' union select 'es311355', '6/30/2013', '6/28/2015' union select 'es311470', '6/30/2013', '12/14/2014' union select 'es311478', '6/30/2013', '6/28/2015' union select 'es311501', '6/30/2013', '6/28/2015' union select 'es311574', '6/30/2013', '6/28/2015' union select 'es311632', '6/30/2013', '6/22/2014'

ok cool. have tally table , data work with. let's see how simple can when use our tally table.

select s.*     , dateadd(week, n - 1, startdatetime) #something s join ctetally t on t.n <= datediff(week, startdatetime, enddatetime) + 1 order sku, dateadd(week, n - 1, startdatetime)

Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more