• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • Last Modified:

sql query

hey guys i have this query

SELECT DISTINCT SUBSTRING(description, 0, 100) as s_description, COUNT(description) as count, description from groupon GROUP BY description


now i need to count the number active Voucher where

convert(datetime, deal_end, 104) >= getdate() and date_redeemed is null


please help me count the active voucher and return it in the top query, the coloum name must be active
0
JCWEBHOST
Asked:
JCWEBHOST
  • 2
  • 2
1 Solution
 
HuaMinChenBusiness AnalystCommented:
Some problems in your select
1) It should be SUBSTRING(description, 1, 100)
2) actually what are you going to count? You can't count the same description!

Many Thanks & Best Regards,
HuaMin
0
 
JCWEBHOSTAuthor Commented:
this statement

SELECT DISTINCT SUBSTRING(description, 0, 100) as s_description, COUNT(description) as count, description from groupon GROUP BY description


returns  the number of description and the distict name now i want to  the colum where   deal_end > todays date and  date_redeemed is null
0
 
jogosCommented:
Group by has same result as a distinct ... only you group by description and do a distinct on the first 100 characters of description -> possible conflict

Notice that substring starts from position 1 not from 0 (but result will be the same)
For your active vouchers CASE is the way to go
http://msdn.microsoft.com/en-us/library/ms181765.aspx

SELECT SUBSTRING(description, 1, 100) as s_description
        , COUNT(description) as count
         , description 
        ,sum ( case when convert(datetime, deal_end, 104) >= getdate() 
                                           and date_redeemed is null 
                then 1 else 0 end
           ) as activeVouchers
from groupon 
GROUP BY description

Open in new window

0
 
JCWEBHOSTAuthor Commented:
sweet this works

SELECT SUBSTRING(description, 1, 100) as s_description
        , COUNT(description) as count
         , description
        ,sum ( case when convert(datetime, deal_end, 104) >= getdate()
                                           and date_redeemed is null
                then 1 else 0 end
           ) as activeVouchers
from groupon
GROUP BY description


now i also need to count the inactive colums? it will be the date < and redeemed is null
0
 
jogosCommented:
For inactive change the 1 and 0 from then to else.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now