Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Loop inside View

    Hello,

    is it possible to build a loop for the following statement?

    Code:
    CREATE VIEW vwObjects as (
    
    Select 2001 as year, 1 as quarter, id as id
    from dbo.objects o
    where o.edate >= '20010101' and o.sdate < '20010401'
    union
    
    Select 2001 as year, 2 as quarter, id as id
    from dbo.objects o
    where o.edate >= '20010301' and o.sdate < '20010701'
    ...
    union
    
    Select 2002 as year, 1 as quarter, id as id
    from dbo.objects o
    where o.edate > '20020101' and o.sdate < '20020401'
    ...
    )
    I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.

    Thank you!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Huh?

    YEAR(edate), MONTH(edate)

    What are you trying to do?

    And what's with LOOP? I don't see no loop
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2007
    Posts
    97
    Oh,sorry. I have one Table for the objects. Every object as a startdate and an enddate. For my cube, I need kind of dimension, so the user can pick a quarter and he will get the sum of all active objects. I tried several ways to realize this.

    My idea is to create of view, that looks like:

    Code:
    year   quarter   id
    2001   1           1
    2001   1           2
    2001   1           3
    2001   2           2
    2001   2           4
    From objects table:
    Code:
    id   startdate   enddate
    1    2001/05/01   2001/13/02
    2    2001/25/02   2001/03/04 
    3    2001/03/01   2001/5/01 
    4    2001/09/05   2001/22/05
    I hope it's more more understandable now.

  4. #4
    Join Date
    Mar 2007
    Posts
    97
    Ok, forget that, I found another way.

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by silas
    Ok, forget that, I found another way.
    Can you elaborate? Your solution may help other users in the future.

  6. #6
    Join Date
    Mar 2007
    Posts
    97
    I couldn't solve this. Even if I could, this will be very slow for big tables. I will have to do a little work off on my design and then I will try this loop with a INSERT INTO, not a view. Greets, Silas
    Last edited by silas; 04-05-07 at 07:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •