SQL query involving @condition

[darkgen]

Hi, so I'm wanting to use a control condition(date) as the start point for a DATEADD

What is probably confusing the issue(me) is that I'm using MS Web developer as the tool to bring these components together. For instance I have a dropdownlist (sqldatasource1) which contains a date field which is the feeder for the second sqldatasource2 which runs a query where I wish to return rows from the last 7 days from sqldatasource1.

sqldatasource2 as follows:

SELECT DISTINCT
SUM(CASE WHEN No = 'No' THEN 1 ELSE 0 END) AS NoCount, SUM(CASE WHEN Yes= 'Yes' THEN 1 ELSE 0 END) AS YesCount,
Actual_Date
FROM vw_view
WHERE (Actual_Date = @Actual_Date)
GROUP BY Actual_Date

@Actual_Date references the control condition set in sqldatasource1. The above will return rows for that single date, where as I'm wanting to return rows for the previous 7 days from that date.

My logic is telling me I need to adjust the WHERE clause similar to "WHERE (Actual_Date >= DATEADD(day, -7, @Actual_Date())" but that's not working

Any help/guidance much appreciated.

Cheers, Paul.

 

[darkgen]

From within the Query Builder within MS Web Developer, it simply states "Error in WHERE clause near 'WHERE'. Unable to parse query.

IF I use "WHERE CAST(Actual_Date AS DATETIME) >= DATEADD(day, -7, @Actual_Date())" it's throwing out "Must declare the scalar variable "@Actual_Date". However it's definitely a DATETIME field, as it works without issue when I simply compare the Actual_Date with the control variable @Actual_Date.

I have an inkling where I change the line "WHERE (Actual_Date = @Actual_Date)" it breaks the control variable @Actual_Date(sqldatasource1).I.E. the method MS Web Developer links the 2 sqldatasource's.

 

[darkgen]

Ahh man, of course.

"WHERE (Actual_Date >= DATEADD(day, -7, @Actual_Date)" Now returns 7 rows, 1 for each day but something else appear to of gone awry with the count. But I'm a little further forward!

Thank you for all your responses. (No doubt I'll be back.)

Cheers, Paul.

EDIT, that was quick.
Its actually returning a count(SUM) for every weekday (for the year period I'm reporting against). Weird. Almost as if it now ignores the WHERE clause.

OK, So no it is not ignoring _all_ of the WHERE clause. It simply returns all rows to the current date(what's contained within the table), starting from -7 days from @Actual_Date. So halfway there!

 

[darkgen]

It's not how it's being grouped but the logic has somehow gone awry. Example I have a table containing 30 records(days of date). The query is supposed to return 7 rows of data, the preceeding 6 days and given date. However the query returns the preceeding 6 days and any remaining rows in the table.

EG. 2012-01-08 is @Actual_Date:

Expected:
2012-01-02 - 2012-01-08
Actual:
2012-01-02 - 2012-01-30

 

[darkgen]

Nailed it.
"BETWEEN"
Thank you.