Hi
I have a table with a column 'value' as nvarchar
I want to select some rows from this table that the value of 'value' can be converted to decimal ...
it's possible that value of 'Value' contains some other characters other than digits .
Hello..
Can any one you help me in retrieving the rows which immediately after a ID 9.
i.e, My condition is to retrieve the Record which immediately follows the event 9.
The retrieved record event should be in (24,25,26)
Table structure is
---------------------------------------------
MatchID SegmentID Event Player1 Player1ClubID
---------------------------------------------
1 0 9 1 88
1 0 24 1 89
1 0 10 1 88
1 0 9 1 89
1 0 25 1 88
1 0 10 1 88
1 0 10 1 89
1 0 10 1 89
----------------------------------------------
My out put should have
---------------------------------------------
MatchID SegmentID Event Player1 Player1ClubID
---------------------------------------------
1 0 24 1 89
1 0 25 1 88
---------------------------------------------
How to get this,
Can any one help me.
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
I have a table MyTable that contains two fields MyData and CreationTime. I would like to write a query
SELECT MyData FROM MyTable
WHERE CreationTime < less than one week from now
Does anyone know the real syntax for that?
Thanks in advance,
Joannès
Hello all,
I've just deleted some rows by error in one of my tables. I'd like to find out a quick way to retrieve them.
I'm using MSSQL server 2000, version 8.00.194
Thank you in advance
I am trying to retrieve all rows with the largest value in a particular column. The largest value could return many rows for a particular users. Here is what I have thus far.
SELECT DISTINCT
ID, NAME, FOP, ACCT, CTNUM, ENDDATE, DEBIT, CREDIT, TRANSACTION_DATE, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR, PAYROLL_IDENTIFIER,
PAYROLL_NUMBER, [EARN-SEQNO], EVENT_SEQUENCE_NUMBER
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, ACCT, PAYROLL_NUMBER,EVENT_SEQUENCE_NUMBER
ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN
FROM PAYROLLYEAREND ) s
WHERE RN = 1 AND ID = '16443' AND PAYROLL_NUMBER ='7'
In the above example, the EVENT_SEQUENCE_NUMBER is populated with values from 0 to 12. Could vary per user and PAYROLL_NUMBER. The query above returns 48 rows. However, all I want are the rows where EVENT_SEQUENCE_NUMBER is equal to the highest, which is in the above example is 12. The result would be 29 rows. The where clause is not part of overall query. Just isolating on one ID.
I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?
View 2 Replies View RelatedHi,
I am new at SQL hopefully this would be a rather easy question for you guys to help me out with.
I have a table called PRODUCT with the following fields:
a. Product Name
b. Product Dept.
c. Purchase Date.
I would like to run a query to obtain all rows tha has more than one purchases on any particular day.
Any replies would be great.
Is it possible to retrieve Distinct rows from this Select Statement?
I'm getting the correct results, but duplicate rows because some customers place more than one order on the same day.
Code:
SELECT dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.CustomerEmail, dbo.Customers.DateCreated,
CONVERT(char, dbo.Customers.DateCreated, 103) AS [DD/MM/YYYY], dbo.loyalty_points.LPoints, dbo.Orders.OrderID
FROM dbo.Customers INNER JOIN
dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (CONVERT(char, dbo.Customers.DateCreated, 103) = CONVERT(char, GETDATE() - 6, 103))
Dear Participants,
We are using merge replication for multi locational database but we are facing one problem in only one table which is not included in replication-
Table name is xxxxmast has only 39 row static information, but it used by every users for all task as select only information from this table like-
Select fin_year into mem_variable from xxxxmast where co_name = :global_variable
go
Code validation from here only.
Right validation from here only.
Report retrival validation from here only.
It means its usage for select from every user frequentely for so many times but we have to only fetch information from this table.
It was working prior fine but rightnow get problem for while-
Today Dated 08-March this table not accessible fro three times in eight hours-
1st time for 10 minute.
2nd time 10 minutes
3rd time 52 minutes.
Users want to login but at the login time years and other validation from this table, so users awaited for above mention time.
We had have do following by yesterday-
Drop table xxxxmast.
Create table xxxxmast.
Insert required data.
This is realy trouble for our application.
Any help realy great for us.
Thanks
R.Mall
hi friends,
i got a error while retrieving more than 100000 rows (records) from a table .. can any one tell me what is the maximum number of rows retrieve from a database to reports... and how can i overcome this issue...
I have a table with a datetime and an Item column.
I want to retrieve only the rows where item didn't change for a given value.
In the example below, given the value of 5 I only want the rows starting at 19:14:50 to 19:26:06.
Dateteime Item
2015-06-05 19:05:03.0002
2015-06-05 19:08:31.0002
2015-06-05 19:14:50.0001
2015-06-05 19:19:33.0001
2015-06-05 19:20:46.0001
[Code] ....
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID )
and the newly inserted row with a insert time stamp
so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.
example of Data would be like
exisiting data
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
New row to insert would be
12 DDS 15 M DFG ST 4/5/07
the data should reflect
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 4/5/07
12 DDS 15 M DFG ST 4/5/07 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
Please provide your input as much as you can even though it might not be a 100% solution.
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View 3 Replies View Related I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
I am facing an issue that Data flow task failing after loading 29000 rows out of 2lakhs rows.
I am loading data from .csv file to OLE DB Destination.
This data flow task is placed inside For each loop container.
is this issue because of any performance issue in SSIS packages such as buffer size.
find the error below:
DFT Load Data from FlatFile:Error: The conditional operation failed.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "DER Add Calc Columns" failed because error code 0xC0049063 occurred, and the error row disposition on "DER Add Calc Columns.Outputs[Derived Column Output].Columns[M_VALUE_NUM]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "DER Add Calc Columns" (48) failed with error code 0xC0209029 while processing input "Derived Column Input" (49). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[code]....
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
Dear All
I have a table with the following structure in sql server 2005
create table app(
sno int,
name varchar(50),
add varchar(50),
city varchar(50),
state varchar(50)
)
it contains the follwing data
------------------------------------------
sno name add city state
------------------------------------------
1 mark street no1 newcity newstate
2 mark street no1 newcity newstate
3 mark street no1 newcity newstate
4 mark street no1 newcity newstate
5 mark street no1 newcity newstate
6 mark street no1 newcity newstate
7 mark street no1 newcity newstate
8 mark street no1 newcity newstate
9 mark street no1 newcity newstate
10 mark street no1 newcity newstate
11 mark street no1 newcity newstate
12 mark street no1 newcity newstate
13 mark street no1 newcity newstate
14 mark street no1 newcity newstate
15 mark street no1 newcity newstate
16 mark street no1 newcity newstate
17 mark street no1 newcity newstate
18 mark street no1 newcity newstate
19 mark street no1 newcity newstate
20 mark street no1 newcity newstate
----------------------------------------
I want to retrive previous 5 records, next 5 records and the record that meet the where condition of a select query.
When I run
select sno,add,name,city,state from app where sno=7
I want the following result
------------------------------------------
sno name add city state
------------------------------------------
2 mark street no1 newcity newstate |
3 mark street no1 newcity newstate |
4 mark street no1 newcity newstate | -- previous 5 records
5 mark street no1 newcity newstate |
6 mark street no1 newcity newstate |
7 mark street no1 newcity newstate --- searched record
8 mark street no1 newcity newstate |
9 mark street no1 newcity newstate |
10 mark street no1 newcity newstate |--- next 5 records
11 mark street no1 newcity newstate |
12 mark street no1 newcity newstate |
----------------------------------------
if there is a method to get the above result set, kindly post the query.
Hi, I need to write a query which I have never attempted before and could do with some help.... I have a Groups table and a Users_Groups look up table. In this model, users can only be assigned to 1 group. If a group is deleted, a trigger should fire and delete any rows in User_Groups having a matching Groups.Ref. Unfortunately, the trigger hasn't been firing and I now have a load of defunct rows in Users_Groups relating users to groups which do not exist.I now need to find all of these defunct rows in Users_Groups so that I can delete them. How can I find rows in Users_Groups where the parent rows and refs in Groups are null? I've tried searching the net for something similar but don't even know how to word the search properly to get any half relevant results. Cheers PS, I do realise I need to tighten the constraints on my database
View 5 Replies View RelatedI'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View 4 Replies View Relatedhelo..
I have 100,000 rows in the database and I want to read results for eg: from 5000 to 5050 by DataReader.
I wrote this code to do this but its too slow:
Dim SlctStr As String = "select * from topicstbl where partID like '" & PagePartID & "'"
Dim ReadCom As New SqlClient.SqlCommand
ReadCom.CommandText = SlctStr
ReadCom.Connection = MainLib.MyConnection
Dim MyReader As SqlClient.SqlDataReader = ReadCom.ExecuteReader()
Dim StartTNum As Long = 5000
For IR As Long = 0 To StartTNum - 1
MyReader.Read()
Next
Do While MyReader.Read
StartTNum += 1
If StartTNum > 5500 Then Exit Do
'''''''''''''''''''
Loop
MyReader.Close()
is there another way to do the same thing better off than this code?
HI Chaps
very simple question this time
Is there any method availabe to retrieve AM or PM value from date, IN TSQL
hoping will get answer shortly
regards,
Anas
I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
Thanks
Steve
I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.
View 4 Replies View RelatedI am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code
Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)
I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?