Try .NET /elinq/
Powered by Try .NET

SQL Server CUME_DIST Function

Using SQL Server CUME_DIST() function over a result set example

To create a window we need to call AggregateBy() function, which let's specify the OVER clause:

var year = 2017;

var query = DbContext.Set<StaffSalesPercentile>()
    .Query((VwStaffSales sales, Staffs staffs, StaffSalesPercentile alias) =>
    {
        var fullName = CONCAT_WS(" ", staffs.FirstName, staffs.LastName);
        var cumeDist = AggregateBy(CUME_DIST())
            .OVER(ORDER(BY(sales.NetSales).DESC));

        var r = SELECT<StaffSalesPercentile>(fullName.@as(alias.FullName),
            sales.NetSales.@as(alias.NetSales),
            sales.Year.@as(alias.Year),
            cumeDist.@as(alias.Percentile));
        FROM(sales).JOIN(staffs).ON(staffs.StaffId == sales.StaffId);
        WHERE(sales.Year == year);

        return r;
    });

foreach (var percentile in query)
    Console.WriteLine((percentile.Year, percentile.FullName, percentile.NetSales, percentile.Percentile));

Using SQL Server CUME_DIST() function over a partition example

The OVER clause accepts an ORDER clause as above or PARTITION clause as below:

var years = new int?[] { 2016, 2017 };

var query = DbContext.Set<StaffSalesPercentile>()
    .Query((VwStaffSales sales, Staffs staffs, StaffSalesPercentile alias) =>
    {
        var fullName = CONCAT_WS(" ", staffs.FirstName, staffs.LastName);
        var cumeDist = AggregateBy(CUME_DIST())
            .OVER(PARTITION(BY(sales.Year))
                .ORDER(BY(sales.NetSales).DESC));

        var r = SELECT<StaffSalesPercentile>(fullName.@as(alias.FullName),
            sales.NetSales.@as(alias.NetSales),
            sales.Year.@as(alias.Year),
            cumeDist.@as(alias.Percentile));
        FROM(sales).JOIN(staffs).ON(staffs.StaffId == sales.StaffId);
        WHERE(years.Contains(sales.Year));

        return r;
    });

foreach (var percentile in query)
    Console.WriteLine((percentile.Year, percentile.FullName, percentile.NetSales, percentile.Percentile));

< BACK | HOME