question

Atul 2 avatar image
Atul 2 asked

Best method to build a transactional database summary

Hi,

I'm new to SQL programming & SQL Server. I'm running SQL Server 2008.

Problem: I have a database table (1 million+ lines) with transactional information (i.e. sales, returns, etc.). A sample is below:

Date| InvoiceNo| ProductID| InvoiceAmt| Returns| Transaction|

1/1/2010 | 1 | A | 100.00| 0.00| Sale
1/1/2010 | 2 | C | 20.00 | 0.00 | Sale
1/3/2010 | 1 | A | 0.00 | -100.00| Return
1/5/2010 | 3 | B | 75.00 | 0.00 | Sale
...

Question: I want to create a couple of summary tables from this raw data. Samples are shown below.

Table 1: Date based summary - for each date in the 'Date' column, I want a summary of activities for that date.
Example:
Date | # of Unique Invoices| Sum(InvoiceAmt)| Sum(Returns)| Count of (Transaction ' 'Sale')|
1/1/2010 | 2 | 120.00 | 0.00 | 2
1/2/2010 | 0 | 0.0000 | 0.00 | 0
1/3/2010 | 1 | 0.0000 | -100.00 | 0
1/4/2010 | 0 | 0.0000 | 0.00 | 0
1/5/2010 | 1 | 75.000 | 0.00 | 1
...

Table 2: I want to create a summary by product (a list of unique ProductID values are stored in another table (PIDTable).
ProductID | # of Sales | # of Returns| Total Sales YTD | Total Returns YTD | Net
A | 25 | 10| 2000 | -750 | 1250
B | 50 | 35| 4500 | -3000 | 1500
...

The above is a sample; I've got some more complex metrics to compute (other than sum) but it gives an idea.

I'm not sure how to go about it. A couple of people I asked who know a little bit of SQL said I need to use cursors and build the above. I'm not sure how as the examples I've seen don't show this kind of data.

Any help/sample code would be much appreciated.

Thanks

sql-server-2008temporary-tables
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

give us your more complex metrics
0 Likes 0 ·
Thanks to both of you for the responses. @a b -- no, it's not a homework problem!! I've been out of school for some time now!! I must admit that the method is much simpler and perhaps more importantly, significantly faster and executes in <40seconds on a 700K+ table with many more computed columns. No need of cursors, etc.!!
0 Likes 0 ·
Darren 2 avatar image
Darren 2 answered

Having to use cursors is nonsense. That's how you would do it without a SQL query frontend. Basically, using a cursor is stepping outside of the 'set model' of databases and SQL. Sure, you can do it, but why? Let the database do the 'how', you just tell it 'what'.

Is this a homework assingment? :)

What you need to look into is aggregate functions. You want to select all rows (maybe within certain limits, such as a summary for one week), where on every given date, you aggregate certain data points.

select date, count(distinct invoiceNo), sum(InvoiceAmt) ... where date < today and date > oneWeekAgo group by ...

Same thing with the second summary, but you need to join to that other table. But with the same principle behind it. If you need to, you can always use a temporary table, if you need intermediary results for any column, or just have a subselect as one column... No use for cursors here.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered

Firstly you will need a calendar table.

The calendar table can be as simple as

create table calendar            
(            
    dte    datetime            
)            

then for your date base summary you can LEFT JOIN the calendar table to your trans information table

select d.dte,            
       distinct_inv = isnull(count(distinct invoiceno), 0),            
       invoice_amt  = isnull(sum(invoice_amt), 0),            
       returns      = isnull(sum(returns), 0),            
       sales_count  = sum(case when transaction = 'sales' then 1 else 0 end)            
from   dte d            
       left join trans t on d.dte = t.date            
group by d.dte            

for the summary by product, i leave it to you. It is very similar to the date base summary

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.