SOLVED: Combine table sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SOLVED: Combine table sum

Author

Topic

pcock
Starting Member

12 Posts

Posted - 2008-08-20 : 20:45:24

Hi All,

I have two tables.

table: customer_cash
customer_id, date_in, date_out, amount_cash
100, 2008-08-01 10:38:00, 2008-04-05 10:25:00, 180.40
100, 2008-08-04 10:27:00, 2008-06-06 10:40:00, 270.50
100, 2008-08-06 10:44:00, 2008-08-08 10:39:00, 405.60
101, 2008-08-01 10:46:00, 2008-08-08 10:41:00, 0.00

table: customer_card
customer_id, date_paid, amount_card
100, 2008-08-01 11:12:00, 380.40
100, 2008-08-03 12:18:00, 88.90
100, 2008-08-07 15:01:00, 173.40
101, 2008-08-05 15:22:00, 404.35

I'd like to write a query summarising customer_cash, but I'd like to sum the amount_card for the date_paid that falls in the customer cash range. The following is a sample output that I like.

customer_id, date_in, date_out, amount_cash, amount_card
100, 2008-08-01 10:38:00, 2008-04-05 10:25:00, 180.40, 468.90
100, 2008-08-04 10:27:00, 2008-06-06 10:40:00, 270.50, 0
100, 2008-08-06 10:44:00, 2008-08-08 10:39:00, 405.60, 173.40
101, 2008-08-01 10:46:00, 2008-08-08 10:41:00, 0.00, 404.35

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 04:11:56

[code]SELECT cc.customer_id,cc.date_in,cc.date_out,cc.amount_cash,SUM(ccd.amount_card) AS amount_card
FROM customer_cash cc
LEFT JOIN customer_card ccd
ON ccd.customer_id=cc.customer_id
AND ccd.date_paid>=cc.date_in
AND ccd.date_paid<=cc.date_out
GROUP BY cc.customer_id,cc.date_in,cc.date_out,cc.amount_cash[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-21 : 15:28:33

quote:
Originally posted by pcock

Hi All,

I have two tables.

table: customer_cash
customer_id, date_in, date_out, amount_cash
100, 2008-08-01 10:38:00, 2008-04-05 10:25:00, 180.40
100, 2008-08-04 10:27:00, 2008-06-06 10:40:00, 270.50
100, 2008-08-06 10:44:00, 2008-08-08 10:39:00, 405.60
101, 2008-08-01 10:46:00, 2008-08-08 10:41:00, 0.00

table: customer_card
customer_id, date_paid, amount_card
100, 2008-08-01 11:12:00, 380.40
100, 2008-08-03 12:18:00, 88.90
100, 2008-08-07 15:01:00, 173.40
101, 2008-08-05 15:22:00, 404.35

I'd like to write a query summarising customer_cash, but I'd like to sum the amount_card for the date_paid that falls in the customer cash range. The following is a sample output that I like.

customer_id, date_in, date_out, amount_cash, amount_card
100, 2008-08-01 10:38:00, 2008-04-05 10:25:00, 180.40, 468.90
100, 2008-08-04 10:27:00, 2008-06-06 10:40:00, 270.50, 0
100, 2008-08-06 10:44:00, 2008-08-08 10:39:00, 405.60, 173.40
101, 2008-08-01 10:46:00, 2008-08-08 10:41:00, 0.00, 404.35

Thanks.

This number looks wrong.

pcock
Starting Member

12 Posts

Posted - 2008-08-21 : 21:58:28

Thank you visakh16 for your kind help and yes I did notice the figures were not accurate sodeep

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources