Próbuję uzyskać ogólną sumę totalsprice, ale rzuca błąd:

kolumna 'facices

ALTER PROCEDURE [dbo].[SalesReport] 

        @FromDate date= '11-May-2018',
        @ToDate date= '05-Jun-2018'

AS
BEGIN
        Set @ToDate= case when @ToDate IS NULL then Convert(varchar(11), getdate(), 106) else @ToDate end

        Select ROW_NUMBER() over (partition by b.BookingID order by b.BookingID) as ID, inv.InvoiceNo, 
        convert(varchar(11),inv.EntryDateTime,106) as EntryDateTime, Count(s.ServiceID) as TotalServices,
        SUM(inv.ServicePrice) as TotalPrice, SUM(inv.ServicePrice) over() as TotalRevenue
        from Invoices inv
        Inner Join Bookings b
        ON b.BookingID= inv.fk_BookingID
        Inner Join ZahidCarWashDB.dbo.Services s
        ON s.ServiceID= inv.fk_ServiceID
        where Convert(varchar(11), inv.EntryDateTime, 106) between @FromDate and @ToDate
        group by convert(varchar(11),inv.EntryDateTime,106), inv.InvoiceNo, b.BookingID



END
0
user9677867 4 czerwiec 2018, 14:40

3 odpowiedzi

Najlepsza odpowiedź

Funkcje okna mieszania i funkcje agregacji mogą być trudne. Czego chcesz:

SUM(inv.ServicePrice) as TotalPrice,
SUM(SUM(inv.ServicePrice)) over () as TotalRevenue

Zwróć uwagę na dodatek SUM(). Wewnętrzny jest SUM() w rzędzie w zestawie wyników. Jedna zewnętrzna jest funkcja okna, która sumuje wartość na wszystkich wierszach.

5
Gordon Linoff 4 czerwiec 2018, 11:41

Problem jest mieszanie w dół okna z funkcjami łączącymi. Możesz wziąć kolejne podejście. Coś takiego:

 SELECT p.id, p.TotalPrice, p.TotalServices
        , SUM(p.TotalPrice) OVER (ORDER BY p.id) AS TotalPriceCumulative
        , SUM(p.TotalServices) OVER (ORDER BY P.id) AS TotalServicesCumulative
 FROM (
 SELECT b.BookingID AS ID -- or row_number() over (order By b.BookingID) AS ID
        , inv.TotalPrice, inv.TotalServices
 FROM Bookings AS b 
 INNER JOIN (SELECT inv1.fk_BookingID, SUM(inv.ServicePrice) as TotalPrice, Count(s.ServiceID) as TotalServices
                FROM Invoices AS inv1
                -- works only if one invoice is released for every invoice
                INNER Join ZahidCarWashDB.dbo.Services s ON s.ServiceID= inv1.fk_ServiceID
                where Convert(varchar(11), inv.EntryDateTime, 106) between @FromDate and @ToDate
             ) AS inv ON b.BookingID= inv.fk_BookingID
 ) AS p
0
Rade Vojvodic 4 czerwiec 2018, 12:14

Myślę, że Gordon ma naprawić swój błąd.
Nie należy konwertować daty do Varchar i porównania.

    Set @ToDate = isnull(@ToDate, getdate());

    Select  ROW_NUMBER() over (partition by b.BookingID order by b.BookingID) as ID, 
            inv.InvoiceNo, 
            convert(varchar(11), inv.EntryDateTime, 106) as EntryDateTime, 
            Count(s.ServiceID)    as TotalServices,
            SUM(inv.ServicePrice) as TotalPrice, 
            SUM(inv.ServicePrice) over() as TotalRevenue
    from Invoices inv
    Inner Join Bookings b
       ON b.BookingID = inv.fk_BookingID
    Inner Join ZahidCarWashDB.dbo.Services s
       ON s.ServiceID = inv.fk_ServiceID
    where inv.EntryDateTime between @FromDate and @ToDate
    group by cast(inv.EntryDateTime as date)
           , inv.InvoiceNo
           , b.BookingID
0
paparazzo 4 czerwiec 2018, 17:41