help with getting statistics from db in SQL

Question

I'm using SQL-Server 2005.

I have two tables Users and Payments which has a foreign key to Users. Both Users and Payments have a date column (Users set their value during registration and Payments gets a value during payment). Users has a column called isPaymentsRecurring as bit which tells me to renew the user or not. The value is 1 on default = recurring. Payments has a column called paymentSum as tinyint where I insert the value of payment. ( first payment is equal to recurring one)

I need to get a few statistics about that for simple line chart, grouped by date. To separate single days I use the hack below to get whole day as a single item.

Day hack

dateadd(dd,datediff(dd,0,date),0)

What I need to get are the values below all must be grouped by day hack.

1.Unique users in system per day

2.Users that ordered once and then set isPaymentRecurring to 0.

3.Sum of paymentSum per day

4.How many users got recurring payment per day. This means orders of user per day except first order in system.

Thanks, I have queries that work but they don't work as I want them to thus I want pros opinion on that.

Answer 1

You could add computed columns to your Payments and Users tables that represent the day, month and year of payment or user registration:

ALTER TABLE dbo.Payments
   ADD PaymentDay AS DAY(PaymentDate) PERSISTED,
   PaymentMonth AS MONTH(PaymentDate) PERSISTED,
   PaymentYear AS YEAR(PaymentDate) PERSISTED

Now these columns give you INT values for the day, month and year and you can easily query them. Since they're persisted, they can even be indexed!

You can easily group your payments by those columns now:

SELECT (list of fields), COUNT(*)
FROM dbo.Payments
GROUP BY PaymentYear, PaymentMonth, PaymentDay

or whatever you need to do. With this approach, you can also easily do stats on a per-month basis, e.g. sum up all payments and group by month.

Update: ok, so you need to understand how to create those queries:

• Unique users in system per day

  SELECT UserDay, UserMonth, UserYear, COUNT(*) 
  FROM dbo.Users
  GROUP BY UserDay, UserMonth, UserYear
  

  That would count the users and group them by day, month, year

• Users that ordered once and then set isPaymentRecurring to 0.

  SELECT UserDay, UserMonth, UserYear, COUNT(*) 
  FROM dbo.Users
  WHERE isPaymentRecurring = 0
  GROUP BY UserDay, UserMonth, UserYear
  

  Is that what you're looking for?? Number of users grouped by day/month/year that have their isPaymentRecurring set to 0 ?

• sum of paymentSum per day

  SELECT PaymentDay, PaymentMonth, PaymentYear, SUM(PaymentSum)
  FROM dbo.Payments
  GROUP BY PaymentDay, PaymentMonth, PaymentYear
  

  That would sum up the PaymentSum column and group it by day, month, year

I don't really understand what you're trying to achieve with the other two queries, and what criteria would have to apply for those queries.