根据T-Sql中的日期范围获取列的总和

I have a column called Work Done where on daily basis some amount of work is caarried out. It has columns

我有一个名为Work Done的专栏,每天都会开展一些工作。它有专栏

Id, VoucherDt, Amount

Now my report has scenario to print the sum of amount till date of the month. For example if Current date is 3rd September 2013 then the query will pick all records of 1st,2nd and 3rd Sept and return a sum of that. I am able to get the first date of the current month. and I am using the following condition

现在我的报告有一个方案可以打印到月份之前的金额总和。例如,如果当前日期是2013年9月3日,那么查询将选择9月1日,2日和3日的所有记录并返回其总和。我能够获得当月的第一个日期。我正在使用以下条件

VoucherDt between FirstDate and GetDate() but it doesnot givign the desired result. So kindly suggest me the proper where condition.

FirstDate和GetDate()之间的凭证,但它没有给出期望的结果。亲切地建议我适当的条件。

3 个解决方案

#1

SELECT SUM(AMOUNT) SUM_AMOUNT FROM <table>
WHERE VoucherDt >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AND VoucherDt < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1)

#2

I think that there might be a better solution but this should work:

我认为可能有更好的解决方案,但这应该有效:

where YEAR(VoucherDt) = YEAR(CURRENT_TIMESTAMP) 
and MONTH(VoucherDt) = MONTH(CURRENT_TIMESTAMP)
and  DAY(VoucherDt) <= DAY(CURRENT_TIMESTAMP)

#3

Try to calc the number of months from the first date that you can store in a datetime an your target dates.

尝试计算从您可以在日期时间和目标日期存储的第一个日期开始的月数。

SELECT SUM(amount)
FROM
(
    SELECT 100000 AS amount, '2013-09-03' AS dt
    UNION ALL SELECT 10000,  '2013-09-02'
    UNION ALL SELECT 1000,   '2013-09-01'
    UNION ALL SELECT 100,    '2013-08-02'
    UNION ALL SELECT 10,     '2013-01-31'
    UNION ALL SELECT 2,      '2012-09-03'
    UNION ALL SELECT 2,      '2012-09-02'
    UNION ALL SELECT 1,      '2012-09-01'
) SourceData
WHERE DATEDIFF(m, '1900-1-1', GETDATE()) = DATEDIFF(m, '1900-1-1', SourceData.dt)

#1

SELECT SUM(AMOUNT) SUM_AMOUNT FROM <table>
WHERE VoucherDt >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AND VoucherDt < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1)

#2