Solved

How to build date ...

Posted on 2013-02-04
8
234 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
ID: 38853118
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
ID: 38853903
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
ID: 38854429
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 2

Author Comment

by:team2005
ID: 38854575
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38855280
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
ID: 38888415
I have solved this myself
0
 
LVL 2

Author Comment

by:team2005
ID: 38888418
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
ID: 38888419
give you some points, for trying to help me
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

778 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