SQL Query + more efficient alternative

Question

I have a query which involves 2 tables 'Coupons' and 'CouponUsedLog' in SQL Server, the query below will obtain some information from these 2 tables for statistics study use. Somehow I feel that while my query works and returns me the desired results, I feel that I can be written in a more efficient way, can someone please advice if there's a better way to rewrite this? Am I using too many unnecessary variables and joins? Thanks.

DECLARE @CouponISSUED        int=null
DECLARE @CouponUSED          int=null
DECLARE @CouponAVAILABLE     int=null
DECLARE @CouponEXPIRED       int=null
DECLARE @CouponLastUsed      Date=null


--Total CouponIssued
SET    @CouponISSUED = 
(
    select count(*) 

    from   Coupon C Left Join 
           couponusedlog CU on C.autoid = CU.Coupon_AutoID

    where  C.VoidedBy is null and 
           C.VoidedOn is null and 
           DeletedBy  is null and 
           DeletedOn  is null and 
           Card_AutoID in (Select AutoID 
                           from   Card 
                           where  MemberID = 'Mem001')
)

--Total CouponUsed
SET @CouponUSED = 
(
    select count(*) 
    from   couponusedlog CU Left Join 
           Coupon C on CU.Coupon_AutoID = V.autoid

    where  CU.VoidedBy is null and 
           CU.VoidedOn is null and 
           C.Card_AutoID in (select AutoID 
                             from   Card 
                             where  MemberID = 'Mem001')
)


SET @CouponAVAILABLE = @CouponISSUED - @CouponUSED


--Expired Coupons
SET @CouponEXPIRED = 
(
    select Count(*) 

    from   Coupon C Left Join 
           couponusedlog CU on C.autoid = CU.Coupon_AutoID

    where  C.VoidedBy is null and 
           C.VoidedOn is null and 
           deletedBy  is null and 
           deletedOn  is null and
           Card_AutoID in (select AutoID 
                           from  Card 
                           where MemberID = 'Mem002') and
           CONVERT (date, getdate()) > C.expirydate
) 

--Last Used On
SET @CouponLastUsed = 
(
    select CONVERT(varchar(10), 
           Max(VU.AddedOn), 103) AS [DD/MM/YYYY] 

    from   couponusedlog CU Left Join 
           coupon C on CU.Coupon_AutoID = C.autoid

    where  CU.voidedBy is null and 
           CU.voidedOn is null and 
           C.Card_AutoID in (select AutoID 
                             from Card 
                             where MemberID = 'Mem002')
)



Select  @CouponISSUED    As Coupon_Issued,
        @CouponUSED      As Coupon_Used, 
        @CouponAVAILABLE As Coupon_Available,
        @CouponEXPIRED   As Coupon_Expired,
        @CouponLastUsed  As Last_Coupon_UsedOn

Answer 1

Are you asking this question out of a proactive desire to be as effecient as possible, or because of an actual performance issue you would like to correct? You can make this more effecient at the cost of having code that is harder to manage. If the performance is okay right now I would highly recommend you leave it because the next person to come along will be able to understand it just fine. If you make one huge effecient but garbled sql statement out of it then when you or anyone else wants to update something about it it's going to take you 3 times longer as you try to re-figure out what the heck you were thinking when you wrote it.

Answer 2

In general its better to do things in a single query if you you're just looking for counts of things particularly against nearly the same data set then in four separate queries.

This query combines what you need into a single query by converting your WHERE Clauses into SUMS of CASE statements. The MAX of the date is just a normal thing you can do when you're doing a count or a sum.

SELECT COUNT(*) couponissued, 
       SUM(CASE 
             WHEN deletedby IS NULL 
                  AND deletedon IS NULL THEN 1 
             ELSE 0 
           END) AS couponused, 
       SUM(CASE 
             WHEN deletedby IS NULL 
                  AND deletedon IS NULL 
                  AND Getdate() > c.expirydate THEN 1 
             ELSE 0 
           END) AS couponex,
 MAX(vu.addedon) CouponEXPIRED 
FROM   [couponusedlog] cu 
       LEFT JOIN [Coupon] c 
         ON ( cu.coupon_autoid = v.autoid ) 
WHERE  cu.voidedby IS NULL 
       AND cu.voidedon IS NULL 
       AND ( c.card_autoid IN (SELECT [AutoID] 
                               FROM   [Card] 
                               WHERE  memberid = 'Mem001') ) 

You can then convert that into a Common Table Expression to do your subtraction and formatting