Derived Table? - 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 2005 Forums

Transact-SQL (2005)

Derived Table?

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-16 : 08:03:17

In order to add extra columns to an accomplished statement, I think I need to address this atatement as a derived table and join it in some manner, but am lacking an entity to join on. There are 5 items in the select list. I want to use this as a core, and link other columns. The database works by PatientNumber as a serial number to link everything as a Primary Key for most tables. It's a join used in this statment, BUT it's not in the select list. I don't see how I can add it because I don't want to group on PatientNumber...

How do I add columns to a grouping statement?

SELECT
vwKC60.Code AS BaseCode,
vwKC60.Description,
SUM(patindex('M',de.Sex)) AS TotalMale,
SUM(PATINDEX(@sSexOrnBiSex + 'F',di.Acquired + de.Sex)) + SUM(PATINDEX(@sSexOrnHoSex + 'M', di.Acquired + de.Sex)) AS HoBiMaleOnly,
SUM(patindex('F',de.Sex)) AS TotalFemale
FROM vwSHKC60BaseCode vwKC60
LEFT JOIN Diagnosis di ON vwKC60.Code = di.Diagnosis
LEFT JOIN Demographic de ON di.PatientNumber = de.PatientNumber
LEFT JOIN Episode ep ON ep.ID = di.ParentID
LEFT JOIN Locations AS lo ON ep.Location = lo.Coded
WHERE (di.DiagnosisDate BETWEEN '01-06-2009' AND '31-08-2009')
AND (di.Provisional = 0)
AND (di.RecordDeleted = 0)
AND ep.Location = 'GU'
AND ep.Location = 'GU'
OR (lo.BelongsTo LIKE '%GU%')
GROUP BY
ALL vwKC60.Code, vwKC60.Description
ORDER BY
vwKC60.Code

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-16 : 08:35:20

You can use a subquery so that it will not be a part of your main group by clause and relate it with the main Query

SELECT
vwKC60.Code AS BaseCode,
vwKC60.Description,
SUM(patindex('M',de.Sex)) AS TotalMale,
SUM(PATINDEX(@sSexOrnBiSex + 'F',di.Acquired + de.Sex)) + SUM(PATINDEX(@sSexOrnHoSex + 'M', di.Acquired + de.Sex)) AS HoBiMaleOnly,
SUM(patindex('F',de.Sex)) AS TotalFemale,
(Select extra_Column from <Table_name> t where T.patient_number=de.PatientNumber and --specify other columns if any so as to uniquely identify your new extra_column)
FROM vwSHKC60BaseCode vwKC60
LEFT JOIN Diagnosis di ON vwKC60.Code = di.Diagnosis
LEFT JOIN Demographic de ON di.PatientNumber = de.PatientNumber
LEFT JOIN Episode ep ON ep.ID = di.ParentID
LEFT JOIN Locations AS lo ON ep.Location = lo.Coded
WHERE (di.DiagnosisDate BETWEEN '01-06-2009' AND '31-08-2009')
AND (di.Provisional = 0)
AND (di.RecordDeleted = 0)
AND ep.Location = 'GU'
AND ep.Location = 'GU'
OR (lo.BelongsTo LIKE '%GU%')
GROUP BY
ALL vwKC60.Code, vwKC60.Description
ORDER BY
vwKC60.Code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources