SQL Server :: Group By Not Returning Expected Results?
Dec 17, 2010
I have a procedure that is used on a search page.. im trying to provide a highlevel resultset which you can then click on a record to view all records associated with it.
My expectation is to return 5 columns.. the ID and Date is what i want to group on.. so that if there are 10 records for ID=5 and Date=10/25/2010 then in my results i would have 1 record. Once im done with the page, you can then click on that line in the gridview to view the 10 records for that id. Does that make sense?
I have the following query, but for the criteria i entered, im getting back 3 records instead of just 2 since there is a differnet reboot count, BUT i really still just need overall data. so it should be only 1 record and seen below is what is returned and what is expected..
I have a array list and am using like a database trying to search for ChildID and ParentID based on certain criteria.
My example below has the following keywords to search for:
Yellow, Mustard, Honey, Orange, Flame, Talisman
I also have a category volume value which I grab the first 2 characters to determine another filter.
My code isn't returning all matches with using 'Contains'. Right now it finds the 'Orange, Flame, Talisman'/AV record and returns 50, 28. My keywords also has 'Yellow, Mustard, Honey'/AV which should return 55 and 28 as well.
HTML Code: <%@ Page Language="VB" AutoEventWireup="false" CodeFile="temp2.aspx.vb" Inherits="temp2" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">
The SQL is below - each result is coming up 3 times rather than just once.
SELECT Consignments.LegacyID, TripDate, CollectionName, DeliveryName, Pallets, Weight, BaseRate, Consignments.FuelSurcharge, AdditionalCharges, BaseRate * Quantity AS 'InvoiceValue', Consignments.Customer, InvoiceNumber, CASE WHEN child.LegacyID = Consignments.Customer THEN child.LegacyID WHEN parent.LegacyID = Consignments.Customer THEN parent.LegacyID ELSE this.LegacyID END AS 'InvoiceAcc' FROM SageAccount this LEFT JOIN SageAccount parent on parent.LegacyID = this.InvoiceAccount LEFT JOIN SageAccount child on this.LegacyID = child.InvoiceAccount JOIN Consignments on (Consignments.Customer = this.LegacyID AND this.Customer = 'True') OR (Consignments.Customer = parent.LegacyID AND parent.Customer = 'True') OR (Consignments.Customer = child.LegacyID AND child.Customer = 'True') WHERE (this.LegacyID = @Customer) AND (TripDate BETWEEN @fromdate AND @todate) AND (InvoiceNumber IS NOT NULL)
The SQL was given to me for another similar query, but this time I have modified it to try to use it for the query I'm doing now, so I'm assuming that I'm doing something stupid.
I have a query using LINQ-to-SQL. It queries an underlying database table Rooms. It uses Where conditions to narrow down the results, namely:
Gender. Current Occupancy < Max Occupancy Available Flag is Checked
I know this should return results but it keeps returning an empty set. Any ideas? Code is below
Dim selectedHalls = (From sh In dbHalls.Rooms _ Where sh.gender = Session("gender").ToString _ Where sh.max_occupancy > sh.current_occupancy _ Where sh.is_available = 1 _ Select sh.building_name).Distinct()
UPDATE: I've verified that the issue is with the statement where sh.is_available = 1, which doesn't make sense since this is a bit field.
I am hoping someone can point me in the right direction here. I am trying to create web service that will return ajaxified results. Specifically, I want to write a web service that will fetch email through a secure connection. However, rather then have the web service return every single email, I just want to fetch maybe 5 emails at a time. I've always used Ajax as a client helper technology and not sure how to go about implementing this on a server side, or if its even possible.
Edit: I solved my problem but if you have anything to add do. Note: I did not create the DB it was created by Wordpress hosted on GoDaddy with my site I have a MySql Database called "wordpress" (for clarity). I want to be able to grab the most recent post from my blog and show it on the landing page for my url. So my thought is this: connect to the MySql DB, run a query to grab the most recent post, display the post.
I built a class to handle the connection and process the request:
public class DAL { private string connectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=[server here]; PORT=[port]; DATABASE=wordpress; USER=[user name here]; PASSWORD=[password here];"; private OdbcConnection blogConnection;
In my web form I have a search box. The user enters a string, and once the search button is clicked, I pass the value from my .js file to my .cs file and do some processing to get the results. Up to this point everything is fine, but now I dont know how to get my results back to my .js page. The serach results at this point are in a dataset, and also in an multidimensional array. I have a multi array because I need to pass back the code and description of the items found.
[Code]....
At this point I have used the passed string 'value' to get my required code/descriptions, but i'm not sure how/if I can get either the Dataset or multidimensional array back to my .js page
I am trying to create a search page, this allows the admin to search through the entries in the database by certain criteria, such as province (like state but diff country)
Here is the code I have so far. The problem is that I am not getting any errors. But I am also not getting any results. The page just posts back and returns to the blank search page. The GridView does not display any results.
here is my code as it stands:
Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Partial Class administration_Search Inherits System.Web.UI.Page Protected Sub ProvinceButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProvinceButton.Click 'get the search string from text box Dim Search As String Search = ("%" & ProvinceButton.Text & "%") 'if the string has value then continue with search If Len(Trim(Search)) > 0 Then Dim con As String = WebConfigurationManager.ConnectionStrings("fubar").ToString() 'build sql string Dim s As String s = ("SELECT id, surname, name FROM orders WHERE province like @strSearch") 'declar connection Dim c As SqlConnection = New SqlConnection(con) 'add command Dim x As New SqlCommand(s, c) 'add parameter x.Parameters.AddWithValue("@strSearch", strSearch) c.Open() Dim r As SqlDataReader = x.ExecuteReader GV.DataSource = r GV.DataBind() c.Close() Else Province.Text = ("Please enter search terms") End If End Sub End Class
On the .aspx page I have a textbox (id: province) and a button (id:submit)
I'm having an issue with SSRS 2008 inside VS 2008, where I have a report that is connected to the database via a Data Source which is working correctly, but when I execute a query inside the query designer to test my data, it returns zero records.
The issue I'm having is that the EXACT same query inside SSMS returns the correct record. I'm really stumped as to why it all is connected, but not returning the record. I have another report in the solution that uses the same database, but that one actually works ok.
Gridview is not being populated now that i built my objectdatasource in the code behind.. why. If i build the source on the aspx page and configure it that way, it returns results.. but when i try to build it in the code behind it just displays my empty template for the gridview.
I haven't been using asp.net for that long and i am wondering if i can use a datalist, repeater, etc to pull data from a database, into one of these, but then group them by group with expandable boxes.
I have a SQL database set up, it will be used to list applications upon a webpage. e.g. office.
So colums are id, pro_name, pro_link, group_name, icon_name
I can extract the data but i am unsure on how to get it to display in the way that i want, as i wish it to show it so it is like below
I have a page where users can search by company name, the data list is pulled from an Excel spreadsheet hosted on the site. I'm seeing some strange results returned, for example:
In the company field say there are the following entries:
Anns Company Bernards Business Company B Dermots Domain Property Company
If I search for "Com" I get the following returns (correct):
Anns Company Company B Property Company
If I search for "Der" I get my "Sorry there are no matching results" message (Incorrect)
If I search for "Ann" I get the following returns (Correct):
Anns Company
And if I search for "e" I get the following returns (Correct):
Bernards Business Dermots Domain Property Company
It seems random as to why it doesn't return some values, I've tried moving rows around to see if that's the problem but it makes no difference. My range is set correctly in the spreadsheet.
When I debug the VB code behind, when I search for something that works correctly e.g. "Com" in the above example, once enter the value and click search Visual Studio begins steping into the code and shows me the contents of the variables however when I search for something that's not working e.g. "Der" and click search it immediatly returns the "Sorry there are no matching results" message and the code behind does not get steped into.
We have a web project that contain its business methods in a class library project called "Bll.dll" some methods of Bll.dll return List<> ... from a source - that i don't remember now - told that returning Collection<> is better than returning List<> Is it a valid ? Note that i don't make any process on values returned from BLL methods .. just view it in a web page
I'm using a linq group by query (with two grouping parameters) and would like to use the resulting data in a nested repeater.
var dateGroups = from row in data.AsEnumerable() group row by new { StartDate = row["StartDate"], EndDate = row["EndDate"] }; "data" is a DataTable from an SqlDataAdapter-filled DataSet. "dateGroups" is used in the parent repeater, and I can access the group keys using Eval("key.StartDate") and Eval("key.EndDate").
Since dateGroups actually contains all the data rows grouped neatly by Start/End date, I'd like to access those rows to display the data in a child repeater. To what would I set the child repeater's DataSource? I have tried every expression in markup I could think of; I think the problem is that I'm trying to access an anonymous member (and I don't know how.) In case it doesn't turn out to be obvious, what would be the expression to access the elements in each iteration of the child repeater? Is there an expression that would let me set the DataSource in the markup, or will it have to be in the codebehind on some event in the parent repeater?
Stack-Overflow :3 I've got a kind of hard-case question.
So I'll try clearly explain my idea with my poor english :/ need select to DataTable SOME values from ???_**GetDisplayData **procedure for each day of previos month (GROUP IT) where group rules for different columns is different
I need to select some values in stored procedure from other stored procedure like this :
SELECT X FROM Y_Procedure(@ProcedureParameters)Also I need to select from dynamic SQL Procedure like
Y_Procedure=@Y+'_Procedure' SELECT X FROM Y_Procedure(@ProcedureParameters)
Also I need to load it to DataTable :-/INSERT INTO @Report (CellHorizontal, CellVertical, CellValue) --to TABLE SELECT Date,X2,X3 FROM Y_GetDisplayData(@Param)
SET NOCOUNT OFF; SELECT * FROM @Report
GetDisplayData works as select with parameters and that doesn't returns SQL DataTable And there is no way to recode GetDisplayData's procedures, it's just constant procedures for me.Finally I need to Group nodes from this table
INSERT INTO @Report (CellHorizontal, CellVertical, CellValue) SELECT T1.Date, IF ((Select grouptype...)=1) T1.X2 + T2.X2 ELSE IF ((Select grouptype...)=2) AVG(T1.X2,T2.X2), IF ((Select grouptype...)=1) T1.X3 + T2.X3 ELSE IF ((Select grouptype...)=2) AVG(T1.X3,T2.X3), (SELECT T2.Date,X2,X3 FROM Y_GetDisplayData(@Param) T2 WHERE T2.Date>T1.Date AND T2.Date>=T1.Date) FROM Y_GetDisplayData(@Param) T1 GROUP BY EVERY DAY ???--and here is epic fail
I can make all stuff on asp server :And C# allows me to use something like SelectCommand = IzmProc + "_GetDisplayData"; And then I will work (select special data) with DataTables on ASP.NET Server but it's better to make all on SQL ... But looking like it's just unrealizable on SQL >_<
I gonna think about C# realization,but my code is very weird , got errors and doesn't works >_<
What am I missing here?I get the error Declaration Expected @ myPostData=
[Code]....
Dim encoding As New ASCIIEncoding() Dim RateXML As String = "123" 'Format the Data Dim myPostData As Byte() myPostData = encoding.GetBytes(RateXML) ^^^^^^^
select NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) as 'UserName', [Message], DateCreated, TypeID from Notifications n, aspnet_Users u
I have a query with group-by clause. I want to use it in recusionCTE.How is it possible?I want to know the totalamount for each fname done through this query:select top 10 fname,SUM(totamt) as 'Total Amount' from tables_list group by fname order by 'Total Amount' descBut when any person logins he must be able to see this list for his own hierarchy down the line including himself.
