April 23, 2012 at 9:26 am
I have a query that gives me these results
Date Amount Category
4/13/2012 1000 a
4/12/2012 800 b
4/11/2012 700 c
4/10/2012 650 c
4/09/2012 600 d
4/05/2012 500 a
What is the SQL code to copy the record with the date 4/05/2012 while changing the date to 4/6/2012?
I want this desired output
Date Amount Category
4/13/2012 1000 a
4/12/2012 800 b
4/11/2012 700 c
4/10/2012 650 c
4/09/2012 600 d
4/08/2012 500 a
4/07/2012 500 a
4/06/2012 500 a
4/05/2012 500 a
Notice there are 3 dates of 4-6-2012,4-7-2012,4-8.2012 that need to take the record with 4/05/2012 which is the last business date.
Please help what do I need to do?
Please do not say I need a calandar table. I need to know the actualy SQL that can help me accomplish this.
Any keywords would be helpful
Should i store the results into a temp table and then create a cursor?
April 23, 2012 at 10:53 am
Why are you starting a new thread for the same discussion? If you would post ddl, sample data and desired output you would find lots of people willing to help.
Please direct all replies to the original thread. http://qa.sqlservercentral.com/Forums/Topic1287477-149-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2012 at 10:58 am
I am showing you current output and desired output.
What SQL code should I use to copy a record?
April 29, 2012 at 8:42 am
---------U need to use a fetch row sintax
---------I did a quick script for u , u need to do something similar.
-- Declare the variables to store the values returned by FETCH.
DECLARE @date datetime, @amount varchar(50),@category int ;
DECLARE contact_cursor CURSOR FOR
SELECT date, amount,category FROM Table1
ORDER BY date asc;
OPEN contact_cursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
declare @datemin datetime
set @datemin=(select min(date) from Table1 )
---create a Table 2 and insert first row from Table 1
select * into Table2
from Table1 where date=@datemin
FETCH NEXT FROM contact_cursor
INTO @date, @amount,category;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
declare @datemax datetime
declare @date_to_insert datetime
set @datemax=(select max(date) from Table2 )
declare @amount_to_insert varchar(50)
declare @category_to_insert int
set @amount_to_insert=(select amount from Table2 where date = @datemax)
set @category_to_insert=(select category from Table2 where date = @datemax)
if @date > dateadd(day,1,@datamax)
begin --if
@date_to_insert= @date
while @date_to_insert> dateadd(day,1,@datamax)
begin--while
insert into Table2 values dateadd(day,1,@datamax),@amount_to_insert,@category_to_insert
@datamax=dateadd(day,1,@datamax)
end --while
end --if
else --
begin
delete from table2 where date=@date---- to get rid of the first row that we insert
insert into Table2
select * from Table1 where date=@date
end--
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM contact_cursor
INTO @date, @amount,category;
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
select * from Table2
order by date desc
drop Table2--if u dont need it
--- Thats all
April 30, 2012 at 7:31 am
tysonlibelula (4/29/2012)
---------U need to use a fetch row sintax
Actually this is not a good approach as far as performance is concerned. The set based solution suggested in the original thread (http://qa.sqlservercentral.com/Forums/Topic1287477-149-3.aspx) will outperform this hands down.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 8:44 am
TY Tyson!!!!!!
I will try this code. I appreciate you taking time to write code and share it with me.
Sean please stop replying to my threads
You are not helpful at all although you may mean well. These people take time and effort to post code to help me come to a solution. I dont want your comments to discourage people from posting possible solutions because they dont fit to your standards.
On the contrary you have not posted any code.
April 30, 2012 at 8:50 am
wutang (4/30/2012)
TY Tyson!!!!!!I will try this code. I appreciate you taking time to write code and share it with me.
Sean please stop replying to my threads
You are not helpful at all although you may mean well. These people take time and effort to post code to help me come to a solution. I dont want your comments to discourage people from posting possible solutions because they dont fit to your standards.
On the contrary you have not posted any code.
wutang you posted two threads on the same topic. Somebody on the other thread posted a solution that was better than a cursor. I suggested that you and the poster view the other solution as it will perform better.
In your other thread adrian posted a great solution. I saw no benefit to writing the same code again. I apologize if you feel that my posts are wasting your time. I have posted thousands of line of code on this site. I will spend my volunteer time helping other people who appreciate the efforts. Best of luck.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply