i have a table with some data imported from another database. In one table shows some records 'manchester customer [][] phoneline [][] january' when I select these data it display in multilines. that mean 2 squares represent enter key.
I need to remove these from the records and replace space. so that i can select field value as a single line
how can i do this ??????
pat
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
Hi,
i searched for many hours now and I need help from the community ;)
I have a list of records and want to include the records as a comma sperated list in an other select statement.
I tried it with std. SQL and later with T-SQL, and I am sure it will work ith a stored procedure, but I am not that professional in this (try to learn...).
Background: I have a lot of data (users) and want to create a records set without having the data processed in my scripts.
So i have: records in table krgroupusers (example data):
lgroupid, luserid
3 , 1
3 , 48
4 , 1
197 , 1
197 , 48
197 , 250
197 , 251
207 , 1
207 , 250
Weiter habe ich eine Tabelle kruser (example data):
lid, strusername , strmail
1 , Administrator , admin@test.de
48 , user1 , user1@test.de
250, user2 , user2@test.de
251, user3 , user3@test.de
A StoredP. or SQL query should give me the following result:
lid, strusername , strmail , sgroups (example data):
1 , Administrator , admin@test.de , 3,4,197,207
48 , user1 , user1@test.de , 3,197
250, user2 , user2@test.de , 197,207
251, user3 , user3@test.de , 197
So I am happy for any help...
I created a package that exports contents from a table to a flat file but all my records are being displayed in a single record. where do i configure it to where each record has its own line. the columns in the flat file are fixed.
View 4 Replies View RelatedI have a table that contains some duplicate records. The entire record is not duplicated, but there are two fields which if both are duplicated, result in what I call a duplicate record. I can see that I have these duplicates by doing the following:
SELECT DISTINCT ReferenceNumber, ShipToCompanyID
FROM tabMedia
WHERE (MediaTypeID = '1') AND (FinishDate > '2011-01-01')
GROUP BY ReferenceNumber, ShipToCompanyID
HAVING (COUNT(ReferenceNumber) > 1) AND (COUNT(ShipToCompanyID) > 1)
ORDER BY ReferenceNumber
but of course, this only returns two fields. I need the entire record returned where the duplicate exists...
Hello everyone -
This is my first post to the forum and I'm very new to SQL. I apologize if this is addressed elsewhere.
Here is an example of the results I am getting from my query
AdmissionID, sNurseInit, sSWInit
100, {NULL}, SAE
100, REG , {Null}
Is there a way to combine (merge, join? I don't know the right word) these records so that a single record for the admission is returned?
AdmissionID, sNurseInit, sSWInit
100, REG, SAE
Thanks in advance!
Amy
How do i eliminate records which has spaces
i tried with
select * from table
where col1 !='' or
col1 is not null
which doesn't work.. any help.
Hi everyone, I have some trouble writing a SQL stored procedure thatcan do the following:We have data in one table in numeric form, but we want to sum the datain this table based on the values of two different alpha fields. Toillustrate, let me write the following example:Table with these records:A B 1.1 2.2 Blah1 Blah1A B 2.3 5.6 Blah2 Blah2B C 7.8 9.1 Blah3 Blah3B C 4.5 1.0 Blah4 Blah4R F 1.1 4.3 Blah5 Blah5B A 3.1 2.7 Blah6 Blah6I need to write a query that will return the following result set fromthe above table:A B 3.4 7.8 Blah1 Blah1B C 12.3 10.1 Blah3 Blah3R F 1.1 4.3 Blah5 Blah5B A 3.1 2.7 Blah6 Blah6If the alphanumberic keys are the same, the sum the numeric columns upwhilst displaying one one of the records, the blah fields don't matterif one only one is displayed. Can anyone recommend the best way to dothis? I'm running MS SQL 2005.Thanks,Herman
View 1 Replies View Related
Here is a very basic question that I have.
I have two tables, A and B. Both have a customernumber and a batchid. This combination is unique in both tables.
How can I pull back the records from table A that do not have a corresponding combination in B?
I know I could find the ones that do match and then exclude them using an inner join and subquery, but is there a simpler way?
THANKS!
Hi
I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).
For example, for the following:
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
Grape Y Green
Grapefruit N Yellow
I would want to return (assuming Colour was the distinct field):
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.
Thanks for any help you can offer.
Hi I have a table that contains duplicates. Custid, order #s
custid,order#
1, 2525
1, 2323
1, 2222
2, 6969
2, 7474
3, 8888
Here what I am trying to do. I want to create a table that contains rows from the above table in this format.
custid, order#,order#,order#
1 ,2525 ,2323, 2222
basically, I want to convert the duplicate rows into fields in a new table.
How can I pull out duplicate records based on certain fields?
Table called Bank
I want to pull out records that have duplicate inv_no, cus_no, amount,ordernum
Not all the fields are the same in each record. But I want the records that have these fields that are the same.
What's the best practice for adding / editing a record into a database with lots of fields ?I am not talking about the mechanics of it, as there are a lot of trivial examples using ADO.NET, stored procs, etc.
Deleting is easy, you just pass in (a few) primary key/keys to uniquely identify the record.
But in the real world when you have, say, a table with 100 fields! Do you code the INSERT sproc by hand, with 100 parameters... then call it with your ADO.NET code ? sounds like a lot of work to me...
What about updating! That's even worst, sometimes you may need to update only 3 or 4 fields, but using sprocs you would have to pass the whole 100 parameters in again, and "update" the whole record (when in fact you are only changing 3 or 4 fields).
With the update i could write different sprocs targeting only the fields i wish to update, but that sounds like duplicating work, vs having one generic update proc.
Sometimes i just feel like bypassing sprocs and having inline sql as it would be less work... but i know it is untidy.. and more potential to be buggy.
So come on guys (and gals)... let's hear your thoughts on how you would handle the insert / update scenarios when you have lots of fields ? Northwind examples are too trivial :-)
I need to list all the records in Table2 which don't have matching field values in Table1.
This the the exact opposite of what I need:
SELECT DISTINCT
Field1,
Field2,
Field3,
Field4,
Field5
FROM
[Table1]
WHERE EXISTS(
SELECT DISTINCT
FieldA,
FieldB,
FieldC,
FieldD,
FieldE
FROM
[Table2]
)
The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.
What I need, however, is the exact opposite.
I tried the above using NOT EXISTS but I get no records at all.
How do do this?
I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:
Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,
[Code] ....
I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!
View 5 Replies View RelatedHello I have some flat files that contain CSV records with different number of fields but the first 4 fields of each record type are the same of each re. eg there would be an entry of one record that has eight fields and another that has 6 fields. Which of the items in the toolbox can i use to filter the records based on the entry in the first 4 fields so i can process the filtered records.
Thank you
Kenalex
Using MSSQL 2012
I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.
Below is my where clause.
WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'
---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')
I am trying to multiple update records in Table B from a single record in Table A. To identify the records that need to be updated I used this:
Select
Table1.cfirstnameas'Table
1 First',Table1.clastnameas'Table
1 Last',Table1.ifamilyid,CR.icontactid,CR.lLiveswithStudent,
Table2.cfirstnameas'Table
2 First',Table2.cLastNameas'Table
2 Last',Table2.ilocationidas'Table
2 Location',Table1.iLocationIDas'Table
1 Location'
fromTable1
JoinTable3
CRonTable1.istudentid=CR.istudentid
JoinTable2
onCR.icontactid=Table2.iContactID
whereCR.lLivesWithStudent=1
andTable1.ifamilyid>0
andTable1.ilocationid<>Table2.iLocationIDandTable1.lCurrent=1
I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2. I needed Table 3 to make the connection from Table 1 to 2.
How to identify different fields with in a group of records?
Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
what is the best way to uses ssis to deal with a CSV file that contains a number of records that contain a different number of fields ?
I could just load as a single column delimited by <CRLF> but then I would have to write the code to parse the line, effectively detokenising the columns myself but if that is the case then why uses ssis at all ?
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies View RelatedWe have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
View 1 Replies View RelatedI have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?
kam
HEllo can anybody tell me how to monitor a long store procedure
line by line. Also how to put progress bar in it to tell user how
much is done.
Sabih.
Hi,
When creating a line chart, I would like to be able to show Markers for the data points, but no line between these points (as you can do in excel).
I have set the line setting to none (for the lines of interest), however the lines still show.
Is this a bug, or am i missing something obvious settings-wise?
Cheers,
M
HiIt's my stored procedure 1 CREATE PROCEDURE singleSearch2
2 @SQ nvarchar(30),
3 @pType nvarchar(11),
4 @pCol nvarchar(11)
5 AS
6 BEGIN
7 DECLARE @SQL NVarChar(1000)
8 SELECT @SQL='SELECT *,'
9 SELECT @SQL=@SQL+' CASE RTRIM(LTRIM(op))'
10 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e'+CHAR(39)+' THEN '+CHAR(39)+'اجاره'+ CHAR(39)
11 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r'+CHAR(39)+' THEN '+CHAR(39)+'رهن'+CHAR(39)
12 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'f'+ CHAR(39)+' THEN '+ CHAR(39) +' Ù?روش '+CHAR(39)
13 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r'+CHAR(39)+' THEN '+CHAR(39)+ 'اجاره - رهن '+CHAR(39)
14 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - Ù?روش '+CHAR(39)
15 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r f'+CHAR(39)+' THEN '+CHAR(39)+' رهن - Ù?روش '+CHAR(39)
16 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - رهن - Ù?روش'+CHAR(39)
17 SELECT @SQL=@SQL+' ELSE op END AS xop, CASE LTRIM(RTRIM(type)) '
18 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'z -'+CHAR(39)+' THEN '+CHAR(39)+'زمین'+CHAR(39)
19 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'m -'+CHAR(39)+' THEN '+CHAR(39)+'مسکونی'+CHAR(39)
20 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'t -'+CHAR(39)+' THEN '+CHAR(39)+'تجاری'+CHAR(39)
21 SELECT @SQL=@SQL+' ELSE [type] END AS [xtype] FROM [data] '
22 SELECT @SQL=@SQL+' WHERE ([type] LIKE %'+CHAR(39)+@pType+CHAR(39)+'%) AND ('+@pCol+' LIKE %'+CHAR(39)+@SQ+CHAR(39)+'%)'
23 Exec (@SQL)
24 END
25 GO
and i face this error: Line 1: Incorrect syntax near '-'.
Hi
Anyone have any idea how to make a line style dashed or dotted in a line chart please?
If I change the series style to dashed or dotted it still appears as a solid line, yet the legend displays a dashed or dotted line....
Thanks
My question is about coding style for specifying constraints when creating tables.
Two styles for defining constraints:
In-line:
CREATE TABLE Fruit
(
FruitID INT IDENTITY(1,1)
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED,
FruitName NVARCHAR(50),
FruitTypeID INT
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY
REFERENCES FruitTypes (FruitTypeID) ON UPDATE CASCADE,
DateCreated DATETIME DEFAULT GETDATE()
)
Out-of-line:
CREATE TABLE Fruit
(
FruitID INT,
FruitName NVARCHAR(50),
FruitTypeID INT,
DateCreated DATETIME
)
ALTER TABLE Fruit ALTER COLUMN FruitID INT NOT NULL
ALTER TABLE Fruit ADD
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED (FruitID),
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY (FruitTypeID)
REFERENCES FruitTypes (FruitTypeID),
CONSTRAINT DF_fruit_date_created DEFAULT
GETDATE() FOR DateCreated
Which style do you prefer and why?
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha