salut, am si eu o intrebare....unde e gresit scriptul asta

--alter procedure spr_gprListaPerioade
declare @datainit varchar(10),
 @datasf varchar(10)

set @datainit = '12/03/2007'
set @datasf = '05/08/2007'

--as
 begin
   declare @perioade table (nrrand int, an int, descr varchar(50), begindate varchar(50), enddate varchar(50))
   declare @nrluni int
   declare @nr int, @anbisect int, @an int, @luna int
   declare @nr_cursor int, @an_cursor int, @descr_cursor varchar(50), @start_cursor varchar(50), @stop_cursor varchar(50)
  
   set @nr=0;
   set @an=cast(substring(@datasf,7,4)as int)
   set @nrluni=cast(substring(@datasf,4,2) as
int)-cast(substring(@datainit,4,2) as int)+1
   if ( (cast(substring(@datasf,7,4) as int) % 4) = 0)
 set @anbisect=29
   else
 set @anbisect=28
   set @luna=cast(substring(@datainit,4,2) as int)-1
  
   declare tabel_perioade cursor for
    select @nr+1,@an,@luna+1, case @luna+1
                           when 1 then 'Ianuarie'
                           when 2 then 'Februarie'
                          when 3 then 'Martie'
                          when 4 then 'Aprilie'
                           when 5 then 'Mai'
                         when 6 then 'Iunie'
                         when 7 then 'Iulie'
                        when 8 then 'August'
                        when 9 then 'Septembrie'
                        when 10 then 'Octombrie'
                       when 11 then 'Noiembrie'
                     when 12 then 'Decembrie'
                     end, 
       case @luna+1
          when cast(substring(@datainit,4,2) as int) then @datainit
          else  '01/'+ cast(@luna as varchar(2))+'/'+cast(@an as varchar(4))
       end,
       case @luna+1
   when cast(substring(@datasf,4,2) as int) then @datasf
          when 1 then '31/01/'+cast(@an as varchar(4))
          when 2 then cast(@anbisect as varchar(2))+'/02/'+cast(@an as
varchar(4))
   when 3 then '31/03/'+cast(@an as varchar(4))
   when 4 then '30/04/'+cast(@an as varchar(4))
    when 5 then '31/05/'+cast(@an as varchar(4))
   when 6 then '30/06/'+cast(@an as varchar(4))
   when 7 then '31/07/'+cast(@an as varchar(4))
   when 8 then '31/08/'+cast(@an as varchar(4))
   when 9 then '30/09/'+cast(@an as varchar(4))
          when 10 then '31/10/'+cast(@an as varchar(4))
   when 11 then '30/11/'+cast(@an as varchar(4))
   when 12 then '31/12/'+cast(@an as varchar(4))
      end

   open tabel_perioade
   fetch next from tabel_perioade into @nr, @an_cursor,@luna, @descr_cursor, @start_cursor, @stop_cursor

   while @@fetch_status = 0
   begin
        if(@nr < (@nrluni+1) )
         begin
  insert into @perioade(nrrand, an, descr, begindate, enddate)
         select @nr_cursor, @an_cursor, @descr_cursor, @start_cursor, @stop_cursor
         fetch next from tabel_perioade into @nr, @an_cursor,@luna, @descr_cursor, @start_cursor, @stop_cursor
 end
   end

   close tabel_perioade
   deallocate tabel_perioade
   
   select * from @perioade order by nrrand

 end

el acum imi intoarce

NULL 2007 Martie 12/03/2007 31/03/2007

eu as vrea sa-mi intoarca

1    2007   Martie  12/03/2007     31/03/2007

2     2007   Aprilie   01/04/2007    30/04/2007

3    2007    Mai     01/05/2007      31/05/2007

.......

 6   2007   August  01/08/2007     05/08/2007

 

Multumesc mult.

Cred ca problema ar fi la felul cum se face numerotarea liniilor (despre asta s-a mai vorbit aici) la

" declare tabel_perioade cursor for
    select @nr+1,@ ..."

---

Gheorghe Ciubuc,SQL Server Influencer, MCP(SQL 2000), MCTS (SQL Server 2005) , OCA(Oracle 9i), Sybase(Brainbench)

Adica pe site-ul nostru aici : http://sqlserver.ro/forums/thread/2081.aspx

---

Gheorghe Ciubuc,SQL Server Influencer, MCP(SQL 2000), MCTS (SQL Server 2005) , OCA(Oracle 9i), Sybase(Brainbench)

problema nu era de numerotarea liniilor neaparat ci de ce intoarce selectul respectiv

am rezolvat astfel.....am renuntat la cursor si folosesc o tabela temporara astfel

--alter procedure spr_gprListaPerioade

declare @datainit varchar(10),

@datasf varchar(10)

set @datainit = '12/03/2007'

set @datasf = '05/08/2007'

--as

begin

declare @perioade2 table (nr_rand int, an int,descr varchar(50), begindate varchar(50), enddate varchar(50))

declare @nrluni int

declare @nr int, @anbisect int, @an int, @luna int

declare @nr_cursor int, @an_cursor int, @descr_cursor varchar(50), @start_cursor varchar(50), @stop_cursor varchar(50)

 

set @nr=1;

set @an=cast(substring(@datainit,7,4)as int)

if ( (cast(substring(@datasf,7,4) as int) % 4) = 0)

set @anbisect=29

else

set @anbisect=28

set @luna=cast(substring(@datainit,4,2) as int)

while (@an<=cast(substring(@datasf,7,4)as int) and @luna<=cast(substring(@datasf,4,2) as int)) -- ) or (@an=cast(substring(@datasf,7,4)as int)

begin

insert into @perioade2(nr_rand, an, descr, begindate, enddate)

select @nr,@an, case @luna

when 1 then 'Ianuarie'

when 2 then 'Februarie'

when 3 then 'Martie'

when 4 then 'Aprilie'

when 5 then 'Mai'

when 6 then 'Iunie'

when 7 then 'Iulie'

when 8 then 'August'

when 9 then 'Septembrie'

when 10 then 'Octombrie'

when 11 then 'Noiembrie'

when 12 then 'Decembrie'

end,

case

when (@luna=cast(substring(@datainit,4,2) as int) and @an=cast(substring(@datainit,7,4)as int)) then @datainit

else '01/'+ (case when @luna<10 then '0' else '' end) + cast(@luna as varchar(2))+'/'+cast(@an as varchar(4))

end,

case

when (@luna=cast(substring(@datasf,4,2) as int) and @an=cast(substring(@datasf,7,4)as int)) then @datasf

when @luna=1 then '31/01/'+cast(@an as varchar(4))

when @luna=2 then cast(@anbisect as varchar(2))+'/02/'+cast(@an as varchar(4))

when @luna=3 then '31/03/'+cast(@an as varchar(4))

when @luna=4 then '30/04/'+cast(@an as varchar(4))

when @luna=5 then '31/05/'+cast(@an as varchar(4))

when @luna=6 then '30/06/'+cast(@an as varchar(4))

when @luna=7 then '31/07/'+cast(@an as varchar(4))

when @luna=8 then '31/08/'+cast(@an as varchar(4))

when @luna=9 then '30/09/'+cast(@an as varchar(4))

when @luna=10 then '31/10/'+cast(@an as varchar(4))

when @luna=11 then '30/11/'+cast(@an as varchar(4))

when @luna=12 then '31/12/'+cast(@an as varchar(4))

end

set @luna=@luna+1

if @luna=13 begin

set @luna=1

set @an=@an+1

end

set @nr=@nr+1

end

select * from @perioade2 order by nr_rand

end

Multumesc mult

Alex.

Ce inregistrari obtii daca valorile parametrilor sunt:

set @datainit = '05/03/2007'

set @datasf = '11/08/2008'
 

? 

Sau

set @datainit = '01/08/2007'

set @datasf = '11/02/2008'

 

Incearca:

CREATE TABLE Luna
(
Luna INT PRIMARY KEY,
Denumire NVARCHAR(25),
DataInceput NVARCHAR(6),
DataSfarsit NVARCHAR(6),
)

INSERT INTO Luna VALUES ( 1  , 'Ianuarie'    , '01/01/' , '31/01/' )
INSERT INTO Luna VALUES ( 2  , 'Februarie'    , '01/02/' , '28/02/' )
INSERT INTO Luna VALUES ( 3  , 'Martie'        , '01/03/' , '31/03/' )
INSERT INTO Luna VALUES ( 4  , 'Aprilie'    , '01/04/' , '30/04/' )
INSERT INTO Luna VALUES ( 5  , 'Mai'        , '01/05/' , '31/05/' )
INSERT INTO Luna VALUES ( 6  , 'Iunie'        , '01/06/' , '30/06/' )
INSERT INTO Luna VALUES ( 7  , 'Iulie'        , '01/07/' , '31/07/' )
INSERT INTO Luna VALUES ( 8  , 'August'        , '01/08/' , '31/08/' )
INSERT INTO Luna VALUES ( 9  , 'Septembrie'    , '01/09/' , '30/09/' )
INSERT INTO Luna VALUES ( 10 , 'Octombrie'    , '01/10/' , '31/10/' )
INSERT INTO Luna VALUES ( 11 , 'Noiembrie'    , '01/11/' , '30/11/' )
INSERT INTO Luna VALUES ( 12 , 'Decembrie'    , '01/12/' , '31/12/' )

DECLARE @dt1 DATETIME    --data inceput
DECLARE @dt2 DATETIME    --data sfarsit
SET @dt1 = '2006-12-09'    --YYYY-MM-DD
SET @dt2 = '2008-03-11'    --YYYY-MM-DD

--in mod normal validarea de mai jos ar tb. realizata de catre apelant
--IF ( @dt1 > @dt2 ) RAISERROR ('Eroare: Data 1 trebuie sa fie <= decat Data 2', 16, 1)

/*eventual schimbi intre ele valorile

IF ( @dt1 > @dt2 )

BEGIN 

    DECLARE @dt_aux DATETIME

    SET @dt_aux = @dt1

    SET @dt1 = @dt2

    SET @dt2  = @dt_aux 

END 

*/

DECLARE @rezultat TABLE
(
Luna INT,
Denumire NVARCHAR(25),
DataInceput NVARCHAR(10),
DataSfarsit NVARCHAR(10)
)

DECLARE @i INT, @data_iteratie DATETIME
SET @i = 0
SET @data_iteratie = CAST(YEAR(@dt1) AS NVARCHAR(4)) + '-' + CAST(MONTH(@dt1) AS NVARCHAR(2)) + '-1' --conversie implicita NVARCAR -> DATETIME

WHILE ( YEAR(@data_iteratie) < YEAR(@dt2) ) OR
    ( YEAR(@data_iteratie) = YEAR(@dt2) AND MONTH(@data_iteratie) <= MONTH(@dt2) )
BEGIN
    SET @i = @i + 1

    DECLARE @an INT, @bisect NCHAR(1)
    SET @an = YEAR(@data_iteratie)
    IF ( @an % 4 = 0 AND NOT (@an % 100 = 0 AND @an % 400 != 0) )
        SET @bisect = 'D'
    ELSE
        SET @bisect = 'N'

    INSERT INTO @rezultat
    SELECT @i,
        L.Denumire,
        L.DataInceput+CAST(YEAR(@data_iteratie) AS NVARCHAR(4) ),
        CASE
            WHEN MONTH(@data_iteratie) = 2 AND @bisect = 'D' THEN '29/02/'
            ELSE L.DataSfarsit
        END + CAST(YEAR(@data_iteratie) AS NVARCHAR(4) )
    FROM Luna L
    WHERE L.Luna = MONTH(@data_iteratie)

    SET @data_iteratie = DATEADD(mm,1,@data_iteratie) --trecem la luna urmatoare
END

UPDATE @rezultat
SET DataInceput = REPLACE(STR(DAY(@dt1),2),' ','0') + SUBSTRING(DataInceput,3,25)
WHERE Luna = 1

UPDATE @rezultat
SET DataSfarsit = REPLACE(STR(DAY(@dt2),2),' ','0') + SUBSTRING(DataSfarsit,3,25)
WHERE Luna = (SELECT MAX(Luna) FROM @rezultat)



SELECT *
FROM @rezultat