|
procedura + cursor = intoarce date calendaristice.....dar nu ce mi-as dori - SQL server 2000
-
09-21-2007, 10:51 AM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
procedura + cursor = intoarce date calendaristice.....dar nu ce mi-as dori - SQL server 2000
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.
|
|
-
09-21-2007, 2:44 PM |
-
ggciubuc
-
-
-
Joined on 03-18-2006
-
Bucharest
-
sysadmin
-
-
|
Re: procedura + cursor = intoarce date calendaristice.....dar nu ce mi-as dori - SQL server 2000
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)
|
|
-
-
09-21-2007, 5:51 PM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: procedura + cursor = intoarce date calendaristice.....dar nu ce mi-as dori - SQL server 2000
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.
|
|
-
09-21-2007, 10:55 PM |
-
B_gd_n[ ]Sahlean
-
-
-
Joined on 07-17-2007
-
Bucuresti
-
sysadmin
-
-
|
Re: procedura + cursor = intoarce date calendaristice.....dar nu ce mi-as dori - SQL server 2000
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'
|
|
-
09-22-2007, 12:54 AM |
-
B_gd_n[ ]Sahlean
-
-
-
Joined on 07-17-2007
-
Bucuresti
-
sysadmin
-
-
|
Re: procedura + cursor = intoarce date calendaristice.....dar nu ce mi-as dori - SQL server 2000
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
|
|
|
|
|