sql - Join two tables to get counts different dates -

sql - Join two tables to get counts different dates -

i have table columns:

id, transactiondate, pointsordered

table b

id,redemptiondate,pointsused

table c

as

id,joindate

what want

all info needed date range lets 2014-01-01 2014-02-01 in yyyy-mm-dd

count of total ids date ( count of ids table a)

count of accounts had first transaction on date

total points ordered date ( sum of points table a)

count of accounts redeemed on date ( count of ids table b )

countofpointsued on date ( sum of points table b)

new customers joined date

i understand id foreign key table b , table c how ensure match dates ?

for eg if bring together date such a.transactiondate=b.redemption.date gives me customers had transaction on date , redeemed on date.

where want count of customers had transaction on date , customers redeemed on date ( irrespetive of fact when did have transaction)

here had tried

select count( distinct a.id) noofcustomers, sum(a.pointsordered), sum(b.pointsused), count(distinct b.id) transaction bring together redemption b on a.transactiondate=b.redemptiondate .transactiondate between '2014-01-01' , '2014-02-01' grouping a.transactiondate,b.redemptiondate

i first grouping info table , bring together results date. shouldn't utilize inner bring together because may lose info if there no matching record on 1 side no transaction on given date redemption. help if you'd have list of dates in range. if don't have can build 1 using cte.

declare @from date = '2014-01-01' declare @to date = '2014-02-01' ; dates ( select @from [date] union select dateadd(day, [date], 1) d dates [date] < @to ) , orders ( select transactiondate [date], count(distinct id) noofcustomers, sum(pointsordered) pointsordered [transaction] transactiondate between @from , @to grouping transactiondate ) , redemptions ( select redemptiondate [date], count(distinct id) noofcustomers, sum(pointsused) pointsused [redemption] redemptiondate between @from , @to grouping redemptiondate ) , joins ( select joindate [date], count(distinct id) noofcustomers [join] joindate between @from , @to grouping joindate ) , firsts ( select transactiondate [date], count(distinct id) noofcustomers [transaction] t1 transactiondate between @from , @to , not exists ( select * [transaction] t2 t2.id = t1.id , t2.transactiondate < t1.transactiondate) grouping transactiondate ) select d.[date], isnull(o.noofcustomers,0) noofcustomersordered, isnull(o.pointsordered,0) totalpointsordered, isnull(f.noofcustomers,0) noofcustomersfirsttran, isnull(r.noofcustomers,0) noofcustomersredeemed, isnull(r.pointsused,0) totalpointsredeemed, isnull(j.noofcustomers,0) noofcustomersjoined dates d left bring together orders o on o.[date] = d.[date] left bring together redemptions r on r.[date] = d.[date] left bring together joins j on j.[date] = d.[date] left bring together firsts f on f.[date] = d.[date]

please note didn't run query may errors, think general thought clear.

sql sql-server join count