Multiple sub queries

[Ringers]

Hi All,

I have added one inner query to my statement and need to add a second [on hand] so it is a new column in the orginial result set.

Can someone please show me the correct syntax, they are all from the same table [MTR_MAT_SUPER_TYPE].

[SQL]
SELECT m.MTR_MATTER_ID, m.MTR_SERIES_CD, m.MTR_YEAR, m.MTR_STATUS_CD, m.MTR_LODG_DATE, m.MTR_SHRT_TITLE, m.MTR_NUMBER, m.MTR_LONG_TITLE,
m.PTP_ALLOC_PART_ID, m.PTP_MOVE_PART_ID,m.LODG_METHOD_CD,m.MTR_COMMENT,m.MTR_MEDIUM_TITLE,m.MTR_ALT_TITLE,m.MTR_CITED_NAME_FLAG,
m.MTR_SUPRESS_FLAG,m.MTR_SUPRESS_ALT_NAME,m.MTR_NUMBER_FORMATTED,m.MTR_REG_CODE,m.INITIATING_DOC_EVENT_ID,m.AREA_CODE,
m.LODGED_BY_CODE,m.LODGED_LOCATION_CODE,m.MTR_JS_CODE,

(SELECT Count(MTR_MATTER_ID) FROM [MTR_MAT_SUPER_TYPE] WHERE MTR_STATUS_CD = 'REGIST'
AND DATEPART(m, [MTR_LODG_DATE]) = DATEPART(m, DATEADD(m, -1, getdate()))
AND [MTR_REG_CODE] is not null
AND isnull([MTR_STATUS_CD],') NOT like 'FINAL') AS [Number Lodged],

(SELECT COUNT(MTR_MATTER_ID), MTR_LODG_DATE
FROM [MTR_MAT_SUPER_TYPE]
WHERE DATEPART(Year, [MTR_LODG_DATE]) = DATEPART(Year, DATEADD(Y, -1, getdate()))
AND MTR_LODG_DATE <= DATEADD(mm, DATEDIFF(mm, 0, GETDATE())-1, 0)
AND [MTR_REG_CODE] is not null
AND isnull([MTR_STATUS_CD],') != 'FINAL') AS [On Hand]

FROM [MTR_MAT_SUPER_TYPE] m

WHERE DATEPART(m, [MTR_LODG_DATE]) = DATEPART(m, DATEADD(m, -1, getdate()))
AND [MTR_REG_CODE] is not null
AND isnull([MTR_STATUS_CD],') NOT like 'FINAL'
[/SQL]

Thanks

 

[markros]

If you're using SQL 2005 or up, instead of writing subquery, use COUNT as a window function with required partition.

PluralSight Learning Library