Solved

How to build date ...

Posted on 2013-02-04
8
237 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to check mis-spellings in a select SQL 2 38
Freeze portion of datamart 2 22
Using datetime as triggers 2 20
Need age at date of document 5 17
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

749 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