I am trying to return data by date but I also need to return data by date "and" by ID. I've queried various select statements but always get only one days worth of data and the date is repeated many times. I only want to return data for today only but with ID as the other variable.
For example I want only the shows for today by date and by ID. ID of course being the key in the DB. Below I will show you a code block followed by a text version of what it looks like in the browser when tested.
Code Snippet
<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Kuow\Kuow.UDL"
set recProgram = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101))"
'strSQL = "SELECT *, Air_Date AS Expr1, Unit AS Expr2 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) AND (Unit = 'TB')"
recProgram.Open strSQL,con
%>
<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>
Output:
ID Unit Subject Title Long_Summary Body_Text Related_Events Air_Date AudioLink
(Reading across the screen from left to right)
1234 WK1 Subject Title a summary some body text Event text 4/13/2007 wkdy20070413-a.rm
Here is the URL used for testing:
http://Test Server IP/test/defaultweekday2.asp
I need to be able to append to this URL an ID number so that not only do I get content by Air_Date but also by ID.
http://Test Server IP/test/defaultweekday2.asp?ID=1234
How to do this?
You might want to look in Books Online about the usage of GROUP BY.
Code Snippet
GROUP BY convert( varchar(10), getdate(), 101 )) , Unit
|||Ok I will do that but for now is your example a working example? If not what other examples could I try?|||
It 'should' work IF you change the SELECT to
"SELECT *, Air_Date = convert( varchar(10), getdate(), 101 )), Unit FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"
|||I tested your suggested by replacing the second half of my select query with your code starting with WHERE...
When I tested it I got this
Microsoft OLE DB Provider for SQL Server error '80040e14'
GROUP BY expressions must refer to column names that appear in the select list.
/test/defaultweekday2.asp, line 24
Code Snippet
strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"
|||Ok I see what your getting at however I just tested it in my browser and got this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near ')'.
/test/defaultweekday2.asp, line 22
Code Snippet
strSQL = "SELECT *, Air_Date = convert( varchar(10), getdate(), 101 )), Unit FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"
I counted the ( ) to make sure there was the correct number of left and rights ones. Does it not like the end of the line or what?|||
As the error message indicates (and you might want to read up on using GROUP BY), any column in the GROUP BY MUST also be in the SELECT list.
Your GROUP BY includes Unit, and the SELECT list does not.
I think that the query I posted earlier 'should' work. But this alteration will not.
GROUP BY requires ALL columns in the SELECT list to EITHER be in the GROUP BY clause, or be aggregations. Trying to select all columns with a [SELECT * ] will not work.
I suggest that you start out small, perhaps with the query that I posted earlier, try to understand how GROUP BY works, and then expand your query a small piece at a time.
|||Arnie -
Sounds good. I'm reading up on it now as I learn and thank you for your most recent reply. Your thinking is helping me think. I'm sure it's obvious I'm new to SQL. I'll be glad when I get good enough that I can provide the people I work for and with the answers they seek in a relatively quick turnaround.
There is nothing worse then having work that is over your head and your figuring out how to do it while your solving real world business problems. Can be stressful. But hey it's one way to ensure I'll remember it.
