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

How to Add a Date randomly Expand / Collapse
Author
Message
Posted Thursday, July 04, 2013 3:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:36 AM
Points: 91, Visits: 164
Hi All,

In a table (sale.person) I have more than 9 lacs row. I have only three columns. I need to add a column by Orderdate (which I have done by Alter table). Next I want to fill up the rows with date which should be set as a Order.

For example if the date of first row is 2013-07-04 then the date of the next row will be 2013-07-03 and so one.

Is there any way so that I can do it easily?
Post #1470359
Posted Thursday, July 04, 2013 3:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 12,212, Visits: 9,192
Simply add an IDENTITY column to your table (if you haven't one already).
Give each row the same value for OrderDate: 2013-07-04. Then simply distract the identity value from the OrderDate to get the required values for your OrderDate column.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1470365
Posted Thursday, July 04, 2013 10:55 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
niladri.primalink (7/4/2013)
Hi All,

In a table (sale.person) I have more than 9 lacs row. I have only three columns. I need to add a column by Orderdate (which I have done by Alter table). Next I want to fill up the rows with date which should be set as a Order.

For example if the date of first row is 2013-07-04 then the date of the next row will be 2013-07-03 and so one.

Is there any way so that I can do it easily?


Considering that there have been only 7.35 lac days since the year "0000", I have to ask why you want to do this. Is it a test table that you're trying to populate? If so, please see the following...
http://www.sqlservercentral.com/articles/Test+Data/88964/


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1470484
Posted Thursday, July 04, 2013 6:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 3,596, Visits: 5,110
What in Buddha's name is a "lac day?"


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1470541
Posted Thursday, July 04, 2013 11:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:36 AM
Points: 91, Visits: 164
Hi All,

Thanks for your response....

Yes I have run a loop like below
declare @i int=900000
declare @mydate date=getdate()
while @i>0

begin
set @mydate= dateadd(day,-1,@mydate)
print @mydate
set @i=@i-1
end


However it is not working as the last date it shows like 01-01-01....What I have done is that I have given first 10,000 a single date and then next 10,000 another date and so on....

However your input has helped me to solve it.
Post #1470556
Posted Friday, July 05, 2013 1:26 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
dwain.c (7/4/2013)
What in Buddha's name is a "lac day?"


"lac" is 100,000 in the OP's language.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1470861
Posted Friday, July 05, 2013 1:29 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
niladri.primalink (7/4/2013)

However your input has helped me to solve it.


Two way street here... please post the code you ended up with so someone else can learn from it. Thanks.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1470863
Posted Saturday, July 06, 2013 12:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 3,596, Visits: 5,110
Jeff Moden (7/5/2013)
dwain.c (7/4/2013)
What in Buddha's name is a "lac day?"


"lac" is 100,000 in the OP's language.


Oh, I get it! It can be used like a productivity measure:

SLOC/lac-days



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1470899
Posted Saturday, July 06, 2013 8:37 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
dwain.c (7/6/2013)
Jeff Moden (7/5/2013)
dwain.c (7/4/2013)
What in Buddha's name is a "lac day?"


"lac" is 100,000 in the OP's language.


Oh, I get it! It can be used like a productivity measure:

SLOC/lac-days




--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1470922
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse