ErikIvanov

Untitled

Sep 22nd, 2021
720
12 hours
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH CTE AS
  2.   (
  3.     SELECT ROW_NUMBER() OVER(ORDER BY main.mainCode) Id, main.mainCode, main.FilialId, main.RegionId, main.PriceCode, main.BegDate, main.EndDate, main.UnitType
  4.            , main.Value, ROW_NUMBER() OVER(PARTITION BY main.mainCode ORDER BY main.Priority DESC) Nrank
  5.     FROM
  6.       (
  7.         SELECT val.id, rest.code MainCode, rest.FilialId, rest.RegionId, CAST(rest.PriceCode as NVARCHAR) PriceCode, rest.BegDate, rest.EndDate, rType.Name UnitType, val.Value
  8.               , cType.Priority * gLevel.Priority Priority
  9.         FROM [restrict] rest
  10.           INNER JOIN restrictGeoLevel gLevel ON gLevel.Id = rest.GeoLevelId
  11.           INNER JOIN restrictValue val ON val.RestrictId = rest.Id
  12.           INNER JOIN restrictUnitType rType ON rType.id = val.RestrictUnitTypeId
  13.           INNER JOIN codeType cType ON cType.Id = rest.CodeTypeId
  14.           INNER JOIN cnGoods good ON good.Code = rest.Code
  15.         WHERE rest.CodeTypeId = 1
  16.           AND rest.IsActive = 1
  17.           AND CAST(GETDATE()-3 AS DATE) BETWEEN DATEADD(DAY, -3, rest.BegDate) AND rest.EndDate
  18.         /* Good*/ UNION ALL
  19.         SELECT val.id, good.code MainCode, rest.FilialId, rest.RegionId, CAST(rest.PriceCode as NVARCHAR), rest.BegDate, rest.EndDate, rType.Name UnitType, val.Value
  20.                , cType.Priority * gLevel.Priority Priority
  21.         FROM   [restrict] rest
  22.           INNER JOIN restrictGeoLevel gLevel ON gLevel.Id = rest.GeoLevelId
  23.           INNER JOIN restrictValue val ON val.RestrictId = rest.Id
  24.           INNER JOIN restrictUnitType rType ON rType.id = val.RestrictUnitTypeId
  25.           INNER JOIN codeType cType ON cType.Id = rest.CodeTypeId
  26.           INNER JOIN GoodContract contr ON contr.Code = rest.code
  27.           INNER JOIN cnGoods good ON good.ContractRef = contr.Ref
  28.         WHERE rest.CodeTypeId = 2
  29.           AND rest.IsActive = 1
  30.           AND CAST(GETDATE()-3 AS DATE) BETWEEN DATEADD(DAY, -3, rest.BegDate) AND rest.EndDate
  31.         /* Contract*/ UNION ALL
  32.         SELECT val.id, good.code MainCode, rest.FilialId, rest.RegionId, CAST(rest.PriceCode as NVARCHAR), rest.BegDate, rest.EndDate, rType.Name UnitType, val.Value
  33.                , cType.Priority * gLevel.Priority Priority
  34.         FROM [restrict] rest
  35.           INNER JOIN restrictGeoLevel gLevel ON gLevel.Id = rest.GeoLevelId
  36.           INNER JOIN restrictValue val ON val.RestrictId = rest.Id
  37.           INNER JOIN restrictUnitType rType ON rType.id = val.RestrictUnitTypeId
  38.           INNER JOIN codeType cType ON cType.Id = rest.CodeTypeId
  39.           INNER JOIN GoodType goodType ON goodType.Code = rest.code
  40.           INNER JOIN cnGoods good ON good.GoodTypeRef = goodType.Ref
  41.         WHERE rest.CodeTypeId = 3
  42.           AND rest.IsActive = 1
  43.           AND CAST(GETDATE()-3 AS DATE) BETWEEN DATEADD(DAY, -3, rest.BegDate) AND rest.EndDate
  44.       /* Global*/ UNION ALL
  45.         SELECT val.id, good.code MainCode, rest.FilialId, rest.RegionId, CAST(rest.PriceCode as NVARCHAR), rest.BegDate, rest.EndDate, rType.Name UnitType, val.Value
  46.                , cType.Priority * gLevel.Priority Priority
  47.         FROM [restrict] rest
  48.           INNER JOIN restrictGeoLevel gLevel ON gLevel.Id = rest.GeoLevelId
  49.           INNER JOIN restrictValue val ON val.RestrictId = rest.Id
  50.           INNER JOIN restrictUnitType rType ON rType.id = val.RestrictUnitTypeId
  51.           INNER JOIN codeType cType ON cType.Id = rest.CodeTypeId
  52.           CROSS JOIN cnGoods good
  53.         WHERE rest.CodeTypeId = 5
  54.           AND rest.IsActive = 1
  55.           AND CAST(GETDATE()-3 AS DATE) BETWEEN DATEADD(DAY, -3, rest.BegDate) AND rest.EndDate
  56.       ) main
  57.   )
  58. SELECT Id, cte.mainCode, cte.FilialId, cte.RegionId, cte.PriceCode, cte.BegDate, cte.EndDate, cte.UnitType, cte.Value
  59. FROM cte
  60. WHERE Nrank = 1
  61.  
  62.  
  63.  
RAW Paste Data