help3

ALTER PROCEDURE lol(@rok_od int ,@rok_do int)
as
begin
declare @rok int
declare @den varchar(10)
declare @sum int

declare list cursor for
select * from
    (SELECT  DATENAME(YEAR,n.den) as rok, DATENAME(WEEKDAY,n.den) as den,sum(n.cena) as suma  from test.Nakup n
    WHERE DATENAME(YEAR,n.den) BETWEEN @rok_od And @rok_do
    group by DATENAME(YEAR,n.den),DATENAME(WEEKDAY,n.den)) as roky_sum
where suma IN(
SELECT MAX(suma) from
(SELECT  DATENAME(YEAR,n.den) as rok, DATENAME(WEEKDAY,n.den) as den,sum(n.cena) as suma  from test.Nakup n
    WHERE DATENAME(YEAR,n.den) BETWEEN @rok_od And @rok_do
    group by DATENAME(YEAR,n.den),DATENAME(WEEKDAY,n.den)) as roky_sum
    group by rok
)
order by rok

fetch next from list into @rok,@den,@sum
WHILE @@FETCH_STATUS=0
BEGIN
    print cast (@rok as char(4))+ ': '+ cast(@den as char(10))+' '+cast(@sum as char(10))
    FETCH NEXT FROM list into @rok,@den,@sum
END
CLOSE LIST
DEALLOCATE LIST
END

execute lol 2012,2014




ALTER PROCEDURE lol2(@name varchar(30),@date_od int,@date_do int)
as
BEGIN

    DECLARE @day varchar(10)
    DECLARE @sum int
    DECLARE list CURSOR FOR
    SELECT* FROM
    (
    select Y,SUM(case when suma>0 then suma else 0 end) as suma from
    (
    select Y,suma from
    (
        SELECT DISTINCT DATENAME(weekday,nn.den) AS Y FROM test.Nakup nn
    ) as y
        LEFT JOIN (SELECT DATENAME(YEAR,n.den) as rok,DATENAME(WEEKDAY,n.den) as den ,sum(n.cena)  as suma
        FROM test.Nakup n join test.Zakaznik z on z.zID=n.zID
        WHERE z.jmeno = @name and DATENAME(YEAR,n.den) between @date_od and @date_do
        group by DATENAME(year,n.den),datename(weekday,n.den)) o on o.den=y.Y
    )t
group by Y
)K
ORDER BY CASE WHEN Y='Monday' THEN 1
                WHEN Y='Tuesday' THEN 2
                WHEN Y='Wednesday' THEN 3
                WHEN Y='Thursday' THEN 4
                WHEN Y='Friday' THEN 5
                WHEN Y='Saturday' THEN 6
                WHEN Y='Sunday' THEN 7
            END

OPEN LIST
FETCH NEXT FROM list into @day,@sum
WHILE @@FETCH_STATUS=0
begin
    print @day+': '+cast(@sum as varchar(10))
    FETCH NEXT from list into @day,@sum
end
close list
deallocate list
END

execute lol2 'pepik',2012,2014

select sum(n.cena) from test.nakup n join test.zakaznik z on z.zID=n.zID
--where z.jmeno='pepik'
where DATENAME(weekday,n.den)='friday'




ALTER PROCEDURE lol3(@rok_od int, @rok_do int)
as
begin
    DECLARE @cena int
    DECLARE @pohlavi varchar(4)
    DECLARE @rok_max int
    DECLARE @rok_min int

    set @rok_max= (SELECT MAX(DATENAME(YEAR,nn.den)) from test.nakup nn)+1
    set @rok_min= (SELECT MIN(DATENAME(YEAR,nn.den)) from test.nakup nn)-1

    if @rok_do=-1 set @rok_do=@rok_max
    if @rok_od=-1 set @rok_od=@rok_min

    DECLARE list CURSOR FOR
    SELECT * FROM
        (SELECT sum(n.cena) as cena,z.pohlavi FROM test.Nakup n
        join test.Zakaznik z on z.zID=n.zID
        WHERE DATENAME(YEAR,n.den) between @rok_od and @rok_do
        group by pohlavi
        )x

    OPEN list
    FETCH NEXT FROM list INTO @cena, @pohlavi
    WHILE @@FETCH_STATUS =0
    begin
        if @rok_do=@rok_max AND @rok_od=@rok_min
        begin
            if  @pohlavi='zena'
                begin
                     print 'zeny '+'nakoupily za: '+cast(@cena as varchar (10))
                end
                if @pohlavi='muz'
                begin
                     print 'muzi '+'nakoupili za: '+cast(@cena as varchar(10))
                end
        end

        if @rok_do =@rok_max AND @rok_od !=@rok_min
        begin
            if  @pohlavi='zena'
            begin
                 print 'zeny od roku '+cast (@rok_od as varchar(4))
                 +' nakoupily za: '+cast(@cena as varchar (10))
            end
            if @pohlavi='muz'
            begin
                 print 'muzi od roku '++cast (@rok_od as varchar(4))
                 +' nakoupili za: '+cast(@cena as varchar(10))
            end
        end

        if @rok_od=@rok_min AND @rok_do !=@rok_max
        begin
            if  @pohlavi='zena'
            begin
                 print 'zeny do roku '+cast (@rok_do as varchar(4))
                 +' nakoupily za: '+cast(@cena as varchar (10))
            end
            if @pohlavi='muz'
            begin
                 print 'muzi do roku '++cast (@rok_do as varchar(4))
                 +' nakoupili za: '+cast(@cena as varchar(10))
            end
        end

        if @rok_od!=@rok_min AND @rok_do !=@rok_max
        begin
            if @pohlavi='zena'
            begin
                 print 'zeny mezi roky '+cast (@rok_od as varchar(4))+' a '+cast(@rok_do as varchar(4))
                 +' nakoupily za: '+cast(@cena as varchar (10))
            end
            if @pohlavi='muz'
            begin
                print 'muzi mezi roky '++cast (@rok_od as varchar(4))+' a '+cast(@rok_do as varchar(4))
                +' nakoupili za: '+cast(@cena as varchar(10))
            end
        end

        FETCH NEXT FROM list into @cena,@pohlavi
    end
close list
deallocate list
END

execute lol3 2012,2014