sql - How do I determine whether a cell in the first record of a customers history contains a value? -
i have tickets table holds several years of customer data. trying determine whether customer has visited x number of time used coupon on first visit. have (sql below) using can break out group looking for. want know group how many of them used coupon on first visit (earliest saledate).
;with t ( select customerid, saledate, totalcoupons, count(*) on (partition customerid) cnt store.dbo.tickets (creationdate between '2014-1-1' , '2014-11-12') , (isdeleted = 0) , (ticketstatus = 'complete') ) select customerid, saledate, totalcoupons, cnt t cnt = 10 order customerid, saledate
as end result need count , don't need see customerid, saledate etc, need (pseudo) select count(customerid) totalcoupons cell has value on , earliest record customerid.
i think have add rowid , use rowid = 1 not sure how work have done.
you can use row_number()
determine first visit , use conditional aggregation. following gets customers in group flag:
with t ( select customerid, saledate, totalcoupons, count(*) on (partition customerid) cnt, row_number() on (partition customerid order saledate) seqnum store.dbo.tickets (creationdate between '2014-01-01' , '2014-11-12') , (isdeleted = 0) , (ticketstatus = 'complete') ) select customerid, saledate, totalcoupons, cnt, max(case when seqnum = 1 , totalcoupons not null) hascouponfirstvisit t cnt = 10;
you can count of them doing:
with t ( select customerid, saledate, totalcoupons, count(*) on (partition customerid) cnt, row_number() on (partition customerid order saledate) seqnum store.dbo.tickets (creationdate between '2014-01-01' , '2014-11-12') , (isdeleted = 0) , (ticketstatus = 'complete') ) select count(customerid) t cnt = 10 , seqnum = 1 , totalcoupons not null;
i'm not sure if totalcoupons not null
looking for, should close enough.
Comments
Post a Comment