HELP! Complicated Update query

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

General SQL Server Forums

New to SQL Server Programming

HELP! Complicated Update query

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2009-04-07 : 15:37:32

I can do a simple update query but not on this. Can anyone help?
I have two tables (bill table and cash table)

Bill table:
BillNumber char(10),
CashCollected money null,
LastCashCollectedDate datetime null

data in the bill table
1001 150.50 3/1/09
1002 null null
1003 100.00 1/1/09
1004 null null
1005 null null
------------------------------

Cash table:
BilNumber char(10),
amount money null,
receivedDate datetime null

data in the cash table
1002 200.50 2/1/09
1002 50.00 3/15/09
1002 100.00 4/5/09
1003 25.80 4/5/09
1005 250.00 4/6/09
1010 123.50 4/6/09
---------------------------------

how can i write a sql query to update the bill table with the result look like this?
1001 150.50 3/1/09
1002 350.50 4/5/09 /* last payment date */
1003 125.80 4/5/09 /* update amount & date */
1004 null null
1005 250.00 4/6/09

Thanks in advance.

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-07 : 16:35:33

Try this one & see what happens.
regards,
Anil.

declare @Bill table (Bilnumber Char(10), CashCollected Money, LastCashCollectedDate Datetime)
insert into @Bill select '1001', 150.50, '2009-03-01 01:12:10.000'
insert into @Bill select '1002', NULL, NULL
insert into @Bill select '1003', 100, '2009-01-01 01:12:10.000'
insert into @Bill select '1004', NULL, NULL
insert into @Bill select '1005', NULL, NULL

declare @Cash table (Bilnumber Char(10), Amount Money, Receiveddate Datetime)
insert into @Cash select '1002', 200.50, '2009-02-01 01:12:10.000'
insert into @Cash select '1002', 50.00, '2009-03-15 01:12:10.000'
insert into @Cash select '1002', 100, '2009-04-05 01:12:10.000'
insert into @Cash select '1003', 25.80, '2009-04-05 01:12:10.000'
insert into @Cash select '1005', 250.00, '2009-04-06 01:12:10.000'
insert into @Cash select '1010', 123.50, '2009-04-06 01:12:10.000'

UPDATE O
set O.CashCollected = B.Col1, O.LastCashCollectedDate = B.Col2
From @Bill O
inner join (
Select A.Bilnumber, MAX(A.CashCollected) as Col1, MAX(A.LastCashCollectedDate) as Col2
From
(
select Bilnumber,SUM(Amount) as CashCollected, MAX(Receiveddate) as LastCashCollectedDate from @Cash
group by Bilnumber
union
Select Bilnumber, Cashcollected, LastCashCollectedDate from @Bill)a
group by A.Bilnumber
)B
on
B.Bilnumber = O.Bilnumber
Select * from @Bill

nizguy
Starting Member

37 Posts

Posted - 2009-04-07 : 16:57:38

Kumar, thank you so much for helping me out.

the only problem that I have on the result is the billNumber 1003 should be $125.80 instead of $100. And I look through the code still don't know how to fix it. Please let me know how do I fix it. thank you

Here is the result set:
1001 150.5000 2009-03-01 01:12:10.000
1002 350.5000 2009-04-05 01:12:10.000
1003 100.0000 2009-04-05 01:12:10.000
1004 NULL NULL
1005 250.0000 2009-04-06 01:12:10.000

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-07 : 17:37:42

Sure, try this one & let me know.

Regards,
Anil Kumar.

UPDATE O
set O.CashCollected = B.Col1, O.LastCashCollectedDate = B.Col2
From @Bill O
inner join (
Select A.Bilnumber, Sum(A.CashCollected) as Col1, MAX(A.LastCashCollectedDate) as Col2
From
(
select Bilnumber,SUM(Amount) as CashCollected, MAX(Receiveddate) as LastCashCollectedDate from @Cash
group by Bilnumber
union
Select Bilnumber, Cashcollected, LastCashCollectedDate from @Bill)a
group by A.Bilnumber
)B
on
B.Bilnumber = O.Bilnumber

nizguy
Starting Member

37 Posts

Posted - 2009-04-07 : 17:48:20

You're da man.

I got the correct result. Now I have to take time to learn and understand the code.

Thank you SO much

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-07 : 17:54:14

You are quite welcome.

regards,
Anil Kumar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources