Запросы в SQL для получения общей выручки

Пример запроса:

Пример ответа:
CREATE OR ALTER PROCEDURE dbo.RK7SQLPROCGETRESTAURANTS
AS
BEGIN
SET NOCOUNT ON
SELECT R."SIFR", R."GUIDSTRING", R."CODE", R."NAME"
  FROM "RESTAURANTS" R
 WHERE R."STATUS" = 3;
END

Пример запроса:

Пример ответа:
CREATE OR ALTER PROCEDURE dbo.RK7SQLPROCGETCLASSIFICATIONS
AS
BEGIN
SET NOCOUNT ON
SELECT CG."IDENT" AS CLASSIFICATION_ID, CG."NAME" AS CLASSIFICATION_NAME, CG1."IDENT" AS GROUP_ID, CG1."NAME" AS GROUP_NAME
  FROM CLASSIFICATORGROUPS CG
    LEFT JOIN CLASSIFICATORGROUPS CG1 ON (CG1."PARENT" = CG."IDENT" AND CG1."STATUS" = 3)
 WHERE CG."PARENT" = 0 AND CG."STATUS" = 3;
END

Пример запроса:

Пример ответа:
IF OBJECT_ID(N'dbo.RK7SQLPROCGETREVENUE', N'P') IS NOT NULL
    DROP PROCEDURE dbo.RK7SQLPROCGETREVENUE;
GO 

CREATE PROCEDURE dbo.RK7SQLPROCGETREVENUE
(
    @STARTDATE NVARCHAR(50),
    @ENDDATE NVARCHAR(50),
    @CLASSIFICATORGROUPID INT
)
AS
BEGIN

SET NOCOUNT ON; -- Добавил точку с запятой, хорошая практика

DECLARE @START_DATE DATETIME = CONVERT(DATETIME, @STARTDATE, 112);
DECLARE @END_DATE DATETIME = DATEADD(day, 1, CONVERT(DATETIME, @ENDDATE, 112));

DECLARE @CLASSIFICATOR_GROUP_ID INT = CASE
  WHEN @CLASSIFICATORGROUPID = 0 THEN (SELECT "SHORTVALUE" FROM "PARAMETERS" WHERE "SIFR" = 6)
  ELSE @CLASSIFICATORGROUPID
END;

WITH "CATEGORY_SUMS" as (
  SELECT GS1."SHIFTNUM" AS "SHIFT_NUM",
      GS1."IRESTAURANT" AS "RESTAURANT_ID",
    C1."SIFR" AS "CURRENCY_ID",
    C1."NAME" AS "CURRENCY_NAME",
    ISNULL(CLG1."IDENT", 0) AS "CATEGORY_ID",
      sum(PB1."PAYSUM") AS "PAYSUM"
    FROM "PAYBINDINGS" PB1
-- выручка
    JOIN "CURRLINES" CL1
      ON (CL1."VISIT" = PB1."VISIT") AND (CL1."MIDSERVER" = PB1."MIDSERVER") AND (CL1."UNI" = PB1."CURRUNI")
    JOIN "PRINTCHECKS" PC1
      ON (PC1."VISIT" = CL1."VISIT") AND (PC1."MIDSERVER" = CL1."MIDSERVER") AND (PC1."UNI" = CL1."CHECKUNI")
-- общая смена
    JOIN "ORDERS" O1
      ON (O1."VISIT" = PB1."VISIT") AND (O1."MIDSERVER" = PB1."MIDSERVER") AND (O1."IDENTINVISIT" = PB1."ORDERIDENT")
    JOIN "GLOBALSHIFTS" GS1
      ON (GS1."MIDSERVER" = O1."MIDSERVER") AND (GS1."SHIFTNUM" = O1."ICOMMONSHIFT")
-- блюда и категория блюд
    LEFT JOIN "SESSIONDISHES" SD1
      ON (SD1."VISIT" = PB1."VISIT") AND (SD1."MIDSERVER" = PB1."MIDSERVER") AND (SD1."UNI" = PB1."DISHUNI")
    LEFT JOIN "MENUITEMS" MI1
      ON (MI1."SIFR" = SD1."SIFR")
    LEFT JOIN DISHGROUPS DG1
      ON (DG1."CHILD" = MI1."SIFR") AND (DG1."Classification" = @CLASSIFICATOR_GROUP_ID)
    LEFT JOIN "CLASSIFICATORGROUPS" CLG1
      ON CLG1."IDENT" = DG1."PARENT"  
-- ресторан
    LEFT JOIN "CASHGROUPS" CG1
      ON (CG1."SIFR" = PB1."MIDSERVER")
    LEFT JOIN "RESTAURANTS" R1
     ON (R1."SIFR" = CG1."RESTAURANT")
-- валюта
    LEFT JOIN "PAYMENTS" P1
      ON (P1."VISIT" = CL1."VISIT") AND (P1."MIDSERVER" = CL1."MIDSERVER") AND (P1."UNI" = CL1."PAYUNIFOROWNERINFO")
    LEFT JOIN "CURRENCIES" C1
      ON (C1."SIFR" = P1."SIFR")
  WHERE GS1."STATUS" = 3 AND GS1."SHIFTDATE" BETWEEN @START_DATE AND @END_DATE
    GROUP BY GS1."SHIFTNUM", GS1."IRESTAURANT", CLG1."IDENT", C1."SIFR", C1."NAME"
)
SELECT
  GS."SHIFTDATE" AS "SHIFT_DATE",
  GS."SHIFTNUM" AS "SHIFT_NUM",
  GS."IRESTAURANT" AS "RESTAURANT_ID",
  R."NAME" AS "RESTAURANT_NAME",
  CASE 
    WHEN M."CATEGORY_ID" = 0 THEN 'Без категории'
  ELSE CLG."NAME" 
  END AS "CATEGORY_NAME",
  M."CURRENCY_ID",
  M."CURRENCY_NAME",
  ISNULL(M."PAYSUM", 0) AS "PAYSUM"
-- общая смена
FROM "GLOBALSHIFTS" GS
-- ресторан
  JOIN "RESTAURANTS" R ON (R."SIFR" = GS."IRESTAURANT")
-- категория блюд
  JOIN (
    SELECT CLG1."IDENT", CLG1."NAME", NULL as "SHIFTNUM"
      FROM "CLASSIFICATORGROUPS" CLG1
     WHERE CLG1."PARENT" = @CLASSIFICATOR_GROUP_ID
    UNION ALL
-- если в продажах за смену есть блюда без указания группы в выбранной категории, то добавляем в вывод строку с пустой категорией
    SELECT 0, '', CS."SHIFT_NUM" 
      FROM "CATEGORY_SUMS" CS 
     WHERE CS."CATEGORY_ID" = 0
  ) CLG ON (CLG."SHIFTNUM" IS NULL OR CLG."SHIFTNUM" = GS."SHIFTNUM")
-- выручка и валюта
  LEFT JOIN "CATEGORY_SUMS" M ON M."SHIFT_NUM" = GS."SHIFTNUM" AND M."CATEGORY_ID" = CLG."IDENT"
WHERE GS."STATUS" = 3 AND GS."SHIFTDATE" BETWEEN @START_DATE AND @END_DATE
order by GS."SHIFTDATE", 
    GS."SHIFTNUM", 
    GS."IRESTAURANT",
    CASE 
      WHEN M."CATEGORY_ID" = 0 THEN 1
    ELSE 0
    END, 
    CLG."NAME"
;

END