Sending and Receiving JSON between jQuery and ASP.NET MVC using Ajax.

It’s taken me a while to get this working without any problems so it’s worth jotting down how it all works. I don’t think I was trying to do anything particularly difficult – just receive Json from an ASP.NET MVC application using jQuery then submitted but the APIs had to be coerced into just the correct way to get it functional.

Receiving Json from ASP.NET MVC using jQuery

First up, here’s the Json format I’m trying to send and receive :

var _shiftPattern = {
  'employee': {'name' : 'Jason Wright', 'number': 1234, 'contractedHours': 39.0 },
  'stepSize': 0.5,
  'days': [
      { 'day': 'Sunday',    'pattern': ['', '', '', '', '', '', '', 'p', 'b', 'w', 'w', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'p', 'p', 'p', 'p', 'p', 'b', 'p', 'p', 'p', '', '', '', '', '', '', '', '', '', '', '' ] },
      { 'day': 'Monday',    'pattern': ['', '', '', '', '', '', '', 'p', 'b', 'w', 'p', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'p', 'p', 'p', 'p', 'p', 'b', 'p', 'p', 'p', '', '', '', '', '', '', '', '', '', '', '' ] },
      { 'day': 'Tuesday',   'pattern': ['', '', '', '', '', '', '', 'p', 'b', 'w', 'b', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'p', 'p', 'p', 'p', 'p', 'b', 'p', 'p', 'p', '', '', '', '', '', '', '', '', '', '', '' ] },
      { 'day': 'Wednesday', 'pattern': ['', '', '', '', '', '', '', 'p', 'b', 'w', 'w', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'w', 'w', 'p', 'p', 'p', 'b', 'p', 'p', 'p', '', '', '', '', '', '', '', '', '', '', '' ] },
      { 'day': 'Thursday',  'pattern': ['', '', '', '', '', '', '', 'p', 'b', 'w', 'p', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'w', 'w', 'p', 'p', 'p', 'b', 'p', 'p', 'p', '', '', '', '', '', '', '', '', '', '', '' ] },
      { 'day': 'Friday',    'pattern': ['', '', '', '', '', '', '', 'p', 'b', 'w', 'b', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'w', 'w', 'p', 'p', 'p', 'b', 'p', 'p', 'p', '', '', '', '', '', '', '', '', '', '', '' ] },
      { 'day': 'Saturday',  'pattern': ['', '', '', '', '', '', '', 'p', 'b', 'w', 'w', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'w', 'w', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' ] }
  ]
};

Note that I’ve opted to maintain Javascript standards by using camel casing – that caused a bit of trouble and I’ll show the problem and work-around later.

The jQuery to fetch this Json is trivial :

$(document).ready(function() {
  $.getJSON("/willingtowork/shiftpattern/1234", null, function(data) {
      _shiftPattern = data;
      renderModel();
  });
});

…and ASP.NET MVC controller action looks like this :

public ActionResult ShiftPattern(int id)
{
    return Content(JsonHelper.Serialize(
        new WillingToWork
        {
            Employee = new Employee { Name = "Jason Wright", Number = id, ContractedHours = 39.0f },
            StepSize = 0.5f,
            Days = new List<Day> {
                new Day { DayName = "Sun", Pattern = new List<string> {"", "", "", "", "", "", "", "p", "p", "b", "p", "p", "p", "p", "w", "w", "", "", "", "", "", "", "", "", "p", "p", "p", "p", "p", "p", "p", "w", "w", "w", "w", "w", "", "", "", "", "", "", "", "", "", "", "", "" }},
                new Day { DayName = "Mon", Pattern = new List<string> {"", "", "", "", "", "", "", "p", "p", "b", "p", "p", "p", "p", "w", "w", "", "", "", "", "", "", "", "", "p", "p", "p", "p", "p", "p", "p", "b", "p", "p", "p", "p", "w", "w", "w", "", "", "", "", "", "", "", "", "" }},
                new Day { DayName = "Tue", Pattern = new List<string> {"", "", "", "", "", "", "", "p", "p", "b", "p", "p", "p", "p", "w", "w", "", "", "", "", "", "", "", "", "p", "p", "p", "p", "p", "p", "p", "b", "p", "p", "p", "p", "w", "w", "w", "", "", "", "", "", "", "", "", "" }},
                new Day { DayName = "Wed", Pattern = new List<string> {"", "", "", "", "", "", "", "p", "p", "b", "p", "p", "p", "p", "w", "w", "", "", "", "", "", "", "", "", "p", "p", "p", "p", "p", "p", "p", "b", "p", "p", "p", "p", "w", "w", "w", "", "", "", "", "", "", "", "", "" }},
                new Day { DayName = "Thu", Pattern = new List<string> {"", "", "", "", "", "", "", "p", "p", "b", "p", "p", "p", "p", "w", "w", "", "", "", "", "", "", "", "", "p", "p", "p", "p", "p", "p", "p", "b", "p", "p", "p", "p", "w", "w", "w", "", "", "", "", "", "", "", "", "" }},
                new Day { DayName = "Fri", Pattern = new List<string> {"", "", "", "", "", "", "", "p", "p", "", "", "", "w", "w", "w", "w", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }},
                new Day { DayName = "Sat", Pattern = new List<string> {"", "", "", "", "", "", "", "", "", "", "", "", "w", "w", "w", "w", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }}
            }
        }
    ), "application/json; charset=utf-8");
}

In this test code I’ve hard-coded some object initialisations but the real issue for me here was originally choosing to use a JsonResult by calling Json() on the Mvc Controller Base but I’ve replaced it with a ContentResult and serialised the Json manually – more on that later.

The code below defines the data transfer objects I’m using to strongly type the Json in C#. Note I’ve implemented IExtensibleDataObject so that any additional data added to the Json in the javascript that I haven’t accounted for is ignored but still maintained if necessary (you may choose to remove this if you want to be strict but any extra data will cause an exception during deserialising.) I might also implement all properties as virtual to enable mocking frameworks (an alternative to defining interfaces which would be a bit complicated for DTOs) but that’s not shown here.

The main point to notice with the DataContract below is the use of DataMemberAttribute and its ability to rename properties – this is how I’m maintaining Pascal casing in C# and Camel casing for the serialised Json.

[DataContract]
public class WillingToWork : IExtensibleDataObject
{
   [DataMember(Name="employee")] public Employee Employee { get; set; }
   [DataMember(Name="days")] public List<Day> Days { get; set; }
   [DataMember(Name="stepSize")] public float StepSize { get; set; }

   public virtual ExtensionDataObject ExtensionData { get; set; }
}

[DataContract]
public class Day : IExtensibleDataObject
{
   [DataMember(Name = "pattern")] public List<string> Pattern { get; set; }
   [DataMember(Name = "day")] public string DayName { get; set; }

   public virtual ExtensionDataObject ExtensionData { get; set; }
}

[DataContract]
public class Employee :IExtensibleDataObject
{
   [DataMember(Name="contractedHours")] public float ContractedHours { get; set; }
   [DataMember(Name="name")] public string Name { get; set; }
   [DataMember(Name="number")] public int Number { get; set; }

   public virtual ExtensionDataObject ExtensionData { get; set; }
}

… and here lies a problem. When I was using the ASP.NET MVC JsonResult, the Json simply would not serialise using the names I’d provided in the DataMember and instead used the property name.

I’d already seen how to use the DataContractJsonSerializer() to manually create test code which correctly used the DataMemberAttribute() name property so I was wondering why JsonResult() wasn’t doing the same. Taking a look with reflector showed the following code in the JsonResult():

public override void ExecuteResult(ControllerContext context)
{
    if (context == null)
    {
        throw new ArgumentNullException("context");
    }
    HttpResponseBase response = context.HttpContext.Response;
    if (!string.IsNullOrEmpty(this.ContentType))
    {
        response.ContentType = this.ContentType;
    }
    else
    {
        response.ContentType = "application/json";
    }
    if (this.ContentEncoding != null)
    {
        response.ContentEncoding = this.ContentEncoding;
    }
    if (this.Data != null)
    {
        JavaScriptSerializer serializer = new JavaScriptSerializer();
        response.Write(serializer.Serialize(this.Data));
    }
}

The problem in this code is the use of the JavaScriptSerializer() which is now marked obsolete (although that seems to be questionable) and doesn’t handle the DataMemberAttribute(). Rather than try to work around this too cleverly I’ve used the ContentResult() instead passing a Json string serialized from a helper using the DataContractJsonSerializer() and making sure to pass the content type. Here’s the serializer helper (not exactly rocket science):

public class JsonHelper
{
   public static string Serialize<T>(T obj)
   {
       var serializer = new DataContractJsonSerializer(obj.GetType());
       var ms = new MemoryStream();
       serializer.WriteObject(ms, obj);
       return Encoding.Default.GetString(ms.ToArray());
   }
}

Posting Json using jQuery back to an ASP.NET MVC Controller

The next step was finishing the loop and allowing the client to post back the Json data in order for it to be persisted. Here’s the jQuery in the client:

function saveModel() {
  $.ajax({
      url: "/willingtowork/save",
      type: "POST",
      data: JSON.stringify(_shiftPattern),
      dataType : "json",
      contentType: "application/json; charset=utf-8",
      success: function(data, textStatus) {
          alert(data.result);
      },
      error: function(request, textStatus, errorThrown) {
          alert(request.responseText);
      }
  });
}

And the controller action that receives this:

[JsonFilter(Param = "data", RootType = typeof(WillingToWork))]
public ActionResult Save(WillingToWork data)
{
  return Json(new {result = "Success" });
}

The key here is the JsonFilterAttribute()

public class JsonFilter : ActionFilterAttribute
{

   public string Param { get; set; }
   public Type RootType { get; set; }

   public override void OnActionExecuting(ActionExecutingContext filterContext)
   {
       if (!(filterContext.HttpContext.Request.ContentType ?? string.Empty).Contains("application/json")) return;
       filterContext.ActionParameters[Param] = JsonHelper.Deserialize<WillingToWork>(filterContext.HttpContext.Request.InputStream);
   }
}

public class JsonHelper
{
   public static T Deserialize<T>(Stream sr)
   {
       var serializer = new DataContractJsonSerializer(typeof (T));
       return (T) serializer.ReadObject(sr);
   }
}

I sourced the JsonFilter from an article online although I’ve lost the reference now but I’m very grateful to whoever it was!!! I’ve also renamed the filter itself and changed it to use a helper for the deserializing. However, that filter does turn a Json string into a typed object with very little plumbing.

And that’s it!! Phew, it took a while and in the end the code needed to complete it is fairly simple. At some point I’ll probably create a new ActionResult() that wraps up the serializing using DataContractJsonSerializer() so that it’s not necessary to remember to use a ContentResult(), serializer and content type!!! Yeuck!

Adding a Windows Explorer Context Menu for the Web Development Server

I wanted to be able to launch a single folder – the results of an ASP.NET WebForms build – in a Web Development Server without having to run up Visual Studio. The result looks like this :

And the solution… add the following to the registry :

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Directory\shell\webdev]
@="Launch in WebDev Server"

[HKEY_CLASSES_ROOT\Directory\shell\webdev\command]
@="\"c:\\windows\\microsoft.net\\framework\\v2.0.50727\\WebDev.WebServer.exe\" /port:54321 /path:\"%V\""

I would like to change that so it uses %windir% instead of C:\Windows but I must have got the syntax wrong because Explorer wouldn’t recognise the command. I’d also like to add an option for .aspx files that launches the parent folder in WebDev then launches an explorer window for the selected aspx file.

One last thing… this isn’t launching the server with any elevated permissions. If that’s required then you’ll need a tool such as Wintellects Elevate command which can be downloaded with full C# source code.

Dynamic Order By in T-SQL

I’ve been looking at some code that looked like this:

CREATE PROCEDURE [dbo].,
    @DraftStatus int,
    @Surname nvarchar(50),
    @PayrollNo nvarchar(50),
    @StartRow int,
    @PageSize int,
    @OrderBy varchar(50),
    @OrgLevelValueID int = null
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(4000)
    set @sql = 'SELECT * FROM '+
            '(SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS RowNum '+
            'FROM LMv_EmployeeDrafts '+
            'WHERE DraftStatus = ' + CONVERT(nvarchar(10), @DraftStatus) +
                ' AND Surname LIKE ''%'+@Surname+'%'' '+
                ' AND CONVERT(nvarchar(10), PayrollNo) LIKE ''%'+@PayrollNo+'%'') AS tmp '+
        'WHERE RowNum-1 BETWEEN ' + CONVERT(nvarchar(10), @StartRow)+
            ' AND (' + CONVERT(nvarchar(10), @StartRow) + ' + ' + CONVERT(nvarchar(10), @PageSize) + ') - 1'
        --IF NOT(@OrgLevelValueID IS NULL) SET @sql = @sql + ' AND OrganisationLevelValueID = ' + CONVERT(varchar(max), @OrgLevelValueID)
        --IF NOT (@OrgLevelValueID IS NULL) SET @sql = @sql + ' AND (' + CONVERT(varchar(max), @OrgLevelValueID) + ' IN ' +
        SET @sql = @sql + ' AND (' + CONVERT(varchar(max), @OrgLevelValueID) + ' IN ' +
        '(SELECT OrgLvlID FROM RMR_GetPreOrgLvlValues(OrganisationLevelValueID)))'

    EXEC sp_executesql @sql
    PRINT @sql
END

Eeeek… not the most user-friendly (and I’ve certainly written dynamic SQL like this before) but it was written like this to allow for different page ordering. There’s also a bug in this code where the ROW_NUMBER() needs to be conditionally partitioned before being filtered.

The solution isn’t necessarily the prettiest either. However, it’s now not a sql string being built up so the execution planner can pre-compile it and it is easier to read! The one disadvantage is if a new order by is introduced the SQL needs to be updated – however, I think this is a reasonable exchange:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY
            CASE WHEN @OrgLevelValueID is not null THEN OrganisationLevelValueID END
        ORDER BY
            CASE WHEN @OrderBy = 'DateAdded Desc' THEN DateAdded END DESC,
            CASE WHEN @OrderBy = 'Title Desc' THEN Title END DESC,
            CASE WHEN @OrderBy = 'Forename Desc' THEN Forename END DESC,
            CASE WHEN @OrderBy = 'Surname Desc' THEN Surname END DESC,
            CASE WHEN @OrderBy = 'PayrollNo Desc' THEN PayrollNo END DESC,
            CASE WHEN @OrderBy = 'DateAdded' THEN DateAdded END,
            CASE WHEN @OrderBy = 'Title' THEN Title END,
            CASE WHEN @OrderBy = 'Forename' THEN Forename END,
            CASE WHEN @OrderBy = 'Surname' THEN Surname END,
            CASE WHEN @OrderBy = 'PayrollNo' THEN PayrollNo END
        ) AS RowNum
    FROM LMv_EmployeeDrafts
    WHERE
        DraftStatus = @DraftStatus
        AND Surname LIKE '%' + @Surname + '%'
        AND PayrollNo LIKE '%' + @PayrollNo + '%'
) AS tmp
WHERE
    RowNum - 1 BETWEEN @StartRow AND @StartRow + @PageSize - 1
    AND (@OrgLevelValueID is null OR @OrgLevelValueID
        IN (SELECT OrgLvlID FROM RMR_GetPreOrgLvlValues(OrganisationLevelValueID)))

A couple of final caveats:

• I’ve not checked the final query in the execution planner to make sure it’s efficient!
• The surname and payroll filtering are being performed using a prefixed ‘%’ which means a table scan – pretty inefficient!

Setting Opera as the default browser on a Windows Mobile

I own an HTC Touch HD as was getting a bit annoyed at Internet Explorer being the default browser when selecting links from emails. However, Opera Mobile has a ‘hidden’ configuration screen and can be used to set it as the default :

1. Run Opera
2. Enter ‘opera:config’ in the address bar
3. Find the ‘Install’ section
4. Select ‘Browser First Time Launch’ checkbox
5. Press ‘Save’
6. Soft reset

Generating a list of numbers in T-SQL

I wanted to create a list of numbers so I could cross join them to another set of data to generate some test data quickly. There are ways to generate numbers using a CTE (Common Table Expression) or if you’re in a real hurry you can make us of a fairly undocumented table called master..spt_values. Seeing as this table isn’t well documented don’t go using it for production code since it might change but for quick one-off tasks it seems fine.

The following SQL creates a list of numbers from 0 to 2047 :

select [number]
from master..spt_values
where type = 'P'

ASP.NET Page Life Cycle Diagram

I saw this months ago and wondered where it had gone so when I came across it by chance whilst googling I thought I would save myself a link to it. Many thanks to Raymond Lewallen who credits Leon Andrianarivony!

Sproc Compile Locks

I came across an article on Microsofts support site which has raised a point which I’d never considered before. It basically says that if the user that executes a stored procedure is not the owner of the procedure then locks are acquired during the process to find a cached plan that may lead to blocking. It also states this could be avoided by fully qualifying the sproc name (e.g. dbo.mysproc) when calling it to avoid the extra lookups. This has been a problem for me in the past since the projects I’ve worked on had been setup with an account running as dbowner, however, if I was setting up a database from scratch I’d set up a specific user(s) for accessing the database and give it just the permissions it requires (sproc execute permissions for example.) I think, therefore, this would fall into these problem. However, I would also make use of the schema prefix (e.g. Sales.Order table) rather than leaving it as dbo and therefore avoid this problem altogether.

Generic Sorting Routine for ASP.NET GridView

I noticed a lot of code in the current project I’m working on which looked something like this :

switch (sortExpression)
{
    case "Name":
        items.OrderBy(i => i.Name);
        break;
    case "Date":
        items.OrderBy(i => i.Date);
        break;
    case "Cost":
        items.OrderBy(i => i.Cost);
        break;
}

This was then repeated all over again for descending orders!! I’ve now replaced it with something like this :

private void Populate(string sortExpression)
{
   var items = GetData();
   gvList.DataSource = CreateValueList(items).OrderByExpression(new OrderByExpression(sortExpression));
   gvList.DataBind();
}

The sort expression is handled by the GridView code such that AllowSorting is turned on and the OnSorting event is wired up to a method :

<asp:GridView ID="gvList" runat="server" AllowSorting="True" OnSorting="gvList_Sorting" DataKeyNames="ID">
   <Columns>
       <asp:TemplateField HeaderText="Name" SortExpression="Name">
           <ItemTemplate>
               <asp:Label Text='<%# DataBinder.Eval(Container.DataItem, "Name") %>' runat="server" ID="lblName" />
           </ItemTemplate>
       </asp:TemplateField>
   </Columns>
</asp:GridView>

The code-behind deals with the sorting events and calls populate :

protected void gvList_Sorting(object sender, GridViewSortEventArgs e)
{
   Populate(e.SortExpression + " " + GetSortDirection(e.SortExpression));
}

private string GetSortDirection(string column)
{

   // By default, set the sort direction to ascending.
   string sortDirection = "ASC";

   // Retrieve the last column that was sorted.
   string sortExpression = ViewState["SortExpression"] as string;

   if (sortExpression != null)
   {
       // Check if the same column is being sorted.
       // Otherwise, the default value can be returned.
       if (sortExpression == column)
       {
           string lastDirection = ViewState["SortDirection"] as string;
           if ((lastDirection != null) && (lastDirection == "ASC"))
           {
               sortDirection = "DESC";
           }
       }
   }

   // Save new values in ViewState.
   ViewState["SortDirection"] = sortDirection;
   ViewState["SortExpression"] = column;

   return sortDirection;
}

What this all boils down to is the OrderByExpression() extension method called in the Populate() method above:

   public static IEnumerable<TSource> OrderByExpression<TSource>(this IEnumerable<TSource> data, OrderByExpression expression)
   {
       string sortOrderMethod = (expression.SortOrder == SortOrderEnum.Ascending) ? "OrderBy" : "OrderByDescending";

       // data.OrderBy(o => o.propertyname);   
       var dataAsQueryable = data.AsQueryable<TSource>();
       ParameterExpression lambdaParameter = Expression.Parameter(typeof(TSource), "o");
       MemberExpression member = Expression.PropertyOrField(lambdaParameter, expression.SortProperties[0]);
       LambdaExpression lambda = Expression.Lambda(member, lambdaParameter);
       Type[] argumentTypes = { dataAsQueryable.ElementType, lambda.Body.Type };
       MethodCallExpression orderBy = Expression.Call(typeof(Queryable), sortOrderMethod, argumentTypes, dataAsQueryable.Expression, lambda);
       return dataAsQueryable.Provider.CreateQuery<TSource>(orderBy);
   }

I have to say thanks to Joseph Albahari and Ben Albahari of LinqPad fame since I found this solution to my problems (after getting within so close through my own efforts whilst our internet connection was down in the office!!) in the samples that came with LinqPad.

The code above creates a dynamic Linq expression then executes it through the queryables provider – although this only occurs once the whole linq expression is enumerated.

The last few modifications I need to make require the order by to cope with multiple properties (The OrderByExpression.SortProperties class already supports multiple properties but I’m only taking the first at the moment) and I need to ensure that the query always deferred since composable against a database – this is important since I’ve yet to consider paging in the GridView and I want to make sure that specifiying .Skip(n).Take(m) can be applied after the ordering but the Linq expression be composed as SQL and executed against the database in one go otherwise I’ll be returning all the rows to the application layer before extracting just the required rows for the current page.

Importing from Excel into SQL Server

I was going through a mountain of pain trying to get an import working from an Excel spreadsheet working into SQL Server 2005 but I finally managed it and here’s how…

Here’s the SQL I’m using (slightly abridged!) :

set transaction isolation level read committed

declare @sql nvarchar(1024)
set @sql = 'select * into ##Temp from opendatasource(''Microsoft.Jet.OLEDB.4.0'', ''Extended Properties=Excel 8.0;Data Source=' + @ExcelFileToImport + ''')...' + @ExcelWorkSheet
exec sp_executesql @sql

Points to note :

• I’m using dynamic sql because the opendatasource() doesn’t like being passed variables… so be sure to validate the contents of @ExcelFileToImport and @ExcelWorkSheet.
• Because I was forced to use dynamic sql I had to use a global temporary table. I wanted to load the data into a temporary table so that the columns were generated on-the-fly and I could check the correct columns were supplied by examining tempdb.sys.columns. It therefore had to be a global temporary because a standard one would have been thrown away after sp_executesql() had finished! For my situation the global temporary table is ok… but you’ll have to consider your own situation and whether concurrency allows.
• I kept getting the error below on the subsequent re-tries of uploading but worked around this by adding ‘set transaction isolation level read committed’ in the sproc.

  The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not support the required transaction interface.

I was having trouble executing within a transaction but with the changes made above the sql could be called with the following :

using (TransactionScope scope = new TransactionScope())
{
    UploadExcelIntoDatabase(importTicket, importFilename);
    scope.Complete();
}

Conditional Aggregate Totals in SQL

I’ve been working on a SQL Server Reporting Services report which had to display several counts on each row. Each row represented a project and each project could have several tasks that were either ordinary tasks or milestones and could also be escalated. The report need to show something like this :

Project Name	Milestone Counts					Task Count					Escalated Task Count
	1st Week	2nd Week	3rd Week	>=4th Week	Total	1st Week	2nd Week	3rd Week	>=4th Week	Total

As you can see a task initially has to break down into two major colours of milestone and ordinary task counts but then is broken down into counts in the 1st, 2nd, 3rd and greater than 4th week totals including the total itself.

Initially this seemed tricky when thinking in terms of using count() but there’s an incredibly simple technique that solves this but which isn’t immediately obvious. The Sum() aggregate function can contain an expression and in this example that expression could compare the task date against late week dates and return either a 1 or 0 :

select ProjectName,
   sum(case when IsMilestone = 1
      and TaskDate < @LateDate
      and Taskdate >@OneWeekLateDate
      then 1 else 0 end) as Week1LateMilestoneCount
from Project
group by ProjectName

I’ve only included one example there but I think it’s enough to get the idea.