Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011
    Posts
    12

    Smile Unanswered: How to sum up datediff result into one?

    Hello gurus,

    I am new on this forum. I am posting here because I am hoping somebody would help me.

    I have this query to display all the information, but I had a problem on how to display the datediff result into one.


    Code:
    SELECT dtaPROJECTS.prj_ID, prj_name, 
    dtaCLIENTS.clt_ID, clt_companyName, 
    dtaCOMPANIES.com_ID, com_name, 
    dtaPARTICIPANTS.prt_ID, prt_firstName, prt_lastName,
    DATEDIFF(MONTH, dtaPARTICIPANT_SUBSCRIPTIONS.prs_startDate, dtaPARTICIPANT_SUBSCRIPTIONS.prs_endDate) AS monthsLengthOfSubscription
    FROM dtaCOMPANIES, dtaCLIENTS, dtaPROJECTS, dtaPARTICIPANTS, dtaPARTICIPANT_SUBSCRIPTIONS
    WHERE dtaCOMPANIES.com_ID = dtaCLIENTS.com_ID
    AND dtaCOMPANIES.com_deleted = 0
    AND dtaCLIENTS.clt_ID = dtaPROJECTS.clt_ID
    AND dtaCLIENTS.clt_deleted = 0
    AND dtaPROJECTS.prj_ID = dtaPARTICIPANTS.prj_ID
    AND dtaPROJECTS.prj_deleted = 0
    AND dtaPARTICIPANTS.prt_ID = dtaPARTICIPANT_SUBSCRIPTIONS.prt_ID
    AND dtaPARTICIPANTS.prt_deleted = 0


    This would display like this:

    Joselito Apple 24
    Joselito Apple 12
    Ramon Kull 12
    Ramon Kull 6
    Ramon Kull 5

    But I want something like this

    Joselito Apple 36
    Ramon Kull 23

    Where it sum up the datediff result and distinct the name of the person. How will I query that based on my existing query.


    Thanks,

    -neo_phyte

  2. #2
    Join Date
    Feb 2011
    Posts
    12
    Problem solved:

    SELECT dtaPROJECTS.prj_ID, prj_name,
    dtaCLIENTS.clt_ID, clt_companyName,
    dtaCOMPANIES.com_ID, com_name,
    dtaPARTICIPANTS.prt_ID, prt_firstName, prt_lastName,
    SUM(DATEDIFF(MONTH, dtaPARTICIPANT_SUBSCRIPTIONS.prs_startDate, dtaPARTICIPANT_SUBSCRIPTIONS.prs_endDate)) AS monthsLengthOfSubscription
    FROM dtaCOMPANIES, dtaCLIENTS, dtaPROJECTS, dtaPARTICIPANTS, dtaPARTICIPANT_SUBSCRIPTIONS
    WHERE dtaCOMPANIES.com_ID = dtaCLIENTS.com_ID
    AND dtaCOMPANIES.com_deleted = 0
    AND dtaCLIENTS.clt_ID = dtaPROJECTS.clt_ID
    AND dtaCLIENTS.clt_deleted = 0
    AND dtaPROJECTS.prj_ID = dtaPARTICIPANTS.prj_ID
    AND dtaPROJECTS.prj_deleted = 0
    AND dtaPARTICIPANTS.prt_ID = dtaPARTICIPANT_SUBSCRIPTIONS.prt_ID
    AND dtaPARTICIPANTS.prt_deleted = 0
    GROUP BY dtaCOMPANIES.com_ID, com_name, dtaCLIENTS.clt_ID, clt_companyName, dtaPROJECTS.prj_ID, prj_name, dtaPARTICIPANTS.prt_ID, prt_lastName, prt_firstName
    ORDER BY dtaPARTICIPANTS.prt_ID, prt_lastName, prt_firstName

Posting Permissions

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