SQL Challenge

[AnthonyJ20]

Hello everyone. What I'm trying to do is use one SQL Statement to pull not only the current month's data but also that of the previous month. Really is it possible, and if so, What in the world would it look like. Below is the SQL statement that I want to transform. I currently have two variables called MyDate and MyDate2 which control what the output will be. Anyone with an idea?

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
& "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND VolDate >= #" & MyDate & "# AND VolDate <= #" & MyDate2 & "# AND FullPay + ImageItems > 0 " _
& "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "


currentArtist = ""
set rs = Cnt.execute(sql)

 

[DotNetGnat]

i would suggest you to use the dateadd() function...use the 'mm' for denoting which month you want...

-DNG

 

[AnthonyJ20]

Thanks for responding DotNetGnat. I've heard of this function before but I'm not familiar with how to use it. Do know of any examples of how I could include this function in my SQL?

 

[DotNetGnat]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_3vtw.asp

so what you need to do is just put a condition to identify which month you want...if you want the current month then you need to supply a value of 0 for number parameter...

and if you want previous month then you need to supply value -1 for number parameter in the dateadd() function...

hope this helps...post back if you enocunter any problems...

-DNG

 

[AnthonyJ20]

Thanks, I'll give it a shot and let you know how it goes.

 

[DotNetGnat]

ok cool...i am sure that shud work out for you...but anyways post back if you face any problems..

-DNG

 

[AnthonyJ20]

Okay DotNetGnat, I destroyed the whole thing. :-( I just can't get the hang of it. Here I am at square one again.

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
& "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND VolDate >= #" & MyDate & "# AND VolDate <= #" & MyDate2 & "# AND FullPay + ImageItems > 0 " _
& "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "

currentArtist = ""
set rs = Cnt.execute(sql)

If you don't mind, can give a hint of how to make this work? Brain cells not handling things well right now.

 

[DotNetGnat]

how does your variables MyDate and MyDate2 look? and how do you decide which month(current or previous) to show

-DNG

 

[AnthonyJ20]

Well, so far I've only been able to get the current month using the request.form variable. The requested date would be in this format.10/12/2005. Singles digit dates the format is 10/2/2005.

So MyDate would be 10/1/2005
and MyDate 2 would be 10/17/2005

The trick is to get last months data as well from the same query.

 

[DotNetGnat]

ok...try something like this:

for current month...

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
    & "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND "_ 
& "VolDate BETWEEN #" & MyDate & "# AND  #" & MyDate2 & "# AND"_ 
"FullPay + ImageItems > 0 " _
    & "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "

for previous month

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
    & "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND "_ 
& "VolDate BETWEEN DateAdd('mm',-1,#" & MyDate & "#) AND  DateAdd('mm',-1,#" & MyDate2 & "#) AND"_ 
"FullPay + ImageItems > 0 " _
    & "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "

do a response.write and see if your query is getting constructed correctly the way you want...

-DNG

 

[AnthonyJ20]

Okay DotNetGnat, I've made some changes to the SQL and everything seems to work up to executing the recordset. I'm getting the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Invalid procedure call
/NewPercentSpreadSheet.asp, line 51

Here is the SQL with line 51 in bold. What do you think is wrong?


Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
& "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND VolDate BETWEEN DateAdd('mm',-1,#" & MyDate & "#) AND DateAdd('mm',-1,#" & MyDate2 & "#) AND FullPay + ImageItems > 0 "_
& "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "

currentArtist = ""
set rs = Cnt.execute(sql)

 

[AnthonyJ20]

DotNetGnat, is the mm in the DateAdd function for calling up a month? If so that may be part of my problem since I don't have a field in the table for the month. Of course I could be way wrong.

 

[DotNetGnat]

oops...try this:

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
    & "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND VolDate BETWEEN DateAdd("m",-1,#" & MyDate & "#) AND  DateAdd("m",-1,#" & MyDate2 & "#) AND FullPay + ImageItems > 0 "_ 
    & "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "
       
   currentArtist = "" 
    set rs = Cnt.execute(sql)

-DNG

 

[AnthonyJ20]

It's got to be close now. I'm getting an expected end of statement error. What am I missing?

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
& "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND VolDate BETWEEN DateAdd("m",-1,#" & MyDate & "#) AND DateAdd("m",-1,#" & MyDate2 & "#) AND FullPay + ImageItems > 0 "_
& "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "

currentArtist = ""
set rs = Cnt.execute(sql)

 

[DotNetGnat]

how about this:

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
    & "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND VolDate BETWEEN "#"&DateAdd("m",-1,#" & MyDate & "#)&"#" AND  "#"&DateAdd("m",-1,#" & MyDate2 & "#)&"#" AND FullPay + ImageItems > 0 "_ 
    & "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "

also do a response.write Sql

-DNG

 

[AnthonyJ20]

Okay, here is the error that came back.

Expected end of statement
/NewPercentSpreadSheet.asp, line 50, column 83
& "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND VolDate BETWEEN "#"&DateAdd("m",-1,#" & MyDate & "#)&"#" AND "#"&DateAdd("m",-1,#" & MyDate2 & "#)&"#" AND FullPay + ImageItems > 0 "_
----------------------------------------------------------------------------------^

 

[DotNetGnat]

put parenthesis...

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
    & "Where ((CustomerBoxes.CustomerID = tblDataTableNew.DataTable) AND (VolDate BETWEEN "#"&DateAdd("m",-1,#" & MyDate & "#)&"#" AND  "#"&DateAdd("m",-1,#" & MyDate2 & "#)&"#") AND (FullPay + ImageItems > 0)) "_ 
    & "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "

-DNG

 

[AnthonyJ20]

I forgot to mention that I tried responsing the sql so that I could see it but the error stops is short of that.

 

[AnthonyJ20]

You know this is killing me that it's so close. Still the same error.


Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/NewPercentSpreadSheet.asp, line 50, column 87
& "Where ((CustomerBoxes.CustomerID = tblDataTableNew.DataTable) AND (VolDate BETWEEN "#"&DateAdd("m",-1,#" & MyDate & "#)&"#" AND "#"&DateAdd("m",-1,#" & MyDate2 & "#)&"#") AND (FullPay + ImageItems > 0)) "_
--------------------------------------------------------------------------------------^

 

[DotNetGnat]

try this:

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
    & "Where ((CustomerBoxes.CustomerID = tblDataTableNew.DataTable) AND (VolDate BETWEEN #&DateAdd("m",-1,#" & MyDate & "#)&# AND  #&DateAdd("m",-1,#" & MyDate2 & "#)&#) AND (FullPay + ImageItems > 0)) "_ 
    & "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "

also comment this line:

'set rs = Cnt.execute(sql)

and then do a

response.write sql to see the actual querystring


-DNG