Updating a table

[Lftsk]

I have an issue, I'm not sure how to solve.

I have 2 tables - table1 and table2

They both have the same 4 column primary key - K1, K2, K3 and K4.

Table1 is a transaction table that contains charges and payments for the past year. Table2 is a summary table that in addition to the key contains 12 columns, one for each month of the year.

I need to sum up all transactions (from table1) into their respective months and update the corresponding month
column in table2.

I'm kind of desperate.

Any help would be greatly appreciated!!

Thank you.

Lftsk.

 

[tlbroadbent]

Can you provide a little more info? How can one tell which month a charge or payment is in? How can one tell if the value is a charge or a payment? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Lftsk]

Sorry I should have been more specific. Each Transaction in table1 (along with it's 4 column key) has a date column and a Transaction Type column. If the Transaction Type Column is a 'C' then it's a charge - if it's a 'P', it's a payment. In table2 I have 13 columns (in addition to it's 4 column key). The first column is the year of the row and the other 12 is each month in that year. Essentially I need to total up all of the transactions (add the charges, subtract the payments) into 12 groups - each group representing a month of the year of the transaction. I then need to update each month column in table2 with these dollar amounts where table1.K1 = table2.K1 AND table1.K2 = table2.K2 AND table1.K3 = table.K3 AND table1.K4 = table2.K4.

Once again, thank you for any help!

 

[tlbroadbent]

Does table2 already contain rows that match table1 or will you be inserting the rows? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[tlbroadbent]

Here is one possible solution. It creates a crosstab of the transactions and uses the resulting row set to update table1. You'll have to modify it to match actual table and column names. I haven't verified the syntax. Hopefully there are no errors.

Update table2 Set
Mnth1=q.Mnth1, Mnth2=q.Mnth2, Mnth3=q.Mnth3,
Mnth4=q.Mnth4, Mnth5=q.Mnth5, Mnth6=q.Mnth6,
Mnth7=q.Mnth7, Mnth8=q.Mnth8, Mnth9=q.Mnth9,
Mnth10=q.Mnth10, Mnth11=q.Mnth11, Mnth12=q.Mnth12
From Table1
Join
(Select k1, k2, k3, k4, TranYear=Year(TranDate),
Sum(Case When Month(TranDate)=1
Then Case When TranType='P'
Then -1.0 * TranAmt
Else TranAmt End
Else 0 End) As Mnth1,
Sum(Case When Month(TranDate)=2
Then Case When TranType='P'
Then -1.0 * TranAmt
Else TranAmt End
Else 0 End) As Mnth2,
.
. <insert code for other months here>
.
Sum(Case When Month(TranDate)=12
Then Case When TranType='P'
Then -1.0 * TranAmt
Else TranAmt End
Else 0 End) As Mnth12
From table2) As qry

On table1.K1=qry.k1
And table1.K2=qry.k2
And table1.K3=qry.k3
And table1.K4=qry.k4
And table1.SummYear=qry.TranYear

The query above assumes table1 contains data to be updated. If you need to insert, you can use the crosstab result and insert it into table1. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Lftsk]

Thanks so much! This helps out quite a bit. The answer to your first question is I would be updating, not inserting. I think this is quite close to solving it. Just one thing and correct me if I'm wrong, but doesn't the above solution create a row for each record in Table1?

I think the problem may be with the Keys. The Key is an account number. The account number contains 4 segments(columns). For example, an account number may be '12345678'
where segment 1 is '12', segment 2 is '34', segment 3 is '56' and segment 4 is '78'. Table1 contains items purchased and payments made to pay for those items. Thus If account number '12345678' purchased 2 items and made one payment, there would be 3 entries with that account number in Table1. For sake of this example, let's say that the transactions all have the same date. Thus we can have duplicate keys in Table1.

In Table2 we can also have duplicate account numbers, however we cannot have two rows with the same account number for the same year. So it might be easier to think of Table2 having a 5 column key (the 5th column being the Year), thus the key in table2 would be unique (no other record in table2 would have that same key.

So the three transactions mentioned above in Table1 would &quot;roll up&quot; or sum up into one row (and the corresponding month (column)) in Table2.

Once again any help is so greatly appreciated!

 

[tlbroadbent]

The query will only update existing rows in table1. I left off the group by clause in the sub-1uery. I apologize for that oversight. The following query shopuld rollup properly.

Update table2 Set
Mnth1=q.Mnth1, Mnth2=q.Mnth2, Mnth3=q.Mnth3,
Mnth4=q.Mnth4, Mnth5=q.Mnth5, Mnth6=q.Mnth6,
Mnth7=q.Mnth7, Mnth8=q.Mnth8, Mnth9=q.Mnth9,
Mnth10=q.Mnth10, Mnth11=q.Mnth11, Mnth12=q.Mnth12
From Table1
Join
(Select k1, k2, k3, k4, TranYear=Year(TranDate),
Sum(Case When Month(TranDate)=1
Then Case When TranType='P'
Then -1.0 * TranAmt
Else TranAmt End
Else 0 End) As Mnth1,
Sum(Case When Month(TranDate)=2
Then Case When TranType='P'
Then -1.0 * TranAmt
Else TranAmt End
Else 0 End) As Mnth2,
.
. <insert code for other months here>
.
Sum(Case When Month(TranDate)=12
Then Case When TranType='P'
Then -1.0 * TranAmt
Else TranAmt End
Else 0 End) As Mnth12
From table2
Group By k1, k2, k3, k4, Year(TranDate)) As qry
On table1.K1=qry.k1
And table1.K2=qry.k2
And table1.K3=qry.k3
And table1.K4=qry.k4
And table1.SummYear=qry.TranYear
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Lftsk]

Just wanted to say THANK YOU! It worked perfectly. Can't tell you how much this helped me.

Thanks again.