I have a pretty big SQL query with a one to many sort of relationship...
There are client accounts that we're reporting on. Each account has four different historical categories attached. Each historical category can have maybe fifty entries in each, sorted by date.
I need to figure out how to grab the unique accounts and show only the three most recent results per each historical subcategory with the account... the three most current results from each of the four subcategories, displayed by the parent client account number.
I've created four views to isolate the subcategories as a start, thinking I could bring them into a parent query ...
but my question would be... how do I generate JUST the top three historical transactions by account number? If I select TOP 3, I get only the 3 newest in the MASTER LIST, not per client account, which is what I need to do.
Does this make sense? I could really use a good SQL helping hand with this one.
hello forum, I need to grab a string value from a list box in from a web form, and pass it to a sql select command statement where that value is equal toall values in a database table(sql 2000). example zip code list box3315433254 845788547535454 selected value is 85475 I am putting that value in a string like this: dim string_zip as stringstring_zip = zip_ListBox.text Question, how do i pass that value to sql stament, i am using this but does not work. SqlCommand1 = New SqlCommand("SELECT zip FROM table WHERE zip = string_zip", SqlConnection1)
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
Hi, First post here :) Subject might not make sense, so allow me to explain. I have a table, tbl_finalized. A unique record consists of an emp_id, building, and school_year. I have another table, (call it table2) where emp_id, building, and school_year are fields as well. What I'm trying to do is... grab all the emp_id's from table 2 ( along with the school_year and building ) and then see if it exists in tbl_finalized. If it does, I don't want it. I want all the usernames in table2 that don't exist in tbl_finalized.
Here's an example: table2 contains the following record: emp_id: xxx, building: 333, school_year: 2008 emp_id: zzz, building: 333, school_year: 2008 emp_id: yyy, building: 444, school_year: 2008
tbl_finalized has the following records: emp_id: yyy, building: 333, school_year: 2008 emp_id: xxx, building: 333, school_year: 2007 emp_id: xxx, building: 333, school_year: 2008
The result I desire is: emp_id: zzz, building: 333, school_year: 2008 emp_id: yyy, building: 444, school_year: 2008
I hope this makes sense. If not, please ask and I will do my best to explain. I have light understanding of joins, but not sure how to get the difference like I desire in this case... TIA.
HelloI want to write a stored procedure (using Enterprise Manager) that can grabthe digits that are inbetween the two dashes (-) in strings like:123-150-401-123-832-4215-61The digits to the left, right and inbetween the dashes could be any length,so a static "get the 5th, 6th and 7th digit" stored procedure won't work.Many thanks,--Chris Michaelwww.INTOmobiles.comDownload 100s of ringtones, wallpapers & logos every month for only £1.50per week
I am passing a parameter and executing two tables and grabbing it's data.. In the future I will put the code into a store-procedure.
--Exec Table 1 declare @id varchar(20), @MEMBER_ID varchar(20) set @id=null set @MEMBER_ID ='55555' --ie. 55555
Select id from emp Where MEMBER_ID = @MEMBER_ID
--Okay, Next I need to execute another table and pass in the id --that was selected from the emp table.
SELECT EMAIL FROM moreInfo WHERE id = @id
Currently, the emp table displays ie. 100 records that matches the member id 55555.. But the second select is empty.. And I need to display email data for the 100 records that were selected from the emp table..
I hope is it not confusing what I am trying to do..
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007
Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.
I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.
Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.
What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.
I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.
Am trying to find a way to insert/update/delete data in a SQL mobile database on a Windows CE 5.0 device FROM a desktop PC.
This situation is completely stand alone, no network (apart form device/desktop), no GPRS etc etc etc.
I've looked at RDA but i dont believe it fits my app. (pulling data from a 2005 server that doesnt exist doesnt really help me much, push can't be used without a pull which kills the idea.)
The goal is a UI on the desktop that can manipulate data in the SQL mobile Database.
I've tried all i can find/think off in relation to this but to no avail.
My latest attempt has been using the simplest method possible (using a VS wizard datasource to the devices DB and tryign to whack that on a form) but this just creates a "Path not found. Check the directory for the database [Path = Mobile Device/ce_swipe/TestDB.sdf".
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!! -- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on go if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1 if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
Hi,New to .Net and SQL. I have two tables that I have joined together. RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name. I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts. I have a details report that gives each contract info. for each specific adjusters rentals. However, I want to just list the adjuster once and give all of their totals. In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.Do I need to code this on the page with a do while loop?Appreciate any help.SELECT SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.Rate) AS TotAvgFROM dbo.RentalControl_Main INNER JOIN dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterIDWHERE (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND (dbo.RentalControl_Main.AgencyID = '2')
I was wondering if there was a keyword that would allow you to return the number of results from a query such as (this is fake just giving an example so that someone can give me the real answer)
I am a beginner with SQL, so please excuse any ignorance here.
Under SQL 6.5, when executing a SELECT DISTINCT query against a table in a certain database, the results were returned alphabetized. Now, after migrating the database to SQL 7.0, the same query does not return the results alphabetized. If I add an index for the column specified in the query to the table, the results come back alphabetized. Is there any way to correct this other than adding indexes for all columns in all tables that are queried in this manner?
I have this line of code that when run in the query doesn't return anything:
srv.AGENT_S_ACC_CODE IN ('" & Replace(Replace(Parameters!arcode.Value," ",""),",","','") & "') The parameter is setup and the query works when I hardcode it with a value but will not return anything with this, does anyone know why?
Hi guys, say i wrote a query that returns 1,000 records.. what kinda query could i write that only returns say the first 50 records of the 1,000 recs..
Hello, I want to select all the customerIDs where an email address exists. Easy: select customerid from customers where emailaddress = @emailaddress Now I want to use the resulted customerIDs from the above query as a parameter to select all the email addresses with emailstatus equal to 3. How do I create this type of while statement in a stored procedure? I prefer to not create a temporary table. My idea was to do it like this: select emailaddress from emailaddresseswhere emailstatus = 3 and customerid = (select customerid from customers where emailaddress = @emailaddress) This doesn't seems to work. I get the error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. thanks
Can anyone give me the basics of speeding up reports that use queries or views or nested views? Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc. We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports. Thanks
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?
ok the following statement returns the correct results in sql query analyzer but in the .net environment with c# it returns back less 3 records SELECT SUM(ao.amount),bl.dpc,bl.city from billinglocation bl inner join shippinglocation sl on bl.billinglocationid = sl.billinglocationid inner join absorbentorder ao on sl.shippinglocationid = ao.shippinglocationid group by bl.dpc, bl.city billinglocation 1 - 1 shippinglocation 1 - many absorbentorder anybody have any ideas why this might be occuringthanks!
I am running SQL Server 7.0 on NT 4.0. I have created a simple query:
SELECT SUM(month1) As total_month1 FROM eac_manload WHERE project_number = 8800 and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)
I first time I run the query I get the correct result. Subsequent times that I run the query the result is 1 record with a Null value. The data has not changed. If I stop MSSQLSERVER and restart the service I get the correct result the first time and the Null value each time thereafter. Anybody out there with any idea of what is going on here? Any help will be appreciated!!
I have duplicate results in from my query. I am using the distinct clause but this does not seem to be working. In the example below, I do not want the 79.15 to repeat multiple times eventhough the first column has all distinct values. I am using distinct in my select but this is not stopping the duplicates on the second row. Any guidance would be greatly appreciated. Thanks
