3click_sql

*client.rb#loyalty_points*


    SELECT Points, TimeStamp, RewardType, RewardTypeID, tblClientRewards.ExpirationDate, tblClientRewards.Returned, Clients.FirstName, Clients.LastName, Clients.ClientID, [Sales].SaleID, [VISIT DATA].ClassID, [VISIT DATA].ClassDate, NULL AS PurchaseID
    FROM tblClientRewards
    LEFT OUTER JOIN Clients
    ON Clients.ClientID = tblClientRewards.RewardTypeID
    AND tblClientRewards.RewardType = 'Referral'
    LEFT OUTER JOIN [Sales]
    ON [Sales].SaleID = tblClientRewards.RewardTypeID
    AND tblClientRewards.RewardType = 'Sale'
    LEFT OUTER JOIN [VISIT DATA]
    ON [VISIT DATA].VisitRefNo = tblClientRewards.RewardTypeID
    AND tblClientRewards.RewardType = 'Class Booking'
    LEFT OUTER JOIN tblReservation
    ON tblReservation.VisitRefNo = tblClientRewards.RewardTypeID
    AND tblClientRewards.RewardType = 'Appointment Booking'
    WHERE tblClientRewards.Deleted = 0 AND tblClientRewards.ClientID = #{@id}
    UNION SELECT Sum(PointsRedeemed) * -1 AS Points,
                  Max(TimeRedeemed) AS TimeStamp,
                  RedemptionType AS RewardType,
                  RedemptionTypeID AS RewardTypeID,
                  NULL AS ExpirationDate,
                  0 AS Returned,
                  NULL AS FirstName,
                  NULL AS LastName,
                  tblClientRewardsRedeem.ClientID,
                  NULL AS SaleID,
                  NULL AS ClassID,
                  NULL AS ClassDate,
                  tblClientRewardsRedeem.RedemptionTypeID AS PurchaseID
                  FROM tblClientRewardsRedeem
                  WHERE tblClientRewardsRedeem.ClientID = #{@id}
                  GROUP BY RedemptionType,
                  RedemptionTypeID,
                  tblClientRewardsRedeem.ClientID,
                  tblClientRewardsRedeem.RedemptionTypeID
                  ORDER BY TimeStamp DESC

*client.rb#account_balance_packages*

    SELECT *
      FROM [PAYMENT DATA]
      WHERE ClientId = #{@id}
      AND NumClasses > 0
      ORDER BY PmtRefNo DESC"

*client.rb#account_balance_information*

    SELECT *
      FROM [PAYMENT DATA]
      WHERE ClientId = #{@id}
      AND NumClasses > 0
      ORDER BY PmtRefNo DESC

*client.rb#because_mbos_get_client_contracts_return*

    SELECT *
      FROM [tblClientContracts]
      JOIN [tblContract] ON tblContract.ContractId = tblClientContracts.ContractId
      WHERE tblClientContracts.ClientId = #{@id} AND tblClientContracts.IAgreed = 1
      AND tblClientContracts.Deleted = 0

*newspost.rb*

    SELECT TOP #{options[:limit]} *
      FROM tblBB
      WHERE CAST(StartDate AS DATE) <= CAST('#{Date.today}' AS DATE)
      AND (EndDate IS NULL OR CAST(EndDate AS DATE) >= CAST('#{Date.today}' AS DATE))
      #{mode} #{headline}
      ORDER BY StartDate DESC

*product.rb#online_store_core*

    SELECT *
      FROM [PRODUCTS]
      WHERE #{conditions_for_online_products("ItemTypeId = #{giftcards ? 4 : 3}")}"

*service.rb*

    SELECT ProductId, Duration, DurationUnit
     FROM [PRODUCTS] where ProductId
      IN (#{service_ids.join(',')})"

*service.rb#introductory_product_ids*

    SELECT ProductId, Introductory, IntroBuyCount FROM [PRODUCTS]

*service.rb#sorted*

    SELECT ProductId from [PRODUCTS]
      WHERE Type = 1
      AND WsShow = 1
      AND Discontinued = 0
      ORDER BY Priority DESC, Description ASC

*session_type.rb#description_for_session_type*

    SELECT TOP 1 * FROM [tblVisitTypes] where TypeId = '#{session_type_id}'

*session_type.rb#default_booking_time*

    SELECT TOP 1 * FROM [tblVisitTypes] where TypeId = '#{session_type_id}'

*visit.rb*

    SELECT * FROM [VISIT DATA] Where VisitRefNo = #{@id}