Views, aliases, confusion - SQL Server Forums

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)

Views, aliases, confusion

Author

Topic

pixelmeow
Starting Member

9 Posts

Posted - 2009-02-11 : 16:07:53

Hello all,

I have two databases, one with the data and views on it (called Dev), and the other with views to call the ones in Development (called Rpts).

Two views in Dev return duplicate groups of fields, one view being based on a date field being within the last year AND within 120 days of "today", the other only being within the last year.

One view in Rpts uses these two views in Dev to get the percentage of records within the last year that are within the last 120 days. My problem is that I can't figure out how to get more out of this view, like any of those other fields in the Dev views. For instance:

Dev, 1st view:


CREATE VIEW vw_TPL_NUMBER_CASES_ONTIME AS
SELECT RECEIPTDATE RECEIPT_DATE
      ,DOCUMENTSENTDATE DOCUMENT_SENT_DATE
      ,CASEID CASE_IDENTIFIER
      ,TYPE DOCUMENT_TYPE
      ,NUMBER DOCUMENT_NUMBER
      ,CASEMANAGER CASE_MANAGER
  FROM CASES
 WHERE RECEIPTDATE >= (SELECT DISTINCT GETDATE() - 365 FROM CASES)
   AND STATUSCODE <> 'X'
   AND (TYPE = 'AMD' OR TYPE = 'LOA')
   AND DATEDIFF(DAY,RECEIPTDATE,DOCUMENTSENTDATE) < 120
GO

Dev, 2nd view:


CREATE VIEW vw_TPL_NUMBER_CASES AS
SELECT RECEIPTDATE RECEIPT_DATE
      ,DOCUMENTSENTDATE DOCUMENT_SENT_DATE
      ,CASEID CASE_IDENTIFIER
      ,TYPE DOCUMENT_TYPE
      ,NUMBER DOCUMENT_NUMBER
      ,CASEMANAGER CASE_MANAGER
  FROM CASES
 WHERE RECEIPTDATE >= (SELECT DISTINCT GETDATE() - 365 FROM CASES)
   AND STATUSCODE <> 'X'
   AND (TYPE = 'AMD' OR TYPE = 'LOA')
GO

Rpts view:


CREATE VIEW e_PERCENT_ON_TIME AS
SELECT DISTINCT LTRIM(STR((
	(SELECT COUNT(*) C
	   FROM DEV..vw_TPL_NUMBER_CASES_ONTIME
	) * 100.00
	/
	(SELECT COUNT(*) C
	   FROM DEV..VW_TPL_NUMBER_CASES
	) * 1.00
))) + '%' AS PERCENTAGE
GO

I need to get the other fields from the views in Dev, so that I can get percentages for each person and so forth, rather than just the percentage of ALL cases.

Please be gentle, I still think in Oracle... (although I would still have to look this one up)

Thanks,
Teresa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources