SELECT 2012 AS CurYear, 2011 AS PastYear, COALESCE(D.InstypeName,D2.InstypeName) AS InstypeName,COALESCE(D.DeptName,D2.DeptName) AS DeptName,ISNULL(D.Visits,0) AS
VisitsPast,ISNULL(D.TotChg,0) AS ChargesPast, ISNULL(D.TotAdj,0) AS AdjustsPast,ISNULL(D2.Visits,0) AS
VisitsCur,ISNULL(D2.TotChg,0) AS ChargesCur, ISNULL(D2.TotAdj,0) AS AdjustsCur,ISNULL(D.Visits,0) + ISNULL(D2.Visits,0) AS TotVisits,
ISNULL(D.TotChg,0) + ISNULL(D2.TotChg,0) AS TotChg,ISNULL(D.TotAdj,0) + ISNULL(D2.TotAdj,0) AS TotAdj FROM 
(SELECT COALESCE(VisCur.InstypeName,ChgCur.InstypeName,AdjCur.InstypeName) AS InsTypeName,
COALESCE(VisCur.DeptName,ChgCur.DeptName,AdjCur.DeptName) AS DeptName,VisCur.Visits,ChgCur.TotChg, AdjCur.TotAdj
FROM dbo.fn_ReportVisits (2011,'1/1/2012','1/12/2012') VisCur 
FULL OUTER JOIN 
dbo.fn_ReportCharges(2011,'1/1/2012','1/12/2012') ChgCur
ON VisCur.InsTypename = ChgCur.Instypename and VisCur.DeptName = ChgCur.Deptname FULL OUTER JOIN dbo.fn_ReportAdjusts(2011,'1/1/2012','1/12/2012') AdjCur
ON AdjCur.InsTypeName = Chgcur.InsTypeName AND ChgCur.DeptName = AdjCur.DeptName)D 
FULL OUTER JOIN 
(SELECT COALESCE(VisCur.InstypeName,ChgCur.InstypeName,AdjCur.InstypeName) AS InsTypeName,
COALESCE(VisCur.DeptName,ChgCur.DeptName,AdjCur.DeptName) AS DeptName,VisCur.Visits,TotChg, AdjCur.TotAdj
FROM dbo.fn_ReportVisits (2012,'1/1/2012','1/12/2012') VisCur 
FULL OUTER JOIN 
dbo.fn_ReportCharges(2012,'1/1/2012','1/12/2012') ChgCur
ON VisCur.InsTypename = ChgCur.Instypename and VisCur.DeptName = ChgCur.Deptname 
FULL OUTER JOIN dbo.fn_ReportAdjusts(2012,'1/1/2012','1/12/2012') AdjCur
ON AdjCur.InsTypeName = Chgcur.InsTypeName AND AdjCur.DeptName = Chgcur.DeptName)D2 ON D2.Instypename = D.InstypeName AND D.DeptName
= D2.DeptName ORDER BY InstypeName,DeptName

CREATE FUNCTION dbo.fn_ReportVisits (@SvcYear SMALLINT,@FromDtEnter DATETIME,@ToDtEnter DATETIME)
RETURNS TABLE
AS
RETURN
(SELECT COUNT(DISTINCT VoucherNo) AS Visits, IName AS InstypeName, DName AS DeptName FROM [vwChgVisits] INNER JOIN [Vouchersdetail] WITH (NOLOCK) ON 
[vwChgVisits].VoucherNo = [Vouchersdetail].Voucher WHERE
DATEPART(yy,[Vouchersdetail].ServiceDate) = @SvcYear AND [vwChgVisits].DateEntered BETWEEN @FromDtEnter
 AND DATEADD(dd,1,@ToDtEnter) GROUP BY IName, DName)

CREATE FUNCTION dbo.fn_ReportCharges (@SvcYear SMALLINT,@FromDtEnter DATETIME,@ToDtEnter DATETIME)
RETURNS TABLE
AS
RETURN
(SELECT SUM(Charge) AS TotChg, InstypeName, DeptName FROM [vwChgCharges] WITH (NOLOCK) WHERE 
DateEntered BETWEEN @FromDtEnter AND DATEADD(dd,1,@ToDtEnter) AND ProcCode <> '1609' AND 
DATEPART(yy,ServiceDate) = @SvcYear 
GROUP BY  InstypeName, DeptName)

CREATE FUNCTION dbo.fn_ReportAdjusts (@SvcYear SMALLINT,@FromDtEnter DATETIME,@ToDtEnter DATETIME)
RETURNS TABLE
AS
RETURN
(SELECT [vwAdjPmtsReports].InsTypeName AS [InsTypeName], SUM([vwAdjPmtsReports].AdjAmount) AS [TotAdj], 
[vwAdjPmtsReports].DeptName AS [DeptName] FROM [vwAdjPmtsReports] WITH (NOLOCK) WHERE 
[vwAdjPmtsReports].DateEntered BETWEEN @FromDtEnter AND DATEADD(dd,1,@ToDtEnter) 
AND DATEPART(yy,[vwAdjPmtsReports].ServiceDate ) = @SvcYear
GROUP BY [vwAdjPmtsReports].InsTypeName, [vwAdjPmtsReports].DeptName)

DBCC TRACEON(1222,-1)

quote:

---

Originally posted by Transact Charlie

well I can see one aspect that could be causing the reports to be real slow and to cause table scans (and therefore more likely to lock).

All your functions do stuff like this in the where clause:
DATEPART(yy,ServiceDate) = @SvcYear
or DATEPART(yy,[vwAdjPmtsReports].ServiceDate ) = @SvcYear
etc

This can't use any index on the column in question which will lead to scans of the table or CI.

I don't know if it's causing your deadlock issues but it's definitely *not optimal*

---

CREATE FUNCTION dbo.fn_ReportVisits (@SvcYear SMALLINT,@FromDtEnter DATETIME,@ToDtEnter DATETIME)
RETURNS TABLE
AS
RETURN
(SELECT COUNT(DISTINCT VoucherNo) AS Visits, IName AS InstypeName, DName AS DeptName FROM [vwChgVisits] INNER JOIN [Vouchersdetail] WITH (NOLOCK) ON 
[vwChgVisits].VoucherNo = [Vouchersdetail].Voucher WHERE
[Vouchersdetail].ServiceDate BETWEEN CAST('1/1/' + CAST(@SvcYear AS CHAR(4)) AS DATETIME) AND 
CAST('12/31/' + CAST(@SvcYear AS CHAR(4)) AS DATETIME) AND [vwChgVisits].DateEntered BETWEEN @FromDtEnter
 AND DATEADD(dd,1,@ToDtEnter) GROUP BY IName, DName)

quote:

---

Originally posted by GilaMonster

Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)

p.s. Do you know what nolock does? It's not a go-faster switch See - [url]http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx[/url]

---

quote:

---

Originally posted by simondeutsch


Nolock does result in dirty reads. But since this report is generally not run on up-to-the-last-minute data, dirty reads aren't a problem. What it really needs is to run by reading its data without blocking other processes.

---