Query to find totals by date

[Ken]

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-Marie]

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.
>
>

[Ken]

"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,

[JimmyJack]

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.
>
>