Query to find totals by date


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Query to find totals by date

  1. #1
    Ken Guest

    Query to find totals by date


    I've got three tables, a transaction table containing an transaction ID, the
    date of the transaction, and a customer ID, a details table containing the
    charges that go with each transaction, and a customer table with customer
    names.

    I've figure out how to write my SQL command to get the customer balances
    but how would I write my query if I wanted the current, 30 day, 60 day, 90
    day balances based on today's date and the date in the transaction table.

    Any Ideas? Thanks to all who can help.



  2. #2
    Jean-Marie Guest

    Re: Query to find totals by date


    Ken,

    Here is the code, the stored procedure will use the dates you want.
    this code is created using SQL 6.

    declare @StartDate DateTime
    declare @EndDate DateTime

    select @Startdate = convert(datetime,substring(convert(varchar,getdate()-3),1,12))
    select @Enddate = convert(datetime,substring(convert(varchar,getdate()-2),1,12))

    Good luck.

    Jean-Marie.

    "Ken" <kenm@faccorp.com> wrote:
    >
    >I've got three tables, a transaction table containing an transaction ID,

    the
    >date of the transaction, and a customer ID, a details table containing the
    >charges that go with each transaction, and a customer table with customer
    >names.
    >
    >I've figure out how to write my SQL command to get the customer balances
    >but how would I write my query if I wanted the current, 30 day, 60 day,

    90
    >day balances based on today's date and the date in the transaction table.
    >
    >Any Ideas? Thanks to all who can help.
    >
    >



  3. #3
    Ken Guest

    Re: Query to find totals by date


    "Jean-Marie " <gahayjea@mailcity.com> wrote:
    >
    >Ken,
    >
    >Here is the code, the stored procedure will use the dates you want.
    >this code is created using SQL 6.
    >
    >declare @StartDate DateTime
    >declare @EndDate DateTime
    >
    >select @Startdate = convert(datetime,substring(convert(varchar,getdate()-3),1,12))
    >select @Enddate = convert(datetime,substring(convert(varchar,getdate()-2),1,12))
    >
    >Good luck.
    >
    >Jean-Marie.
    >
    >"Ken" <kenm@faccorp.com> wrote:
    >>
    >>I've got three tables, a transaction table containing an transaction ID,

    >the
    >>date of the transaction, and a customer ID, a details table containing

    the
    >>charges that go with each transaction, and a customer table with customer
    >>names.
    >>
    >>I've figure out how to write my SQL command to get the customer balances
    >>but how would I write my query if I wanted the current, 30 day, 60 day,

    >90
    >>day balances based on today's date and the date in the transaction table.
    >>
    >>Any Ideas? Thanks to all who can help.
    >>
    >>

    >


    Jean-Maria,

    Thanks, but I guess I was unclear in my question. I am using Access2000.
    I have 3 tables. The first is a list of transactions that have a date and
    a customer ID number. The second table is a list of detailed charges that
    go with each transaction (there may be more than 1 detail for each transaction).
    The third is a list of customers. What I want to do is get a list of all
    customer account balances. I want balances that are less than 30 days old,
    and >30. I want these balances to show up in seperate columns not separate
    rows.

    I figured out, with some help, that my SQL query looks something like:

    PARAMETERS Id Long;
    SELECT CUSTOMERS.FNAME, CUSTOMERS.LNAME, Sum(DETAILS.CHARGE) AS Current,
    0 AS 30Days
    FROM CUSTOMERS INNER JOIN (TRANS INNER JOIN DETAILS ON TRANS.ID = DETAILS.TRANSID)
    ON CUSTOMERS.ID = TRANS.CUSTOMER
    WHERE (((TRANS.DATE) Between Date() And Date() - 29) AND ((TRANS.CUSTOMER)
    = Id Or Id = -1))
    GROUP BY CUSTOMERS.FNAME, CUSTOMERS.LNAME
    HAVING (Sum(DETAILS.CHARGE) > 0)
    ORDER BY CUSTOMERS.LNAME;

    UNION
    SELECT CUSTOMERS.FNAME, CUSTOMERS.LNAME, 0 As Current, Sum(DETAILS.CHARGE)
    AS 30Days
    FROM CUSTOMERS INNER JOIN (TRANS INNER JOIN DETAILS ON TRANS.ID = DETAILS.TRANSID)
    ON CUSTOMERS.ID = TRANS.CUSTOMER
    WHERE (((TRANS.DATE) Between Date() - 30 And Date() - 59) AND ((TRANS.CUSTOMER)
    = Id Or Id = -1))
    GROUP BY CUSTOMERS.FNAME, CUSTOMERS.LNAME
    HAVING (Sum(DETAILS.CHARGE) > 0)
    ORDER BY CUSTOMERS.LNAME;


    This works, except that if a customer has a balance in both columns then
    I get record. The first record shows the <30 day balance and the second
    record shows the >30 day balance.

    How can I modify this query so that both balances show up in the same record?


    Thanks,

  4. #4
    JimmyJack Guest

    Re: Query to find totals by date

    You'll have to use a CASE statement:

    SELECT CASE WHEN DATEDIFF(dd,[TransDate],GETDATE()) BETWEEN 0 AND 30 THEN
    [TransDate] ELSE NULL END AS [AGEGRP1],
    CASE WHEN DATEDIFF(dd,[TransDate],GETDATE()) BETWEEN 31 AND 60 THEN
    [TransDate] ELSE NULL END AS [AGEGRP2],
    CASE WHEN DATEDIFF(dd,[TransDate],GETDATE()) BETWEEN 61 AND 90 THEN
    [TransDate] ELSE NULL END AS [AGEGRP3],
    CASE WHEN DATEDIFF(dd,[TransDate],GETDATE()) > 90 THEN [TransDate] ELSE NULL
    END AS [AGEGRP4]
    FROM Transactions


    "Ken" <kenm@faccorp.com> wrote in message news:3ac2556e$1@news.devx.com...
    >
    > I've got three tables, a transaction table containing an transaction ID,

    the
    > date of the transaction, and a customer ID, a details table containing the
    > charges that go with each transaction, and a customer table with customer
    > names.
    >
    > I've figure out how to write my SQL command to get the customer balances
    > but how would I write my query if I wanted the current, 30 day, 60 day, 90
    > day balances based on today's date and the date in the transaction table.
    >
    > Any Ideas? Thanks to all who can help.
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center