Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help on Update/Query Expand / Collapse
Author
Message
Posted Monday, September 22, 2008 5:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:04 PM
Points: 115, Visits: 351
Hi,

Can anybody help me out with the below issue please?

I'm using SQl server 2005. Please find the attached file for the data sample and my comments. Basically i need an update command or select query to achive the result. If you can give an idea how i can get the desired result that would be a great help.

Thanks a lot for the help in advance.

--Neela

Post #573930
Posted Monday, September 22, 2008 6:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 36,613, Visits: 31,061
Question of the day...

You have severely limited the amount of help you can get by...

1. Posting as a spreadsheet
2. Posting as a spreadsheet that many can't read
3. Not posting according to the methods recommended in the link below in my signature
4. All the above


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #573953
Posted Tuesday, September 23, 2008 12:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:04 PM
Points: 115, Visits: 351
Sorry for the mistakes. I'll post it correctly.
Post #574713
Posted Tuesday, September 23, 2008 1:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:04 PM
Points: 115, Visits: 351
Can anybody help me with the below issue?

--===== 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

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
Select Amount from MyTable Where AOD='02/01/2008' and date='02/01/2008'
with
Select Amount from mytable where AOD='01/01/2008' and date='02/01/2008'

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
Post #574728
Posted Tuesday, September 23, 2008 11:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 36,613, Visits: 31,061
I didn't forget ya... I just had a really long day and haven't been able to do the necessary writeup along with the code. I'll finish this when I get home later "tonight". It's 01:25 here right now and I need a nap.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #574900
Posted Wednesday, September 24, 2008 6:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 36,613, Visits: 31,061
I think this might do it...

 SELECT hi.Date,
hi.Area,
CASE WHEN hi.Amount = 0 THEN ISNULL(lo.Amount,0) ELSE hi.Amount END AS Amount,
hi.AOD
FROM MyTable lo
RIGHT JOIN MyTable hi ON lo.Date = hi.Date
AND lo.AOD = DATEADD(mm,-1,hi.AOD)
ORDER BY hi.AOD,hi.Date



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #575663
Posted Wednesday, September 24, 2008 8:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:04 PM
Points: 115, Visits: 351
Thanks alot for the reply Jeff.

this query worked really well. But i still have the zeros in amount field for the below records.

Date Area Amount AOD
2008-02-01 BA 0 2008-03-01
2008-02-01 BA 0 2008-04-01
2008-03-01 BA 0 2008-04-01

These needs to be filled with previous month's amounts.
with below records respectively

2008-02-01 BA 50 2008-02-01
2008-02-01 BA 50 2008-03-01
2008-03-01 BA 30 2008-03-01

Thanks alot for the help.
Neela

Post #575685
Posted Wednesday, September 24, 2008 11:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 36,613, Visits: 31,061
Ok... let's do this... here's the original data... mark each zero row with the rownum from where it's supposed to get it's data. If you leave any at zero, please explain why... and tell me if I have them sorted correctly... right now, I have them sorted by AOD and Date which met the requirements of your second post.

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



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #575705
Posted Thursday, September 25, 2008 9:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:04 PM
Points: 115, Visits: 351
Good Morning Jeff,

Here is my explanation:

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 the your time to solve this. I really appreciate it.
---Neela
Post #576110
Posted Friday, September 26, 2008 5:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 36,613, Visits: 31,061
Hmmm... alright, Neela... thanks. I've got this. I'm on my way to work... I'll bang it out tonight.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #576704
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse