Account CalYear CalMonth Amount
Comm 2006 12 80
Comm 2007 01 100
Comm 2007 02 125
Incr 2007 01 21
Incr 2007 02 28
I want to update the incr account so it shows the correct difference
between the previous month, the change basically. Corrected it would
look like this:
Account CalYear CalMonth Amount
Comm 2006 12 80
Comm 2007 01 100
Comm 2007 02 125
Incr 2007 01 20
Incr 2007 02 25
1. It would be better to have one column storing a datetime value
equal to the first of the month represented.
2. It also might be better to not have actualized rows for the Incr
account at all, and to generate them dynamically with a VIEW.
For example:
CREATE TABLE #X (
Account varchar(10) NOT NULL
, DT datetime NOT NULL
, Amount int NOT NULL
, CONSTRAINT pk_X PRIMARY KEY (Account, DT)
)
INSERT #X VALUES ('Comm', '2006-12-01', 80)
INSERT #X VALUES ('Comm', '2007-01-01', 100)
INSERT #X VALUES ('Comm', '2007-02-01', 125)
-- INSERT #X VALUES ('Incr', '2007-01-01', 21)
-- INSERT #X VALUES ('Incr', '2007-02-01', 28)
SELECT Account, DT, Amount FROM #X
UNION ALL
SELECT 'Incr', B.DT, B.Amount-A.Amount "Amount"
FROM #X A INNER JOIN #X B
ON A.Account=B.Account and B.DT = dateadd(month,1,A.DT)
3. However, if you are stuck with what you've got, here's how it can
be done:
UPDATE theTable
SET Amount =
( SELECT C.Amount
FROM (
SELECT 'Incr', B.CalYear, B.CalMonth, B.Amount-A.Amount "Amount"
FROM theTable A INNER JOIN theTable B
ON A.Account='Comm' AND B.Account='Comm'
AND dateadd(month, B.CalMonth - 1, dateadd(year, B.CalYear-1980,
'1980-01-01'))
= dateadd(month, A.CalMonth -1 , dateadd(year,
A.CalYear-1980, '1980-02-01'))
) C
WHERE C.Account = theTable.Account
AND C.CalYear = theTable.CalYear
AND C.CalMonth = theTable.CalMonth
)
WHERE theTable.Account = 'Incr'
UPDATE Whatever
SET Amount =
COALESCE(
(SELECT Amount from Whatever as A
WHERE A.Account = 'Comm'
AND A.Year = Whatever.Year
AND A.Month = Whatever.Month), 0)
-
COALESCE(
(SELECT Amount from Whatever as B
WHERE B.Account = 'Comm'
AND (((Whatever.Month <> 1
AND B.Year = Whatever.Year
AND B.Month = Whatever.Month - 1)
OR (Whatever.Month = 12
AND B.Year = Whatever.Year - 1
AND B.Month = 12)))), 0)
WHERE Account = 'Incr'
Very strange table design. by the way.
Roy Harvey
Beacon Falls, CT
Why post untested code, when it takes only a minute or two more to
test it in Query Analyzer?
If the original poster had provided CREATE TABLE and INSERTs for test
data I would have tested the code. They did not.
I didn't see that you included the CREATE TABLE and INSERTs you used
to test your third alternative. I could have tested against that if
you had. Why didn't you post that too?
Why do you spit on us and want us to do your homework/job for you?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Why did you invent your own two column data types that avoid the
temporal data types? Have you ever read the ISO-8601 rules for
temporal data?
CREATE TABLE StinkingFoobar -- until we get a real name
(acct_type CHAR(4) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
squid_amt INTEGER NOT NULL, -- read ISO-11179 before you program
again!!
PRIMARY KEY (acct_type, start_date)
);
Did I guess right?? Gee, wish you had helped!!
>> I want to update the incr account so it shows the correct difference between the previous month <<
No. Yhat is soooooo non-RDBMS!! This is a computed value and needs
to be done in a VIEW using the OLAP functions. RTFM.
Meh. I proposed a different design, so I posted DDL and inserts for
it. When adapting back to the original design, I left it out, since
the poster did. I apologize for the inconsistency.
For what it's worth, here they are.
CREATE TABLE #X (
Account varchar(10) NOT NULL
, CalYear int NOT NULL
, CalMonth int NOT NULL
, Amount int NOT NULL
, CONSTRAINT pk_X PRIMARY KEY (Account, CalYear, CalMonth)
)
INSERT #X VALUES ('Comm', 2006, 12, 80)
INSERT #X VALUES ('Comm', 2007, 01, 100)
INSERT #X VALUES ('Comm', 2007, 02, 125)
INSERT #X VALUES ('Incr', 2007, 01, 21)
INSERT #X VALUES ('Incr', 2007, 02, 28)
Yes, it's always better when the original author posts DDL and inserts
with their question. But it's often the people who have very little
experience that fail to do this, and it's they that often need the
help the most.
I also apologize if I seemed prickly or unfriendly in asking. From my
own experience, when I write off-the-cuff code in a newsgroup and
don't test it, I usually find I've made mistakes later. If I test
before posting my error rate goes down a few percentage points.
For what it's also worth, I just tried your code. After changing
"Month" -> "CalMonth" and "Year" -> "CalYear" to match the table
heading in the original poster's question, your solution does work.
Wow. Are you getting enough sleep at night, Mr. Celko? The vitriol
appears to be rising.
No, Celko, I bet most people on this forum have NOT read your precious . . .
which was it this time?? Oh yeah - ISO-8601. Smarter guys would have
realized that after the 3rd or 4th time they asked that question. Even DUMB
people should figure it out after the 15th or 16th time. Where does that
leave you??
--
TheSQLGuru
President
Indicium Resources, Inc.
"rpresser" <rpre...@gmail.com> wrote in message
news:1181939844....@q75g2000hsh.googlegroups.com...
CREATE TABLE X (
Groupid int not null,
Account varchar(10) NOT NULL
, DT datetime NOT NULL
, Amount int NOT NULL
, CONSTRAINT pk_X PRIMARY KEY (Account, DT)
)
INSERT X VALUES (1,'Comm', '2006-12-01', 80)
INSERT X VALUES (2,'Comm', '2007-01-01', 100)
INSERT X VALUES (3,'Comm', '2007-02-01', 125)
INSERT X VALUES (1,'Incr', '2007-01-01', 21)
INSERT X VALUES (2,'Incr', '2007-02-01', 28)
CREATE VIEW v_test
AS
SELECT Groupid,(SELECT TOP 1 X1.Amount -X.Amount FROM X X1
WHERE X1.Account='Comm'AND X1.Groupid >X.Groupid) AS diff
FROM X WHERE Account='Comm'
UPDATE X SET Amount =diff FROM v_test
JOIN X ON X.Groupid=v_test.Groupid
WHERE Account='Incr'
SELECT * FROM X
DROP TABLE X
DROP VIEW v_test
"Ctal" <witp_...@yahoo.com> wrote in message
news:1181929348.2...@p77g2000hsh.googlegroups.com...
A general approach I take to this problem is to use a temporary table
with an identity column, and insert into that the records I want to
get differences of, e.g.
CREATE TABLE #Comms (
[ID] INTEGER IDENTITY(1, 1) PRIMARY KEY,
CalYear INTEGER,
CalMonth INTEGER,
Amount INTEGER
)
INSERT INTO #Comms
SELECT CalYear, CalMonth, Amount
FROM YourTable
WHERE Account = 'Comm'
ORDER BY CalYear, CalMonth
-- Gives a table of the form:
-- ID CalYear CalMonth Amount
-- 1 2006 12 80
-- 2 2007 01 100
-- 3 2007 02 125
...then, to get differences for adjacent months (assuming no gaps in
your records)...
SELECT C1.CalYear, C1.CalMonth, (C1.Amount - C2.Amount) AS Diff
FROM #Comms C1
INNER JOIN #Comms C2 -- self join, C1 record will join to 'previous'
record in C2.
ON C1.ID = (C2.ID + 1)
...I find this a very straightforward, easy to understand approach -
admittedly at the cost of some IO / CPU, but this sort of thing tends
not to crop up all that often.