I am filtering out rows based on site ID form a maste table which is a foriegn key in all other table. So basically I am filtering out that master table and using join filter I extended the filter to the child tables. But my problem is I have thousands of rows even for a particular site. So, I would like to further filter the rows using a date field in the child tables. But how can I place that date filtering in addition to the extended join filter. If I add another filter for the same table for date column, it seems the two filterings for the same table does not get the AND behaviour. How can I specify two filterings then?
This is nutty. I never got this error on my local machine. The only lower case m in the sql is by near the variable Ratingsum like in line 59. [SqlException (0x80131904): Incorrect syntax near 'm'.An expression of non-boolean type specified in a context where a condition is expected, near 'type'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +269 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 view_full_article.btnRating_Click(Object Src, EventArgs E) +565 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746</pre></code> Here is my button click sub in its entirety: 1 Sub btnRating_Click(ByVal Src As Object, ByVal E As EventArgs) 2 'Variable declarations... 3 Dim articleid As Integer 4 articleid = Request.QueryString("aid") 5 Dim strSelectQuery, strInsertQuery As String 6 Dim strCon As String 7 Dim conMyConnection As New System.Data.SqlClient.SqlConnection() 8 Dim cmdMyCommand As New System.Data.SqlClient.SqlCommand() 9 Dim dtrMyDataReader As System.Data.SqlClient.SqlDataReader 10 Dim MyHttpAppObject As System.Web.HttpContext = _ 11 System.Web.HttpContext.Current 12 Dim strRemoteAddress As String 13 Dim intSelectedRating, intCount As Integer 14 Dim Ratingvalues As Decimal 15 Dim Ratingnums As Decimal 16 Dim Stars As Decimal 17 Dim Comments As String 18 Dim active As Boolean = False 19 Me.lblRating.Text = "" 20 'Get the user's ip address and cast its type to string... 21 strRemoteAddress = CStr(MyHttpAppObject.Request.UserHostAddress) 22 'Build the query string. This time check to see if IP address has already rated this ID. 23 strSelectQuery = "SELECT COUNT(*) As RatingCount " 24 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 25 strSelectQuery += " AND ip = '" & strRemoteAddress & "'" 26 'Open the connection, and execute the query... 27 strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString 28 conMyConnection.ConnectionString = strCon 29 conMyConnection.Open() 30 cmdMyCommand.Connection = conMyConnection 31 cmdMyCommand.CommandType = System.Data.CommandType.Text 32 cmdMyCommand.CommandText = strSelectQuery 33 intCount = cmdMyCommand.ExecuteScalar() 34 intSelectedRating = Int(Me.rbRating.Text) 35 conMyConnection.Close() 36 'Close the connection to release these resources... 37 38 If intCount = 0 Then 'The user hasn't rated the article 39 'before, so perform the insert... 40 strInsertQuery = "INSERT INTO tblArticleRating (rating, ip, itemID, comment, active) " 41 strInsertQuery += "VALUES (" 42 strInsertQuery += intSelectedRating & ", '" 43 strInsertQuery += strRemoteAddress & "', " 44 strInsertQuery += articleid & ", '" 45 strInsertQuery += comment.Text & "', '" 46 strInsertQuery += active & "'); " 47 cmdMyCommand.CommandText = strInsertQuery 48 conMyConnection.Open() 49 cmdMyCommand.ExecuteNonQuery() 50 conMyConnection.Close() 51 Me.lblRating.Text = "Thanks for your vote!" 52 Comments = comment.Text.ToString 53 54 If Len(Comments) > 0 Then 55 emailadmin(comment.Text, articleid) 56 End If 57 'now update the article db for the two values but first get the correct ratings for the article 58 strSelectQuery = _ 59 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount " 60 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 61 conMyConnection.Open() 62 cmdMyCommand.CommandText = strSelectQuery 63 dtrMyDataReader = cmdMyCommand.ExecuteReader() 64 dtrMyDataReader.Read() 65 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString) 66 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString) 67 Stars = Ratingvalues / Ratingnums 68 conMyConnection.Close() 69 'Response.Write("Values: " & Ratingvalues) 70 'Response.Write("Votes: " & Ratingnums) 71 72 UpdateRating(articleid, Stars, Ratingnums) 73 Else 'The user has rated the article before, so display a message... 74 Me.lblRating.Text = "You've already rated this article" 75 End If 76 strSelectQuery = _ 77 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount " 78 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 79 conMyConnection.Open() 80 cmdMyCommand.CommandText = strSelectQuery 81 dtrMyDataReader = cmdMyCommand.ExecuteReader() 82 dtrMyDataReader.Read() 83 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString) 84 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString) 85 Stars = Ratingvalues / Ratingnums 86 If (Ratingnums = 1) And (Stars <= 1) Then 87 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote" 88 ElseIf (Ratingnums = 1) And (Stars > 1) Then 89 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote" 90 ElseIf (Ratingnums > 1) And (Stars <= 1) Then 91 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes" 92 ElseIf (Ratingnums > 1) And (Stars > 1) Then 93 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes" 94 End If 95 96 'Response.Write(String.Format("{0:f2}", Stars)) 97 'Response.Write("Values: " & Ratingvalues) 98 'Response.Write("Votes: " & Ratingnums) 99 If (Stars > 0) And (Stars <= 0.5) Then 100 Me.Rating.ImageUrl ="./images/rating/05star.gif" 101 ElseIf (Stars > 0.5) And (Stars < 1.0) Then 102 Me.Rating.ImageUrl = "./images/rating/05star.gif" 103 ElseIf (Stars >= 1.0) And (Stars < 1.5) Then 104 Me.Rating.ImageUrl = "./images/rating/1star.gif" 105 ElseIf (Stars >= 1.5) And (Stars < 2.0) Then 106 Me.Rating.ImageUrl = "./images/rating/15star.gif" 107 ElseIf (Stars >= 2.0) And (Stars < 2.5) Then 108 Me.Rating.ImageUrl = "./images/rating/2star.gif" 109 ElseIf (Stars >= 2.5) And (Stars < 3.0) Then 110 Me.Rating.ImageUrl = "./images/rating/25star.gif" 111 ElseIf (Stars >= 3.0) And (Stars < 3.5) Then 112 Me.Rating.ImageUrl = "./images/rating/3star.gif" 113 ElseIf (Stars >= 3.5) And (Stars < 4.0) Then 114 Me.Rating.ImageUrl = "./images/rating/35star.gif" 115 ElseIf (Stars >= 4.0) And (Stars < 4.5) Then 116 Me.Rating.ImageUrl = "./images/rating/4star.gif" 117 ElseIf (Stars >= 4.5) And (Stars < 5.0) Then 118 Me.Rating.ImageUrl = "./images/rating/45star.gif" 119 ElseIf (Stars >= 4.5) And (Stars <= 5.0) Then 120 Me.Rating.ImageUrl = "./images/rating/5star.gif" 121 End If 122 dtrMyDataReader.Close() 123 conMyConnection.Close() 124 End Sub
If you want to reduplicate the error, click over here and try to submit a rating: http://www.link-exchangers.com/view_full_article.aspx?aid=51 Thanks for helping me figure this out.
Happy Friday! A while since I have posted a question, and this one is probably real easy. I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
I like to define my procedure parameter type to match a referenced table colum type, similar to PL/SQL "table.column%type" notation. That way, when the table column is changes, I would not have to change my stored proc. Any suggestion?
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected we need to store values as is
Error: base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range. --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) at MS.Internal.MS COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default); DataRow dr; DataTable dt = new DataTable(); DataColumn dc;
try {
dc = dt.Columns.Add("Availability", typeof(decimal)); €¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal €¦€¦€¦.
Implement time interval type in the form of a user defined type in SS2k8r2? Specifically an interval type described in the book Temporal Data and the Relational Model by C. J. Date at all. As an example, an interval is below:
1/4/2006:1/10/2006
which would mean the time period from 1/4 to 1/10.
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Hi all, I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine. The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings. Any suggestions. Best Regards Wafi Mohtaseb
Hello Friends How are you?? Friends i am getting problem in SQL Server 2005. I am deployng web application on production server as well as Databse also. In production server i inserted new field in all tables which is rowguid and its type is uniqueidentifier. The default binding for this field is newsequentialid(). In some pages it works ok but in some places it generates error like 'Conversion from type 'DBNull' to type 'String' is not valid'. Can anybody help me to solve this problem. Its urgent so plz reply me as soon as possible. I'll be very thankfull to you. Thanks in Advance. Regards,
I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!
Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:
Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?
Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.
Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...
Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.
E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?
I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:
SELECT (
SELECT MT.MessageId 'MessageId', MT.MessageType 'MessageType', FROM MessageTable MT ORDER BY MT.messageid desc FOR XML PATH('MessageStatus'), TYPE
) FOR XML PATH('Report'), TYPE
For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error:
<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>
Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections?
I need help with filtering a specific set of numbers. I have a Sql database that is connected to my sql report I have created a tsql statement that pulls a clients name, PO, and invoice number. The prblem I am having is I have 2 different types of invoice numbers one number looks like 123456-1234-T the other looks like 123455-1234-L I need to beable to pull only the invoices with T on one report and L on another report can some on show me how I can sort these in a tsql script
This may be simple, but I can't figure a way to do this. I have the following data returned to a table and need to sum only the items where HDMethod=0 in the table footer. For some reason, something like:
=Sum(Iif(Fields!HDMethod.Value=0, Fields!BDExtended.Value, Nothing)) returns all the rows. There is a LEFT OUTER JOIN: dbo.[Billing Detail].Item = dbo.[History Detail].Item between the tables touched in the query if that helps.
I am having a UDT problem. When I run the Create Type command. I receive the "could not find type" error. I have seen other posts on here related to the default namespace in VB.NET. When I add the namespace I receive "Incorrect syntax near '.'." What is the format of the EXTERNAL NAME parameter. Thanks for any help. Code below...
Incorrect Syntax Error:
DROP ASSEMBLY CadSqlUdtsCREATE ASSEMBLY CadSqlUdtsAUTHORIZATION [dbo]FROM 'E:CAD.NETCADUDTsReleaseCadSqlUdts.dll'WITH PERMISSION_SET = SAFEGOCREATE TYPE dbo.ReportingAreaUDTEXTERNAL NAME CadSqlUdts.[CadSqlUdts.CadSqlUdts].ReportingAreaUDT;GO
Could Not Find Type Error:
DROP ASSEMBLY CadSqlUdtsCREATE ASSEMBLY CadSqlUdtsAUTHORIZATION [dbo]FROM 'E:CAD.NETCADUDTsReleaseCadSqlUdts.dll'WITH PERMISSION_SET = SAFEGOCREATE TYPE dbo.ReportingAreaUDTEXTERNAL NAME CadSqlUdts.ReportingAreaUDT;GO What's up??
a text input for filtering a gridview that displays the data an SqlDataSource that contains the query. Users can either enter something into the text input or leave it blank. Depending on that, the gridview should either display all data (unfiltered, because nothing was entered into the text field) or filtered data (when something is entered). Now my problem is in defining the query in the SqlDataSource. I could do something like this: SELECT * FROM myTable WHERE myField = @p1; and then add in the appropriate <asp:ControlParameter /> under the <SelectParameters> tag. However, this sorta "fixes" the filter. Regardless of whether users actually type something in or not, the filter is in effect. I want it in such a way that if users do not type in anything, the query essentially becomes: SELECT * FROM myTable; Is there any way to achieve this? Thanks in advance, jason
I'm not sure if this is possible and have been having trouble figuring out the code to do this. I am assigning row_number to a gridview. I then want to filter the results with a dropdown. I am able to get the filter to filter the status but it either renumbers the gridview or it leaves the row numbers blank. Is there a way to have the row_numbers stick to the gridview when I filter? Example below. Thanks Normal:IssueNumber(row_number), Status1, Open2, Open3, Closed4, Open5, Closed "Open" Filter:IssueNumber(row_number), Status1, Open2, Open4, Open "Closed" Filter:IssueNumber(row_number), Status3, Closed5, Closed
Why is Select * from [Merchandise] where [Product Name] like '[ABCD]%'the same as Select * from [Merchandise] where [Product Name] between 'A' and 'D'I can run Select * from [Merchandise] where [Product Name] like 'A%'and get Products that start with the letter "A" but they don't show up when I try to get all "A","B","C","D" Products.
I think I might be missing something here. Here is what I'd like to do:1. Retrieve a list of data from SQL Server.2. Display that data in a gridview.3. Have the user click on a button to then see a subset of that data. (filtering) I can't seem to make this work. When the user clicks the button, I need the GridView to update to show only the specified data. In 1.1 I would created a DataView for the filtering, but am trying to use the latest and greatest. I've seen examples online of people using DropDownLists to act as the dynamic filter parameter. How can I programatically assign this to make it work?Thanks!
Our current method of limiting what data a user can see is implemented solely through our Web based business intelligence tools. No filtering is enabled at the database level. This has become somewhat cumbersome as security is tied exclusively to these tools. The tools use one common logon to access the underlying database.
I would like to implement security at the database level (SQL Server 2000) and thereby produce a more flexible/portable solution. I was thinking of setting up individual database accounts for each user and then tying these into our company structure table by passing system_user result to a constraint.
For example System User name 'Store 2' would reference Store '2' in the structure table. Depending on the user, different columns will need to be referenced to filter the rows. A store user would be validated against the store column, an Area Manager user would be validated against the Area Manager column and Head Office users would not be valiadated at all i.e. they are not filtered.
1) What is the best method to implement such a look up. Can or should I use Check constraints for such a solution?
2) Would a UDF be useful?
Any ideas on the best approach to take would be greatly appereciated.
I'm new to SQL Server so if the following sounds stupid then apologies. I am trying to design a query which compares columns and filters according to the result of the comparison. We are a UK based charity that provides financial help to families with disabled children (www.familyfund.org.uk). We have a large database (250,000 entries) which we know contains some duplicate/split files from a recent migration. We need to identify these files but not delete them. Currently I am using the following:
SELECT dbo.Families.famId, dbo.Address.street, dbo.Children.childId, dbo.Address.postcode FROM dbo.Children INNER JOIN dbo.Families ON dbo.Children.family_no = dbo.Families.famId INNER JOIN dbo.Persons ON dbo.Families.famId = dbo.Persons.famId INNER JOIN dbo.Address ON dbo.Persons.addressId = dbo.Address.addressId WHERE (NOT (dbo.Children.eligStatus IN (3, 4))) GROUP BY dbo.Children.childId, dbo.Address.postcode, dbo.Families.famId, dbo.Address.street HAVING (dbo.Address.street IS NOT NULL) ORDER BY dbo.Address.street
Obviously this returns all 250,000 records and then we have to search manually. We would like to run a query which compares families.famID to address.street so that where famId has more than one address attched it is returned to the results grid. Does this make sense? is it possible? Any help would be gratefully received
if you notice on the 8 and 9th rows the only difference between them is in the E column(0 and 1). What I am trying to do here is to display all with max(E). So in the above example, I should display rows 1-7,9,10 (8th row will not display because the 9th row has 1 in the E column). this is the query I have been using on SQL Server 2000 but I keep on displaying all the rows:
SELECT A,B,C,D,max(E) FROM <table> WHERE ( A = '012345A' ) AND ( B >= '01/01/2001' ) AND ( C <= '01/31/2001' ) GROUP BY A, B, C, D
ok, i'm building a page to display a list of courses, a user rating and 'last visited' date.
I have 3 tables - course (a list of all courses) review (a list of all ratings) visit (user visits to each course)
I've put together an SQL statment that returns everything i need, however its not quite right. SELECT course.courseID, course.courseName, course.courseURL, avg(review.fldRating) AS fldAverage, visit.visitDate FROM course
LEFT OUTER JOIN review ON course.courseId = review.fldcourseId
LEFT OUTER JOIN visit ON course.courseId = visit.courseId and visit.userId = 2
GROUP BY course.courseId, course.courseName, course.courseURL, visit.visitDate ORDER BY course.courseId, visit.visitDate DESC
The problem lies with the fact that each time a user enters a course a new record is inserted into the visit table - so the visit table will show how many times a user has entered a course and on which dates.
because the user has entered course 1 twice, the list is now showing 2 course1's - how can I change the statemtent to only select the most recent user visit, but still keep the complete list of courses?
I'm a bit of an SQL novice, so appologies if I've not explained this very well, Thanks in advance,
Hi, This is a really complicated issue and is hard to explain but i have the following:
select name, MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name
which is fine and brings up the correct results but if I want to find out from those records what another field is in table 2 for each record it pulls up too many results (i want just the one result from table 2 and then find what user it is)
if I do..
select name, table2.username MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name, table2.username
.. it pulls up too many results cos there are different usernames
if i dont group by table2.username then it give an error
Hello I am trying to gilter a table by getdate() (i also tried now()) but I cannot seem to be able to do it I place my code below if anyone can help. am grateful, my db is sql 2005.
<% Dim Recordset1__MMColParam Recordset1__MMColParam = getdate() If (Request("MM_EmptyValue") <> "") Then Recordset1__MMColParam = Request("MM_EmptyValue") End If %> <% Dim Recordset1 Dim Recordset1_cmd Dim Recordset1_numRows
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command") Recordset1_cmd.ActiveConnection = MM_connpeepeek_STRING Recordset1_cmd.CommandText = "SELECT usr_image1, dateimage_usr FROM diddle.ps_usr_image WHERE dateimage_usr = ?" Recordset1_cmd.Prepared = true Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 135, 1, -1, Recordset1__MMColParam) ' adDBTimeStamp
Set Recordset1 = Recordset1_cmd.Execute Recordset1_numRows = 0 %>
I am trying to filter data from columns and this is just not working. If I select all the criteria below and try to run it - I do not get any records returned.
WHERE (DropDt >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 13, 0)) AND (DropDt <= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) and Type IN ('Employee', 'Refinance')
Hi, I have following query. I want to insert the value MBR_COV_EFF_DATE to table fixed_MM if the function dbo.CheckTheDate2 returns correct date and if it returns NULL I want to insert it on error_MM table. How can I do this?
select dbo.CheckTheDate2(MBR_COV_EFF_DATE,'MBR_COV_EFF_DATE') AS MBR_COV_EFF_DATE from [unfixed_MM]
VisitTimes Company 2007-07-10 14:24:38.000 Microsoft 2007-03-10 11:14:38.000 Microsoft 2007-12-01 13:04:56.000 SQLTeam 2007-12-13 12:54:52.000 GoldMan Sac 2007-08-11 02:15:38.000 Oracle 2007-02-11 12:45:04.000 SAP Ltd
I am asked to write a stored procedure that get a count of each count of visit on a START and END date I wrote the below SP but am not getting the right result I think the **where VisitTime >= @Start AND VisitTime <= @End)** is not being evaluated. Help pls
CREATE procedure dbo.GetVisits @Start varchar(50), @End varchar(50) as SELECT TOP 100 PERCENT COUNT(company) AS VisitCount, company FROM visits.dbo.IViewVisits where EXISTS (SELECT * FROM VISITS.dbo.IViewVisits where VisitTime >= @Start AND VisitTime <= @End) GROUP BY company ORDER BY COMPANY ASC
