DECLARE @CurrentYear smallint -- Current year
DECLARE @Quarter varchar (5)
SET @CurrentYear = (SELECT DATENAME(yy, GETDATE()))
SET @Quarter = 'Q' + CAST ((SELECT DATENAME(qq, GETDATE())) as varchar(5)) 
SELECT v.Auditor_ID,
	CASE 
		WHEN t.ScheduledYear is NULL THEN @CurrentYear ELSE t.ScheduledYear END AS ScheduledYear,
	CASE
		WHEN t.ScheduledQuarter is NULL THEN @Quarter ELSE t.ScheduledQuarter END AS ScheduledQuarter,
COUNT (t.Audit_ID) as AuditCount,
tb.Auditor_ID
FROM (dbo.v_AuditorName as v
FULL OUTER JOIN  dbo.jntbl_AuditToAuditor as j ON j.Auditor_ID= v.Auditor_ID
FULL OUTER JOIN dbo.tbl_Audit as t on j.Audit_ID = t.Audit_ID)
FULL JOIN dbo.v_AuditorName as tb on tb.Auditor_ID = v.Auditor_ID
WHERE (t.ScheduledYear= @CurrentYear or t.ScheduledYear is NULL)
AND (t.ScheduledQuarter = @Quarter or t.ScheduledQuarter is NULL)
GROUP BY v.Auditor_ID, t.ScheduledYear, t.ScheduledQuarter, tb.Auditor_ID
ORDER BY t.ScheduledYear ASC
PRINT @Quarter
PRINT @CurrentYear

-- I need to run a query to see the number of audits an auditor has for the quarter.

CREATE TABLE #tbl_Audit ([Audit_ID] int, [Quarter] char(2), [Year] char(4))
GO

INSERT INTO #tbl_Audit ([Audit_ID], [Quarter], [Year])
SELECT 1231, 'Q4', 2011 UNION ALL
SELECT 1232, 'Q1', 2012 UNION ALL
SELECT 1233, 'Q1', 2012 UNION ALL
SELECT 1234, 'Q1', 2012 UNION ALL
SELECT 1235, 'Q1', 2012 UNION ALL
SELECT 1236, 'Q1', 2012 UNION ALL
SELECT 1237, 'Q1', 2012
GO

CREATE TABLE #jntbl_AuditToAuditor ([Audit_ID] int, [Auditor_ID] int)
GO

INSERT INTO #jntbl_AuditToAuditor ([Audit_ID], [Auditor_ID])
SELECT 1231, 1 UNION ALL
SELECT 1232, 2 UNION ALL
SELECT 1233, 3 UNION ALL
SELECT 1234, 2
GO

	SELECT ad.[Auditor_ID], au.[Year], au.[Quarter], COUNT(*)
	  FROM #jntbl_AuditToAuditor ad
INNER JOIN #tbl_Audit au
		ON ad.[Audit_ID] = au.[Audit_ID]
  GROUP BY ad.[Auditor_ID], au.[Year], au.[Quarter]
GO

DROP TABLE #jntbl_AuditToAuditor, #tbl_Audit
GO

DECLARE @CurrentYear smallint -- Current year
DECLARE @Quarter varchar (5)
SET @CurrentYear = (SELECT DATENAME(yy, GETDATE()))
SET @Quarter = 'Q' + CAST ((SELECT DATENAME(qq, GETDATE())) as varchar(5)) 
SELECT v.Auditor_ID,
	CASE 
		WHEN t.ScheduledYear is NULL THEN @CurrentYear ELSE t.ScheduledYear END AS ScheduledYear,
	CASE
		WHEN t.ScheduledQuarter is NULL THEN @Quarter ELSE t.ScheduledQuarter END AS ScheduledQuarter,
COUNT (t.Audit_ID) as AuditCount,
tb.Auditor_ID
FROM (dbo.v_AuditorName as v
FULL OUTER JOIN  dbo.jntbl_AuditToAuditor as j ON j.Auditor_ID= v.Auditor_ID
FULL OUTER JOIN dbo.tbl_Audit as t on j.Audit_ID = t.Audit_ID)
AND  (t.ScheduledYear= @CurrentYear or t.ScheduledYear is NULL)
AND (t.ScheduledQuarter = @Quarter or t.ScheduledQuarter is NULL)
FULL JOIN dbo.v_AuditorName as tb on tb.Auditor_ID = v.Auditor_ID
GROUP BY v.Auditor_ID, t.ScheduledYear, t.ScheduledQuarter, tb.Auditor_ID
ORDER BY t.ScheduledYear ASC
PRINT @Quarter
PRINT @CurrentYear

quote:

---

Originally posted by visakh16

Make this small change and see if it fits your requirement?

DECLARE @CurrentYear smallint -- Current year
DECLARE @Quarter varchar (5)
SET @CurrentYear = (SELECT DATENAME(yy, GETDATE()))
SET @Quarter = 'Q' + CAST ((SELECT DATENAME(qq, GETDATE())) as varchar(5)) 
SELECT v.Auditor_ID,
	CASE 
		WHEN t.ScheduledYear is NULL THEN @CurrentYear ELSE t.ScheduledYear END AS ScheduledYear,
	CASE
		WHEN t.ScheduledQuarter is NULL THEN @Quarter ELSE t.ScheduledQuarter END AS ScheduledQuarter,
COUNT (t.Audit_ID) as AuditCount,
tb.Auditor_ID
FROM (dbo.v_AuditorName as v
FULL OUTER JOIN  dbo.jntbl_AuditToAuditor as j ON j.Auditor_ID= v.Auditor_ID
FULL OUTER JOIN dbo.tbl_Audit as t on j.Audit_ID = t.Audit_ID)
AND  (t.ScheduledYear= @CurrentYear or t.ScheduledYear is NULL)
AND (t.ScheduledQuarter = @Quarter or t.ScheduledQuarter is NULL)
FULL JOIN dbo.v_AuditorName as tb on tb.Auditor_ID = v.Auditor_ID
GROUP BY v.Auditor_ID, t.ScheduledYear, t.ScheduledQuarter, tb.Auditor_ID
ORDER BY t.ScheduledYear ASC
PRINT @Quarter
PRINT @CurrentYear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

Auditor_ID| ScheduledYear|ScheduledQuarter|AuditCount|Auditor_ID
NULL	          2012	          Q1	       8	NULL

quote:

---

Originally posted by visakh16

which is the master table for Auditor?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

which is the master set?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

then start with tbl_Auditor and LEFT JOIN other tables to it if you want to include all auditor info in output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

it wont if you dont have any where conditions based on left joined table. if at all you need to have additional filters move them to ON condition of LEFT JOINs rather than in WHERE condition

read the below article for the idea

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

CREATE FUNCTION v_AuditorCount (@Quarter VARCHAR(5), @CurrentYear SMALLINT)
RETURNS TABLE 
AS 
RETURN

	SELECT   v.Auditor_ID,
	CASE 
		WHEN j.ScheduledYear is NULL THEN @CurrentYear ELSE j.ScheduledYear END AS ScheduledYear,
	CASE
		WHEN j.ScheduledQuarter is NULL THEN @Quarter ELSE j.ScheduledQuarter END AS ScheduledQuarter,
	COUNT (t.Audit_ID) AS AuditCount
	FROM dbo.v_AuditorName AS v
	INNER JOIN 
		(	SELECT Auditor_ID, a.Audit_ID, ScheduledQuarter, ScheduledYear
		FROM
		(	SELECT v.Auditor_ID, NULL AS [Audit_ID]
			FROM dbo.v_AuditorName AS v
			UNION ALL
			SELECT jt.Auditor_ID, jt.Audit_ID
			FROM dbo.jntbl_AuditToAuditor AS jt 
		)	AS jt LEFT JOIN tbl_Audit AS a ON jt.Audit_ID = a.Audit_ID 
		) AS j 
		ON j.Auditor_ID = v.Auditor_ID
	LEFT OUTER JOIN 
		dbo.tbl_Audit AS t ON j.Audit_ID = t.Audit_ID
	WHERE  (t.ScheduledYear= @CurrentYear or t.ScheduledYear is NULL)
	AND (t.ScheduledQuarter = @Quarter or t.ScheduledQuarter is NULL)
	GROUP BY v.Auditor_ID,j.ScheduledYear, j.ScheduledQuarter
GO


SELECT Auditor_ID, ScheduledYear,ScheduledQuarter,SUM (AuditCount) as AuditCount
FROM v_AuditorCount('Q3', 2010)
GROUP BY Auditor_ID, ScheduledYear,ScheduledQuarter