Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    13

    Unanswered: Calculate column

    Hi,

    I have a column called "Days Pending" that I want to calculate based on other columns namely, "Date Invoiced" and "Date Recieved". How do I put a condition where this calculation is done ONLY when the "Date Recieved" field is NOT NULL. (no rHi,

    I have a column called "Days Pending" that I want to calculate based on other columns namely, "Date Invoiced" and "Date Recieved". How do I put a condition where this calculation is done ONLY when the "Date Recieved" field is NOT NULL. (no recieved date if the bill has not been paid,hence the days pending calculation)

    Sounds elementary, but just cant find the right command i guess!

    Suggestions?ecieved date if the bill has not been paid,hence the days pending calculation)

    Sounds elementary, but just cant find the right command i guess!

    Suggestions?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    y'dont
    its derived data, so derive it as required in a query, when required.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    13

    Errrm

    Didnt exactly solve my problem, but thanx for the quick reply!

    Im tryin to put this condition in a query. when the query reads Date Invoiced and finds Null value for Date Recieved, it calculates date pending, by subtracting from the current date.
    I was tryin to put a null value in Date Pending when there is a Date Recieved.

    Thanks again!

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    Maybe use calculated columns, like this (tested on 2008R2):
    Code:
    create table mytable(
      DateInvoiced date not null,
      DateReceived date default null,
      DaysPending AS DateDiff( day, DateInvoiced, DateReceived),
      CalculationIsDone AS 
    		CASE WHEN DateReceived IS NOT NULL
    		     THEN 'Done'
    		     ELSE 'Not done'
    		END
    );
    
    Insert into mytable( DateInvoiced) values ( SYSDATETIME() );
    
    Select * from mytable;
    
    DateInvoiced DateReceived DaysPending CalculationIsDone
    ------------ ------------ ----------- -----------------
    2012-02-18   NULL         NULL        Not done
    
    (1 row(s) affected)
    
    
    Update mytable 
    set DateReceived = DateAdd( day, 12, SYSDATETIME());
    
    Select * from mytable;
    
    DateInvoiced DateReceived DaysPending CalculationIsDone
    ------------ ------------ ----------- -----------------
    2012-02-18   2012-03-01   12          Done
    
    (1 row(s) affected)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select datediff(now(), DateInvoiced) as DaysSinceInvoiced from mytable
    where DateReceived is not null
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2012
    Posts
    13
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •