CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
user240141
user240141

Reputation:

Taking sum of column based on date range in T-Sql

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

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

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

Upvotes: 2

Views: 2324

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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)

Upvotes: 1

Santiago Regojo
Santiago Regojo

Reputation: 405

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)

Upvotes: 0

ksalk
ksalk

Reputation: 503

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)

Upvotes: 1

Related Questions