• First_Value

شرح Syntax:

1- Scalar_expression: مقدار آن می‌تواند نام یک فیلد یا Subquery باشد.

2- Over : در بخش اول بطور مفصل آن را بررسی نمودیم.

قبل از بررسی تابع First_Value،ابتدا Script زیر را اجرا نمایید، که شامل یک جدول و درج چند رکورد در آن است.

Create Table Test_First_Last_Value
(SalesOrderID int not null,
 SalesOrderDetailID int not null ,
 OrderQty smallint not null);
 GO
Insert Into Test_First_Last_Value
       Values (43662,49,1),(43662,50,3),(43662,51,1),
          (43663,52,1),(43664,53,1),(43664,54,1),
  (43667,77,3),(43667,78,1),(43667,79,1),
          (43667,80,1),(43668,81,3),(43669,110,1),
          (43670,111,1),(43670,112,2),(43670,113,2),
          (43670,114,1),(43671,115,1),(43671,116,2)

مثال: ابتدا Scriptی ایجاد می‌نماییم،بطوریکه جدول Test_Firts_Last_Value را براساس فیلد SalesOrderID گروه بندی نموده و اولین مقدار فیلد SalesOrderDetailID در هرگروه را مشخص نماید.

SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
       FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
       ORDER BY SalesOrderDetailID) FstValue
FROM Test_First_Last_Value s
     WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
     ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty

خروجی:

     مطابق Script چهار گروه در خروجی ایجاد شده است و در فیلد FstValue ، اولین مقدار هر گروه نمایش داده می‌شود. اگر بخش‌های قبلی Window Function‌ها را مطالعه کرده باشید، تحلیل این تابع کار بسیار ساده ای است. 

• Last_Value

      این تابع نیز در نسخه SQL Server 2012 ارائه گردیده است. و آخرین مقدار از یک مجموعه مقادیر را بر می‌گرداند، به عبارتی فانکشن Last_Value عکس فانکشن First_Value عمل می‌نماید و Syntax آن به شرح ذیل میباشد:

LAST_VALUE ( [scalar_expression ) 
    OVER ( [ partition_by_clause ] order_by_clause rows_range_clause ) 

شرح Syntax تابع Last_Value شبیه به تابع First_Value می‌باشد.

مثال: همانند مثال قبل Scriptی ایجاد می‌نماییم،بطوریکه جدول Test_Firts_Last_Value را براساس فیلد SalesOrderID گروه بندی نموده و آخرین مقدار فیلد SalesOrderDetailID در هرگروه را مشخص نماید. 

SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
       LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
       ORDER BY SalesOrderDetailID RANGE BETWEEN UNBOUNDED PRECEDING  AND UNBOUNDED FOLLOWING)  LstValue
FROM Test_First_Last_Value s
     WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
     ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty

خروجی:

خروجی جدول،به چهار گروه تقسیم،و آخرین مقدار هر گروه،در فیلد LstValue نمایش داده شده است. در این مثال نیز تحلیلی نخواهیم داشت، چون فرض بر آن است که بخش‌های قبلی را مطالعه نموده ایم.

موفق باشید.

[windows drive]:\Windows\System32\drivers\etc

# localhost name resolution is handled within DNS itself.
127.0.0.1  localhost
127.0.0.1 tehran.localhost
127.0.0.1 mashhad.localhost
127.0.0.1 sabzevar.localhost

host = Request.Headers["HOST"].ToLower();

void Application_BeginRequest(object sender, EventArgs e)
    {
        string host, url, newUrl;
        host = Request.Headers["HOST"].ToLower();   //for localhost
        //host = Request.Url.ToString();  //  for server
        url = Request.Url.PathAndQuery.ToLower();
        
        if (host.EndsWith(".localhost:8001") && (url == "" || url == "/"))
        {
            int cityId = City.GetCityIdByCityName(host.Replace(".localhost:8001", ""));
            newUrl = string.Format("~/postlist/{0}/", cityId);
            HttpContext.Current.RewritePath(newUrl);
            return;
        }

    }

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable()]
[SqlUserDefinedType(Format.Native)]
public struct JalaliDate : INullable
{
    private Int16 m_Year;
    private byte m_Month;
    private byte m_Day;
    private byte m_Hour;
    private byte m_Minute;
    private byte m_Second;
    private bool is_Null;


    public Int16 Year
    {
        get
        {
            return (this.m_Year);
        }
        set
        {
            m_Year = value;
        }
    }

    public byte Month
    {
        get
        {
            return (this.m_Month);
        }
        set
        {
            m_Month = value;
        }
    }

    public byte Day
    {
        get
        {
            return (this.m_Day);
        }
        set
        {
            m_Day = value;
        }
    }

    public byte Hour
    {
        get
        {
            return (this.m_Hour);
        }
        set
        {
            m_Hour = value;
        }
    }

    public byte Minute
    {
        get
        {
            return (this.m_Minute);
        }
        set
        {
            m_Minute = value;
        }
    }

    public byte Second
    {
        get
        {
            return (this.m_Second);
        }
        set
        {
            m_Second = value;
        }
    }

    public bool IsNull
    {
        get
        {
            return is_Null;
        }
    }

    public static JalaliDate Null
    {
        get
        {
            JalaliDate jl = new JalaliDate();
            jl.is_Null = true;
            return (jl);
        }
    }


    public override string ToString()
    {
        if (this.IsNull)
        {
            return "NULL";
        }
        else
        {
            return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2") + " " + this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
        }
    }


    public static JalaliDate Parse(SqlString s)
    {
        if (s.IsNull)
        {
            return Null;
        }

        System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
        string str = Convert.ToString(s);
        string[] JDate = str.Split(' ')[0].Split('/');

        JalaliDate jl = new JalaliDate();

        jl.Year = Convert.ToInt16(JDate[0]);
        byte MonthsInYear = (byte)pers.GetMonthsInYear(jl.Year);
        jl.Month = (byte.Parse(JDate[1]) <= MonthsInYear ? (byte.Parse(JDate[1]) > 0 ? byte.Parse(JDate[1]) : (byte)1) : MonthsInYear);
        byte DaysInMonth = (byte)pers.GetDaysInMonth(jl.Year, jl.Month); ;
        jl.Day = (byte.Parse(JDate[2]) <= DaysInMonth ? (byte.Parse(JDate[2]) > 0 ? byte.Parse(JDate[2]) : (byte)1) : DaysInMonth);
        if (str.Split(' ').Length > 1)
        {
            string[] JTime = str.Split(' ')[1].Split(':');
            jl.Hour = (JTime.Length >= 1 ? (byte.Parse(JTime[0]) < 23 && byte.Parse(JTime[0]) >= (byte)0 ? byte.Parse(JTime[0]) : (byte)0) : (byte)0);
            jl.Minute = (JTime.Length >= 2 ? (byte.Parse(JTime[1]) < 59 && byte.Parse(JTime[1]) >= (byte)0 ? byte.Parse(JTime[1]) : (byte)0) : (byte)0);
            jl.Second = (JTime.Length >= 3 ? (byte.Parse(JTime[2]) < 59 && byte.Parse(JTime[2]) >= (byte)0 ? byte.Parse(JTime[2]) : (byte)0) : (byte)0);
        }
        else { jl.Hour = 0; jl.Minute = 0; jl.Second = 0; }

        return (jl);
    }

    public SqlString GetDate()
    {
        return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2");
    }

    public SqlString GetTime()
    {
        return this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
    }

    public SqlDateTime ToGregorianTime()
    {
        System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
        return SqlDateTime.Parse(pers.ToDateTime(this.Year, this.Month, this.Day, this.Hour, this.Minute, this.Second, 0).ToString());
    }

    public SqlString JalaliDateAdd(SqlString interval, int increment)
    {
        System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
        DateTime dt = pers.ToDateTime(this.Year, this.Month, this.Day, this.Hour, this.Minute, this.Second, 0);
        string CInterval = interval.ToString();
        bool isConvert = true;
        switch (CInterval)
        {
            case "Year":
                dt = pers.AddYears(dt, increment);
                break;
            case "Month":
                dt = pers.AddMonths(dt, increment);
                break;
            case "Day":
                dt = pers.AddDays(dt, increment);
                break;
            case "Hour":
                dt = pers.AddHours(dt, increment);
                break;
            case "Minute":
                dt = pers.AddMinutes(dt, increment);
                break;
            case "Second":
                dt = pers.AddSeconds(dt, increment);
                break;
            default:
                isConvert = false;
                break;
        }

        if (isConvert == true)
        {
            this.Year = (Int16)pers.GetYear(dt);
            this.Month = (byte)pers.GetMonth(dt);
            this.Day = (byte)pers.GetDayOfMonth(dt);
            this.Hour = (byte)pers.GetHour(dt);
            this.Minute = (byte)pers.GetMinute(dt);
            this.Second = (byte)pers.GetSecond(dt);
        }


        return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2") + " " + this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
    }
}

USE Northwind

GO

CREATE TABLE dbo.TestTable
(
Id int NOT NULL IDENTITY (1, 1),
TestDate dbo.JalaliDate NULL
)  ON [PRIMARY]
GO

Insert into TestTable (TestDate) Values ('1392/02/09'),('1392/02/09 22:40'),('1392/12/30 22:40')

sp_configure 'clr enabled', 1
Reconfigure

Insert into TestTable (TestDate) Values ('1392/02/09'),('1392/02/09 22:40'),('1392/12/30 22:40')

Declare @a JalaliDate  = '1392/02/07 00:00:00'
Declare @b JalaliDate = '1392/02/05 00:00:00'

SELECT DATEDIFF("DAY",@b.ToGregorianTime(),@a.ToGregorianTime()) AS DiffDate

- Tigertoolbox
- The SQL Server Maintenance Solution 
- dbatools 
- Microsoft SQL Server Samples Repository
- SQL-Server-First-Responder-Kit

•  event اجرا شده (مثلا push) و شخص فراخوان این رخداد
• جزئیات commit ایی که منجر به این event شده از جمله SHA hash کامیت، author و committer کامیت
• تعداد Issue ها، Fork‌ها و Star‌های ریپازیتوری در آن لحظه
• و بسیاری اطلاعات دیگر

    steps:
    - name: Dump GitHub Context
      env:
        GITHUB_CONTEXT: ${{ toJson(github) }}
      run: echo "$GITHUB_CONTEXT"

GITHUB_CONTEXT: {
  "token": "***",
  "ref": "refs/heads/master",
  "sha": "3251394ad66ae8419e606fbf78570906ff2f01d3",
  "repository": "mjebrahimi/github_actions_test",
  "repositoryUrl": "git://github.com/mjebrahimi/github_actions_test.git",
  "run_id": "66966756",
  "run_number": "17",
  "actor": "mjebrahimi",
  "workflow": ".NET Core",
  "head_ref": "",
  "base_ref": "",
  "event_name": "push",
  "event": {
    "after": "3251394ad66ae8419e606fbf78570906ff2f01d3",
    "base_ref": null,
    "before": "b6c382e8fe74916daf6821a5a71efe480bd98a13",
    "commits": [
      {
        "author": {
          "email": "mj.ebrahimi72@gmail.com",
          "name": "Mohammad Javad Ebrahimi",
          "username": "mjebrahimi"
        },
        "committer": {
          "email": "noreply@github.com",
          "name": "GitHub",
          "username": "web-flow"
        },
        "distinct": true,
        "id": "3251394ad66ae8419e606fbf78570906ff2f01d3",
        "message": "Update dotnetcore.yml",
        "timestamp": "2020-03-31T04:48:53+04:30",
        "tree_id": "e27a05129010b3b2a7b18b92d91cd73a32babb8f",
        "url": "https://github.com/mjebrahimi/github_actions_test/commit/3251394ad66ae8419e606fbf78570906ff2f01d3"
      }
    ],
    "compare": "https://github.com/mjebrahimi/github_actions_test/compare/b6c382e8fe74...3251394ad66a",
    "created": false,
    "deleted": false,
    "forced": false,
    "head_commit": {
      "author": {
        "email": "mj.ebrahimi72@gmail.com",
        "name": "Mohammad Javad Ebrahimi",
        "username": "mjebrahimi"
      },
      "committer": {
        "email": "noreply@github.com",
        "name": "GitHub",
        "username": "web-flow"
      },
      "distinct": true,
      "id": "3251394ad66ae8419e606fbf78570906ff2f01d3",
      "message": "Update dotnetcore.yml",
      "timestamp": "2020-03-31T04:48:53+04:30",
      "tree_id": "e27a05129010b3b2a7b18b92d91cd73a32babb8f",
      "url": "https://github.com/mjebrahimi/github_actions_test/commit/3251394ad66ae8419e606fbf78570906ff2f01d3"
    },
    "pusher": {
      "email": "mj.ebrahimi72@gmail.com",
      "name": "mjebrahimi"
    },
    "ref": "refs/heads/master",
    "repository": {
      "archive_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/{archive_format}{/ref}",
      "archived": false,
      "assignees_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/assignees{/user}",
      "blobs_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/git/blobs{/sha}",
      "branches_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/branches{/branch}",
      "clone_url": "https://github.com/mjebrahimi/github_actions_test.git",
      "collaborators_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/collaborators{/collaborator}",
      "comments_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/comments{/number}",
      "commits_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/commits{/sha}",
      "compare_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/compare/{base}...{head}",
      "contents_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/contents/{+path}",
      "contributors_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/contributors",
      "created_at": 1585584602,
      "default_branch": "master",
      "deployments_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/deployments",
      "description": null,
      "disabled": false,
      "downloads_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/downloads",
      "events_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/events",
      "fork": false,
      "forks": 0,
      "forks_count": 0,
      "forks_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/forks",
      "full_name": "mjebrahimi/github_actions_test",
      "git_commits_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/git/commits{/sha}",
      "git_refs_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/git/refs{/sha}",
      "git_tags_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/git/tags{/sha}",
      "git_url": "git://github.com/mjebrahimi/github_actions_test.git",
      "has_downloads": true,
      "has_issues": true,
      "has_pages": false,
      "has_projects": true,
      "has_wiki": true,
      "homepage": null,
      "hooks_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/hooks",
      "html_url": "https://github.com/mjebrahimi/github_actions_test",
      "id": 251358686,
      "issue_comment_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/issues/comments{/number}",
      "issue_events_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/issues/events{/number}",
      "issues_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/issues{/number}",
      "keys_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/keys{/key_id}",
      "labels_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/labels{/name}",
      "language": "C#",
      "languages_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/languages",
      "license": null,
      "master_branch": "master",
      "merges_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/merges",
      "milestones_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/milestones{/number}",
      "mirror_url": null,
      "name": "github_actions_test",
      "node_id": "MDEwOlJlcG9zaXRvcnkyNTEzNTg2ODY=",
      "notifications_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/notifications{?since,all,participating}",
      "open_issues": 0,
      "open_issues_count": 0,
      "owner": {
        "avatar_url": "https://avatars1.githubusercontent.com/u/23256135?v=4",
        "email": "mj.ebrahimi72@gmail.com",
        "events_url": "https://api.github.com/users/mjebrahimi/events{/privacy}",
        "followers_url": "https://api.github.com/users/mjebrahimi/followers",
        "following_url": "https://api.github.com/users/mjebrahimi/following{/other_user}",
        "gists_url": "https://api.github.com/users/mjebrahimi/gists{/gist_id}",
        "gravatar_id": "",
        "html_url": "https://github.com/mjebrahimi",
        "id": 23256135,
        "login": "mjebrahimi",
        "name": "mjebrahimi",
        "node_id": "MDQ6VXNlcjIzMjU2MTM1",
        "organizations_url": "https://api.github.com/users/mjebrahimi/orgs",
        "received_events_url": "https://api.github.com/users/mjebrahimi/received_events",
        "repos_url": "https://api.github.com/users/mjebrahimi/repos",
        "site_admin": false,
        "starred_url": "https://api.github.com/users/mjebrahimi/starred{/owner}{/repo}",
        "subscriptions_url": "https://api.github.com/users/mjebrahimi/subscriptions",
        "type": "User",
        "url": "https://api.github.com/users/mjebrahimi"
      },
      "private": false,
      "pulls_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/pulls{/number}",
      "pushed_at": 1585613933,
      "releases_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/releases{/id}",
      "size": 40,
      "ssh_url": "git@github.com:mjebrahimi/github_actions_test.git",
      "stargazers": 0,
      "stargazers_count": 0,
      "stargazers_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/stargazers",
      "statuses_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/statuses/{sha}",
      "subscribers_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/subscribers",
      "subscription_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/subscription",
      "svn_url": "https://github.com/mjebrahimi/github_actions_test",
      "tags_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/tags",
      "teams_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/teams",
      "trees_url": "https://api.github.com/repos/mjebrahimi/github_actions_test/git/trees{/sha}",
      "updated_at": "2020-03-31T00:01:15Z",
      "url": "https://github.com/mjebrahimi/github_actions_test",
      "watchers": 0,
      "watchers_count": 0
    },
    "sender": {
      "avatar_url": "https://avatars1.githubusercontent.com/u/23256135?v=4",
      "events_url": "https://api.github.com/users/mjebrahimi/events{/privacy}",
      "followers_url": "https://api.github.com/users/mjebrahimi/followers",
      "following_url": "https://api.github.com/users/mjebrahimi/following{/other_user}",
      "gists_url": "https://api.github.com/users/mjebrahimi/gists{/gist_id}",
      "gravatar_id": "",
      "html_url": "https://github.com/mjebrahimi",
      "id": 23256135,
      "login": "mjebrahimi",
      "node_id": "MDQ6VXNlcjIzMjU2MTM1",
      "organizations_url": "https://api.github.com/users/mjebrahimi/orgs",
      "received_events_url": "https://api.github.com/users/mjebrahimi/received_events",
      "repos_url": "https://api.github.com/users/mjebrahimi/repos",
      "site_admin": false,
      "starred_url": "https://api.github.com/users/mjebrahimi/starred{/owner}{/repo}",
      "subscriptions_url": "https://api.github.com/users/mjebrahimi/subscriptions",
      "type": "User",
      "url": "https://api.github.com/users/mjebrahimi"
    }
  },
  "workspace": "/home/runner/work/github_actions_test/github_actions_test",
  "action": "run1"
}