Блог

У меня проблема с группировкой диапазонов. Я пытаюсь сгруппировать результат в каждой 10-й шведской кроне. Проблема в том, что PriceSEK совпадает с LowerBoundSEK и UpperBoundSEK.

Как вы видите ниже, я добавил -0.001, который на данный момент действует просто как визуальное представление желаемого результата. Он по-прежнему группируется на 0,1.

Как я могу настроить эту группировку, чтобы получить желаемый результат 0,4, подсчитанный дважды в правильном диапазоне? (0.301-0.4)

http://sqlfiddle.com /#!6/f7624/3

Тестировать SQL

 CREATE TABLE #Test (
    ID int NOT NULL IDENTITY(1,1),
    PriceEUROCent smallmoney NOT NULL,
    DateSent datetime NOT NULL,
    Quantity int NOT NULL,
    SomeID int NOT NULL,
    CurrencyID int NOT NULL
)

CREATE TABLE #Currencies (
    CurrencyID int IDENTITY(1, 1),
    CurrencyValue numeric(17,3)
)

INSERT INTO #Currencies SELECT 10 --9.617


INSERT INTO #Test
    (PriceEUROCent, DateSent, Quantity, SomeID, CurrencyID)
    VALUES
        ('2.70', '2016-09-27 11:00', 1, 1, 1),
        ('3.00', '2016-09-27 12:00', 1, 1, 1),
        ('4.0', '2016-09-27 14:00', 1, 1, 1),
        ('4.0', '2016-09-27 14:00', 1, 1, 1),
        ('6.80', '2016-09-27 12:00', 1, 1, 1),
        ('8.00', '2016-09-28 14:01', 3, 1, 1)



DECLARE @RangeWidth numeric(17,3), @Currency numeric(17,3), @RangeWidthSEK numeric(17, 3)

SET @RangeWidth = .1

SELECT 
    DT.SomeID,
    DT.LowerBoundSEK,
    DT.UpperBoundSEK,
    DT.SomeDate,
    SUM(DT.Quantity) AS Quantity,
    SUM(DT.SumPriceSEK) AS SumPriceSEK
    FROM (
        SELECT 
            (PriceEUROCent / 100) * C.CurrencyValue AS PriceSEK,
            FLOOR((PriceEUROCent / 10) * C.CurrencyValue) * @RangeWidth   0.001 AS LowerBoundSEK,
            (FLOOR((PriceEUROCent / 10) * C.CurrencyValue) * @RangeWidth)   @RangeWidth AS UpperBoundSEK,
            (FLOOR((PriceEUROCent / 10) * C.CurrencyValue) * @RangeWidth)   @RangeWidth AS SumPriceSEK,
            SomeID,
            Quantity,
            CONVERT(VARCHAR(10), DateSent, 120) AS SomeDate
            FROM #Test T
                JOIN #Currencies C ON T.CurrencyID = C.CurrencyID
    ) DT
    GROUP BY
    FLOOR(DT.PriceSEK/@RangeWidth), 
    DT.SomeDate,
    DT.LowerBoundSEK,
    DT.UpperBoundSEK,
    DT.SomeID




-- DEBUG SELECT
SELECT T.DateSent, T.Quantity, T.SomeID, C.CurrencyValue,
    (PriceEUROCent / 100) * C.CurrencyValue * T.Quantity AS PriceSEK,
    PriceEUROCent * T.Quantity AS PriceEUROCent
    FROM #Test T
    JOIN #Currencies C ON T.CurrencyID = C.CurrencyID
  

Редактировать:

Желаемый результат:

 1   0.201   0.3 2016-09-27  2   0.300
1   0.301   0.4 2016-09-27  2   0.800
1   0.601   0.7 2016-09-27  1   0.700
1   0.701   0.8 2016-09-28  3   2.400
  

Я решил это с помощью дополнительного тестирования. Самым большим изменением было CEILING и изменение JOIN на ранжированное соединение.

 CREATE TABLE #Test (
    ID int NOT NULL IDENTITY(1,1),
    PriceEUROCent smallmoney NOT NULL,
    DateSent datetime NOT NULL,
    Quantity int NOT NULL,
    SomeID int NOT NULL,
    CurrencyID int NOT NULL
)

CREATE TABLE #Currencies (
    CurrencyID int IDENTITY(1, 1),
    CurrencyValue numeric(17,3)
)

INSERT INTO #Currencies SELECT 10 --9.617


INSERT INTO #Test
    (PriceEUROCent, DateSent, Quantity, SomeID, CurrencyID)
    VALUES
        ('1', '2016-09-27 11:00', 1, 1, 1),
        ('2', '2016-09-27 11:00', 1, 1, 1),
        ('2.200', '2016-09-27 12:00', 1, 1, 1),
        ('2.999', '2016-09-27 12:00', 1, 1, 1),
        ('3', '2016-09-27 12:00', 1, 1, 1),
        ('4.0', '2016-09-27 14:00', 1, 1, 1), 
        ('4.0', '2016-09-27 14:00', 1, 1, 1),
        ('6.80', '2016-09-27 12:00', 1, 1, 1),
        ('7.1', '2016-09-27 14:01', 3, 1, 1)


DECLARE @RangeWidth numeric(17,3)

SET @RangeWidth = .1

SELECT 
    R.SomeID,
    R.DateSent,
    R.LowerBoundSEK,
    R.UpperBoundSEK,
    SUM(R.UpperBoundSEK * T.Quantity) AS SumPrice,
    SUM(T.Quantity) AS Quantity
    FROM
    (
        SELECT
            T.SomeID,
            C.CurrencyValue,
            CONVERT(VARCHAR(10), T.DateSent, 120) AS DateSent,
            (CEILING((PriceEUROCent / 10) * C.CurrencyValue)
                * @RangeWidth)   0.001 - @RangeWidth
                    AS LowerBoundSEK,
            (CEILING((PriceEUROCent / 10) * C.CurrencyValue) * @RangeWidth) 
                AS UpperBoundSEK
            FROM #Test T
                JOIN #Currencies C ON T.CurrencyID = C.CurrencyID
                    GROUP BY 
                        CEILING((PriceEUROCent / 10) * C.CurrencyValue),
                        C.CurrencyValue,
                        T.SomeID,
                        CONVERT(VARCHAR(10), T.DateSent, 120)
    ) R
        JOIN #Test T ON (T.PriceEUROCent * R.CurrencyValue / 100) 
                            BETWEEN R.LowerBoundSEK AND R.UpperBoundSEK 
        GROUP BY
            R.SomeID,
            R.DateSent,
            R.LowerBoundSEK,
            R.UpperBoundSEK
                ORDER BY 
                    R.SomeID,
                    R.DateSent,
                    R.LowerBoundSEK

DROP TABLE #Test
DROP TABLE #Currencies