functions20316

USE TSQL2012

/*1. Napisać funkcję, która zwróci dane zamówień klientów o wartości od @from do @to*/
IF EXISTS (SELECT * FROM sys.objects WHERE name= 'OrdersFromTO' AND TYPE='IF')
    DROP FUNCTION [dbo].[OrdersFromTo]
GO


CREATE FUNCTION OrdersFromTo (
    @FROM INT = 0,
    @TO INT = 0
)
RETURNS TABLE
AS
RETURN
    SELECT
        oc.custid,
        oc.companyname,
        o.orderid,
        COUNT(*) AS countorders,
        SUM(od.qty * od.unitprice) AS priceorders
    FROM Sales.OrderDetails od
        INNER JOIN Sales.Orders o ON od.orderid = o.orderid
        INNER JOIN Sales.Customers oc ON o.custid = oc.custid
    GROUP BY
        oc.companyname, o.orderid, oc.custid
    HAVING
        SUM(od.qty * od.unitprice) >= @FROM AND SUM(od.qty * od.unitprice) <= @TO
;
GO

SELECT * FROM dbo.OrdersFromTo(0,100) ORDER BY custid
SELECT companyname, SUM(countorders) AS liczbazam, SUM(priceorders) AS sumazam FROM dbo.OrdersFromTo(0,100) GROUP BY companyname ORDER BY companyname

/*Comentarz. Funkcja zwraca companyname oraz liczbę zamówień, numer zamówienia, wartości sumy zamówiemia których jest w podanym przez użytkownika zakresie*/


/*2. Wykorzystując funkcję z punktu 1 ustalić w jakim zakresie wartości (analizować przedziały o szerokości 100) jest największa liczba zamówień. Zrobić podobne zestawienie dla wszystkich klientów.*/
DECLARE @idcomp INT = 1
WHILE @idcomp <= 91
    BEGIN
        DECLARE @from1 INT = 0, @to1 INT = 100, @RESULT INT, @result1 INT = 0, @fromres INT = 0, @tores INT = 100
        WHILE @to1 <= 9000
            BEGIN
                SET @RESULT = (SELECT SUM(countorders) AS liczbazam FROM dbo.OrdersFromTo(@from1,@to1) WHERE custid = @idcomp GROUP BY companyname)
                IF @RESULT > @result1
                    BEGIN
                        SET @result1 = @RESULT
                        SET @fromres = @from1
                        SET @tores = @to1
                    END

                SET @from1 = @from1 + 100
                SET @to1 = @to1 + 100
            END
        SELECT Sales.Customers.companyname, @result1 AS maxzam, @fromres AS zakresod, @tores AS zakresdo FROM Sales.Customers WHERE custid = @idcomp
        SET @idcomp = @idcomp + 1
    END


/*3. Napisać funkcję, która dla danego dostawcy @supplierid zwraca informację o średniej liczbie dni (typ decimal(19,2) ), które upłynęły od zamówienia (orderdate) do dostawy (shipperdate) dla wszystkich zrealizowanych zamówień z danego miesiąca @month (np. listopad 2006)*/
IF EXISTS (SELECT * FROM sys.objects WHERE name= 'AmountOfDays' AND TYPE='IF')
    DROP FUNCTION [dbo].[AmountOfDays]
GO

CREATE FUNCTION AmountOfDays (
    @supplierid INT,
    --@days decimal(19,2),
    @monthzr INT,
    @yearzr INT
)
RETURNS TABLE
AS
RETURN
    SELECT
        CAST(DAY(so.shippeddate-so.orderdate) AS DECIMAL(19,2)) AS dayys
    FROM Sales.Orders so
    --INNER JOIN Sales.Orders so ON so.shipperid = ss.shipperid
    WHERE @supplierid = so.shipperid AND @monthzr = MONTH(so.shippeddate) AND @yearzr = YEAR(so.shippeddate)
    GROUP BY so.shipperid, so.shippeddate, so.orderdate
;
GO

SELECT AVG(dayys) AS avgdays FROM dbo.AmountOfDays(3,8,2007)

/*Comentarz. Próbowałem zrobić w funkcji AVG(CAST(DAY(so.shippeddate-so.orderdate) as decimal(19,2))), ale fukcja 'avg' nie działała*/


/*4. Wykorzystując funkcję z punktu 3 znaleźć dla każdego z dostawców miesiąc, w którym średni czas realizacji zamówień był najdłuższy*/
DECLARE @id INT = 1
WHILE @id <= 3
    BEGIN
        DECLARE @month1 INT = 7, @year1 INT = 2006, @czasmax1 DECIMAL(19,2), @czasmax2 DECIMAL(19,2) = 0
        WHILE @month1 <=12 AND @year1 <=2008
            BEGIN
                SET @czasmax1 = (SELECT AVG(dayys) AS avgdays FROM dbo.AmountOfDays(@id,@month1,@year1))
                IF @czasmax2 < @czasmax1
                    BEGIN
                        SET @czasmax2 = @czasmax1
                    END

                IF @month1 = 12
                    BEGIN
                        SET @year1 = @year1 + 1
                    END
                SET @month1 = @month1 + 1
            END
        SELECT companyname, @czasmax2 AS avgmax FROM Sales.Shippers WHERE shipperid = @id
        SET @id = @id + 1
    END