Help On Specific Query
Apr 12, 2008
Hello Valueable Friends , I have an issue and i am going to need a help..
I have a rents table with fields that matter now
Stuffid , rentdate , rentCustomer ........
On my stuff table i Added a new field called LastCustRent
So I want to update this field With the Customerid that rented each
stuff last time
I tried this
Select Stuffid ,max(rentdate) as maxrentdate , rentcust
from rents
Group by rentcust , stuffid
But I do not take unique results
Can someone help me on how will i get only the customer of my last rent time for each stuffid on my rents table?
I sell my mother in law.Is anybody interested?
View 4 Replies
ADVERTISEMENT
Jul 20, 2005
Here is tested schemaif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[TestTable]GOCREATE TABLE [dbo].[TestTable] ([SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOinsert into testtable values ('123','pass',null,'fail','skip')insert into testtable values ('456','fail',null,'pass','skip')insert into testtable values ('789',null,'fail','skip','pass')insert into testtable values ('345','pass','pass','pass','fail')I would like to fetch the COLUMNNAME where the value is fail.Basically I need to know which test failed, test1, test2, test3 ortest4?Is this possible?
View 2 Replies
View Related
Jun 1, 2001
Is any way we can tell sql server to keep specific (long runing) query in procedure Cache.
I already tried to do this by creating job (run every 1 hr from 8 am to 6 pm)
but is not enough
Thanks
View 1 Replies
View Related
Dec 11, 2003
Hi,
I'm in an unfortunate situation. We are posting information using a stored procedure to an outside SQL server connected to through a System DSN on our server (win 2003 server) using php's ODBC functions (we never had any luck connecting directly to the SQL server using php's mssql functions).
Everything is working fine, we can connect, send querys, etc ... but between 1am and 10am we recieve errors when trying to execute queries (though we can connect fine).
Whoops ... forgot to get the error returned before it turned 10am ... I can post it tomorrow
I think the database is being locked, but unfortunately I know very little about MS SQL server
The people who's database we are connecting to are not being helpful ... I was hoping I could get some suggestions on what would be the cause.
Anything you can suggest would be a huge help! Thanks!
- Joe
View 2 Replies
View Related
Jul 23, 2005
Hi,Can't post specifics at the moment but if this seems like a commonproblem any help would be appreciated.When querying with ~6 tables, using mostly left outer joins, I getstandard performance with the where clausewhere XXX is not nullhowever, if I try the clausewhere XXX = 4the query takes upwards of 5-6 minutes (I just stop it running at thatpoint.The field XXX is in the 'main' table (to which the joins attach), it'san integer field too so I can't see too many problems there.There's no index on the XXX field, but if I remove the joins, I getstandard performance doing either query.So why would the second query's performance differ so significantly?Cheers,Chris
View 3 Replies
View Related
Mar 19, 2008
Hi !
i have a query like select top 3 column1 from table 1 order by Id desc
here the column1 is of varchar(512) data type but i want to get only first 250 chars of it and if they are less than 250 then get the whole range
plz let me know how it is possible throguh query or any other way
Regards,
View 1 Replies
View Related
May 23, 2015
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
View 4 Replies
View Related
Feb 27, 2008
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?
View 4 Replies
View Related
Mar 23, 2007
Ok, please pardon my complete ignorance; but I am extremely SQL challenged. I manage the server right now and I am the closest thing to a DBA at the moment with this server.
Here is the problem. I have a job that runs every hour and until last week it was running without any problems. Now, once a day it hangs, and I end up having to restart sql to get it to be functional again. I guess I could just kill the spid, but regardless, it's fails.
It doesn't happen at the same time each day. I thought it was from the backups doing a SQL backup job, but I eliminated that possibility. I can't find what is causing the problem, because the SQL logs just stop at the time that this happens. It's like some other job/query is running and taking all the sql server resources. But I can never catch it when it's happening and I have no event logs or SQL logs to go on.
Is there any query that I can run to find what happened around the specific time the job failed to run? I just need to find what process did what when this job stopped working.
Again, I apologize if my lack of SQL knowledge is making me ask dumb questions, but I am really lost.
Thanks in advanced!
View 3 Replies
View Related
Feb 7, 2012
I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
View 3 Replies
View Related
Jul 28, 2014
I am trying to create an index for a specific query that is used a lot.
Unfortunately I am keep getting a clustered index scan. (4.6 mil rows)
Googling "AND" and "OR" is tricky, but did lead met to many articles about filtered indexes.
None of which gave me a clear answer.
How can I make an index for this query?
Here's the code setup
------------------------------------------------------------------------------------------
-- Setup
------------------------------------------------------------------------------------------
IF OBJECT_ID('FilterTbl') IS NOT NULL
DROP TABLE FilterTbl
CREATE TABLE FilterTbl
(
SurroIDInt IDENTITY PRIMARY KEY,
CompIDSmallInt,
[code]....
View 3 Replies
View Related
Dec 28, 2014
This is my table:
use tempdb
go
if object_id('Data', 'u') is not null drop table Data
go
with temp as (
select top 10000 row_number() over (order by c1.object_id) Id
from sys.columns c1 cross join sys.columns c2
[code]....
What index would be best for these three queries? With best I mean the execution time, I don't care about additional space.
This is the index I currently use:
create nonclustered index Ix_Data on Data (StateId, PalletId, BoxId, Id)
The execution plan is SELECT (0%) - Stream Aggregate (10%) - Index Scan (90%).
Can this be optimized (maybe to use Index Seek method)?
View 7 Replies
View Related
Aug 21, 2015
I need to return 1 ip address for each machine. I can easily achieve this with using group by and return max(ipaddress) however I would like to filter out specific range '192.168.%' but only want to do that if machine has more then 1 ip address. If the machine has only 1 ip address then it can return it even if its '192.168.%'
below is test table to show sample data
CREATE TABLE dbo.[testip](
[machineid] INT NULL,
[ipaddress] [varchar](20) NULL
)
GO
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'155.119.1.22')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'192.168.1.5')
[code]....
View 4 Replies
View Related
Sep 18, 2015
I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.
I know that i can build this with a correlated subquery, but i´am curious about using CTE.
I post sample create Script:
create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)
insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union
[code]....
View 5 Replies
View Related
Aug 26, 2006
MSSQL Server 2000 SP3 in both houston and memphisI have a database in houston, lets call it RED. Specific tables fromdatabase RED are copied to database BLUE. Database BLUE is then backedup, ftp'd to memphis and restored. In memphis there is a single tablein this database that will not open in enterprise manager when youchoose to open all rows you get a generic ODBC timeout error no numbersjust simply "TIMEOUT".If I log into the servers in houston and open the table from databaseRED or BLUE there is no issue. I can however return up to 66,199 rowswithout an error. If i choose a number higher than this i get thetimeout error. I discovered I could run a querySelect Distinct * from f0911and it would display all my data. I am able to export the data fromthe table using this query to a csv file and reimport the data into atable and it works just fine.I built another sql 2000 server SP4 and there is no issue with thetable in the database when it is restored there. I have also triedrestoring as a different database name on the production (sql 2000 sp3)server to no avail.I can't figure out why this single table is not functioning properly inthe one instance of sql 2000.If anyone has any ideas please share them, I'm running out myself. I'mobviously very new at sql database administration and would appreciateany advice.Also, i don't believe the issue has to do with timeout countdowns.Everywhere i could change them (Enterprise manager and SQL Serveritself) they are set to unlimited if possible. I also don't see howthe problem could be related to the service pack of the sql server,seeing as although it works on the sp4 server in my possession it stillworks just fine on the sp3 server in houston.Thanks,Michael Smith
View 1 Replies
View Related
Feb 18, 2008
I have a program that is automatically ran through a job. The program gets the most recent files that have been uploaded to a server. I would like to be able to query the database to see when the last time this job was ran successfully and set this date as the date to look for files newer than the last successful run date.
Could someone point me in the right direction to what tables this data is stored in on a 2005 SQL Server Database?
View 3 Replies
View Related
Jun 30, 2015
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
View 2 Replies
View Related
Oct 10, 2007
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
View 4 Replies
View Related
Jan 23, 2007
Hi All,
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
Any suggestions is appreciated
Thanks A lot in advance
e,g :
http://localhost/reportserver?/testreports/employee sales&UserID='ABC'&LName=Lastname='victor'&rs:Command=Render
View 1 Replies
View Related
Dec 8, 2006
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
here is an example:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67
so I want to set any contact_id_N = 0 where contact_id_N is = 67
so in the end, the table will look like this:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0
is there a way to do it in one statement?
View 2 Replies
View Related
Feb 11, 2015
Lest Say with File-stream we have a table document where the file-stream column is located, then we have another table called product-document where the productID and the Document-node are both foreign key of the Product and the Document tables respectively. if i want to query the file-stream document for a specific product, i can just join all three tables, but with file-table there is something i probably didn't get as how to query the file table and get a document for a specific product.
View 0 Replies
View Related
Mar 27, 2007
Hi there !
Thanks for taking the time to read this thread.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report.
However, I only want to select specific data from the dataset to fill textboxes and others.
I cannot change the stored procedure, but the sample of the data is shown below:-
Row Stat Val
0 dtRpt1 02/01/2005
1 Value1 1
2 Value2 2000
3 dtMailSent 02/28/2005
4 Value3 0
5 Value4 5
6 Value5 658
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories.
In my first table, I tried putting the cells as follows:-
(expressions are highlighted in italics and bold)
TextBox1 =IIF(Fields!Row.Value =0, Fields!Val.Value,"")
Table1
Column1
DetailRow1 =IIF(Fields!Row.Value =1, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =2, Fields!Val.Value,"")
Table2
Column1
DetailRow1 =IIF(Fields!Row.Value =3, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =4, Fields!Val.Value,"")
DetailRow3 =IIF(Fields!Row.Value =5, Fields!Val.Value,"")
DetailRow4 =IIF(Fields!Row.Value =6, Fields!Val.Value,"")
I only expect this report to print out one page holding the previous values.
However, it ended up printing like this
----------------------------------------------------------
Table1
Column1
DetailRow1 1
DetailRow2
Column1
DetailRow1
DetailRow2 2000
Table2
Column1
DetailRow1 02/28/2005
DetailRow2
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2 0
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3 5
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3
DetailRow4 658
------------------------------------------------------
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value.
Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
Is this the correct expression ?
=IIF((Fields!Row.Value,"Dataset") =1, (Fields!Val.value, "Dataset"), "")
and it give me an error
The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
Thanks!
Bernard
View 3 Replies
View Related
Jun 28, 2012
I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:
Table - Vehicle_Event
Vehicle_Id____Date_Time______________Speed
C1____________2012-06-28_10:10:00____5
C1____________2012-06-28_10:11:00____0
C1____________2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00____4
C1____________2012-06-28_10:14:00____3
[code].....
OUTPUT_Required:
Vehicle_Id____Date_Time___________________________ __________Speed
C1____________2012-06-28_10:10:00___________________________5
C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00___________________________4
C1____________2012-06-28_10:14:00___________________________3
C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0
[Code] .....
I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.
View 6 Replies
View Related
Oct 29, 2015
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
[code]....
View 1 Replies
View Related
Aug 18, 2015
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.
Please see my query below:-
SELECT Â Â Week_Day.Week_Day_name, Subject.Subject_Code, Â Daily_Timetable.Period_No
FROM Â Â Â Â Week_Day LEFT JOIN
           Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN
           Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
View 4 Replies
View Related
Mar 21, 2008
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
View 1 Replies
View Related
Oct 22, 2015
Here's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
View 9 Replies
View Related
Oct 29, 2015
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
[Code] ....
View 8 Replies
View Related
Apr 11, 2012
I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
View 3 Replies
View Related
Jul 29, 2015
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
View 2 Replies
View Related
May 22, 2007
Hi, I'm a newbie doing my very first UPDATE procedure. I want to UPDATE a specific row in my Sql db table, WHERE FirstName = "John" AND LastName = "Smith". What am I missing in my code below to do this specific update: Most greatful for any help, thanks.<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @User_ID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue"/>
<asp:parameter Name="User_ID" Type="Int32" />
</updateparameters>
</asp:SqlDataSource>
View 11 Replies
View Related
Jul 13, 2004
SQL question here:
I am setting up a blog, and I would like the data list to default to the most recent post ONLY. How would I define the filter to get the most recent post?
I have a publishing date in my table, and I can sort the post, but I have no clue how to select just one row to display.
I can get it to work by adding a dropdown list the is bound to the publishing date column, then retreving the post based on the control, but I don't want to use the control. (Making the control not visible is not an option.
View 2 Replies
View Related
Jul 31, 2001
I select a couple of rows from a table and sort them by the Date column. Now I would want to return only the 3rd row...
Thanks for any help!
View 1 Replies
View Related
