CODEWITHSUNDEEP
sqlsql-servert-sql
BattlFrog
BattlFrog

Reputation: 3397

Variable inside subquery not receiving variable value

I have never tried to pass a variable value into a subquery before, and I seem to be going about it wrong. I am not receiving any errors, but I also get no results, whereas if I hard-code the values for startdate, enddate & role into the subquery, it runs correctly.

================edit =======================

My bad, I posted the earlier test script I was playing with which was indeed full of errors, sorry for that. Here is the actual code I am working with, in full:

DECLARE @Role varchar,
    @StartDate datetime,
    @Enddate datetime;

SET     @Role = 'Volunteer';
SET     @StartDate = DATEADD(d, -100, getdate());
SET     @Enddate = getdate();

SELECT rolename, Category, [1] as [1 Day],[2] as [2 Days],[3] as [3 Days],[4] as [4 Days],[5] as [5 Days],[6] as [6 Days],[7] as [7 Days]  FROM
(SELECT    CriminalHistoryID, vps.RoleName, 'Rec2PreClr' as Category,
        DATEDIFF(d, chc.ReceiveDate, chc.PreClrDate) as DaysDif
  FROM  dbo.v_PersonStatus vps
              INNER JOIN dbo.CriminalHistoryCheck chc
                    ON vps.PersonID = chc.PersonID
  WHERE chc.ReceiveDate BETWEEN @StartDate AND @Enddate AND vps.rolename = @Role and DATEDIFF(d, chc.ReceiveDate, chc.PreClrDate) BETWEEN 0 and 7
  UNION
  SELECT      CriminalHistoryID, vps.RoleName, 'PreClr2Clr' as Category,
              DATEDIFF(d, chc.PreClrDate, chc.FinalDate) as DaysDif
  FROM  dbo.v_PersonStatus vps
              INNER JOIN dbo.CriminalHistoryCheck chc
                    ON vps.PersonID = chc.PersonID
  WHERE chc.ReceiveDate BETWEEN @StartDate AND @Enddate AND vps.rolename = @Role AND DATEDIFF(d, chc.PreClrDate, chc.FinalDate) BETWEEN 0 and 7
  UNION
  SELECT      CriminalHistoryID, vps.RoleName, 'Rec2Clr' as Category,
              DATEDIFF(d, chc.ReceiveDate, FinalDate) as DaysDif
  FROM  dbo.v_PersonStatus vps
              INNER JOIN dbo.CriminalHistoryCheck chc
                    ON vps.PersonID = chc.PersonID
  WHERE chc.ReceiveDate BETWEEN @StartDate AND @Enddate AND vps.rolename = @Role AND DATEDIFF(d, chc.ReceiveDate, FinalDate) BETWEEN 0 and 7
  ) UNIONqry
PIVOT
(count(CriminalHistoryID) for DaysDif in ([1],[2],[3],[4],[5],[6],[7])) pvt
Order by RoleName, Category

The values for @Startdate, @Enddate & @Role do not seem to be getting into the subquery. Do I need to do something to tell the sub to look outside the box?

Upvotes: 0

Views: 143

Answers (1)

What have you tried
What have you tried

Reputation: 11148

Two issues:

  1. You're not assigning a value to volStatus
  2. It should be @volstatus and not @ volstatus - no space

So, change:

WHERE chc.ReceiveDate BETWEEN @StartDate AND @Enddate AND vps.rolename = @ VolStatus and DATEDIFF(d, chc.ReceiveDate, chc.PreClrDate) BETWEEN 0 and 7

To:

WHERE chc.ReceiveDate BETWEEN @StartDate AND @Enddate AND vps.rolename = @VolStatus and DATEDIFF(d, chc.ReceiveDate, chc.PreClrDate) BETWEEN 0 and 7

Upvotes: 1

Related Questions