sql - Assign a "Group ID" to a recursive cte based on WHERE... IN... in cte -


i trying pull information multitude of tables. of includes procurement information based on entries have maintenance records. have maintenance records (wiirs) exist on physical units. in cases, many parts (bomlines) have been purchased maintenance.

i pulling wiir information 1 table wiirid pk , bomlineid fk, procurement date table via intermediary table, , trying group them wiirid, finding earliest procurement info. if there way identify groups of information coming out of cte fantastic. open suggestions.

use emms_srm -- declare variables use pull data declare @start datetime, @end datetime set @start = '5/1/2014' set @end = '5/31/2014'  --make table variable store sorting list report. declare @sortlist table ( wiirid int not null ,bomlineid int not null ) -- make table variable hold group list min sc info declare @grouplist table ( wiirid int null ,wiirbomlineid int null ,groupbomlineid int not null ,srmscid int null ,srmsclineid int null ,srmscdate datetime null --  ,groupid int not null ) ; --build list of wiirs , bomlines report. insert @sortlist select distinct vw.wiirid ,vw.bomlineid  dbo.vw_wiirs vw (vw.iscomplete = 'true') , (vw.isarchived = 'false') , (isdeleted = 'false') , (vw.closeoutdate between @start , @end) , (vw.wiirtypeid = 1) -- new build (in-house or make) , (vw.wiirsubtypeid in (1,3,4,7,8,23)) , (vw.partnumber not '%r') , (vw.partnumber not '%rfm') , (vw.partnumber not '%l') , (vw.partnumber not '%p')  order vw.wiirid;  --build list of children bomlines @sortlist prep_cte1(bomlineid, parentbomlineid, bomid, partid, title, description/*, groupid*/) ( --anchor member definition select bl.bomlineid     ,bl.parentbomlineid     ,bl.bomid     ,bl.partid     ,bl.title     ,replace(replace(bl.description,char(10),''),char(13),'') 'description'     --need assign group id based on @sortlist  dbo.bomlines bl  bl.bomlineid in (select bomlineid @sortlist) union -- recursive member definition select bl.bomlineid     ,bl.parentbomlineid     ,bl.bomid     ,bl.partid     ,bl.title     ,replace(replace(bl.description,char(10),''),char(13),'')     --need assign group id based on @sortlist  dbo.bomlines bl inner join prep_cte1 p on bl.parentbomlineid = p.bomlineid ) insert @grouplist --statement executes cte select sl.wiirid ,sl.bomlineid ,pc1.bomlineid ,r.srmscid ,r.srmsclineid ,min(r.srmscdate) --  ,pc1.groupid  prep_cte1 pc1 left join dbo.shoppinglineitems sli on pc1.bomlineid = sli.bomlineid left join dbo.requisitions r on sli.requisitionid = r.requisitionid left join @sortlist sl on pc1.bomlineid = sl.bomlineid  group /*pc1.groupid,*/ sl.wiirid, sl.bomlineid, pc1.bomlineid, r.srmscid, r.srmsclineid  order sl.wiirid;  select * @grouplist --should return 118 rows @sortlist srmsc information based on group

addition: trying build example in sqlfiddle, first time using it. existing question, "groupid" i'm trying create lie within cte, hope. @sortlist returns 118 rows, each unique wiirid , unique bomlineid. bomlines table has unique bomlineid values, houses self-referential "parentbomlineid" field. cte supposed pull levels of chilren in dbo.bomlines, need have @sortlist.wiirid or @sortlist.bomlineid assigned of children can group 1 of values, know children belong wiirid or wiirbomlineid. cte returns 2500+ rows, original 118 have non-null value in @sortlist.wiirid or @sortlist.bomlineid

once have 2500+ rows groupid, retrieve procurement information 2 tables (linked via 2 different tables), retrieve earliest shopping cart 1 table, , last purchase order received another, within specific wiir groups. so, each of original 118 lines should report out min(srmscdate) , max(flashdate) group of bomlines falls underneath (out of 2500+ retrieved cte).

the following changes have been made code select groupid.

1.joins @sortlist have been introduced within prep_cte1 ensure bomlineid on @sortlist selected.

2.in final insert-select statement, sl.wiirid + '-' + sl.bomlineid selected groupid, uniquely identifies each group.

revised code:

use emms_srm -- declare variables use pull data declare @start datetime, @end datetime set @start = '5/1/2014' set @end = '5/31/2014'  --make table variable store sorting list report. declare @sortlist table ( wiirid int not null ,bomlineid int not null ) -- make table variable hold group list min sc info declare @grouplist table ( wiirid int null ,wiirbomlineid int null ,groupbomlineid int not null ,srmscid int null ,srmsclineid int null ,srmscdate datetime null --  ,groupid int not null ) ; --build list of wiirs , bomlines report. insert @sortlist select distinct vw.wiirid ,vw.bomlineid dbo.vw_wiirs vw (vw.iscomplete = 'true') , (vw.isarchived = 'false') , (isdeleted = 'false') , (vw.closeoutdate between @start , @end) , (vw.wiirtypeid = 1) -- new build (in-house or make) , (vw.wiirsubtypeid in (1,3,4,7,8,23)) , (vw.partnumber not '%r') , (vw.partnumber not '%rfm') , (vw.partnumber not '%l') , (vw.partnumber not '%p') order vw.wiirid;  --build list of children bomlines @sortlist prep_cte1(bomlineid, parentbomlineid, bomid, partid, title, description/*, groupid*/) ( --anchor member definition select bl.bomlineid     ,bl.parentbomlineid     ,bl.bomid     ,bl.partid     ,bl.title     ,replace(replace(bl.description,char(10),''),char(13),'') 'description' dbo.bomlines bl inner join @sortlist sl on bl.bomlineid = sl.bomlineid union -- recursive member definition select bl.bomlineid     ,bl.parentbomlineid     ,bl.bomid     ,bl.partid     ,bl.title     ,replace(replace(bl.description,char(10),''),char(13),'')     --need assign group id based on @sortlist  dbo.bomlines bl inner join prep_cte1 p on bl.parentbomlineid = p.bomlineid inner join @sortlist sl on bl.bomlineid = sl.bomlineid ) insert @grouplist --statement executes cte select sl.wiirid ,sl.bomlineid ,pc1.bomlineid ,r.srmscid ,r.srmsclineid ,min(r.srmscdate) --  ,pc1.groupid ,sl.wiirid + '-' + sl.bomlineid groupid prep_cte1 pc1 left join dbo.shoppinglineitems sli on pc1.bomlineid = sli.bomlineid left join dbo.requisitions r on sli.requisitionid = r.requisitionid left join @sortlist sl on pc1.bomlineid = sl.bomlineid  group /*pc1.groupid,*/ sl.wiirid, sl.bomlineid, pc1.bomlineid, r.srmscid, r.srmsclineid, sl.wiirid + '-' + sl.bomlineid  order sl.wiirid;  select * @grouplist --should return 118 rows @sortlist srmsc information based on group

Comments

Post a Comment

Popular posts from this blog

javascript - RequestAnimationFrame not working when exiting fullscreen switching space on Safari -

request animation frame stops working when exiting fullscreen clicking on original space's safari window. fine if fullscreen mode canceled escape key or calling cancelfullscreen(). steps reproduce: open https://dl.dropboxusercontent.com/u/769042/prezi/safari-fullscreen.html click "draw raf", kittie appears click "fullscreen", go fullscreen click "draw raf", kittie appears go space original safari was, showing "click exit fullscreen mode", click anywhere, out fullscreen click "draw raf", nothing happens what handling click calling: window.requestanimationframe(draw); which draws on canvas context: function draw() { ctx.drawimage(img, math.random()*500|0, math.random()*400|0, 100, 100); } i checked .hidden , .visibilitystate, updated correctly. tested on osx 10.9.3, safari 7.0.4 (9537.76.4). has workaround/solution other switching old settimeout? this sounds webkit bug 88940 : using reque...
Read more

jsf - How to ajax update an item in the footer of a PrimeFaces dataTable? -

this visual of table have: +---------+------------+-------------------+ | header1 | header2 | header3 | +---------+------------+-------------------+ | row | input | calc'ed output | | row b | input b | calc'ed output b | | etc.. | etc.. | etc.. | +---------+------------+-------------------+ | total: total calc'ed output | +------------------------------------------+ and stripped-down code: <p:datatable id="mytable" value="#{mybean.somelist}" var="currentitem"> <p:column headertext="header1"> <h:outputtext value="#{currentitem.name}" /> </p:column> <p:column headertext="header2"> <pe:inputnumber value="#{currentitem.inputval}"> <p:ajax event="change" listener="#{mybean.changelistener}" update="outputval outputtotal...
Read more

django - CSRF verification failed. Request aborted. CSRF cookie not set -

error: reason given failure: csrf cookie not set. in general, can occur when there genuine cross site request forgery, or when django's csrf mechanism has not been used correctly. post forms, need ensure: browser accepting cookies. view function uses requestcontext template, instead of context. in template, there {% csrf_token %} template tag inside each post form targets internal url. if not using csrfviewmiddleware, must use csrf_protect on views use csrf_token template tag, accept post data. you're seeing section of page because have debug = true in django settings file. change false, , initial error message displayed. can customize page using csrf_failure_view setting. did find, nothing helped, must foolish(( my template <form method="post">{% csrf_token %} {% field in form %} <div class="control-group"> {{ field }} </div> {% endfor %} <input type=...
Read more