[SQL]

2002-11-29 Thread A.M. 
I have a reference which is guaranteed not to have a valid reference 
(preferably NULL) until it is processed later. How can I defer a 
reference entry so that both NULL and an actual reference are valid in 
the reference field? The processing which I speak of takes place later 
and not in the same transaction (thus making DEFERRABLE useless, 
right?). A solution I thought of is to forget keys and just make it an 
INT8 since I am also guaranteed that its referenced item is never 
deleted. But certainly, there is a more reliable way to do it, right?
	In summary, what I want is

 field INT8 REFERENCES sometable NULL OK

or

field INT8 CHECK(SELECT id FROM someothertable WHERE field=id)

(can I do this? this allows NULL right?)

The rule is ref2 can either be a valid reference or NULL.
CREATE TABLE thetable(
	ref2 INT8 REFERENCES someothertable,
	...
);

Thanks for any help.
><><><><><><><><><
AgentM
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL]

2002-11-29 Thread A.M. 
Wow. That's a real head-slapper. Thanks for the quick answer!

On Friday, November 29, 2002, at 11:28  PM, Tom Lane wrote:


"A.M." <[EMAIL PROTECTED]> writes:

In summary, what I want is



  field INT8 REFERENCES sometable NULL OK


Er ... NULL *is* OK, unless you say

   field INT8 REFERENCES sometable NOT NULL

			regards, tom lane



><><><><><><><><><
AgentM
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Getting the latest unique items

2002-12-11 Thread A.M. 
I have a table as follows:
CREATE TABLE student_gradedmaterial(
	id SERIAL,
	studentid INT8 REFERENCES student,
	gradedmaterialid INT8 REFERENCES gradedmaterial,
	caid INT8 REFERENCES ca,
	...
	submittime TIMESTAMP,
   	gradedtime TIMESTAMP,
	score INT4
);

Every time a student submits a homework, one new entry in the table is 
created. I know how to grab the latest version based on the submittime 
but naturally, I'd like to be able to count how many homeworks are 
graded and ungraded (ungraded means score is NULL). This smells of a 
subselect:

graded (grab row count):
SELECT UNIQUE id FROM student_gradedmaterial WHERE EXISTS (SELECT the 
latest unique submissions);
or:
SELECT COUNT(score) FROM student_gradedmaterial WHERE 
gradedmaterialid=X AND submittime = MAX(SELECT submittime FROM 
student_gradedmaterial WHERE gradedmaterialid=X);

(Sub-selects just make my head explode.) Any hints for me? Thanks.
><><><><><><><><><
AgentM
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Getting the latest unique items

2002-12-11 Thread A.M. 
When I try to run the following query:

select distinct on(student_gradedmaterial.id) student_gradedmaterial.id 
from coursesection_student,student_gradedmaterial WHERE 
gradedmaterialid=1 AND 
coursesection_student.studentid=student_gradedmaterial.studentid AND 
coursesectionid=1 and score is not null order by submittime desc;

I get the following error:

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY 
expressions

Should I use GROUP BY somehow?
SELECT DISTINCT ON (student_gradedmaterial.id) 
student_gradedmaterial.id from 
coursesection_student,student_gradedmaterial where gradedmaterialid=1 
and coursesection_student.studentid=student_gradedmaterial.studentid 
and coursectionid=1 and score is not null having max(big subselect of 
max times);

?

The relevant schema follows:
CREATE TABLE coursesection_student (
   coursesectionid INT8 REFERENCES coursesection,
   studentid INT8 REFERENCES student,
   status INT4 DEFAULT 0 NOT NULL, --2 switched, 1 dropped, 0 
enrolled
   touch BOOL DEFAULT 'true',
   UNIQUE(coursesectionid,studentid)
);
CREATE TABLE coursesection_ca(
   coursesectionid INT8 REFERENCES coursesection,
   caid INT8 REFERENCES ca
);
CREATE TABLE gradedmaterial (
   id SERIAL PRIMARY KEY,
   name TEXT,
   visible BOOLEAN DEFAULT 'f',
   openforsubmission BOOLEAN DEFAULT 'f',
   description TEXT,
   webpage TEXT,
   predefcomments TEXT,
   weight INT4,
   restrictedfiletypes TEXT,
   duetime TIMESTAMP
);

CREATE TABLE coursesection_gradedmaterial(
   gradedmaterialid INT8 REFERENCES gradedmaterial,
   coursesectionid INT8 REFERENCES coursesection
);

CREATE TABLE student_gradedmaterial(
   id SERIAL,
   studentid INT8 REFERENCES student,
   gradedmaterialid INT8 REFERENCES gradedmaterial,
   caid INT8 REFERENCES ca,
   score INT4,
   comments TEXT,
   submittime TIMESTAMP,
   gradedtime TIMESTAMP,
   file OID,
   emailtostudent BOOLEAN DEFAULT 'f',
   suffix VARCHAR(6) DEFAULT '.zip'
);
On Wednesday, December 11, 2002, at 01:36  PM, Tomasz Myrta wrote:

I'm not sure if I understood your problem,
but did you try with "distinct on"?
select distinct on (id)
from
...
order by submittime desc

Regards,
Tomasz Myrta


A.M. wrote:

> I have a table as follows:
> CREATE TABLE student_gradedmaterial(
> id SERIAL,
> studentid INT8 REFERENCES student,
> gradedmaterialid INT8 REFERENCES gradedmaterial,
> caid INT8 REFERENCES ca,
> ...
> submittime TIMESTAMP,
>gradedtime TIMESTAMP,
> score INT4
> );
>
> Every time a student submits a homework, one new entry in the table 
is
> created. I know how to grab the latest version based on the 
submittime
> but naturally, I'd like to be able to count how many homeworks are
> graded and ungraded (ungraded means score is NULL). This smells of a
> subselect:
>
> graded (grab row count):
> SELECT UNIQUE id FROM student_gradedmaterial WHERE EXISTS (SELECT the
> latest unique submissions);
> or:
> SELECT COUNT(score) FROM student_gradedmaterial WHERE 
gradedmaterialid=X
> AND submittime = MAX(SELECT submittime FROM student_gradedmaterial 
WHERE
> gradedmaterialid=X);
>
> (Sub-selects just make my head explode.) Any hints for me? Thanks.
>  ><><><><><><><><><
> AgentM
> [EMAIL PROTECTED]
>
>
>
> ---(end of 
broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to 
[EMAIL PROTECTED])
>





><><><><><><><><><
AgentM
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] UNION and ORDER BY ... IS NULL ASC

2003-04-04 Thread A.M. 
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3

I have come across some unexpected behavior while dealing with a UNION 
and ORDER BY. I'd like some advice. Here's a scenario where I want to 
order by null values:

CREATE TABLE test(a int);
SELECT a FROM test UNION SELECT a FROM test ORDER BY a IS NULL ASC;
returns:

ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of 
the result columns

whereas:

SELECT a FROM test UNION SELECT a FROM test ORDER BY a;

works fine. The column name is the same in both queries, yet I get an 
error! Obviously, this is a gross oversimplification of what I want to 
do, but I couldn't get it working in this minimal case. I also tried 
using the column number, and that returns the same results as the name. 
What am I doing wrong? Thanks for any info.
><><><><><><><><><
AgentM
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Column limits in table/ views

2003-06-07 Thread A.M. 
I have 560 columns of NUMERIC(10,14). To not run up against max column 
restraints, I split the information into two tables. Does the column 
limit on tables imply the same limit for views or selects or could I 
potentially select a row across both tables and make a view that hides 
the split?



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Column Types

2003-09-14 Thread Muhyiddin A.M Hayat 



If in MySQL i'm using type EMUN what type in 
Postgres?

[SQL]

2003-09-14 Thread Muhyiddin A.M Hayat 



 

[SQL] auto_increment

2003-09-19 Thread Muhyiddin A.M Hayat 



How to Create auto_increment field in 
PostreSQL.
Can I create them using 
Trigger.

Re: [SQL] auto_increment

2003-09-20 Thread Muhyiddin A.M Hayat 



Ok, but if i do rollback, the auto_increment don't roolback.
How to use nextval(), currval() and setval() 
functions.
 
- Original 
Message - 

  From: 
  Cavit Keskin 
  
  To: 'Muhyiddin A.M Hayat' 
  Sent: Saturday, September 20, 2003 2:15 
  PM
  Subject: RE: [SQL] auto_increment
  
  
  Create table tablename(
    id serial,
    ……..
  );
   
  Serial type is integer and autoincrement
  When you create  this table creates automatic sequence tablename_id_seq
  Stored sequence last value;
  Try execSQL : select * from tablename_id_seq;
   
  My english is very very little and bad
   

Re: [SQL] auto_increment

2003-09-20 Thread Muhyiddin A.M Hayat 
Where/How can i put this below sql statement, to set value of
guest_guest_id_seq before i do insert to table
  SELECT setval('guest_guest_id_seq', max(guest.guest_id)) FROM guest;

i have been try

CREATE TRIGGER "before_insert_guest_update_room_number" BEFORE INSERT
ON "public"."guest" FOR EACH ROW
EXECUTE PROCEDURE "public"."generate_guest_id"();

but error



- Original Message - 
From: "Richard Huxton" <[EMAIL PROTECTED]>
To: "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, September 20, 2003 6:05 PM
Subject: Re: [SQL] auto_increment


> On Saturday 20 September 2003 09:43, Muhyiddin A.M Hayat wrote:
> > Ok, but if i do rollback, the auto_increment don't roolback.
>
> It's not supposed to.
>
> > How to use nextval(), currval() and setval() functions.
>
> Something like:
>   INSERT INTO my_table(nextval('my_sequence_name'),'aaa',1);
> But you'll get the same problem.
>
> What are you trying to do with the auto-increment? If you want to
guarantee
> that the numbers go up in sequence and have no gaps (e.g. 1,2,3,4,5 NOT
> 1,2,4,6) then you'll need to do some more work.
>
> Think about what you want to have happen when three clients insert rows at
the
> same time and one rolls back. Once you've decided what you want, ask again
if
> you need some help.
>
> -- 
>   Richard Huxton
>   Archonet Ltd
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] SUM() & GROUP BY

2003-09-28 Thread Muhyiddin A.M Hayat 



 
hotel=# SELECThotel-#   
"public".billing.id,hotel-#   
"public".billing.guest_id,hotel-#   
"public".billing.trx_date,hotel-#   
"public".billing.trx_time,hotel-#   
"public".billing.payment_method,hotel-#   
"public".billing.tax,hotel-#   
"public".billing.dep_id,hotel-#   
"public".department."name",hotel-#   SUM("public".items.price) AS 
total,hotel-#   "public".billing.amount_paidhotel-# 
FROMhotel-#   "public".billing_itemshotel-#   INNER 
JOIN "public".billing ON ("public".billing_items.billing_id = 
"public".billing.id)hotel-#   INNER JOIN "public".department ON 
("public".billing.dep_id = "public".department.id)hotel-#   INNER 
JOIN "public".items ON ("public".billing_items.items_id = 
"public".items.id)hotel-# GROUP BY  
"public".billing.id;ERROR:  Attribute billing.guest_id must be GROUPed 
or used in an aggregate functionhotel=#
 
What Worng ??
 
How to using SUM() & GROUP 
BY

Re: [SQL] SUM() & GROUP BY

2003-09-28 Thread Muhyiddin A.M Hayat 
so

SELECT
  "public".billing.id,
  "public".billing.guest_id,
  "public".billing.trx_date,
  "public".billing.trx_time,
  "public".department."name" AS depart,
  "public".payment_method.description AS payment_method,
  "public".billing.amount_paid,
  "public".billing.tax,
  "public".billing.creator
FROM
  "public".payment_method
  INNER JOIN "public".billing ON ("public".payment_method.id =
"public".billing.payment_method)
  INNER JOIN "public".department ON ("public".billing.dep_id =
"public".department.id)
  INNER JOIN "public".billing_items ON ("public".billing.id =
"public".billing_items.billing_id)

Result:

 id | guest_id |  trx_date  | trx_time | depart | payment_method
|amount_paid | tax | creator
+--++--+++--
---+-+-
  1 |1 | 2003-09-28 | 16:08:52 | Resto  | Cash   |2.00 |
10 | middink
  1 |1 | 2003-09-28 | 16:08:52 | Resto  | Cash   |2.00 |
10 | middink
  2 |1 | 2003-09-29 | 07:50:17 | Resto  | Visa   |1.00 |
10 | middink
  2 |1 | 2003-09-29 | 07:50:17 | Resto  | Visa   |1.00 |
10 | middink


So, i would like to view billing amount, value billing amount
sum(item.price) from , so  my new query :


SELECT
  "public".billing.id,
  "public".billing.guest_id,
  "public".billing.trx_date,
  "public".billing.trx_time,
  "public".department."name" AS depart,
  "public".payment_method.description AS payment_method,
  "public".billing.tax,
  (SUM(("public".items.price *
"public".billing_items.quantity)-("public".billing_items.discount))
  *
  ("public".billing.tax/100)) AS tax_amount,
  (SUM(("public".items.price * "public".billing_items.quantity)))
  AS billing_amount,
  (SUM(("public".items.price * "public".billing_items.quantity))
  -
  (SUM(("public".items.price *
"public".billing_items.quantity))*("public".billing.tax/100))
   )
  AS total,

  "public".billing.amount_paid,
  "public".billing.creator
FROM
  "public".payment_method
  INNER JOIN "public".billing ON ("public".payment_method.id =
"public".billing.payment_method)
  INNER JOIN "public".department ON ("public".billing.dep_id =
"public".department.id)
  INNER JOIN "public".billing_items ON ("public".billing.id =
"public".billing_items.billing_id)
  INNER JOIN "public".items ON ("public".billing_items.billing_id =
"public".items.id)

GROUP BY
  "public".billing.id,
  "public".billing.guest_id,
  "public".billing.trx_date,
  "public".billing.trx_time,
  "public".department."name",
  "public".payment_method.description,
  "public".billing.amount_paid,
  "public".billing.tax,
  "public".billing.creator

Result:

 id | guest_id |  trx_date  | trx_time | depart | payment_method | tax
|tax_amount | billing_amount | total | amount_paid | creator
+--++--+++-+
++---+-+-
  1 |1 | 2003-09-28 | 16:08:52 | Resto  | Cash   |  10 |3600
| 36000. | 32400 |2.00 | middink
  2 |1 | 2003-09-29 | 07:50:17 | Resto  | Visa   |  10 |1200
| 12000. | 10800 |1.00 | middink


but i have another problem :
 - how to simple below statment :
 (SUM(("public".items.price *
"public".billing_items.quantity)-("public".billing_items.discount))
*
("public".billing.tax/100)) AS tax_amount,
(SUM(("public".items.price * "public".billing_items.quantity)))
AS billing_amount,
(SUM(("public".items.price * "public".billing_items.quantity))
-
(SUM(("public".items.price *
"public".billing_items.quantity))*("public".billing.tax/100))
 )
AS total,

- I have discount in public".billing_items.discount, how to including the
discount to billing_amount

- Original Message - 
From: "Oliver Elphick" <[EMAIL PROTECTED]>
To: "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, September 29, 2003 3:56 AM
Subject: Re: [SQL] SUM() & GROUP BY


> Any items in the select list need to be aggregated (e.g.
> SUM("public".items.price)) or mentioned in the GROUP BY list.  Suppose
> there are several billing.guest_id values for each billing.id; which
> value should be listed in the output?
>
>
> -- 
> Oliver Elphick[EMAIL PROTECTED]
> Isle of Wight, UK http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>  
>  "Blessed is the man that endureth temptation; for when
>   he is tried, he shall receive the crown of life, which
>   the Lord hath promised to them that love him."
>   James 1:12
>


billing.sql
Description: Binary data

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] SUM() & GROUP BY

2003-09-28 Thread Muhyiddin A.M Hayat 



SELECT  "public".billing.id,  
"public".billing.guest_id,  "public".billing.trx_date,  
"public".billing.trx_time,  "public".department."name" AS 
depart,  "public".payment_method.description AS 
payment_method,  "public".billing.amount_paid,  
"public".billing.tax,  "public".billing.creatorFROM  
"public".payment_method  INNER JOIN "public".billing ON 
("public".payment_method.id ="public".billing.payment_method)  
INNER JOIN "public".department ON ("public".billing.dep_id 
="public".department.id)  INNER JOIN "public".billing_items ON 
("public".billing.id 
="public".billing_items.billing_id)Result: id | 
guest_id |  trx_date  | trx_time | depart | 
payment_method|amount_paid | tax | 
creator+--++--+++-+-+-  
1 |    1 | 2003-09-28 | 16:08:52 | 
Resto  | Cash   
|2.00 |10 | middink  1 
|    1 | 2003-09-28 | 16:08:52 | 
Resto  | Cash   
|2.00 |10 | middink  2 
|    1 | 2003-09-29 | 07:50:17 | 
Resto  | Visa   
|1.00 |10 | middink  2 
|    1 | 2003-09-29 | 07:50:17 | 
Resto  | Visa   
|1.00 |10 | middinkSo, i would like to view billing amount, 
value billing amountsum(item.price) from , so  my new query 
:SELECT  "public".billing.id,  
"public".billing.guest_id,  "public".billing.trx_date,  
"public".billing.trx_time,  "public".department."name" AS 
depart,  "public".payment_method.description AS 
payment_method,  "public".billing.tax,  
(SUM(("public".items.price 
*"public".billing_items.quantity)-("public".billing_items.discount))  
*  ("public".billing.tax/100)) AS tax_amount,  
(SUM(("public".items.price * "public".billing_items.quantity)))  AS 
billing_amount,  (SUM(("public".items.price * 
"public".billing_items.quantity))  -  
(SUM(("public".items.price 
*"public".billing_items.quantity))*("public".billing.tax/100))   
)  AS total,  "public".billing.amount_paid,  
"public".billing.creatorFROM  "public".payment_method  
INNER JOIN "public".billing ON ("public".payment_method.id 
="public".billing.payment_method)  INNER JOIN "public".department 
ON ("public".billing.dep_id ="public".department.id)  INNER JOIN 
"public".billing_items ON ("public".billing.id 
="public".billing_items.billing_id)  INNER JOIN "public".items ON 
("public".billing_items.billing_id ="public".items.id)GROUP 
BY  "public".billing.id,  "public".billing.guest_id,  
"public".billing.trx_date,  "public".billing.trx_time,  
"public".department."name",  
"public".payment_method.description,  
"public".billing.amount_paid,  "public".billing.tax,  
"public".billing.creatorResult: id | guest_id |  
trx_date  | trx_time | depart | payment_method | tax|tax_amount | 
billing_amount | total | amount_paid | 
creator+--++--+++-+++---+-+-  
1 |    1 | 2003-09-28 | 16:08:52 | 
Resto  | Cash   
|  10 |3600| 36000. | 32400 
|    2.00 | middink  2 
|    1 | 2003-09-29 | 07:50:17 | 
Resto  | Visa   
|  10 |1200| 12000. | 10800 
|    1.00 | middinkbut i have another problem 
: - how to simple below statment : 
(SUM(("public".items.price 
*"public".billing_items.quantity)-("public".billing_items.discount))    
*    ("public".billing.tax/100)) AS 
tax_amount,    (SUM(("public".items.price * 
"public".billing_items.quantity)))    AS 
billing_amount,    (SUM(("public".items.price * 
"public".billing_items.quantity))    -    
(SUM(("public".items.price 
*"public".billing_items.quantity))*("public".billing.tax/100)) 
)    AS total,- I have discount in 
public".billing_items.discount, how to including thediscount to 
billing_amount- Original Message - From: "Oliver Elphick" 
<[EMAIL PROTECTED]>To: 
"Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>Cc: <[EMAIL PROTECTED]>Sent: Monday, September 29, 2003 3:56 AMSubject: Re: [SQL] 
SUM() & GROUP BY> Any items in the select list need to be 
aggregated (e.g.> SUM("public".items.price)) or mentioned in the GROUP BY 
list.  Suppose> there are several billing.guest_id values for each 
billing.id; which> value should be listed in the 
output?>>> -- > Oliver 
Elphick    
[EMAIL PROTECTED]> Isle of Wight, 
UK 
http://www.lfix.co.uk/oliver> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 
3E1D 
0C1C>  
>  
"Blessed is the man that endureth temptation; for 
when>   he is tried, he shall receive 
the crown of life, which>   the Lord 
hath promised to them that love 
him.">   
James 1:12>


billing.sql
Description: Binary data

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] PL/PGSQL TUTORIAL

2003-10-11 Thread Muhyiddin A.M Hayat 


Where can i find a complete full terminated database 
schema in SQL?I want to see a real complete database schema with views, 
triggers.. etc,...
 

[SQL] Create View

2003-10-14 Thread Muhyiddin A.M Hayat 



Dear all,
 
I Have This table
 
 
Table Billing:
 
    
id   
trx_date   trx_time depart   payment_method    
billing_amount    amount_paid 
balance   creator 
1  10/09/2003  
21:55:02   Resto    
Visa   13.800,00 
10.000,00   
3.800,00   middink
Table Payment
 
    id 
r   trx_date    
trx_timedescriptions   
payment_method   amount  
creator 
1 10/08/2003  18:17:40  Payment  Cash 2.000,00 middink
 
 
I would like to create "View " from above table 
with result look like:
 
 
 
trx_date   trx_time descriptions 
   
 
payment_method   debet   credit balance creator 
10/09/2003  
21:55:02   Resto  Billing  13.800,00 Paid: 
10.000,00 Visa  3.800,00   3.800,00 middink 
10/08/2003  18:17:40Payment  
Cash    
                
    
2.000,00   1.800,00  
middink
 
 
How can I create View like above?

[SQL] Calc

2003-10-15 Thread Muhyiddin A.M Hayat 



Dear all,
 
I have below table
 
+-++-+
 | id   |    
db    
|cr                
          |
+-++-+
 |1 
|    
    
| 200  
|

 |2 
| 100    
|  
|

 |3 
| 100    
|  
|

 |4 
| 150    
|  
|

 |5 
|    
    
| 200  
|
 
I Would like to calc that balance, and look 
like
 

+-++-+---+
 | id   |    
db    
|cr                
          | 
bal    |
+-++-+---+
 |1 
|    
    
| 200  
|200   |

 |2 
| 100    
|  
|100   |

 |3 
| 100    
|  
|0    
|

 |4     
| 150    
|  
|-150  |

 |5 
|    
    
| 200  
|50 |
 
 
What can I do to get result like that
 
 
 
 
 
 
 
 
 
 

Re: [SQL] Calc

2003-10-16 Thread Muhyiddin A.M Hayat 
> Something like this:
>
> select id,db,cr,(select sum(cr-db) from calc sub where sub.id <=
> calc.id) from calc;
>
> This of course assumes that ID indicates the correct order of the
> entries and it will blow up if you allow nulls for cr or db (which
> you shouldn't since that would literally be interpreted as "they
> withdrew 10 and deposited an unknown amount"). If you have null
> values already and they should be interpreted as 0 just do this:
>
> select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0)) from
> calc sub where sub.id <= calc.id) from calc;
>
> I assume no responsibility for potential lack of scalability of this
> query. :) It's quite possible a faster solution exists - we'll see
> what comes up on the list.
>


If  data from "View" without ID, how can I do?

My View:

  trx_date  | trx_time |  descriptions|
payment_method | debet |  credit  | creator
+--+--+-
---+---+--+-
 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 | Visa
|  3800 |  | middink
 2003-10-16 | 03:28:30 | Payment - Thank You  | Visa
|   | 4.00 | middink
 2003-10-08 | 18:17:40 | Payment - Thank You  | Cash
|   | 5.00 | middink





---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Calc

2003-10-16 Thread Muhyiddin A.M Hayat 

> >   trx_date  | trx_time |  descriptions|
> > payment_method | debet |  credit  | creator
> > +--+--+
> >- ---+---+--+-
> >  2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 |
> > Visa
> >
> > |  3800 |  | middink
> >
> >  2003-10-16 | 03:28:30 | Payment - Thank You  |
> > Visa
> >
> > |   | 4.00 | middink
> >
> >  2003-10-08 | 18:17:40 | Payment - Thank You  |
> > Cash
> >
> > |   | 5.00
>
> The id field only keeps the transactions in the correct order so you
> can sum the previous transactions. You can do the same thing with
> your date and time fields (though using a single datetime field would
> be easier). A caution, though: you will have trouble if two
> transactions share the same date and time.

if i would like to pleaced ID(Virtual ID)  for example number of record for
each record, how to ?


 num_rec |  trx_date  | trx_time |  credit  |
descriptions  | payment_method | debet
-++--+--+---
-++---
   1 | 2003-10-09 | 21:55:02 |  | Resto Biling : Rp.
13,800, Paid : Rp.  10,000 | Visa   |  3800
   2 | 2003-10-16 | 03:28:30 | 4.00 | Payment
| Visa   |
   3 | 2003-10-08 | 18:17:40 | 5.00 | Payment
| Cash   |







---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Rank

2004-05-04 Thread Muhyiddin A.M Hayat 



 
Dear All,
 
I Have below table
 
 id | site_name | point+---+---  1 | Site 
A    |    40  2 | Site 
B    |    90  3 | Site 
D    |    22  4 | Site 
X    |    98
 

 Would like to calc that Rank for each site, and look like
 
 
 
 id | site_name | point | 
rank+---+---+--  1 | Site A    
|    40 |3  2 | Site 
B    |    90 |2  
3 | Site D    |    22 
|4  4 | Site X    
|    98 |    1
 

What can I do to get result like 
that

[SQL] Rank

2004-05-04 Thread Muhyiddin A.M Hayat 



Dear All,
 
I Have below table
 
 id | site_name | point+---+---  1 | Site 
A    |    40  2 | Site 
B    |    90  3 | Site 
D    |    22  4 | Site 
X    |    98
 

 Would like to calc that Rank for each site, and look like
 
 
 
 id | site_name | point | 
rank+---+---+--  1 | Site A    
|    40 |3  2 | Site 
B    |    90 |2  
3 | Site D    |    22 
|4  4 | Site X    
|    98 |    1
 

What can I do to get result like 
that

[SQL] Cross tabulations

2004-10-19 Thread Muhyiddin A.M Hayat 




Dear all,I need to do something similar to 
a cross tabulation, but without anyaggregation.I have below 
table id | employee_id |   state   
| 
check_time+-+---+ 21 
|   1 | 
In    | 2004-10-12 21:37:13 22 
|   1 | Break Out | 
2004-10-12 21:37:31 23 
|   1 | Break In  
| 2004-10-12 21:37:42 24 
|   1 | 
Out   | 2004-10-12 21:37:50 25 
|   1 | 
In    | 2004-10-13 19:20:36 26 
|   1 | 
In    | 2004-10-14 01:33:48 27 
|   1 | Break Out | 
2004-10-14 01:59:15 28 
|   1 | Break In  
| 2004-10-14 03:15:45 29 
|   1 | 
Out   | 2004-10-14 03:17:23 30 
|   3 | 
In    | 2004-10-14 03:17:43 31 
|   3 | Break Out | 
2004-10-14 19:32:34 32 
|   2 | 
In    | 2004-10-14 20:34:15 33 
|   3 | 
In    | 2004-10-15 02:01:28 34 
|   3 | Break Out | 
2004-10-15 02:02:07 35 
|   3 | 
In    | 2004-10-16 02:06:43 36 
|   1 | 
In    | 2004-10-16 02:07:33 37 
|   1 | Break Out | 
2004-10-16 02:09:09 38 
|   1 | Break In  
| 2004-10-16 04:10:21 39 
|   1 | 
Out   | 2004-10-16 04:12:27 40 
|   3 | Break Out | 
2004-10-16 21:38:22
 
 
I need something like this:
 
   date    | employee_id 
|    in    | break_out | break_id |   
out---+-+--+---+--+--2004-10-12 
|   1 | 21:37:13 | 
21:37:31  | 21:37:42 |21:37:502004-10-14 
|   1 | 01:33:48 | 
01:59:15  | 03:15:45 |03:17:232004-10-14 
|   3 | 03:17:43 | 
19:32:34  | 03:15:45 |03:17:23   

 
 
 
 

Re: [SQL] Cross tabulations

2004-10-25 Thread Muhyiddin A.M Hayat 
Dear,

Thanks, that query is work, so.
So, i would like to calculate total work time




select date, employee_id,
   (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'In') as in,
   (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break Out') as
break_out,
   (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break In') as
break_in,
   (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Out') as out
  from (select distinct employee_id, check_time::date as date from test) as
x;


out - in = work_time






- Original Message - 
From: "Greg Stark" <[EMAIL PROTECTED]>
To: "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, October 20, 2004 2:35 PM
Subject: Re: [SQL] Cross tabulations


> "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]> writes:
>
> > Dear all,
> >
> > I need to do something similar to a cross tabulation, but without any
> > aggregation.
>
> join your table to itself four times:
>
> select *
>   from (select check_time::date as date, employee_id,
check_time-check_time::date as in from test where state = 'In') as a
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as break_out from test where state = 'Break
Out') as b using (employee_id,date)
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as break_in from test where state = 'Break In')
as d using (employee_id,date)
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as out from test where state = 'Out') as e using
(employee_id,date) ;
>
> Note that this will do strange things if you don't have precisely four
records
> for each employee.
>
> Alternatively use subqueries:
>
> select date, employee_id,
>(select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'In') as in,
>(select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break Out') as
break_out,
>(select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break In') as
break_in,
>(select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Out') as out
>   from (select distinct employee_id, check_time::date as date from test)
as x;
>
> This will at least behave fine if there are missing records and will give
an
> error if there are multiple records instead of doing strange things.
>
> Neither of these will be particularly pretty on the performance front.
>
> -- 
> greg
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Create Calendar

2004-12-10 Thread Muhyiddin A.M Hayat 



Dear All,
 
How to create Calendar using Function/View. 

For example i would like to display date 2004-12-01 
to 2004-12-20. 
 
date
--
2004-12-01 
2004-12-02 
2004-12-03 
2004-12-04 
2004-12-05
..
.. 
2004-12-20

[SQL] Calendar Function

2005-01-28 Thread Muhyiddin A.M Hayat 



Dear All,
 
How to create Calendar Function or Query. I would 
like to display date form -mm-dd to -mm-dd or display date in one 
Month
 
e.g:
 
    
date 2005-01-01 2005-01-02 2005-01-03 2005-01-04 2005-01-05 2005-01-06 2005-01-07 2005-01-08 2005-01-09 2005-01-10 2005-01-11 2005-01-12 2005-01-13 2005-01-14 2005-01-15 2005-01-16 2005-01-17 2005-01-18 2005-01-19 2005-01-20 2005-01-21 2005-01-22 2005-01-23 2005-01-24 2005-01-25 2005-01-26 2005-01-27 2005-01-28 2005-01-29 2005-01-30 2005-01-31
 
 

Re: [SQL] Calendar Function

2005-02-01 Thread Muhyiddin A.M Hayat 




Ok, thanks
 
But if i would like to display date in one Month,
 
 e.g :
date in feb 2005
 
  
calendar 2005-02-01 2005-02-02 2005-02-03 2005-02-04 2005-02-05 2005-02-06 2005-02-07 2005-02-08 2005-02-09 2005-02-10 2005-02-11 2005-02-12 2005-02-13 2005-02-14 2005-02-15 2005-02-16 2005-02-17 2005-02-18 2005-02-19 2005-02-20 2005-02-21 2005-02-22 2005-02-23 2005-02-24 2005-02-25 2005-02-26 2005-02-27 2005-02-28
 

date in feb 2004
 
  
calendar 2004-02-01 2004-02-02 2004-02-03 2004-02-04 2004-02-05 2004-02-06 2004-02-07 2004-02-08 2004-02-09 2004-02-10 2004-02-11 2004-02-12 2004-02-13 2004-02-14 2004-02-15 2004-02-16 2004-02-17 2004-02-18 2004-02-19 2004-02-20 2004-02-21 2004-02-22 2004-02-23 2004-02-24 2004-02-25 2004-02-26 2004-02-27 2004-02-28 2004-02-29
 

  - Original Message - 
  From: 
  Franco Bruno Borghesi 
  To: Muhyiddin A.M Hayat 
  Cc: pgsql-sql@postgresql.org 
  Sent: Friday, January 28, 2005 11:46 
  PM
  Subject: Re: [SQL] Calendar 
Function
  maybe somthing like this:CREATE OR REPLACE FUNCTION 
  calendar (DATE, DATE) RETURNS SETOF DATE LANGUAGE 'plpgsql' AS 
  'DECLARE    v_from ALIAS FOR $1;    
  v_to ALIAS FOR $2;    v_current DATE DEFAULT 
  v_from;BEGIN    WHILE (v_current<=v_to) LOOP 
          RETURN NEXT 
  v_current;        
  v_current:=v_current+1;    END LOOP;    
      RETURN;END;';test it:SELECT * FROM 
  calendar('2005-01-01', '2005-01-31');Muhyiddin A.M Hayat wrote: 
  



Dear All,
 
How to create Calendar Function or Query. I 
would like to display date form -mm-dd to -mm-dd or display date in 
one Month
 
e.g:
 
    
date 2005-01-01 2005-01-02 2005-01-03 2005-01-04 2005-01-05 2005-01-06 2005-01-07 2005-01-08 2005-01-09 2005-01-10 2005-01-11 2005-01-12 2005-01-13 2005-01-14 2005-01-15 2005-01-16 2005-01-17 2005-01-18 2005-01-19 2005-01-20 2005-01-21 2005-01-22 2005-01-23 2005-01-24 2005-01-25 2005-01-26 2005-01-27 2005-01-28 2005-01-29 2005-01-30 2005-01-31
 
 

[SQL] Debet-Credit-Balance Calculation

2005-04-19 Thread Muhyiddin A.M Hayat 



Dear All,I have problem to calculation 
balance from debet and credit.my transaction table:
 
 id |    
trx_timestamptz | 
account  | trx_type_id |   
amount++--+-+-  
3 | 2005-04-14 17:16:49+08 | 01.2010100.2 
|   1 |  
100.00  4 | 2005-04-14 17:17:12+08 | 01.2010100.2 
|   1 
|    5.00  5 | 2005-04-14 17:17:12+08 | 
01.2010100.3 |   1 
|   60.00  6 | 2005-04-14 17:17:47+08 | 01.2010100.2 
|   2 
| 7000.00  7 | 2005-04-16 00:32:50+08 | 
01.2010100.3 |   1 
|    2.00 11 | 2005-04-16 02:45:06+08 | 
01.2010100.2 |   1 
|   10.00 12 | 2005-04-16 02:46:02+08 | 01.2010100.2 
|   1 
|    2.00 13 | 2005-04-16 02:46:59+08 | 
01.2010100.2 |   2 
|   163000.00 14 | 2005-04-16 02:50:17+08 | 01.2010100.5 
|   1 |   
10.00 15 | 2005-04-16 02:53:42+08 | 01.2010301.1 
|   1 |   
10.00 16 | 2005-04-16 02:57:22+08 | 01.2010100.1 
|   1 |  
200.00 17 | 2005-04-16 23:56:44+08 | 01.2010200.0 
|   1 | 
1000.00 18 | 2005-04-17 18:58:57+08 | 01.2010100.3 
|   1 |   
10.00 19 | 2005-04-17 19:13:05+08 | 01.2010100.2 
|   1 |   
10.00 20 | 2005-04-17 19:13:45+08 | 01.2010100.2 
|   1 |   
20.00 21 | 2005-04-17 19:15:36+08 | 01.2010100.2 
|   1 
|    5.00 22 | 2005-04-17 19:17:17+08 | 
01.2010100.5 |   2 
|    1.00 23 | 2005-04-17 19:18:06+08 | 
01.2010100.4 |   1 
|   20.00 24 | 2005-04-17 21:45:31+08 | 01.2010100.2 
|   1 
| 9000.00 25 | 2005-04-17 22:16:08+08 | 
01.2010100.6 |   1 
|   10.00
 
-
CREATE TABLE "public"."transactions" (  
"id" SERIAL,   "trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT 
('now'::text)::timestamp(6) with time zone NOT NULL,   "account" 
CHAR(16) NOT NULL,   "trx_type_id" INTEGER NOT NULL,   
"amount" NUMERIC(15,2) DEFAULT 0 NOT NULL,   "uid" INTEGER NOT NULL, 
  CONSTRAINT "transactions_pkey" PRIMARY KEY("id")) WITH 
OIDS;
---
 
and transaction type :
 
 id | trx_name | 
t_type+--+  1 | Credit   | 
CR  2 | Debet    | DB
 
-CREATE TABLE "public"."trx_type" (  "id" SERIAL, 
  "trx_name" VARCHAR(32),   "t_type" CHAR(2),   
CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"),   CONSTRAINT 
"trx_typei_check0" CHECK ((t_type = 'CR'::bpchar) OR (t_type = 
'DB'::bpchar))) WITH OIDS; 
 

-
 
so, i'm using this query:
 
SELECT  trans.id,  
trans.trx_timestamptz,  trans.account,  trans.debet,  
trans.creditFROM  (    
SELECT  
transactions.id,  
transactions.trx_timestamptz,  
transactions.account,
 
  
CASE    WHEN trx_type.t_type  = 
'DB' THEN  
transactions.amount    
ELSE  
0  END AS 
debet,  CASE  
WHEN trx_type.t_type = 'CR' 
THEN  
transactions.amount    
ELSE  
0  END AS credit    
FROM  
transactions  INNER JOIN trx_type ON 
(transactions.trx_type_id = trx_type.id)
 
  ) AS trans
 
result from above query :
 
 id |    
trx_timestamptz | 
account  |   debet   
|   
credit++--+---+-  
3 | 2005-04-14 17:16:49+08 | 01.2010100.2 
| 0 |  100.00  
4 | 2005-04-14 17:17:12+08 | 01.2010100.2 
| 0 |    
5.00  5 | 2005-04-14 17:17:12+08 | 01.2010100.3 
| 0 |   
60.00  6 | 2005-04-14 17:17:47+08 | 01.2010100.2 |   
7000.00 |   
0  7 | 2005-04-16 00:32:50+08 | 01.2010100.3 
| 0 |    
2.00 11 | 2005-04-16 02:45:06+08 | 01.2010100.2 
| 0 |   
10.00 12 | 2005-04-16 02:46:02+08 | 01.2010100.2 
| 0 |    
2.00 13 | 2005-04-16 02:46:59+08 | 01.2010100.2 | 163000.00 
|   0 14 | 
2005-04-16 02:50:17+08 | 01.2010100.5 
| 0 |   
10.00 15 | 2005-04-16 02:53:42+08 | 01.2010301.1 
| 0 |   
10.00 16 | 2005-04-16 02:57:22+08 | 01.2010100.1 
| 0 |  
200.00 17 | 2005-04-16 23:56:44+08 | 01.2010200.0 
| 0 | 1000.00 18 | 
2005-04-17 18:58:57+08 | 01.2010100.3 
| 0 |   
10.00 19 | 2005-04-17 19:13:05+08 | 01.2010100.2 
| 0 |   
10.00 20 | 2005-04-17 19:13:45+08 | 01.2010100.2 
| 0 |   
20.00 21 | 2005-04-17 19:15:36+08 | 01.2010100.2 
| 0 |    
5.00 22 | 2005-04-17 19:17:17+08 | 01.2010100.5 |  
1.00 |   
0 23 | 2005-04-17 19:18:06+08 | 01.2010100.4 
| 0 |   
2

Re: [SQL] Debet-Credit-Balance Calculation

2005-04-20 Thread Muhyiddin A.M Hayat 
There is an easy way to do this; write a plpgsql set returning
function which adds the balance to the last column of the table.  That
query will always have a cost in both time and memory proportional to
the size of the table, and the memory cost may bite you as table size
grows...
--
Can you give me some example function which adds the balance to the last 
column of the table.

Thanks. 


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] Debet-Credit-Balance Calculation

2005-04-20 Thread Muhyiddin A.M Hayat 
There is an easy way to do this; write a plpgsql set returning
function which adds the balance to the last column of the table.  That
query will always have a cost in both time and memory proportional to
the size of the table, and the memory cost may bite you as table size
grows...
--
Can you give me some example function which adds the balance to the last 
column of the table. or other query which same result and more faster

Thanks. 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Balance Calculation

2005-05-21 Thread Muhyiddin A.M Hayat 



Dear All,I have problem to calculation 
balance from debet and credit.my transaction table:

 
 id |    
trx_timestamptz | 
account  | trx_type_id |   
amount++--+-+-  
3 | 2005-04-14 17:16:49+08 | 01.2010100.2 
|   1 |  
100.00  4 | 2005-04-14 17:17:12+08 | 01.2010100.2 
|   1 
|    5.00  5 | 2005-04-14 17:17:12+08 | 
01.2010100.3 |   1 
|   60.00  6 | 2005-04-14 17:17:47+08 | 01.2010100.2 
|   2 
| 7000.00  7 | 2005-04-16 00:32:50+08 | 
01.2010100.3 |   1 
|    2.00 11 | 2005-04-16 02:45:06+08 | 
01.2010100.2 |   1 
|   10.00 12 | 2005-04-16 02:46:02+08 | 01.2010100.2 
|   1 
|    2.00 13 | 2005-04-16 02:46:59+08 | 
01.2010100.2 |   2 
|   163000.00 14 | 2005-04-16 02:50:17+08 | 01.2010100.5 
|   1 |   
10.00 15 | 2005-04-16 02:53:42+08 | 01.2010301.1 
|   1 |   
10.00 16 | 2005-04-16 02:57:22+08 | 01.2010100.1 
|   1 |  
200.00 17 | 2005-04-16 23:56:44+08 | 01.2010200.0 
|   1 | 
1000.00 18 | 2005-04-17 18:58:57+08 | 01.2010100.3 
|   1 |   
10.00 19 | 2005-04-17 19:13:05+08 | 01.2010100.2 
|   1 |   
10.00 20 | 2005-04-17 19:13:45+08 | 01.2010100.2 
|   1 |   
20.00 21 | 2005-04-17 19:15:36+08 | 01.2010100.2 
|   1 
|    5.00 22 | 2005-04-17 19:17:17+08 | 
01.2010100.5 |   2 
|    1.00 23 | 2005-04-17 19:18:06+08 | 
01.2010100.4 |   1 
|   20.00 24 | 2005-04-17 21:45:31+08 | 01.2010100.2 
|   1 
| 9000.00 25 | 2005-04-17 22:16:08+08 | 
01.2010100.6 |   1 
|   10.00
 
-
CREATE TABLE "public"."transactions" (  
"id" SERIAL,   "trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT 
('now'::text)::timestamp(6) with time zone NOT NULL,   "account" 
CHAR(16) NOT NULL,   "trx_type_id" INTEGER NOT NULL,   
"amount" NUMERIC(15,2) DEFAULT 0 NOT NULL,   "uid" INTEGER NOT NULL, 
  CONSTRAINT "transactions_pkey" PRIMARY KEY("id"),   
CONSTRAINT "transactions_trx_type_id_fkey" FOREIGN KEY 
("trx_type_id")    REFERENCES 
"public"."trx_type"("id")    ON DELETE 
CASCADE    ON UPDATE CASCADE    NOT 
DEFERRABLE) WITH OIDS;
---
 
and transaction type :
 
 id | trx_name | 
t_type+--+  1 | Credit   | 
CR  2 | Debet    | DB
 
-CREATE TABLE "public"."trx_type" (  "id" SERIAL, 
  "trx_name" VARCHAR(32),   "t_type" CHAR(2),   
CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"),   CONSTRAINT 
"trx_typei_check0" CHECK ((t_type = 'CR'::bpchar) OR (t_type = 
'DB'::bpchar))) WITH OIDS; 
 

-
 
so, i'm using this query:
 
SELECT  trans.id,  
trans.trx_timestamptz,  trans.account,  trans.debet,  
trans.creditFROM  (    
SELECT  
transactions.id,  
transactions.trx_timestamptz,  
transactions.account,
 
  
CASE    WHEN trx_type.t_type  = 
'DB' THEN  
transactions.amount    
ELSE  
0  END AS 
debet,  CASE  
WHEN trx_type.t_type = 'CR' 
THEN  
transactions.amount    
ELSE  
0  END AS credit    
FROM  
transactions  INNER JOIN trx_type ON 
(transactions.trx_type_id = trx_type.id)
 
  ) AS trans
 
result from above query :
 
 id |    
trx_timestamptz | 
account  |   debet   
|   
credit++--+---+-  
3 | 2005-04-14 17:16:49+08 | 01.2010100.2 
| 0 |  100.00  
4 | 2005-04-14 17:17:12+08 | 01.2010100.2 
| 0 |    
5.00  5 | 2005-04-14 17:17:12+08 | 01.2010100.3 
| 0 |   
60.00  6 | 2005-04-14 17:17:47+08 | 01.2010100.2 |   
7000.00 |   
0  7 | 2005-04-16 00:32:50+08 | 01.2010100.3 
| 0 |    
2.00 11 | 2005-04-16 02:45:06+08 | 01.2010100.2 
| 0 |   
10.00 12 | 2005-04-16 02:46:02+08 | 01.2010100.2 
| 0 |    
2.00 13 | 2005-04-16 02:46:59+08 | 01.2010100.2 | 163000.00 
|   0 14 | 
2005-04-16 02:50:17+08 | 01.2010100.5 
| 0 |   
10.00 15 | 2005-04-16 02:53:42+08 | 01.2010301.1 
| 0 |   
10.00 16 | 2005-04-16 02:57:22+08 | 01.2010100.1 
| 0 |  
200.00 17 | 2005-04-16 23:56:44+08 | 01.2010200.0 
| 0 | 1000.00 18 | 
2005-04-17 18:58:57+08 | 01.2010100.3 
| 0 |   
10.00 19 | 2005-04-17 19:13:05+08 | 01.2010100.2 
| 0 |   
10.00 20 | 2005-04-17 19:13:45+08 | 01.2010100.2 
| 0 |   
20.00 21 | 2005-04-17 19:15:36+08 | 01.2010100.