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.

 

[tom_e]

What's happening when you try to run the adjusted WHERE clause?
Have you tried putting the DATEADD into your SELECT statement and then using that value in the WHERE clause?

 

[kkelly]

SYSDATE -7 ?

 

[tom_e]

SYSDATE -7 ?

That'll just give today's date -7 wouldn't it?

 

[GravyMonster]

SysDate is MySQL isn't it? Given the OP mentions MS Web Developer, I assume this is MSSQL, inwhich case you want GETDATE() or GETUTCDATE().

Either way 'WHERE (Actual_Date >= DATEADD(day, -7, @Actual_Date())' should work - assuming that the Actual_Date field is of datetime type. If it's (n)varchar for example, then you'll need to use a CAST:

WHERE CAST(Actual_Date AS DATETIME) >= DATEADD(day, -7, @Actual_Date())

OP, when you say 'doesn't work' what exactly do you mean? Errors, or incorrect data returned?

 

[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.

 

[AMMUT]

Might be stating the obvious but do you need the '()' after the @Actual_Date? You didn't have it in the original where clause.

So it would become:

WHERE (Actual_Date >= DATEADD(day, -7, @Actual_Date)

Also, you might have to run a select to get the DATEADD part to work correctly:

WHERE (Actual_Date >= SELECT DATEADD(day, -7, @Actual_Date)

 

[cotton31]

It's not your opening and closing parenthesis after your variable is it? e.g. @Actual_Date() just needs to be @Actual_Date

Might be stating the obvious but do you need the '()' after the @Actual_Date? You didn't have it in the original where clause.

So it would become:

WHERE (Actual_Date >= DATEADD(day, -7, @Actual_Date)

Gah, beaten!

 

[GravyMonster]

Oh, how did I completely miss the brackets? That'll do it

 

[tom_e]

It's always silly little things like that, the hours I've wasted staring at code trying to find the little syntax error I've made that's brought my reports to their knees is staggering!

 

[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!

 

[HungryHippos]

If you want to group by date, you will probably want to run this in your Query:

GROUP BY CAST(Actual_Date AS date)

This should group by the date rather than the date and time.

 

[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

 

[cotton31]

So don't you need:

WHERE (Actual_Date BETWEEN DATEADD(day, -7, @Actual_Date) AND @Actual_Date)

assuming I've understood your problem correctly?

 

[darkgen]

Nailed it.
"BETWEEN"
Thank you.

 

[cotton31]

No worries