"SQL query" queries!

[taval]

I have an SQL query that displays all the customers that backed up within a certain period of time(days). The query looks something like this:<br><br>----<br>StrQuery = &quot;SELECT Customer.CustID,FileDescription.CustId, FileDescription.UploadDate from FileDescription,Customer where FileDescription.CustId = Customer.CustID and not(DateDiff(day, UploadDate, getdate()) &gt; &quot; & &quot;10&quot; & &quot&quot;<br>----<br><br>the sample of the results looks like this:<br><br>-------------------------------------------<br><br>CustID&nbsp;&nbsp;&nbsp;&nbsp;UploadDate<br>------&nbsp;&nbsp;&nbsp;&nbsp;----------<br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/23/00 12:08:01 AM<br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/23/00 12:08:02 AM<br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/24/00&nbsp;&nbsp;6:18:01 AM<br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/24/00&nbsp;&nbsp;6:18:02 AM<br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/25/00 10:28:02 AM<br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/25/00 10:28:02 AM<br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8/2/00 10:58:03 AM<br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8/2/00 10:58:11 AM<br><br>-------------------------------------------<br><br>The above results are for backups within the last 10 days (as the query is setup). My first question is get to query only a unique (one) custID, right now there are 8 custID with the same value showing up? My second question is how do get only the first and last dates backed up within the last 10 days for instance, so from above how do I display 7/23/00 12:08:01 AM and 8/2/00 10:58:11 AM. To help make it clear I want the result to look something like this:<br><br>--------------------------------------------------<br><br>CustID&nbsp;&nbsp;&nbsp;&nbsp;UploadDate(first)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UploadDate(last)<br>------&nbsp;&nbsp;&nbsp;&nbsp;-----------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-----------------<br><br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/23/00 12:08:01 AM&nbsp;&nbsp;&nbsp;8/2/00 10:58:11 AM<br><br>--------------------------------------------------<br><br>Another thing that has bugged me recently is that I have a variable called numDays in my asp set to an integer value. When I modify my SQL query to look like this:<br><br>----<br>StrQuery = &quot;SELECT Customer.CustID,FileDescription.CustId, FileDescription.UploadDate from FileDescription,Customer where FileDescription.CustId = Customer.CustID and not(DateDiff(day, UploadDate, getdate()) &gt; &quot; & &quot;numDays&quot; & &quot&quot;<br>----<br><br>I get the error message saying that there is an invalid column name on that line.<br><br>Anyway, I would be very grateful if anyone could help me with any of my problems.<br><br>Thanks.<br>Taha<br><br><br><br><br><br>

 

[wduty]

The first question I'm not clear on. You say you have eight customer IDs. But what I see is eight <i>records</i> with ONE customer ID.<br>If I'm understanding your second question, there are a number of ways you could do this. The simplest I can think of is a bit of a hack but it would work. In asp/ado the default recordset cursor position is initially at the first row. You could put the first date in a variable right after getting the recordset then move to the last recordset with a movelast method and put the last date in a second variable. Something like:<br><br>[execute your query]<br><font color=red>firstdate = record(&quot;UploadDate&quot<br>record.movelast<br>lastdate = record(&quot;UploadDate&quot<br>response.write (custID&&quot; &quot;&firstdate&&quot; &quot;&lastdate)</font><br><br><br>Is this what you're asking? <br>A more <i>legit</i> way to do this is to write an SQL statement that gets only the two records you want. You can probably do something like that by embedding SELECT statements in the WHERE clause and using MAX, MIN or other functions as palbano suggested to me recently but that's the kind of thing I can't just write out of my head - I would need to work it out by trial and error so if you need that kind of precision you'll have to figure it out.<br>As to your other question... I don't know. Maybe you can try&nbsp;&nbsp;CStr(numdays) but it sounds more like a spelling or odd syntax error. Someone will probably come along with better suggestions. <br><br> <p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br>

 

[Royce]

Not sure if I'm understanding correctly, but isn't your second question solved if you simply take the quotes off of the variable numDays?&nbsp;&nbsp;It looks like you're sending the words numDays as a literal string when you want to send the data it represents.

 

[taval]

royce, sorry that was a mistake I put the following SQL query and run it off ASP:<br><br>StrQuery = &quot;SELECT Customer.CustID,FileDescription.CustId, FileDescription.UploadDate from FileDescription,Customer where FileDescription.CustId = Customer.CustID and not(DateDiff(day, UploadDate, getdate()) &gt; &quot; & numDays & &quot&quot;<br><br>and it says that numFays is an invalid cloumn name, I have declared the integer numDays and it has a value. But why do I get this error message.<br><br>

 

[Guest_imported]

Taval, if you want the output to be in the following format:<br><br>CustID&nbsp;&nbsp;&nbsp;&nbsp;UploadDate(first)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UploadDate(last)<br>------&nbsp;&nbsp;&nbsp;&nbsp;-----------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-----------------<br><br>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/23/00 12:08:01 AM&nbsp;&nbsp;&nbsp;8/2/00 10:58:11 AM<br><br>you could use the syntax like the one I used just for testing purposes:<br><br>Select s.stor_id,min(s.ord_date)as FirstDate , max(s1.ord_date)as LastDate from sales s,sales s1 where s.stor_id = s1.stor_id group by s.stor_id<br><br>I've written that to test it with the Pubs database and I've got the following output in the browser:<br><br>Store_ID&nbsp;&nbsp;&nbsp;FirstDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LastDate <br>6380&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9/13/94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9/14/94 <br>7066&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5/24/93&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9/13/94 <br>7067&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/15/92&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9/14/94 <br>7131&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5/29/93&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9/14/94 <br>7896&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2/21/93&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12/12/93 <br>8042&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/11/93&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9/14/94 <br><br>As you noticed I'm using self-join in SQL.I think it's quite alike to the problem you want to solve and may be that is what you wanted.<br><br><br><br>

 

[taval]

Thanks, I got the query to work now, but I have another problem.<br><br>----------------------<br>SELECT f.CustId, min(f.UploadDate)as FirstDate, max(fd.UploadDate)as LastDate from FileDescription f, FileDescription fd, Customer where f.CustId = fd.CustId and f.FileID = fd.FileID and not(DateDiff(day, f.UploadDate, getdate()) &gt; &quot;& numDays & &quot; ) Group by f.CustId Order By f.CustId<br>-----------------------<br><br>The above shows the details of customers that have backed up between a certain time period. I want to know the details of customers who have NOT backed up within the time period.<br><br>I was guessing one way to do this is to take all the customers that have been backed up away from all of the customers which should leave all the customers that haven't backed up???. How would I do this?<br><br>Grateful for any help given.<br>Thanks.

 

[Guest_imported]

I think it could be the same query, but without <font color=red>not</font>in it :<br><br>SELECT f.CustId, min(f.UploadDate)as FirstDate, max(fd.UploadDate)as LastDate from FileDescription f, FileDescription fd, Customer where f.CustId = fd.CustId and f.FileID = fd.FileID and <u>DateDiff(day, f.UploadDate, getdate())</u> &gt; &quot;& numDays & &quot; Group by f.CustId Order By f.CustId<br>

 

[Guest_imported]

I think it could be the same query, but without <font color=red>not</font> in it :<br><br>SELECT f.CustId, min(f.UploadDate)as FirstDate, max(fd.UploadDate)as LastDate from FileDescription f, FileDescription fd, Customer where f.CustId = fd.CustId and f.FileID = fd.FileID and <u>DateDiff(day, f.UploadDate, getdate())</u> &gt; &quot;& numDays & &quot; Group by f.CustId Order By f.CustId<br>

 

[taval]

I've tried doing it eihtout the not but it does'nt give the correct results, I think the way to do it is to take all of the customers who have backedup away from alll the customers in the db to see the customers that have not backed up?. The thing is I don't know how I would set this query up.<br><br>Grateful for any help.<br>Thanks.