SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help on Update/Query


Help on Update/Query

Author
Message
Neel009
Neel009
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87296 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Neel009
Neel009
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 351
Sorry for the mistakes. I'll post it correctly.
Neel009
Neel009
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87296 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87296 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Neel009
Neel009
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87296 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Neel009
Neel009
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87296 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search