Auto Populate date from last saved date.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Auto Populate date from last saved date.

Author

Topic

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 09:09:51

Hi,

I have a Table:DateRange
I have a SP called DATESP which dates in 2 parameters: starts date and end date in mm/dd/yyyy format and fills in the rows of the DateRange table with all the dates between start and end date including the 2 proved.

For Example: if you provide 08/25/2009 as start date and 08/30/2009 as end.
DATESP will populate the DatRange table with
08/25/2009
08/26/2009
08/27/2009
08/28/2009
08/29/2009
08/30/2009 and i run another SP to get the dates from here to get some data for all these dates.

Now I want to automate this. in the sense, I want to know, if it is possible for to create a SP, which will access some table to check the last date for which the data has been captured and starts TO FILL THE DateRange table at the next date(here it will be 08/31/2009) and add 6 more days to it to make it 1 week fill the DateRange table via DATESP and get data.

My idea:
1) May be i can create a table with 3 column: start date, end date, and status.
Every time i use a row, the status should change as used.
So, this way the SP can check for the first,'not used' status row and use the start and end date mentioned there.

Is this possible, is yes how.
If there is any other way to do it efficiently, please suggest.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 09:19:39

Not Clear about what you exactly need.

Rahul Shinde

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 09:30:20

Ultimately, this is what I am trying to do:

i have 3 SP:
1) SP to create table: input parameter: name for the table
2) SP to create dates range: input parameter: start date and end date
3) SP to get data from another DB clean the data and output in .txt file: input parameter: table name(the same table name we have entered in SP1 above)

I am currently running these 3 SP's manually and giving the input's.
I want to automate the entire process. Is this possible? if yes, can you give me some guideline on how we can approach this issue.

Thank you.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 10:26:50

Hi,

I have a table dateRange with the following details:
startDate endDate flag
08/01/2009 08/03/2009 1
08/05/2009 08/07/2009 2
08/08/2009 08/09/2009 2
08/10/2009 08/12/2009 2
08/13/2009 08/15/2009 2
08/16/2009 08/18/2009 2

I want to create a SP, which will take the start and end date from this table where flag is 1, do something, and change the status of the flag to 0 and change the status of the next row only to 1.

I wrote this SP, but it gives me error.

create procedure [dbo].[TESTINGdateRange]
as

begin
delete from Test4

select startDate, endDate from dateRange where flag = '1'

insert into Test4(date)
select DATEADD(day,number,startDate) from master..spt_values
where type='p' and number between 0 and DATEDIFF(day,startDate,endDate)

update dateRange
set flag = '0'
where startDate = startDate

update Test4
SET date = convert(varchar, convert(datetime, date), 101)

end

ERROR: Invalid column name 'startDate'.
Invalid column name 'endDate'.

Please advise.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 10:49:21

Can someone please help me out here.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 11:07:04

The error is from your "insert into test4(date)" statement. you are referring to colulmns from [daterange] table but that table is not in your FROM clause. Try this:

THIS code assumes only one row will be flag='1' (as is in your sample data)


insert into Test4(date)
select DATEADD(day,number,startDate) 
from master..spt_values v
inner join dateRange r
       on r.flag = '1'
       and v.type = 'P'
       and v.number <= DATEDIFF(day, r.startDate, r.endDate)

Not sure what the purpose of your final UPDATE statement is. Are you storing the date as VARCHAR? That's not a good idea.

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 11:15:32

Hi TG,

Thank you.
Yes I am storing the date as varchar, since i want the date format to be in mm/dd/yyyy format.
I have tried using the datetime and smalldatetime and tried to convert them in the final step, but they wont give me the exact mm/dd/yyyy format. thats why.

btw, i wanted to change the flag to 0 after the row is used and change the flag of the next row only to 1.

update dateRange
set flag = '0'
where flag = '1'

but for updating next row to 1, how would i do that.
is there is a select next row or update n ext row something to change the flag status?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 11:25:11

You're welcome.

Do you want to move to the next row (and work through the whole table) from within a single call to your SP or do you want to process the "next" row the next time the SP is called?

There is no reason to store a date as anything but datetime or smalldatetime - especially for formatting reasons. At whatever point you want to "present" the date in specific format you should do that in the presentation layer of an application. If, for some reason, you want to display the date from within a sql server query window in "mm/dd/yyyy" format you can use this:
convert(varchar(10), <dateColumn>, 101)

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 11:31:44

The second one. I do not want to use cursors to go through the next row for the entire table.

But the once the SP is called, it should pick value from row 1 and change status to 0 after the work and change status for next row only to 1.
Next time the same SP is called, it will look for status 1 and use that and after the work change it to 0 and next row only to 1.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 12:53:48

Any suggestion TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 13:03:11

Assuming the date chronology defines the sequence you want to work through then perhaps something like this:


declare @nextDate datetime
select @nextDate = min(startDate) 
from   DateRange
where  startDate > (select startDate from DateRange where status = 1)

--do your work

update dr set
       [status] = case when startDate = @nextDate then 1 else 0 end
from   DateRange dr
where  [status] != case when startDate = @nextDate then 1 else 0 end

So this code will NOT be called concurrently, right?

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 13:12:45

This status change should happen on the current row and next row only, and only once for every time this SP is called.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 13:16:33

Thank you very much TG.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 13:31:59

The code I posted assumes that all the rows will be status=0 except for the "current" row which will be status=1. Each time you call the code the next row (from the current one) will be the one row with status=1. If that is not the way it works and you have other status value in there that you need to maintain how about this for the UPDATE part of the code I posted:


update dr set
       [status] = 
              case 
              when startDate = @nextDate then 1
              when [status] = 1 then 0
              end
from   DateRange dr
where  [status] = 1
or     startDate = @nextDate

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-08 : 13:45:14

Yes TG, i am maintaining other status value. I changed your code a little. 0 for already used. 1 for next to be used and 2 for yet to be used.

I got the point you are making.

Thank you very much.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 13:48:16

Great - you're welcome

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources