SELECT  Id,
        COGCenter,
        Location,
        impel_timestamp,
        postilion_timestamp,
        ISNULL(impel_supply_value, 0) AS impel_supply_value,
        ISNULL(postilion_supply_value, 0) AS postilion_supply_value,
        ISNULL(trx, 0) AS trx,
        ISNULL(zwr_value, 0) AS roz_impel,
        ISNULL(previous_supply, 0) - ISNULL(trx, 0) AS roz_postilion,
        ABS(ISNULL(previous_supply, 0) - ISNULL(trx, 0) - ISNULL(zwr_value, 0)) AS diff,
        ( CASE ABS(ISNULL(previous_supply, 0) - ISNULL(trx, 0))
            WHEN zwr_value THEN 'rozliczony'
            ELSE CASE WHEN clar_id IS NULL THEN 'nierozliczony'
                      ELSE 'wyjasniony'
                 END
          END ) AS roz_status,
        ( CASE ISNULL(previous_supply, 0)
            WHEN 0 THEN 0
            ELSE ROUND(ISNULL(zwr_value, 0) / previous_supply, 4)
          END ) AS wsp_impel,
        ( CASE WHEN postilion_supply_value IS NOT NULL
                    AND postilion_supply_value <> 0
               THEN ISNULL(ROUND(( ISNULL(postilion_supply_value, 0)
                                   - ISNULL(trx, 0) ) / postilion_supply_value,
                                 4), 0)
               ELSE 0
          END ) AS wsp_postilion,
        ( CASE WHEN previous_supply <> 0
                    AND previous_supply IS NOT NULL
               THEN CASE WHEN DATEDIFF(month, minimum_days, impel_timestamp) <= 3
                         THEN CASE WHEN ( ROUND(ISNULL(zwr_value, 0)
                                                / previous_supply, 2) * 100 ) > 25
                                   THEN 100 * ( ROUND(ISNULL(zwr_value, 0)
                                                      / previous_supply, 2)
                                                * 100 - 25 )
                                   ELSE 0
                              END
                         WHEN ( DATEDIFF(month, minimum_days, impel_timestamp) > 3
                                AND DATEDIFF(month, minimum_days,
                                             impel_timestamp) <= 6
                              )
                         THEN CASE WHEN ( ROUND(ISNULL(zwr_value, 0)
                                                / ISNULL(previous_supply, 0),
                                                2) * 100 - 20 ) > 0
                                   THEN 100 * ( ROUND(ISNULL(zwr_value, 0)
                                                      / previous_supply, 2)
                                                * 100 - 20 )
                                   ELSE 0
                              END
                         ELSE CASE WHEN ( ROUND(ISNULL(zwr_value, 0)
                                                / ISNULL(previous_supply, 0),
                                                2) * 100 - 15 ) * 100 > 0
                                   THEN 100 * ( ROUND(ISNULL(zwr_value, 0)
                                                      / previous_supply, 2)
                                                * 100 - 15 )
                                   ELSE 0
                              END
                    END
               ELSE 0
          END ) AS impel_kara,
        zwr_timestamp,
        zwr_id,
        ISNULL(zwr_value, 0) AS zwr_value,
        TellerId
FROM    ( SELECT    ( ( postilion.Cassette1Amount * ISNULL(machine.Cassette1,
                                                           0)
                        + postilion.Cassette2Amount * ISNULL(machine.Cassette2,
                                                             0) )
                      + postilion.Cassette3Amount * ISNULL(machine.Cassette3,
                                                           0) )
                    + postilion.Cassette4Amount * ISNULL(machine.Cassette4, 0) AS postilion_supply_value,
                    impel.zas_value AS impel_supply_value,
                    ISNULL(( SELECT SUM(Amount) AS Expr1
                             FROM   dbo.TellerTransactions AS trx
                             WHERE  ( TellerId = impel.TellerId )
                                    AND ( Timestamp BETWEEN ISNULL(( SELECT MAX(Timestamp)
                                                                     FROM   dbo.SupplyOperation AS impel_prev
                                                                     WHERE  ( TellerId = impel.TellerId )
                                                                            AND ( Timestamp < impel.zas_timestamp )
                                                                            AND ( OperationType = 'ZAS' )
                                                                   ), '1753-01-01')
                                                    AND     impel.zas_timestamp )
                           ), 0) AS trx,
                    ISNULL(impel.Id, postilion.Id) as Id,
                    ISNULL(impel.TellerId, postilion.TellerId) as TellerId,
                    machine.Location,
                    machine.COGCenter,
                    impel.zas_timestamp AS impel_timestamp,
                    postilion.CreatedAt AS postilion_timestamp,
                    ISNULL(( SELECT TOP 1
                                    pprev.OperationValue
                             FROM   SupplyOperation pprev
                             WHERE  pprev.TellerId = impel.tellerId
                                    AND pprev.operationType = 'ZAS'
                                    AND pprev.Timestamp < impel.zas_timestamp
                             ORDER BY pprev.timestamp DESC
                           ), 0) AS previous_supply,
                    ( SELECT    MIN(Timestamp) AS Expr1
                      FROM      [dbo].[TellerTransactions] AS transaction_min
                      WHERE     ( TellerId = impel.TellerId )
                    ) AS minimum_days,
                    impel.zwr_timestamp,
                    impel.zwr_id,
                    impel.zwr_value,
                    clar.Id AS clar_id
          FROM      ( SELECT    zas.Id,
                                zas.TellerId,
                                zas.OperationValue AS zas_value,
                                zas.Timestamp AS zas_timestamp,
                                zwr.OperationValue AS zwr_value,
                                zwr.Timestamp AS zwr_timestamp,
                                zwr.Id AS zwr_id
                      FROM      ( SELECT    zas.Id AS zas_id,
                                            zas.Timestamp AS zas_date,
                                            MAX(zwr.Timestamp) AS zwr_date
                                  FROM      dbo.SupplyOperation AS zas
                                            LEFT OUTER JOIN dbo.SupplyOperation
                                            AS zwr ON zwr.TellerId = zas.TellerId
                                                      AND zwr.Timestamp <= zas.Timestamp
                                                      AND zwr.OperationType = 'ZWR'
                                                      AND CONVERT(VARCHAR(10), zwr.Timestamp, 121) = CONVERT(VARCHAR(10), zas.Timestamp, 121)
                                  WHERE     ( zas.OperationType = 'ZAS' )
                                  GROUP BY  zas.Timestamp,
                                            zas.Id
                                ) AS zas_zwr_pair
                                INNER JOIN dbo.SupplyOperation AS zas ON zas.Id = zas_zwr_pair.zas_id
                                                                         AND zas.OperationType = 'zas'
                                LEFT OUTER JOIN dbo.SupplyOperation AS zwr ON zwr.TellerId = zas.TellerId
                                                                              AND zwr.Timestamp = zas_zwr_pair.zwr_date
                                                                              AND zwr.OperationType = 'zwr'
                    ) AS impel
                    FULL OUTER JOIN dbo.eServiceCashLevels AS postilion ON impel.TellerId = postilion.TellerId
                                                                           AND impel.zas_timestamp = postilion.CreatedAt
                    LEFT OUTER JOIN dbo.TellerMachine AS machine ON ISNULL(impel.TellerId, postilion.TellerId) = machine.Id
                    LEFT OUTER JOIN dbo.SupplyClarifications AS clar ON clar.OperationId = impel.Id
        ) AS tmp