Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Given an item that should be in category X, once the category Y rows are already there you should be able to add the X with the code below. You'll need to adapt it to however you will be using it - in a cursor, as a set-based operation for all items with a category X row, or whatever.</p> <pre><code>DECLARE @y_id INT, @x_id INT, @item_id INT SELECT @y_id = category_id FROM dbo.Categories WHERE name = 'Y' SELECT @x_id = category_id FROM dbo.Categories WHERE name = 'X' SET @item_id = ??? ;WITH Begin_Dates AS (SELECT BI.item_id, BI.begin_date AS begin_date FROM Items BI WHERE NOT EXISTS (SELECT * FROM Item_Categories BIC2 WHERE BIC2.item_id = BI.item_id AND BIC2.category_id = @y_id AND BIC2.begin_date = BI.begin_date) UNION SELECT BIC1.item_id, end_date AS begin_date FROM Item_Categories BIC1 WHERE BIC1.category_id = @y_id AND NOT EXISTS (SELECT * FROM dbo.Items WHERE item_id = BIC1.item_id AND BIC1.end_date = end_date)), End_Dates AS (SELECT EI.item_id, EI.end_date AS end_date FROM Items EI WHERE NOT EXISTS (SELECT * FROM Item_Categories EIC2 WHERE EIC2.item_id = EI.item_id AND EIC2.category_id = @y_id AND EIC2.begin_date = EI.begin_date) UNION SELECT EIC1.item_id, begin_date AS end_date FROM Item_Categories EIC1 WHERE EIC1.category_id = @y_id AND NOT EXISTS (SELECT * FROM dbo.Items WHERE item_id = EIC1.item_id AND EIC1.begin_date = begin_date)) INSERT INTO dbo.Item_Categories ( item_id, category_id, begin_date, end_date ) SELECT @item_id, @x_id, BD.begin_date, ED.end_date FROM Begin_Dates BD INNER JOIN End_Dates ED ON ED.end_date &gt; BD.begin_date AND NOT EXISTS (SELECT * FROM End_Dates ED2 WHERE ED2.end_date &gt; BD.begin_date AND ED2.end_date &lt; ED.end_date) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POFlatten date range memberships retaining only the highest priority membership (TRANSACT-SQL)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTom H
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POFlatten date range memberships retaining only the highest priority membership (TRANSACT-SQL)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI am looking at this solution. It's got some problems with it (some of which are because you used outside information which doesn't hold true precisely in the actual DB). I'll see what I can do with it though. Thank you.
      singulars
      1. PO
      1. USShadowRanger
    2. COOnce I figured out what you were doing (it was a little tricky since you mixed birth dates with begin dates, which was true in my simplified scenario, but wrong in the real world scenario), I was able to get it working for my specific scenario and applicable to a set of "item_ids" instead of just one. Thank you very much.
      singulars
      1. PO
      1. USShadowRanger
    3. COOne minor correction: You used the item_categories end_date and begin_date unmodified in your WITH setup. While we can use the item begin and end dates unmodified (since it must be a member of a category at the begin and end of its life), we can't do the same for category membership dates, or we'll have a one day overlap every time we switch between the override and the default category (category membership is inclusive on both begin and end dates). Changing to "DATEADD(dy, 1, end_date) AS begin_date" and "DATEADD(dy, -1, begin_date) AS end_date" in each category selection fixed that though.
      singulars
      1. PO
      1. USShadowRanger
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload