Sql server – creating a specific table using counts

Topic

New Reply

Hi, I am very new to SQL Server and would appreciate help with a task I have to do. I have a table [Table1] where I am showing a customer (CUSTID) along with an Account (AccountId) [cust can have many accounts] a date of opening[Opdate] and a date of closing [Cldate]. What I want to do is have a small table showing the months of the year along one axis and “Open” and “Closed” as the header line, with the input being the count of accounts either opened or closed during that month. How do I create such a table in SQL server?? Thanks for your help!

Blitz

Reply | Quote

Replies

Blitz, I think you’re going about it the wrong way. The count of accounts either opened or closed during a given month should be a *calculated* value. In other words, calculate such a value during a report’s run-time so the value is always current and reflects the most recent data/changes.

However, if you do want such a totals table, you’d want a table with the following columns:
ClientID (FK relationship to ClientID in main client table)
AcctOpenCount
AcctCloseCount
CountYear (e.g. 2011)
CountMonth (e.g. 07)

Hope I understood your question properly.

Reply | Quote