Date computation

[Lethugs]

Hi,

Im trying to get the depreciation value of an equipment every month. My system gets the number of months from date of purchased up to current date for the computation of depreciation. I can run the report anytime but the computation must be like this:

Sample:
Date purchased January 1-15 : When I run the report for January, there should be 1 month depreciation, 2 months for the month of Feb etc.

Date purchased January 16-31 : When I run the report for January, No depreciation yet, 1 month for the month of Feb, 2 months for March, etc.

Date Purchased Feb 1-15 : When I run the report for January, Should not be displayed, then 1 month depreciation for the month of Feb.

The Idea is if the equipment is bought from 1-15 it should have 1 month depreciation after the end of that month while those bought from 16-31, it should be counted the following month

Im using this in the select statement to compute for the month but not satsfying the above condition

DATEDIFF(MONTH, (CONVERT(Datetime, [Item].[Dpchsed],101)), GETDATE()) AS [Depreciated Months]

I already got the computation for depreciation, I just need how to compute the number of months from the condition above. Im using vb.net and sql server 2008 r2

Thanks for helping

[LastMitch]

I already got the computation for depreciation, I just need how to compute the number of months from the condition above. Im using vb.net and sql server 2008 r2

This statement:

DATEDIFF(MONTH, (CONVERT(Datetime, [Item].[Dpchsed],101)), GETDATE()) AS [Depreciated Months]

Why did you created this and put it in DATEDIFF() it doesn't look right:

(CONVERT(Datetime, [Item].[Dpchsed],101))    

I don't know what is column item.

Instead of putting everything in one query create a query include AND or WHERE.

SELECT DATEDIFF(MONTH, DATETIME, GETDATE()) 
AND CONVERT(DATETIME, [Item].[Dpchsed],101) 
AS [Depreciated Months];

Something like what I wrote so the database can read and separate the variable from your query.

I think your database can't read your query correctly so that's why it didn't calculate.

[adam_k]

You could try something like this:

DATEDIFF(MONTH, (CONVERT(Datetime, case when day([Item].[Dpchsed]) <= 15 then [Item].[Dpchsed] else dateadd(m, 1, [Item].[Dpchsed]) end ,101)), getdate()) AS [Depreciated Months]

but... When you run the query the first 15 days of the month, you will be missing a month and same will happen for items bought with day(item.Dpchsed) > day(getdate()), but that's the limitation of the datediff. Also, since we are adding a month to the purchase date you should probably either check the dates in the case or in the where clause or datediff will return negative numbers.

My suggestion would be to drop the day part of the date and set all purchases to have occured in the 1st day of each month and instead of using getdate(), use the first day of the next month. This will solve the problem of datediff.

[adam_k]

@LastMitch, I'm sorry to inform you that OP's statement is valid, just not what he is looking for.
on the other hand:

SELECT DATEDIFF(MONTH, DATETIME, GETDATE()) 
AND CONVERT(DATETIME, [Item].[Dpchsed],101) 
AS [Depreciated Months];

won't fly.
What OP is doing is convert Dpchsed from table Item to a datetime field with a particular format (101) and then compare it against today's date to get how many months apart the 2 days are.

Your query won't be processed at all, since AND is not valid in the select statement, DATETIME is a datatype and not a field (At least not one I can see in OPs post) and to convert Dpchsed to a datetime won't do much by itself.

No offense intended.

[LastMitch]

No offense intended.

I won't get offended. =)

It's good to learn from my mistakes.

You have more experience.

[Lethugs]

Thanks adam_k and Thank you for explaining my post. I tried revising your code to different approach but no luck at all. I will go with your advise but will not still drop the day part of Dpchsed. I thinking of the idea checking the day if falls from 1-15 then force it to turn 1st day of the month, while if falls from 16-30 then make it 16, this is internally only not the physical date, can you please guide me how to do it?

thanks again

[adam_k]

I'll use SQLfiddle -hope that's OK - to prove my point about setting day part of the date to '01' and also give you the answer how to set it to '01' or '16' depending on the date stored in the field.

[SQL Fiddle][1]

**MS SQL Server 2008 Schema Setup**:

    create table test (date datetime)

    insert into test
    values 
    ('2013-01-01')
    insert into test
    values 
    ('2013-01-10')
    insert into test
    values 
    ('2013-01-15')
    insert into test
    values 
    ('2013-01-20')
    insert into test
    values 
    ('2013-01-31')
    insert into test
    values 
    ('2013-02-01')
    insert into test
    values 
    ('2013-02-10')

    insert into test
    values 
    ('2013-02-20')
    insert into test
    values 
    ('2013-03-20')

**Query 1**:

    select 'a',DATEDIFF(MONTH, (CONVERT(Datetime, case when day(date) <= 15 then convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '01')
    else convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '16')
    end ,101)), '2013-03-01') AS [Depreciated Months], date
    from test
    union
    select 'b', DATEDIFF(MONTH, (CONVERT(Datetime, case when day(date) <= 15 then convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '01')
    else convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '16')
    end ,101)), '2013-02-10') AS [Depreciated Months], date
    from test
    union 
    select 'c', 
    DATEDIFF(MONTH, (CONVERT(Datetime, case when day(date) > 15 then convert(datetime, convert(char(4),year(dateadd(m,1,date))) + 
    '-' + convert(varchar(2), month(dateadd(m,1,date))) + '-' + '01')
    else convert(datetime, convert(char(4),year(date)) + 
    '-' + convert(varchar(2), month(date)) + '-' + '01')
    end ,101)), 

    convert(datetime, convert(char(4),year('2013-03-01')) + 
    '-' + convert(varchar(2), month('2013-03-01')) + '-' + '28')) 

    AS [Depreciated Months], date
    from test

**[Results][2]**:

    | COLUMN_0 | DEPRECIATED MONTHS |                            DATE |
    -------------------------------------------------------------------
    |        a |                  0 |    March, 20 2013 00:00:00+0000 |
    |        a |                  1 | February, 01 2013 00:00:00+0000 |
    |        a |                  1 | February, 10 2013 00:00:00+0000 |
    |        a |                  1 | February, 20 2013 00:00:00+0000 |
    |        a |                  2 |  January, 01 2013 00:00:00+0000 |
    |        a |                  2 |  January, 10 2013 00:00:00+0000 |
    |        a |                  2 |  January, 15 2013 00:00:00+0000 |
    |        a |                  2 |  January, 20 2013 00:00:00+0000 |
    |        a |                  2 |  January, 31 2013 00:00:00+0000 |
    |        b |                 -1 |    March, 20 2013 00:00:00+0000 |
    |        b |                  0 | February, 01 2013 00:00:00+0000 |
    |        b |                  0 | February, 10 2013 00:00:00+0000 |
    |        b |                  0 | February, 20 2013 00:00:00+0000 |
    |        b |                  1 |  January, 01 2013 00:00:00+0000 |
    |        b |                  1 |  January, 10 2013 00:00:00+0000 |
    |        b |                  1 |  January, 15 2013 00:00:00+0000 |
    |        b |                  1 |  January, 20 2013 00:00:00+0000 |
    |        b |                  1 |  January, 31 2013 00:00:00+0000 |
    |        c |                 -1 |    March, 20 2013 00:00:00+0000 |
    |        c |                  0 | February, 20 2013 00:00:00+0000 |
    |        c |                  1 |  January, 20 2013 00:00:00+0000 |
    |        c |                  1 |  January, 31 2013 00:00:00+0000 |
    |        c |                  1 | February, 01 2013 00:00:00+0000 |
    |        c |                  1 | February, 10 2013 00:00:00+0000 |
    |        c |                  2 |  January, 01 2013 00:00:00+0000 |
    |        c |                  2 |  January, 10 2013 00:00:00+0000 |
    |        c |                  2 |  January, 15 2013 00:00:00+0000 |



  [1]: http://sqlfiddle.com/#!3/798cd/1

  [2]: http://sqlfiddle.com/#!3/798cd/1/0

I've included 3 different queries (a,b,c) and their matching results to show you the difference in results. I'm not using getdate() on purpose to show what the query results would be if run in different dates. Feel free to change it to getdate(), but in query c only change it inside the functions and keep the rest as is.

In my opinion, the query that satisfies all the requirements and especially this one

I can run the report anytime but the computation must be like this

is query c. By manipulating both the date and getdate, you can be sure that 1 month will be counted for even when you've purchased equipment at February 10 and you run the query at February 20.

Let me know if additional help is needed.

[Lethugs]

It worked, all item purchased within 1-15 days, i forced the day to first day of the month then added 1 to its total month, those from 16-30 forced it to turn last day of the month then computed the month difference. Thanks for the idea adam_k. the only problem is that even i run the report at the first day of the month, those bought from previous month already has depreciation, but thats ok for now. :)

Thanks for all your help