如何找到每个值的日期之间的总数 [英] How to find the total between the dates for each values

问题描述

表 1

Period  datefrom   dateto      code  id

01/2012 18/12/2011 28/12/2011  A     Emp1
01/2012 11/01/2012 14/01/2012  B     Emp1
02/2012 20/12/2011 25/12/2011  A     Emp2
02/2012 01/02/2012 08/01/2012  B     Emp2 'from and to date is greater than current date.
.....

我想通过系统日期验证获取 datefrom 和 dateto 之间的总价值

i want to take total of value between datefrom and dateto with system date validation

预期输出

ID     PERIOD   A  B TOTAL

Emp1   01/2012  11 4 15
Emp2   02/2012  6  0 6 

'B 为 0，因为起始日期和截止日期大于当前日期.

'B is 0 because from and to date is greater than current date.

如何查询这个计算..?

How to make a query for this calcuatlion..?

任何建议....

推荐答案

SET DATEFORMAT dmy

SELECT
    ID,
    Period,
    (SELECT ISNULL(SUM(DATEDIFF(DAY,datefrom,dateto) + 1),0)
        FROM Test 
        WHERE DATEDIFF(DAY,datefrom,dateto) > 0 and code = 'A'
        and x.Period = Period and x.id = id)
     as ACode,
    (SELECT ISNULL(SUM(DATEDIFF(DAY,datefrom,dateto) + 1),0)
        FROM Test 
        WHERE DATEDIFF(DAY,datefrom,dateto) > 0 and code = 'B'
        and x.Period = Period and x.id = id)
     as Bcode
FROM TABLENAME x
GROUP BY
    Period,
    ID

添加到日期差异的加一是为了说明部分天数.

The plus one added to the date diff is to account for part days.

这篇关于如何找到每个值的日期之间的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！