SQL with a JOIN to the TOP 1 of another table

So I needed to do a join where a table would join only to the top row returned from the second table. In fact in this one I needed to have 2 joins that do that, so here is how I created it:


SELECT d.TableName,d.GroupName,d.CircuitID,d.ServiceIndicator,d.ErrorPriorityCode,d.OmniaAddressID,d.LegacyKeyID,d.ServiceIndicatorSource,
d.ServiceIndicatorOriginal,d.RowID,
assignment.AssignedBy_UserId as LastAssignedBy,
assignment.AssignedTo_UserId as LastAssignedTo,
assignment.Comment as LastAssignmentComment,
assignment.DateAssigned as LastDateAssigned,
checkout.UserId as LastCheckedOutBy,
checkout.ResolutionId as LastCheckoutResolutionId,
checkout.Comments as LastCheckoutComment,
checkout.Closed as LastCheckoutClosed,
checkout.CheckoutDate as LastCheckoutDate,
checkout.CheckinDate as LastCheckInDate

FROM dbo.Details AS d
LEFT JOIN circuitjoin AS cmCircuit
ON d.CircuitID = cmCircuit.Id
Left Join dbo.DetailAssignment assignment
ON assignment.RowId =
(select top 1 a.RowId from DetailAssignment a
where a.DetailServiceIndicator = d.ServiceIndicator
order by a.DateAssigned desc)

LEFT JOIN dbo.DetailCheckout AS checkout
ON checkout.RowId =
(Select top 1 c.RowId from dbo.DetailCheckout c
where c.DetailServiceIndicator = d.ServiceIndicator
order by c.CheckoutDate desc)

Edit page for bootstrap layout

This is a sample of what I’m currently using for simple edit forms. This is using bootstrap 3’s panels, and is a very simple and clean page.

 


@model ScheduleTemplateViewModel

@{

ViewBag.Title = “ScheduleTemplate”;

Layout = “~/Views/Shared/_Layout.cshtml”;

}

@using(Html.BeginForm(“Edit”, “ScheduleTemplate”, FormMethod.Post, new {@class = “form-horizontal”, @role = “form”}))

{

@Html.AntiForgeryToken()

@Html.ValidationSummary(true)

@Html.HiddenFor(model => model.Id)

@Html.HiddenFor(model => model.PropertyId)

@Html.HiddenFor(model=>model.HostedAccountId)

<div class=”row” >

<div class=”col-sm-8″><h3>@ViewBag.Task @ViewBag.Title</h3></div>

<div class=”col-sm-4″></div>

<!–  –>

</div>

<div class=”panel panel-primary”>

<div class=”panel-heading”><span class=”panel-title”>@ViewBag.Title Details</span><span class=”pull-right”>@Html.LabelFor(model => model.PropertyName):

@Html.DisplayFor(model => model.PropertyName)</span>

</div>

<div class=”panel-body”>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.Name,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″>@Html.EditorFor(model => model.Name)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.Name)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.Type,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.Type)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.Type)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.FixedStartTime,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.FixedStartTime)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.FixedStartTime)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.FixedEndTime,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.FixedEndTime)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.FixedEndTime)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.FixedLength,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.FixedLength)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.FixedLength)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.DefaultLength,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.DefaultLength)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.DefaultLength)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.DefaultPreBufferTime,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.DefaultPreBufferTime)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.DefaultPreBufferTime)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.DefaultPostBufferTime,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.DefaultPostBufferTime)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.DefaultPostBufferTime)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.EnforcePreBufferTime,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.EnforcePreBufferTime)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.EnforcePreBufferTime)</div>

</div>

</div>

<div class=”row form-group”>

<div class=”col-sm-3″>@Html.LabelFor(model => model.EnforcePostBufferTime,new {@class = “pull-right control-label”})</div>

<div class=”col-sm-5″> @Html.EditorFor(model => model.EnforcePostBufferTime)

<div class=”help-inline”>@Html.ValidationMessageFor(model => model.EnforcePostBufferTime)</div>

</div>

</div>

</div>

</div>

<div>

<input class=”btn btn-primary” type=”submit” value=”Save” /> @Html.ActionLink(“Back to list”, “Index”)

</div>

}

<div>

@Html.ActionLink(“Back to list”, “Index”)

</div>

 

New solution with standard setup

In my projects folder use the Project that starts with an underscore as a template:Open the template solution

  1. Do a find / replace on all files to replace old proj name with new
  2. Change all the project names to the new names
  3. Close the solution
  4. Delete the solution files (3 files in the solution root folder)
  5. Create a new solution (other project types)
  6. Add the projects to the new solution The project dependencies are already there from previous settings.
  7. Add the AD project as an existing project.
  8. Build. It will have errors because I need to setup the Entities, but otherwise it is ready with IOC and Authorization code already setup.

Kendo UI Grid – Delete without edit

This seems like it would be trivial, and I guess it is if you get it right, but I was trying to change a grid so that it would not use InCell editing, but continue to have the ‘Delete’ function triggered from the delete button on the same row. My thought was I want to turn off the ability to EDIT, so I tried combinations of removing the .Editable property or setting it to InLine, and then changing Batch to false because I was getting an error that it must be InCell if using batch edits.

(I’m only showing the changes made)

In the end I needed to remove the Command.Save() from the toolbar AND set:

<code>

.Editable(e => e.Mode(GridEditMode.InLine))   //this is changed FROM InCell

.DataSource(datasource => datasource

.Batch(true)  //Leave true

</code>