Solved

How to build date ...

Posted on 2013-02-04
8
226 Views
Last Modified: 2013-02-14
Hi!

Have a sql that lock like this:

Declare
@StartDate datetime,
@EndDate datetime,
@Date datetime,
@SQL varchar(2000),
@version_id varchar(50),
@MyCursor CURSOR,
@@perioden varchar(4),
@moneden varchar(2),
@aaret1 varchar(2),
@aaret2 int,
@moneden2 int

Set @StartDate = '2012/10/01'
Set @EndDate = '2015/12/31'
Set @Date = @StartDate


SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select version_id From dbo.DIM_Prognose

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @version_id
                                           
WHILE @@FETCH_STATUS = 0              
BEGIN
    
   
  -- Hent ut uke+år fra perioden -> setts i 2 felter
  SET @perioden = SUBSTRING (@version_id ,3 , 4)
  SET @moneden = SUBSTRING (@perioden ,1 , 2)
  SET @aaret1 = SUBSTRING (@perioden,3,2)
  SET @aaret2 = @CAST(@aaret1 AS INT)
  SET @moneden2 = @CAST(@moneden1 AS INT)
   
  Set @Startdate = ?
  Set @Endate = ? 
  Set @Date = @StartDate
     
-- Loop through dates
  WHILE @Date <=@EndDate
  BEGIN
 
    -- Insert record in dimension table
    INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'hgno' 
    )

    INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'hgse' 
    )

    INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'hgdk' 
    )

    INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'hgfi' 
    )

   INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'vvv' 
    )

-- Goto next day
   Set @Date = @Date + 7
  END
   FETCH NEXT FROM @MyCursor
   INTO @version_id
   Set @Date = @StartDate
END

Open in new window



My problem is how to set the @Date -> @startdate and @EndDate
regarding to @perioden that get this values:

PU0212
PU0612
PU4412
PU0313

Example : PU0212 , where 02 = month and 12 is the year

The dates i want from this is 16 weeks in time
so the result from my example wil be:

0212
0312
0412
0512
0612
0712
0812
0912
..
..
1812

But the stardate must be converted to date, like -> 2012-02-01 -> 01 is the first day of week 02...

Last date -> 1812 -> 2012-18-02 -> 02 is the first day of week 18

How can i do this ?
0
Comment
Question by:team2005
  • 5
  • 3
8 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
you aren't making this clear

PU0212
PU0612
PU4412
PU0313

Example : PU0212 , where 02 = month and 12 is the year


so what is the 44 in pu4412?

its also not clear what you mean by this as the results....
0212
0312
0412
0512
0612
0712
0812
0912
..
..
1812

But the stardate must be converted to date, like -> 2012-02-01 -> 01 is the first day of week 02...

Last date -> 1812 -> 2012-18-02 -> 02 is the first day of week 18


and how 1812 becomes 2012-18-02  ...

please restate your problem....
and be clear about what date formats you are attempting to use  e.g iso, american, european,arabic

it may help if you provide some insight/example on what the actual "business" scenario  you are attempting to solve actually is...
0
 
LVL 2

Author Comment

by:team2005
Comment Utility
Hi!

Try to explane this:

Have a customer that have a forcast modell, and have periods like PU0212 or PU4412...

I must based on this values, insert 16 records on every PUxxxx

PU4412 -> Week = 44 and year =2012

So i must insert 16 records on every PUxxxx

Dates here wil be -> 0212 -> record 1      0312 -> record 2
0412 -> record 3    0512 -> record 4 e.t.c.....
counted 16 weeks .. the final record for PU0212 will be -> 1812 -> 2012-18-?

If i have PU5212 it must check if week >=52, and start from 01 if total of weeks is 52>

PU5212 gives this dates :

5212 - 0113 - 0213 ...... 1513    -> 16 weeks

Hope this explane this better
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
somewhat ... ok so the first two numbers are a week number not month...

so now you need to tell us which defintion of week you are wanting to use (e.g. iso or microsoft)  ... your week 52 example also doesn't really address what happens (how you want to deal with) the 53rd week of every year (each year has 52 full weeks plus 1 or 2 additional days), and if there are any special considerations for leap years...

and then which day do you wish to consider as the start/end day of a week...

and i still find this ambiguous
Last date -> 1812 -> 2012-18-02 -> 02 is the first day of week 18
what "date" representation is 2012-18-02   yyyy-dd-mm ? or is it your own internal usage  yyyy-wk-dy?
0
 
LVL 2

Author Comment

by:team2005
Comment Utility
Hi!

Valid range of weeks are -> 1...52

So if week goes from 52 to 01, the year must increment by one.

Start day of the week -> the first day
End day of the week -> last day of the week
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
ok lets try again

pu0113  is week 1 year 2013

so when does that week start?
 01 Jan 2013 ie Tuesday   ending 06 jan 2013 ie Monday
    all other weeks in 2013 starting on Tuesdays and ending on Mondays

and  pu5213 week 52 year 2013
starts tuesday 24th december and ends monday 30th december
tuesday 31 december 2013 isn't part of any week...
because pu0114 week 1 year 2014
starts wednesday 1 january and ends tuesday 6 january ....
0
 
LVL 2

Author Comment

by:team2005
Comment Utility
I have solved this myself
0
 
LVL 2

Author Comment

by:team2005
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for team2005's comment #a38888415

for the following reason:

solved by myself
0
 
LVL 2

Author Closing Comment

by:team2005
Comment Utility
give you some points, for trying to help me
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now