Reporting from SQL Server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reporting from SQL Server 2000

Author

Topic

hnomani
Starting Member

35 Posts

Posted - 2001-05-22 : 14:02:12

I am working on a reporting project using SQL Server 2000 database and a reporting tool (Crystal or Access). I have a report that is in tabular format, but each column represents a different condition. I am wondering, what would be the best approach to build this kind of Report?

Example Code: ( My report has more than 15 complex conditions)

The Report Columns:

State:
Company Number:
1 Line
2 Lines
and so on..

To get State & Company
SELECT DISTINCT state_cd, company_nbr
FROM vw_offer_act_res

To get 1 Line

SELECT COUNT(*)
FROM (SELECT state_cd, company_nbr, svc
FROM vw_offer_act_res
WHERE line_ind = ‘R’
GROUP BY state_cd, company_nbr, svc
HAVING COUNT(svc) = 1) a
GROUP BY a.state_cd, a.company_nbr

To get 2 Line

SELECT COUNT(*)
FROM (SELECT state_cd, company_nbr, svc
FROM vw_offer_act_res
WHERE line_ind = ‘R’
GROUP BY state_cd, company_nbr, svc
HAVING COUNT(svc) = 2) a
GROUP BY a.state_cd, a.company_nbr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources