用日期方法进行SQL查询。 [英] Sql query with date approach .

问题描述

I need a Column where condition is Target_L *X / Y

where I need x and y should come automatically from query.
Y=Total Number of working days in month ,Excluding Sunday  i.e for Jan, 27 days are  working days.
X=Working days till yesterday's date i.e 23(Minus Sunday, 1 to 26 = 26-3=23)
   today is 27 january 2018 

final =<pre>Target_L

* 23/27



-------- ----------

还有第二种方法

X =总工作日，即包括星期日在内的1月31日

Y =到日期工作日包括星期天

26/31



来自两者任何方法对我都有好处，我需要q查询这个..我有日期列，月份列。

使用SQL SERVER



我尝试过：



sql server date方法的新方法

* 23/27

------------------
There is a second approach also
X= Total working days i.e 31 for January Including Sunday
Y=till date working days Including Sunday
26/31

From both Any approach is Good for me,I need q query for this ..I have date column,Month column.
Using SQL SERVER

What I have tried:

new to sql server date approach

推荐答案

你需要一个返回工作天数的函数两个约会之间。但我建议一个CLR，它对这种类型的计算更快。

You need a function that return the number of working days between two date. but i suggest a CLR that it's faster for that type of computation.

  [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read)]
    public static SqlInt32 NETWORKDAYS(SqlDateTime start, SqlDateTime end, object weekend, SqlChars holidays)
    {
     
        if (start.IsNull || end.IsNull) return SqlInt32.Null;
        int sign = start <= end ? 1 : -1;
        if (start > end)
        {
            SqlDateTime tmp = start;
            start = end;
            end = tmp;
        }
        int stage1 = end.Value.Date.Subtract(start.Value.Date).Days + 1;
        int d1 = (int) GetStartWeekOffsetforMonday(start.Value.DayOfWeek);
        int d2 = (int)GetStartWeekOffsetforMonday(end.Value.DayOfWeek);
        int weekendcount;
        bool[] isweekend = GetWeekends(weekend, out weekendcount, "NETWORKDAYS");
        int remnant = (stage1 - 1) % 7;
        int stage2 = stage1 - weekendcount * (stage1 - remnant) / 7;
        for (int i = d1; i <= d1 + remnant; i++) if (isweekend[i % 7]) stage2--;
        if (holidays.IsNull) return stage2 * sign;
        List<DateTime> hols = GetHolidays(holidays, "NETWORKDAYS");
        foreach(DateTime hday in hols)
            if (!isweekend[ GetStartWeekOffsetforMonday(hday.DayOfWeek)] && hday >= start.Value.Date && hday <= end.Value.Date) stage2--;
        return stage2 * sign;
    }

private static int GetStartWeekOffsetforMonday(DayOfWeek Day)
    {

        switch (Day)
        {
            case DayOfWeek.Monday: return 0;
               
            case   DayOfWeek.Tuesday: return 1;
            case DayOfWeek.Wednesday: return 2;
            case DayOfWeek.Thursday:  return 3;
            case DayOfWeek.Friday:    return 4;
             case   DayOfWeek.Saturday:  return 5;
            case   DayOfWeek.Sunday  :    return 6;
        }
        return 0;
    }

这篇关于用日期方法进行SQL查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！