Hi I have a query that return set of records between 2 given timestamps. What I want to do is to include one record before and one record after to the result. For example I have the following data
Row Timestamp
1 01/01/2007
My query returns row 3 to 5 for example and I want to include row 2 and 6 to the result as well. How can I do that? Is it possible to do it in 1 query?
I have an ASP.NET application where I need to filter the records returned from a SQL query using a calculated distance to a point that is set by the user. To do this directly, I would need to include a square root of an expression in the SQL query. It seems like I should be able to do this by writing my SQL query something like: SELECT SQRT(expression ....) AS distance, column2, column3 ... FROM mytable WHERE distance < 50 Unfortunately, SQL queries apparently don't like the SQRT function. I am using .NET 2.0 with VS2005 and a MS Access backend (that will soon be moved to SQL/Server). As an alternate, maybe I can query the data into a temporary record set with a extra field for distance and then step my way through the temporary record set and replace the values in the distance field with a calculated distance. Once the temporary record set is prepared, then I would delete the records that don't pass the distance requirement or otherwise hide them and bind the data to a gridview control. Thanks, David
I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?
I have set up a SP for CDOSYS Mail as outlined in the following link: http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech
Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail
How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.
Existing table layout
table name - tblcomments Record_ID Comment_Section_ID Comment 1 5 Test 5 comment 1 7 Test 7 comment 2 5 New comment 2 7 Old comment 3 5 Stop 3 7 Go
Desired table layout table name - #tempComment Record_ID Comment_Section_5 Comment_Section_7 1 Test 5 comment Test 7 comment 2 New comment old comment 3 Stop Go
Once I figure out how to get the data in the layout above, I will need to join the table with my record table.
table name - tblRecord Record_ID Record_Type_ID Record_Status 1 23 Closed 2 56 Open 3 67 Open 4 09 Closed 5 43 In progress
I would like to be able to join the tables in the query below for the final output.
Select r.Record_ID, r.Record_Type_ID, r.Record_Status, c.Comment_Section_5, c.Comment_Section_7 from tblRecord r left outer join #tempComment c on r.record_ID = c.record_ID
How I can get the data in the desired #tempComment table layout mentioned above?
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
How to Get previous record thru sql query For the example my table: 1 usera item1 1.00 01/02/072 usera item1 2.00 02/02/073 userc item2 3.00 03/02/07 --how to use the query to make them join became like this (get/join with the next record) 1 usera item1 1.00 01/02/07 item1 2.00 02/02/073 userc item2 3.00 03/02/07 null null null >.<need help ... thanks alot
I have a table called Customers which holds the customers details including CustID primary key a table called DVDs which holds all the dvd details including DVDID primary key and a table called WishList which holds the CustID and DVDID I want to take the CustID given my the user and display all the dvds in the wishlist with that CustID the code I have now is int CustID = Convert.ToInt16(Session["CustID"]); comm = new SqlCommand("SELECT DVDs.DVDID, dvds.Title, Director, Actor, SUBSTRING(Description,0,200) AS Description, Image FROM DVDs, Customers, WishList WHERE WishList.CustID = @CustID AND WishList.DVDID = DVDs.DVDID", conn); comm.Parameters.Add("@CustID", SqlDbType.Int).Value = CustID; but when i run the query it displays each dvd 5 times not sure why can any one help cheers
I'm a bit new to MS-SQL so i thought maybe you could help me.
This is my prob. I have an Access application that i have upsized to a MS-SQL server. I have a query based on 3 tables: Customers, Companys and Payreminders. When i run this query, i can't add new records or make any changes. If i only run the query with the tables: companys and payreminders i can add new records and edit them. If i run a query that's based on companys and customers, i can edit and add new records, but when i run the query based on companys and customers i can't add or edit records.
companys, payreminders: Editable payreminders, companys: Editable Companys, customers: Non editable Companys, payreminders, customers: Non editable
Something must go wrong when i use the companys and customers table. Can anybody help or give a suggestion what to do.
happy friday... my table has 200,000 + records and I want to see the particular record which I think it is causing the problem. How could i query 195,203rd record? thank you, yanoroo
Essentially, what I’m attempting to do is for each Customer, Division, SalesRepType determine who the most recent assigned SalesRepNumber is and when (EnterDate) that person was assigned. So using the sample data, I would expect the following results.
I’ve tried various ways of using a CTE and ROW_NUMBER trying to get at this, but the area that is giving me the problem is in Division A, SalesRepType 1. Here is what gets me close, but I’m picking on SalesRepNumber 200 instead of 100 for Division A and
SalesRepType 1. WITH cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate) AS ( SELECT
Hendra writes "I'd like to know is there any way or any statement that can help me to get first or last record from the group by query ? I'm using sql Server 2000 Thank's
i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/
i have a database with a list of customers and goods that they have ordered. I want to send one email to each customer regardless of the number of products he has ordered. eg.
Table has 10 fields and I need to return them all. The three most importaint, at least for the filter I need are:
id, studentid, date, canceled.
I need to return the last max(date) grater than or equal to @dateparam which is not canceled for each studentid
I have worked out some solutions but am not happy with them. Specially woried about performance when the table grows. I am expecting in full production a table growth of about 3 million records per month.
what would be grate is if there where a way of returning a the coresponding id like in:
select studentid, max(date), related(id) as ids from tablea where canceled=0 group by studentid
then I could do:
Select * from tablea inner join (select studentid, max(date), related(id) as ids from tablea a where canceled=0 group by studentid ) b on (a.id=b.ids)
Edit: Newer mind. I tested this query more after writing this, and now it seems to work!I hope it continues to work. In following query, else is never executed.CREATE PROCEDURE Put_into_basket( @Product_code varchar(20))ASBEGIN SET NOCOUNT ON;IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@Product_code) BEGIN INSERT dbo.t_shopping_basket (Product_code, Name,Price) SELECT Product_code, Name,Price FROM dbo.t_product WHERE Product_code= @Product_code ENDELSE --this part is never executed BEGIN UPDATE dbo.t_shopping_basket SET Quantity=Quantity+1 WHERE Product_code=@Product_code ENDENDGO The query should test if there is a record or row with Product_code=@Product_code. If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of. If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.But when I test query, it never runs the quantity+1 part.
I was wondering if anyone could quickly identify why the query is not parsing through each line in the temp table? I am sure its something stupid and easy, but if anyone has an idea, I would greatly appreciate the help!
My results are the same row repeated exactly the same for the number of rows in the temp table.
FROM brSession e INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId) LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId WHERE e.ssBriefingID = 11749 and((not prConfirmModeId = 0) or (prPerNbr is null)) ORDER BY ssStartTime
I'm no SQL whizz yet but I'm learning hard, and need to get some information from our DB rather urgently so have resorted to this fantastic forum, only I can't find what I'm looking for.
Basically I'm selecting a whole load of entries that have a (admission)date field after 2001, but I only want to return the Earliest (admission) for each (patients number).
Here is the script I have created to select all the data, but how can I limit the results to just the earliest (admission date) for each (patient).
SELECT Admission_Year, Admission_Month, Age_On_Admission, [Length of stay(continuing)], [Patient's Number], [Cons epis seq no], Sex, [Main Primary Pas Diag], [Date of Death], [Epi duration], [OP Code1], [Admission date], [Date of Death] - [Admission date] AS [days before death],[Intended Management] FROM dbo.Admissions WHERE (Admission_Year > 2001) AND (Age_On_Admission > '64') AND ([Intended Management] = 'inpatient') AND ([Date of Death] IS NULL)
I would really appreciate it if anyone can help with this, I'm sorry I can't really contribute to this forum as an SQL expert as .net is really my forte and I usually spend my time contributing to the asp.net forums. :)
If I wanted to run a query on any table and in the recordset that is returned have an 'id' field (or whatever) with the record id of that record, how would I do this?
I'm thinking something like
Select field1, field2, recordNumber // derived somehow - not an actual field from table
If I selected the data below, how could I write a query to only keep the latest record. I added a "FileDate" column to label the last record (FileDate 200807)I want to display.
LeaseIDOpID Year MonthMCFFileDate 208801407982006 107660200807 208801407982006 107680200801 208801407982006 107784200704
I am having table which is having 5 columns say A,B,C,D and E. There are chances to change in C,D,E columns. I want to identify if any change happened in the above column and show to user prev value + new value.
i.e. i want to prepare query by using calculated extra 6 column where i want to put change happned in C,D,E columns.
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct pg.grid_id [Price Grid], pg.grid_name [Grid Name], case when exists (Select c.grid_id from customers c inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5 where c.type = 'C' and dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
I'm trying to place records into groups of 100, so I need a query that will return records but not based upon an autonumber type field. For instance, if I had a set of records that had been sequentially numbered (by autonumber) with IDs to greater than 3,600 but there were actually only 300 IDs remaining ranging from 1 to 3600 (say the others were deleted), then I would want to use a query to make three groups of 100 of the remaining IDs.
I can use SELECT TOP 100 for the first 100 records. How do I get subsequent groups of 100?
I need assistance in finding the oldest CDR record in Cisco Callmanager DB using sql query. The CCM version is 3.3(5). Any assistance is needed. Thanks.
I have created a sample Table with inserting below data,
ID Subject CreatedDate 1 Test 2015-09-20 22:59:07.373 2 Test 2015-09-21 09:16:58.290 3 Test 2015-09-21 09:18:17.500
I am loading a dropdown with Subject. The dropdown list shows the 3 items with same subject name since i have three records with same subject. I need a SQL select query for - if subject 'Test' is selected from the dropdown, need to be able to pull its corresponding associated ID from the table based on CreatedDate?
Hi all: for example: testDataContext db=new testDataContext();var res=db.tables.single(p=>p.columnName=="hi"); if there is a record in the database, it works well, but if there isn't , it will throw an exception, then , How could I know the record exists or not ? I don't think exception is a resonable way. and in my opinion, there should be--------even must be ------a resonable way , to evaluate the query result to a bool variable, then program could judge the bool variable like : if(bExist) show("yes, I find it");else show("sorry, the record doesn't exist in the database"); I can't imagine I got the bool variable via exception... thanks to all..
I want to do something likeif "Product_code" is Nullinsert dbo.t_Shopping_cart (Product_code,Name,Price,Product_group,Quantity,Total)select Product_code,Name,Price,Product_group,Quantity=1,Yhteensa=1FROM dbo.t_Shopping_cartWHERE Product_code='AHTU140213' elseupdate dbo.t_Shopping_cartset Quantity=Quantity+1where Product_code='AHTU140213' In short: I cant test existence of a record.RegardsLeif
Ok, to start I will say I am a novice so detailed solutions are much appreciated. I believe I am on the right track. Here is what I am trying to accomplish. I have a page that uses an xml driven google map. When you click on the map marker, the info bubble displays brief information about a community and a link specified from the xml file. I have specified the links in the xml file to pass a query string variable ex: /community.aspx?name=uniqueCommunityNameHere. I tested this by simply placing a label on the community.aspx page that restated the "name" value - works fine (first commented out line of Page_Load). Now I need to complete my VB function that pulls the data record from the database - based on the parameter input from the query string - when the page loads. So if the user clicks the link on the marker for community1, it will populate community.aspx with info about community1 from the database... if they click community7 it will display that community info etc. Data Table: "Community" Data Fields: ID, Name, Description, Address, City, State, Phone, SalesRep. ASPX Page Design Elements: lblID, lblName, lblDesc, lblAddress.... etc. Here is my start... I will need help with the sql statement for sure - how do I create a where clause from the query string parameter passed in? Thanks so much for your help!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load'My initial test to see that the query string is working lblName.Text = Request("name").ToString() ' Get the community info when loading community.aspx for the user selected communityIf Not IsPostBack ThenLoadCommunityInfo(Request("name").ToString())End IfEnd SubPrivate Sub LoadCommunityInfo(ByVal community)' Define data objectsDim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReader ' Read the connection string from Web.configDim connectionString As String = ConfigurationManager.ConnectionStrings("CONHTConnectionString").ConnectionString' Initialize connectionconn = New SqlConnection(connectionString)' Create commandcomm = New SqlCommand("SELECT * FROM Community WHERE Name = " & community, conn)' Enclose database code in Try-Catch-FinallyTry' Open the connectionconn.Open()' Execute the commandreader = comm.ExecuteReader()' Populate the list of community information -> Make the labels.text = the database fields --> lblName.text = table-Community field-"Name"lblName.Text = "Name"lblDesc.Text = "Description"....' Close the readerreader.Close()Catch' Display error messagedbErrorLabel.Text = "Error loading the community information!<br />"Finally' Close the connectionconn.Close()End TryEnd Sub
I have two tables in my database: order_id with fields order (text) and comp_ID (int) and another table called customers with comp_ID (int) and company name (text) and other company information fields. The link between the two tables is the comp_ID. With every order that's made the company that made the order is stored with it in the order_id table. If I type in the order id (text), I want to be able to use the order id to search the order_id table and find out what the comp_ID of the company that made that order is. Then use that comp_ID to pull up the record of company information from the customers table with the same comp_ID. Is there some way to do this in one query? Or how do I accomplish this?
Hi I want to know what are the three different ways by which we can get the record cound for a particular table using SQL Query analyserThanks in advance
