Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is a method which abuses the row_number function. Please check the comments for some explanation.</p> <pre><code>;with Seq as ( -- Create a master sequence of events -- Trust the date column to be accurate (don't match a Begin to an earlier End) select id, activitytype, beginend , coalesce(businesskey, '') as businesskey -- Needed to match nulls as equal , [date], technician, note , row_number() over (partition by technician, businesskey, activitytype order by [date], beginend, id) as rownumber from TimeEntry ) select b.id as BeginID , e.id as EndID , coalesce(b.technician, e.technician) as Technician , coalesce(b.businesskey, e.businesskey) as BusinessKey , coalesce(b.activitytype, e.activitytype) as ActivityType , coalesce(convert(char(10), b.[date], 103), 'Incomplete') as BeginDate , coalesce(convert(char(10), e.[date], 103), 'Incomplete') as EndDate , coalesce(convert(char(5), b.[date], 108), 'Incomplete') as BeginTime , coalesce(convert(char(5), e.[date], 108), 'Incomplete') as EndTime , b.note as BeginNote , e.note as EndNote from (select * from Seq where beginend = 'Begin') b -- Get all Begins full outer join (select * from Seq where beginend = 'End') e -- Get all Ends on b.technician = e.technician and b.businesskey = e.businesskey and b.activitytype = e.activitytype and b.rownumber = e.rownumber - 1 -- Match a Begin with only the very next End of that type order by coalesce(b.[date], e.[date]) , coalesce(b.id, e.id) , coalesce(b.technician, e.technician) , coalesce(b.businesskey, e.businesskey) , coalesce(b.activitytype, e.activitytype) </code></pre> <p>And the <a href="http://sqlfiddle.com/#!3/1fc2b/1" rel="nofollow">SQL Fiddle</a> should anyone want the DDL or demo.</p>
    singulars
    1. This table or related slice is empty.
    1. POT-SQL join to get both mated and non mated start and stop records
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTim Lehner
    1. USTim Lehner
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POT-SQL join to get both mated and non mated start and stop records
      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. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COUpvote from me for adding a demo.
      singulars
      1. PO
      1. USsurfmuggle
    2. COBoth of these answers work swimmingly. Tim all things being equal you were first to answer and your code is about 20 lines shorter but you are also >3K rep I would like award the bounty to Steve /if/ its OK with you.
      singulars
      1. PO
      1. USJoe Johnston
    3. COIt's all good, as they say. I'm glad the ranking concept worked for you.
      singulars
      1. PO
      1. USTim Lehner
 

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