i have a view that is made up of two tables, one of which is a view of cross joined tables that needs to be filtered. how do I go about filtering this view (or an underlying table in the view, rather) using variables/parameters (session & control) from an asp.net site. everything works, except the part where i need to begin the data being pulled based on a selected date. if i try and filter the final view, i lose the outer joins that give me all of the choices i need. any ideas?
view defiinition (the one that needs to be filtered):
SELECT dbo.aspnet_Users.UserId, dbo.pats_activities.id AS actID, dbo.pats_activities.org_subID, dbo.pats_activities.act_name, dbo.pats_activities.act_desc,
dbo.pats_activities.act_valtype, dbo.pats_months.dday
FROM dbo.pats_activities CROSS JOIN
dbo.pats_months CROSS JOIN
dbo.aspnet_Users
WHERE (dbo.pats_months.dday BETWEEN @bd AND DATEADD(month, 11, @bd))
view definition #2 (the view that is referenced in the asp page, a "crosstab" of sorts):
SELECT dbo.allact_allvaltypes_allsubs_allmonths.UserId, dbo.allact_allvaltypes_allsubs_allmonths.org_subID,
dbo.allact_allvaltypes_allsubs_allmonths.actID, dbo.allact_allvaltypes_allsubs_allmonths.act_name,
dbo.allact_allvaltypes_allsubs_allmonths.act_desc, dbo.allact_allvaltypes_allsubs_allmonths.act_valtype, SUM(ISNULL(dbo.pats_userdata.actVal, 0))
AS TotalVal, SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 1)))) AS M1,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 2)))) AS M2,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 3)))) AS M3,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 4)))) AS M4,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 5)))) AS M5,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 6)))) AS M6,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 7)))) AS M7,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 8)))) AS M8,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 9)))) AS M9,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 10)))) AS M10,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 11)))) AS M11,
SUM(ISNULL(dbo.pats_userdata.actVal, 0) * (1 - ABS(SIGN(DATEPART(mm, dbo.allact_allvaltypes_allsubs_allmonths.dday) - 12)))) AS M12
FROM dbo.pats_userdata RIGHT OUTER JOIN
dbo.allact_allvaltypes_allsubs_allmonths ON dbo.pats_userdata.userID = dbo.allact_allvaltypes_allsubs_allmonths.UserId AND
dbo.pats_userdata.d = dbo.allact_allvaltypes_allsubs_allmonths.dday AND
dbo.pats_userdata.actID = dbo.allact_allvaltypes_allsubs_allmonths.actID
GROUP BY dbo.allact_allvaltypes_allsubs_allmonths.UserId, dbo.allact_allvaltypes_allsubs_allmonths.org_subID,
dbo.allact_allvaltypes_allsubs_allmonths.actID, dbo.allact_allvaltypes_allsubs_allmonths.act_name,
dbo.allact_allvaltypes_allsubs_allmonths.act_desc, dbo.allact_allvaltypes_allsubs_allmonths.act_valtype
