Solved

How to build date ...

Posted on 2013-02-04
8
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

627 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