Post reply

IIf statements

  • August 31, 2005 at 8:59 am

    #110270

    Hi Can anyone help me please?

     

    I am new to sql and am currently upgrading from an access db to sql server 2000.

     

    I am having difficulty in reproducing a query. I have 2 tables ([CARAVAN] and [INVOICING]. The Caravan table contains stock info such as InvoiceDate, Make, Year etc and is linked to the Invoice table via an InvoiceNo.

     

    The Invoice table contains payment details for each InvoiceNo and as the customer may make a number of payments before completing the order these amounts need to be recorded along with the date the payment was made. I therefore have currency fields (Payment1, Payment2 etc up to Payment5), each with a corresponding date field (Date1, Date2 etc up to Date5).

     

    I need to be able to create a query that eliminates any records with an InvoiceDate > than the @End_Date variable (simple enough). However I also need to calculate the total payments received at that given date ignoring any payments that have been made after the @End_Date but including any payment received previously to the given date.

     

    In access I just created a calculated field with some VBA: IIf([Date2]<=[Enter End Date],([Payment2]*1)). And this worked fine, however I have no idea how to recreate this in SQL.

     

    Any Help would be a godsend.

    Cheers ARMO

  • August 31, 2005 at 9:12 am

    #586513

    CASE WHEN Date2 <= @DateEnd THEN Payment2 ELSE NULL END

  • August 31, 2005 at 9:28 am

    #586521

    Thank you very very much, i have been struggling with that all day. I was starting to think i should pack it all in and become a labourer. You have restored my faith in computers.

  • August 31, 2005 at 9:31 am

    #586525

    That's why the design should be normalized .

  • August 31, 2005 at 10:17 am

    #586561

    Building labourers earn more than computer programmers and everyone always needs a builder!

  • August 31, 2005 at 11:25 am

    #586582

    I think our profession is safe too... in our lifetime at least.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply