September 23, 2008 at 4:09 pm
Can anybody help me with the below issue?
I want to update the Amount column which has value zero with previous month's(AOD column) value (dont want to hardcode it)
For example
I want to Update Amount from MyTable Where AOD='02/01/2008' and date='02/01/2008'
with Amount from mytable where AOD='01/01/2008' and date='02/01/2008'
--===== Create the test table with
CREATE TABLE [dbo].[mytable](
[date] [datetime] NULL,
[Area] [nchar](10) NULL,
[Amount] [nchar](10) NULL,
[AOD] [datetime] NULL
) ON [PRIMARY]
--===== Insert the test data into the test table
Insert into mytable
SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'Feb 1 2008 12:00AM','BA ','50 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'Mar 1 2008 12:00AM','BA ','40 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'Apr 1 2008 12:00AM','BA ','30 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'May 1 2008 12:00AM','BA ','20 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'Mar 1 2008 12:00AM','BA ','30 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'Apr 1 2008 12:00AM','BA ','40 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'May 1 2008 12:00AM','BA ','50 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL
SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL
SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'Mar 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'Apr 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'May 1 2008 12:00AM','BA ','20 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'Mar 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL
SELECT 'Apr 1 2008 12:00AM','BA ','20 ','Mar 1 2008 12:00AM' UNION ALL
SELECT 'May 1 2008 12:00AM','BA ','10 ','Mar 1 2008 12:00AM'
--Select the record using the below statement
Select * from MyTable Order by AOD,Date
Final result should look like this
Date Area Amount AOD
2008-01-01 00:00:00.000 BA 0 2008-01-01 00:00:00.000
2008-02-01 00:00:00.000 BA 50 2008-01-01 00:00:00.000
2008-03-01 00:00:00.000 BA 40 2008-01-01 00:00:00.000
2008-04-01 00:00:00.000 BA 30 2008-01-01 00:00:00.000
2008-05-01 00:00:00.000 BA 20 2008-01-01 00:00:00.000
2008-01-01 00:00:00.000 BA 0 2008-02-01 00:00:00.000
2008-02-01 00:00:00.000 BA 50 2008-02-01 00:00:00.000
2008-03-01 00:00:00.000 BA 30 2008-02-01 00:00:00.000
2008-04-01 00:00:00.000 BA 40 2008-02-01 00:00:00.000
2008-05-01 00:00:00.000 BA 50 2008-02-01 00:00:00.000
2008-01-01 00:00:00.000 BA 0 2008-03-01 00:00:00.000
2008-02-01 00:00:00.000 BA 50 2008-03-01 00:00:00.000
2008-03-01 00:00:00.000 BA 30 2008-03-01 00:00:00.000
2008-04-01 00:00:00.000 BA 20 2008-03-01 00:00:00.000
2008-05-01 00:00:00.000 BA 10 2008-03-01 00:00:00.000
2008-01-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000
2008-02-01 00:00:00.000 BA 50 2008-04-01 00:00:00.000
2008-03-01 00:00:00.000 BA 30 2008-04-01 00:00:00.000
2008-04-01 00:00:00.000 BA 20 2008-04-01 00:00:00.000
2008-05-01 00:00:00.000 BA 20 2008-04-01 00:00:00.000
Thanks alot for the help
Neela
September 24, 2008 at 5:12 am
Took me a little bit of thinking but quite simple in the end.
Use this select statement as the source for an UPDATE query...
SELECT
M.*,
'NewAmount' = (
SELECT
Amount
FROM
mytable
WHERE
[Date] = M.[Date]
ANDAOD = DATEADD(mm,-1,M.AOD)
)
FROM
mytable AS M
Hope this helps,
Ash
September 24, 2008 at 2:59 pm
Thanks Ash for the reply. Unfortunately the query that you have specified is not working as expected.
Date Area AmountAOD New NumberExpected Result
1/1/2008BA 0 1/1/2008 NULL0
2/1/2008BA 501/1/2008 NULL50
3/1/2008BA 401/1/2008 NULL40
4/1/2008BA 301/1/2008 NULL30
5/1/2008BA 201/1/2008 NULL20
1/1/2008BA 0 2/1/2008 00
2/1/2008BA 0 2/1/2008 5050
3/1/2008BA 302/1/2008 4030
4/1/2008BA 402/1/2008 3040
5/1/2008BA 502/1/2008 2050
1/1/2008BA 0 3/1/2008 00
2/1/2008BA 0 3/1/2008 050
3/1/2008BA 0 3/1/2008 030
4/1/2008BA 203/1/2008 020
5/1/2008BA 103/1/2008 010
1/1/2008BA 0 4/1/2008 200
2/1/2008BA 0 4/1/2008 1050
3/1/2008BA 0 4/1/2008 3030
4/1/2008BA 0 4/1/2008 4020
5/1/2008BA 20 4/1/2008 5020
Thanks,
Neela
September 25, 2008 at 2:12 am
Hey Neela,
Schoolboy error - I didn't read the question properly, where you clearly said "I want to update the Amount column which has value zero."
This should simply be a case of adding the line "WHERE M.Amount = 0" to the query I suggested.
However...
You gave as an example...For example
I want to Update Amount from MyTable Where AOD='02/01/2008' and date='02/01/2008'
with Amount from mytable where AOD='01/01/2008' and date='02/01/2008'
As a corrollary, where date = '02/01/2008' and AOD='04/01/2008' you would the Amount (which is currently 0) to be updated with the value where date = '02/01/2008' (the same date) and AOD='03/01/2008' (a month earlier).
The Amount value for this criteria in your table is also 0, but you want an Expected result of 50. So perhaps I am not understanding exactly how the data should be updated? If you can clarify I should be able to help you. I feel we are almost there!
Ash
September 25, 2008 at 10:09 am
Yeah we are almost there
Let me explain in this way.
RowNum UpdateFrom date Area Amount AOD
--------- ---------- ----------------------- ---------- ---------- -----------------------
1 2008-01-01 00:00:00.000 BA 0 2008-01-01 00:00:00.000
2 2008-02-01 00:00:00.000 BA 50 2008-01-01 00:00:00.000
3 2008-03-01 00:00:00.000 BA 40 2008-01-01 00:00:00.000
4 2008-04-01 00:00:00.000 BA 30 2008-01-01 00:00:00.000
5 2008-05-01 00:00:00.000 BA 20 2008-01-01 00:00:00.000
6 2008-01-01 00:00:00.000 BA 0 2008-02-01 00:00:00.000
7 2008-02-01 00:00:00.000 BA 0 2008-02-01 00:00:00.000
8 2008-03-01 00:00:00.000 BA 30 2008-02-01 00:00:00.000
9 2008-04-01 00:00:00.000 BA 40 2008-02-01 00:00:00.000
10 2008-05-01 00:00:00.000 BA 50 2008-02-01 00:00:00.000
11 2008-01-01 00:00:00.000 BA 0 2008-03-01 00:00:00.000
12 2008-02-01 00:00:00.000 BA 0 2008-03-01 00:00:00.000
13 2008-03-01 00:00:00.000 BA 0 2008-03-01 00:00:00.000
14 2008-04-01 00:00:00.000 BA 20 2008-03-01 00:00:00.000
15 2008-05-01 00:00:00.000 BA 10 2008-03-01 00:00:00.000
16 2008-01-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000
17 2008-02-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000
18 2008-03-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000
19 2008-04-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000
20 2008-05-01 00:00:00.000 BA 20 2008-04-01 00:00:00.000
We have zero for rownum 1. I want to keep this as zero as we dont have data for AOD= 2007-12-01(Previous month of 2008-01-01). This zero should be there for rownum 6 ,11, 16.
rownum 7 should be updated with the amount in rownum 2 which is 50
rownum 12 should be updated with the amount in rownum 7 which is 50(the value we just updated)
rownum 13 should be updated with the amount in rownum 8 which is 30
rownum 17 should be updated with the amount in rownum 12 which is 50
rownum 18 should be updated with the amount in rownum 13 which is 30
rownum 19 should be updated with the amount in rownum 14 which is 20
Thanks a lot for helping me on this. I really appreciate it.
Neela
September 25, 2008 at 10:19 am
Hey Neela,
I thought that might be the case. We just need to call the UPDATE several times to cover all the zeroes.
I will look at this tomorrow morning if that is alright. Shouldn't take too long.
Ash
September 25, 2008 at 11:18 am
Thanks alot Ash
September 26, 2008 at 3:05 am
Morning Neela,
I think I've cracked it. Just added a WHILE loop to call the UPDATE statement whilst there are zeros. Then get rid of the NULLS at the end by making them zeros.
DECLARE @Zerosint
SELECT @Zeros = (SELECT COUNT(*) FROM mytable WHERE Amount = 0)
WHILE @Zeros > 0
BEGIN
UPDATE
mytable
SET
Amount = M1.NewAmount
FROM
mytable AS N1
INNER JOIN
(
SELECT
M.*,
'NewAmount' = (
SELECT
Amount
FROM
mytable
WHERE
[date] = M.[date]
AND AOD = DATEADD(mm,-1,M.AOD)
)
FROM
mytable AS M
WHERE
M.Amount = 0
)
AS M1
ON
N1.[date] = M1.[date]
ANDN1.Area = M1.Area
ANDN1.AOD = M1.AOD
SELECT @Zeros = (SELECT COUNT(*) FROM mytable WHERE Amount = 0)
END
UPDATE mytable SET Amount = 0 WHERE Amount IS NULL
Let me know how it goes.
Cheers,
Ash
September 26, 2008 at 10:00 am
September 27, 2008 at 12:59 pm
There is one lightning quick method to do this in a set based fashion that depends on a clustered index. The method is discussed at some great length in the following article...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
It's just like the running total problem, but without accumulating a total. Rather, it "smears" data downward.
Here's the code for this problem, including the original test data code...
drop table MyTable
go
--===== Create the test table... do notice the changes in Nullability
CREATE TABLE [dbo].[mytable](
[date] [datetime] NOT NULL,
[Area] [nchar](10) NOT NULL,
[Amount] [nchar](10) NULL,
[AOD] [datetime] NOT NULL
) ON [PRIMARY]
--===== Insert the test data into the test table
Insert into mytable
SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'Feb 1 2008 12:00AM','BA ','50 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'Mar 1 2008 12:00AM','BA ','40 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'Apr 1 2008 12:00AM','BA ','30 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'May 1 2008 12:00AM','BA ','20 ','Jan 1 2008 12:00AM' UNION ALL
SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'Mar 1 2008 12:00AM','BA ','30 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'Apr 1 2008 12:00AM','BA ','40 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'May 1 2008 12:00AM','BA ','50 ','Feb 1 2008 12:00AM' UNION ALL
SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL
SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL
SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'Mar 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'Apr 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'May 1 2008 12:00AM','BA ','20 ','Apr 1 2008 12:00AM' UNION ALL
SELECT 'Mar 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL
SELECT 'Apr 1 2008 12:00AM','BA ','20 ','Mar 1 2008 12:00AM' UNION ALL
SELECT 'May 1 2008 12:00AM','BA ','10 ','Mar 1 2008 12:00AM'
--===== Create the necessary clustered index (part of the solution)
ALTER TABLE MyTable ADD PRIMARY KEY CLUSTERED (DATE,AOD,Area)
--===== This uses what some refer to as the "quirky update"
DECLARE @PrevDate DATETIME
DECLARE @PrevAmount NCHAR(10)
DECLARE @PrevAOD DATETIME
SELECT @PrevDate = 0, @PrevAmount = 0, @PrevAOD = 0
UPDATE t1
SET @PrevAmount = Amount = CASE WHEN t1.Amount = 0 THEN @PrevAmount ELSE t1.Amount END,
@PrevDate = t1.Date,
@PrevAod = t1.Aod
FROM dbo.MyTable t1 WITH(INDEX(0))
--===== Display the results for verification
SELECT ROW_NUMBER() OVER (ORDER BY AOD,Date) AS RowNum,
Date,
Area,
Amount,
AOD
FROM MyTable
ORDER BY AOD,Date
Now... if you can't make any changes to the nullability of the original table, simply use SELECT/INTO to create a TempTable with the correct clustered index and execute the code on that. It will run much quicker than you could previously imagine.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2008 at 1:06 pm
As a side bar, I strongly question the value of assigning the NCHAR datatype to a column, like the amount column, that will obviously hold a numeric value.;) I also question the value of a table that has no obvious sign of a Primary Key. I realize that this is test code, but it's always important to know what the primary key of even a test table is because they default to being clustered which would certainly get in the way of the solution I posted (unless you copy data to a temp table, first).
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2008 at 11:14 pm
Thaks alot Ash.
September 27, 2008 at 11:15 pm
Thanks alot for helping me Jeff.
September 28, 2008 at 6:35 pm
You bet, Neela.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply