don't know what to title this question... 
Author Message
 don't know what to title this question...

Hi!

I'm using a mysql server, but am posting this question here because I
haven't found any other sql newsgroups with helpful people in it. You guys
are cool.

Anyway, I have a table like this:

CREATE TABLE temp_transactions (
    amount bigint(20) DEFAULT '0' NOT NULL,
    description varchar(250) DEFAULT '' NOT NULL,
    date_time datetime NOT NULL
)

Which is a temporary table that holds monetary transactions of different
types on my site. I use this to create a simple account statement using:

SELECT amount, description, date_time
FROM temp_credits_from_subscriptions
ORDER BY date_time ASC

This gives me a basic financial statement. What I want to do is create a
query which would not only give the time/date, description and amount of a
transaction, but also include the current balance which is the amount of
that transaction plus all previous. The results would look like:

amount    desc    date_time        balance
-7400    none    1-1-99 1:08:22    -7400
400      none    1-3-99 1:08:22    -7000
800      gift    3-1-99 5:08:22    -6200
-1000    haha    1-1-00 1:28:47    -7200

Is there any easy way to do this?

Thanks,
-Dale



Fri, 04 Apr 2003 03:00:00 GMT
 don't know what to title this question...

How do you determine the starting balance, by the first record?  How do you
know what the first record is (aside from the obvious: earliest)?

www.aspfaq.com


Quote:
> Hi!

> I'm using a mysql server, but am posting this question here because I
> haven't found any other sql newsgroups with helpful people in it. You guys
> are cool.

> Anyway, I have a table like this:

> CREATE TABLE temp_transactions (
>     amount bigint(20) DEFAULT '0' NOT NULL,
>     description varchar(250) DEFAULT '' NOT NULL,
>     date_time datetime NOT NULL
> )

> Which is a temporary table that holds monetary transactions of different
> types on my site. I use this to create a simple account statement using:

> SELECT amount, description, date_time
> FROM temp_credits_from_subscriptions
> ORDER BY date_time ASC

> This gives me a basic financial statement. What I want to do is create a
> query which would not only give the time/date, description and amount of a
> transaction, but also include the current balance which is the amount of
> that transaction plus all previous. The results would look like:

> amount    desc    date_time        balance
> -7400    none    1-1-99 1:08:22    -7400
> 400      none    1-3-99 1:08:22    -7000
> 800      gift    3-1-99 5:08:22    -6200
> -1000    haha    1-1-00 1:28:47    -7200

> Is there any easy way to do this?

> Thanks,
> -Dale



Fri, 04 Apr 2003 03:00:00 GMT
 don't know what to title this question...

Do:

SELECT t1.Amount, t1.Description, t1.Date_Time ,
       t1.Amount +
            COALESCE( ( SELECT SUM( t2.Amount )
                        FROM temp_transactions AS t2
                        WHERE t2.Date_Time < t1.Date_Time ) , 0 ) AS Balance
FROM temp_transactions AS t1
ORDER BY t1.date_time

    If you are doing this for every account or something, then specify the
join condition accordingly in the WHERE clause.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)



Fri, 04 Apr 2003 03:00:00 GMT
 don't know what to title this question...

Here's an example of the first way I thought of, using a cursor (I've
included my own DDL as well - I don't like BigInts  :-)):

DROP TABLE temp_transactions1
CREATE TABLE temp_transactions1 (
    amount INT NOT NULL,
    description VARCHAR(250) NOT NULL,
    date_time DATETIME NOT NULL
)

INSERT INTO temp_transactions1(amount,description,date_time)
         VALUES(2247,'Payday - woohoo!','10/01/2000')
INSERT INTO temp_transactions1(amount,description,date_time)
         VALUES(-700,'Rent','10/02/2000')
INSERT INTO temp_transactions1(amount,description,date_time)
         VALUES(-400,'Gift','10/04/2000')
INSERT INTO temp_transactions1(amount,description,date_time)
         VALUES(-200,'Electricity','10/14/2000')
INSERT INTO temp_transactions1(amount,description,date_time)
         VALUES(2247,'Payday - woohoo!','10/15/2000')




DECLARE temp1 CURSOR LOCAL FAST_FORWARD FOR
 SELECT amount,description,date_time
     FROM temp_transactions1 ORDER BY date_time
OPEN temp1


BEGIN



END
CLOSE temp1
DEALLOCATE temp1

To return this as one recordset, instead of several, you could create a
second temporary table and INSERT into that within the loop, instead of
doing an individual SELECT each time... I guess that depends on the
application's requirements.

There may be a more efficient way to do this in a set-based query, but I
can't think of one at this time of night (unless you store the calculation
in a separate table, and change it as you process -- which I think will
still ultimately require a cursor).

www.aspfaq.com


Quote:
> Hi!

> I'm using a mysql server, but am posting this question here because I
> haven't found any other sql newsgroups with helpful people in it. You guys
> are cool.

> Anyway, I have a table like this:

> CREATE TABLE temp_transactions (
>     amount bigint(20) DEFAULT '0' NOT NULL,
>     description varchar(250) DEFAULT '' NOT NULL,
>     date_time datetime NOT NULL
> )

> Which is a temporary table that holds monetary transactions of different
> types on my site. I use this to create a simple account statement using:

> SELECT amount, description, date_time
> FROM temp_credits_from_subscriptions
> ORDER BY date_time ASC

> This gives me a basic financial statement. What I want to do is create a
> query which would not only give the time/date, description and amount of a
> transaction, but also include the current balance which is the amount of
> that transaction plus all previous. The results would look like:

> amount    desc    date_time        balance
> -7400    none    1-1-99 1:08:22    -7400
> 400      none    1-3-99 1:08:22    -7000
> 800      gift    3-1-99 5:08:22    -6200
> -1000    haha    1-1-00 1:28:47    -7200

> Is there any easy way to do this?

> Thanks,
> -Dale



Fri, 04 Apr 2003 03:00:00 GMT
 don't know what to title this question...

Quote:
>                         WHERE t2.Date_Time < t1.Date_Time )

If the actual time isn't stored (often dates are just entered as CHAR(10),
and SQL pads it with midnight), isn't there a danger here of skipping or
double-counting records?

I'll try it out, but that stood out to me...

www.aspfaq.com



Fri, 04 Apr 2003 03:00:00 GMT
 don't know what to title this question...
In fact... if there are two records with the same datetime value, the
balance gets reset to 0.

If your application eliminates the possibility of identical datetime values,
then by all means this is a much more efficient and graceful solution than
my pig cursor.  However, if there is a possibility of duplicate datetime
values, you'll want to test it out...

UJ, any ideas on how to prevent duplicate datetime values from destroying
the COALESCE?

www.aspfaq.com



Fri, 04 Apr 2003 03:00:00 GMT
 don't know what to title this question...

Quote:
>> If the actual time isn't stored (often dates are just entered
>> as CHAR(10), and SQL pads it with midnight), isn't there a
>> danger here of skipping or double-counting records?

    But the time has to be stored some where right! Or there must be some
way to identify a transaction uniquely. From what little DDL that the user
posted, I gather that this column contains the timestamp of the transaction.
So it will work as long as these are unique or within a limit - in SQL
Server the accuracy of datetime is up to 1/300th of a second. I don't know
about mySQL.
    In any case, you can use any column(s) that uniquely identify a
transaction & a column to identify the order of the transactions.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)



Fri, 04 Apr 2003 03:00:00 GMT
 don't know what to title this question...

Quote:
>     But the time has to be stored some where right!

Not necessarily, often transactions are summed up by date.  But you're
right, if this is the case, then there should be some other way to uniquely
identify each row.

Quote:
> Server the accuracy of datetime is up to 1/300th of a second.

Not to beat a dead horse, but another factor is that these timestamps might
be inserted (and further to that, edited before insertion)... not
necessarily generated by SQL Server.  So the accuracy there MIGHT be
irrelevant... I can still enter all times as midnight, or noon, or...

I just wanted to point out the dangers of relying on timestamps when there
isn't always a guarantee that they'll be unique.

www.aspfaq.com



Fri, 04 Apr 2003 03:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. don't know what to title this question...

2. Don't know the field name but do know the column order

3. Help: An Easy Question, But I Don't Know The Answer!!!!!(FPW26)

4. Noob question, probably very simple (sorry I don't know this myself)

5. Don't know if it's possible : grouping

6. I've just replaced the last dBA and don't know the sa password

7. Funny Behaving Locate - don't know where's the problem

8. Oninit didn't work fine and I don't know why

9. Reading Excel Cells..........I don't know how

10. Microsoft Don't Know...

11. Don't know how to get parameters returned from sp using ADO

12. Database properties settings changed back to default settings and I don't know why


 
Powered by phpBB® Forum Software