Dawin Dawin - 1 month ago 12
SQL Question

Update stored procedure no getting column

I have this stored procedure:

SELECT
s.Id AS IdCategory
,s.Description AS Category
,COUNT(s.Description) AS TotalYEAR
INTO #AuditYear
FROM
[dbo].[Audit] aa (NOLOCK)
INNER JOIN
[dbo].[AuditActOrCondition] a (NOLOCK)
ON aa.Id = a.IdAudit
INNER JOIN [dbo].[ActOrCondition] ac (NOLOCK)
ON a.IdActCondition = ac.Id
INNER JOIN [dbo].[ACType] t (NOLOCK)
ON ac.Type = t.Id
INNER JOIN [dbo].[ACSubType] s (NOLOCK)
ON ac.Subtype = s.Id
WHERE YEAR(aa.CreateDate) = (SELECT YEAR FROM [dbo].[KPIS] k1 (NOLOCK) WHERE k1.Id = @Id)
GROUP BY s.Id,s.Description


SELECT
s.Id AS IdCategory
,s.Description AS Category
,COUNT(s.Description) AS TotalMonth
INTO #AuditMonth
FROM
[dbo].[Audit] aa (NOLOCK)
INNER JOIN [dbo].[AuditActOrCondition] a (NOLOCK)
ON aa.Id = a.IdAudit
INNER JOIN [dbo].[ActOrCondition] ac (NOLOCK)
ON a.IdActCondition = ac.Id
INNER JOIN [dbo].[ACType] t (NOLOCK)
ON ac.Type = t.Id
INNER JOIN [dbo].[ACSubType] s (NOLOCK)
ON ac.Subtype = s.Id
WHERE MONTH(aa.CreateDate) = (SELECT MONTH FROM [dbo].[KPIS] k1 (NOLOCK) WHERE k1.Id = @Id)
GROUP BY s.Id,s.Description


SELECT y.IdCategory
,y.Category
,ISNULL(m.TotalMonth,0) AS TotalMonth
,y.TotalYEAR
FROM #AuditYear y
LEFT JOIN #AuditMonth m
ON y.IdCategory = m.IdCategory


And I want to add column called BranchOfficeId so I do a INNER JOIN like:

SELECT
s.Id AS IdCategory
,s.Description AS Category
,COUNT(s.Description) AS TotalYEAR
,b.BranchOfficeId
INTO #AuditYear
FROM
[dbo].[Audit] aa (NOLOCK)
INNER JOIN [dbo].[Department] AS d (NOLOCK)
ON aa.IdDepartment = d.Id
INNER JOIN [dbo].[BranchOffice] AS b (NOLOCK)
ON b.BranchOfficeId = d.BranchOfficeId
INNER JOIN
[dbo].[AuditActOrCondition] a (NOLOCK)
ON aa.Id = a.IdAudit
INNER JOIN [dbo].[ActOrCondition] ac (NOLOCK)
ON a.IdActCondition = ac.Id
INNER JOIN [dbo].[ACType] t (NOLOCK)
ON ac.Type = t.Id
INNER JOIN [dbo].[ACSubType] s (NOLOCK)
ON ac.Subtype = s.Id
WHERE YEAR(aa.CreateDate) = (SELECT YEAR FROM [dbo].[KPIS] k1 (NOLOCK) WHERE k1.Id = @Id)
GROUP BY s.Id,s.Description, b.BranchOfficeId


SELECT
s.Id AS IdCategory
,s.Description AS Category
,COUNT(s.Description) AS TotalMonth
INTO #AuditMonth
FROM
[dbo].[Audit] aa (NOLOCK)
INNER JOIN [dbo].[AuditActOrCondition] a (NOLOCK)
ON aa.Id = a.IdAudit
INNER JOIN [dbo].[ActOrCondition] ac (NOLOCK)
ON a.IdActCondition = ac.Id
INNER JOIN [dbo].[ACType] t (NOLOCK)
ON ac.Type = t.Id
INNER JOIN [dbo].[ACSubType] s (NOLOCK)
ON ac.Subtype = s.Id
WHERE MONTH(aa.CreateDate) = (SELECT MONTH FROM [dbo].[KPIS] k1 (NOLOCK) WHERE k1.Id = @Id)
GROUP BY s.Id,s.Description


SELECT y.IdCategory
,y.Category
,ISNULL(m.TotalMonth,0) AS TotalMonth
,y.TotalYEAR
FROM #AuditYear y
LEFT JOIN #AuditMonth m
ON y.IdCategory = m.IdCategory


But when I update my complex type it only get columns:

public int IdCategory { get; set; }
public string Category { get; set; }
public int TotalMonth { get; set; }
public Nullable<int> TotalYEAR { get; set; }


Can anyone explain me why I cant get BranchOfficeId value? Regards

Answer
SELECT y.IdCategory
        ,y.Category
        ,ISNULL(m.TotalMonth,0) AS TotalMonth
        ,y.TotalYEAR
         FROM #AuditYear y
            LEFT JOIN #AuditMonth m
            ON y.IdCategory = m.IdCategory

The #AuditYear is the result from your first select, then, you only need the column BranchOfficeId in the last select:

SELECT y.IdCategory
        ,y.Category
        ,ISNULL(m.TotalMonth,0) AS TotalMonth
        ,y.TotalYEAR, y.BranchOfficeId
         FROM #AuditYear y
            LEFT JOIN #AuditMonth m
            ON y.IdCategory = m.IdCategory