Hello,
I would like to create more lines by concatenating values.
When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +
'example'> the result is <This is an example> (on the same line).
I woul like to get:
<This
is
an
example> (each 'word' on a new line, but in 1 field)
Whis SQL statement do i have to use?
How do I change a tab (char(9)), newline char (char(10) ) or carriage return (char(13)) in my string into a single space character? This is when I select from a table, the value should have any linefeed or tab converted to space.
Thanks in advance..
This is obviously a radical idea but some actually DO want to store linefeeds in varchar columns.
In MySQL I can escape difficult characters for example
INSERT INTO sometable(address) VALUES("23 SomeRoad
SomeTown
SomeCounty");
Does anyone know how to do this in Transact SQL?
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies View RelatedHow can I save the text of a rich text box in a sql express 2005 datatable ?
Of course I'd like to save the string and the format of the text (bold, color etc...).
Which column type I have to use for this RTF field ?
Thank you.
Are Primary Key fields automatically indexed, or do you have to create a seperate index for a PK in order for it to be indexed? I'm using SQL Server 2005.
View 7 Replies View RelatedI have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?
TIA
I have the following fields in table A:
GL_ID|GL_Name_VC | Amount |Period_TI|Year_SI
===================================================
1000| Inventory| 8,000.00 | 01 | 2005
===================================================
1000| Inventory| -3,000.00 | 02 | 2005
===================================================
1000| Inventory| 5,000.00 | 02 | 2005
===================================================
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
GL_ID | GL_Name_VC |Op Bal|Period_Dr|Period_Cr|Period Bal|Closing Bal
======================================================================
1000 | Inventory |8,000 | 5,000 | -3,000 | 2,000 |10,000
The above report has an Op Bal column which is the sum of all amount in Period 01 in
Year 2005 carried forward as opening balance in Period 02, Period_Dr Column would contain
all positive amount in Period 02 & Period_Cr Column would contain all negative amount
in Period 02. Period Bal is the summation of both Period_Dr & Period_Cr and Closing Bal
column is the summation of Op Bal + Period Bal.
Guys, hope someone out there can help me with the sql command for the above report?
Hi
In Sqlserve we can create dynmic field on sql like:
Select '' as Name from Employee.
It creates a string field.
I want to how to create a Bit field in this way.
Regards
Deepak
Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver
Hi there, my situation is
I have a table x with 3 filed
a nvarchar(100), b smalldatetime, c text(16)
. I want to create a view like this:
select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%
So, I always get a message error said wrong datatype, how can i do, please help me.
I would like to create a field that increments automatically by 1 - to use it as an identifier for a row. This is so dammed simple but I can not find how to do it !
Thank you very much for any help.
By the way I configure SQLExpress with VS2005 and SQL Express Manager.
Thank you very much,
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
In my SQL Database I insert a Google Map link as this
http://maps.google.com/maps?q=42.2362,-71.7439+(ESN=0-10073)
How can I mask it so that it appears in my Web page layout as a hyperlink, for my reports.
Something like: Map This
Now i m working in a existing project. I have to do some updations. Inthat project database table contain a table(usergroups) field name as'Name'. When i am trying to insert a new record in (usergoups) tablelike as follows'insert into UserGroups(Name,modusr,moddt) values ('sam',131,'7/23/200610:02:13 PM')The response ll come as follows,Invalid column name 'FirstName'.Invalid column name 'LastName'.The usergroups table structure is as follows,UserGroupId int 4 (PK field)Name varchar 50modusr int 4moddt datetime 8Plz tell the correct insert query to add records for the abovetable....Thanks in advance
View 3 Replies View RelatedI am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier. Here is few result samples I receive from this code I am using now.
ReqNo ProcID
7102005 1409
7102005 1796
7139003 1411
7139003 6097
7261030 1409
7261030 1796
7268303 3998
7268303 4000
I would like to create a single row for each "ReqNo" and have a field that will an array of the "ProcID" results I receive. In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field? I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table.
So, ideally my result would be:
ReqNo ProcSet
7102005 1409, 1796
7139003 1411, 6097
7261030 1409, 1796
7268303 3998, 4000
Is this possible?
declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
select
@startdate = dateadd (dd, -7, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
select
@enddate = dateadd (dd, 1 , @startdate)
select distinct
pp_req_no as ReqNo,
pp_cproc_id_r as ProcID
from
risdb_rch08_stag..performed_procedure
(index pp_serv_time_r_ndx)
where
pp_service_time_r between @Startdate and @Enddate
and pp_status_v = 'CP'
and pp_rep_id > 0
order by
pp_req_no, pp_cproc_id_r
I'm want to store a time from a datetimepicker control(format = time) into a sql table. I'm using c# and windows forms with vs2008. Some example code would be nice. Thanks.
// dateTimePicker2
//
this.dateTimePicker2.Format = System.Windows.Forms.DateTimePickerFormat.Time;
this.dateTimePicker2.Location = new System.Drawing.Point(225, 21);
this.dateTimePicker2.Name = "dateTimePicker2";
this.dateTimePicker2.ShowUpDown = true;
this.dateTimePicker2.Size = new System.Drawing.Size(96, 20);
this.dateTimePicker2.TabIndex = 1;
Question?
1. What type should the time field be in the sql table? I want to be able to calculate with it at some time.
What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.
I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.
So instead of:
a
122
b
joe
c
st
I would have:
d
122 joe st
Thanks!
I am a C# developer who was forced to use a DataSet with Infragistics grid instead of the class object I was using as a data source, so that I can implement an AddNew functionality directly in the grid.
In code I would simply add a read only property to do this, and it was much simpler, but if someone could point in the right direction as to can a view do what I want.
The field I want to create is called "Info" short for HasInfo.
Info has 3 values (from an enumeration) NoInfo = 0; HasResume = 1; HasHomeInfo = 2
The business logic for this is if we have a resume, I show a resume icon in the grid, if we do not have a resume, if we have home phone # or mobil # then an icon of phones are shown in the grid.
To find out if a contact has a resume the SQL is going to be something like if exists (select ContactResumeID from ContactResume Where ContactResume.ContactID = Contact.ContactID) then 1; and if that is not true; check if Contact.HomePhone is Not Null Or Empty or Contact.WorkPhone is not null or empty then 2 else 0
I can do this in C# blindfolded, but in SQL I am fish out of water for compound if statements.
The SQL for this view is shown below (creating using SQL Server Gui Selector which I think is real cool).
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Employees]
AS
SELECT dbo.Company.CompanyID,
dbo.Company.Name AS CompanyName,
dbo.Contact.FirstName,
dbo.Contact.LastName,
dbo.Position.Title,
dbo.Department.DepartmentName,
dbo.ContactPosition.WorkPhone,
dbo.ContactPosition.Extension,
dbo.Contact.ContactID,
dbo.ContactPosition.ContactPositionID,
dbo.Department.DepartmentID,
dbo.Position.PositionID
FROM dbo.ContactPosition INNER JOIN dbo.Contact
ON dbo.ContactPosition.ContactID = dbo.Contact.ContactID INNER JOIN dbo.Position
ON dbo.ContactPosition.PositionID = dbo.Position.PositionID INNER JOIN dbo.Department
ON dbo.Position.DepartmentID = dbo.Department.DepartmentID INNER JOIN dbo.Company
ON dbo.Position.CompanyID = dbo.Company.CompanyID
Thanks,
Flying Elephant Software
I'm trying to create a calculated field that gives me the avg 75 percentile.
Right now I get this value by doing the following:
Create data set:
Select top 75 percent <field>
from <table>
Then I create the following calculated field
Avg(Fields!<field>.value,"<data_set_name>")
But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.
Is it possible?
Thanks!
Hello,
I would like to create an automatique counter Field like in ACCESS from Microsoft.
How can i do that ?
Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?
I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.
View 13 Replies View RelatedI am a student, and I am so confused by SQL code that calculates incomes of my contract in a year or each month of year.
I have 3 tables:
lodgings_Contract:
id_contract indentity primary,
id_person int,
id_room varchar(4),
day_begin datetime,
day_end datetime,
day_register datetime
money_per_month money
electric:
id_electric indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_electric money,
status bit
Water:
id_Water indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_water money,
status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year'
, Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes'
From lodgings_Contract a
, electric b
, Water c
Where a.id_room = b.id_room
And a.id_room = c.id_room
And b.status = 1
And c.status = 1
Group by Year(day_register)
I have a table that houses fully qualified table names, roughly 15. How can I iterate that table and create a view ? For example, something like this
Code:
Create Table tocreateviewfrom (dbname varchar(100))
Insert Into tocreateviewfrom Values (foxfire.dbo.abcd), (foxfire.dbo.abcde), (foxfire.dbo.abcdf), (foxfire.dbo.abcdg), (foxfire.dbo.abcde), (foxfire.dbo.abcdl)
--Then somehow iterate each value in the field dbname to create a view something like
Create View viewcreatedfromtable As
Select * from foxfire.dbo.abcd
Union All
Select * from foxfire.dbo.abcde
[Code] ....
I tried this, but it is only printing the last result returned not the entire result set
Code:
Declare @database varchar(max), @sql varchar(max)
Declare c1 Cursor For
Select Program
from tocreateviewfrom
Open c1
[Code] .....
I have two tables: TestA and TestB. Both tables have 3 fields: ID,Name, and RunDate. I need to create a query which will join the twotables first on Name but then I need to match up the RunDates eventhough the RunDates won't be the same.CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')Here's a common scenario:User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Thenthe user updates TestB data for Account 1, 15 minutes later. I wantthese two records to match. The user must always update TestA databefore they update TestB data. Therefore, there might be more rows inTestA then in TestBHere's what the results should look like for the above data.Name TestA Date TestB Date---- ---------- ----------Account 1 9/1/2004 12:00pm 9/1/2004 12:15PMAccount 1 9/2/2004 1:00pm 9/2/2004 1:15PMAccount 1 9/3/2004 3:00pm (NULL)Account 2 9/5/2004 4:00pm 9/5/2004 4:15PMAny help would be much appreciated!!!!
View 2 Replies View RelatedI'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB
"6abc" "0006"
"457def" "0457"
"7232hij" "7232"
I have implemented a temporary solution using a CASE statement:
SELECT ColA, ColB =
CASE
WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4))
WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3))
WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2))
WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1))
ELSE ''
END
FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4)
DECLARE @num int
SET @ColBstring = ''
SET num = 1;
-- Get the numeric prefix from ColumnA's string
WHILE(isnumeric(substring(colA, 1, num)) = 1)
@ColBstring = (substring(colA, 1, num)
num = num + 1
-- Prepend the ColumnB string with zeros
WHILE(LEN(@ColBstring) < 4)
@ColBstring = '0' + @ColBstring
Thanks for any help,
Mike
Hello Friends,
I am creating a report in which I want to create group column using two field value. Is it possible to do so? We have a requirement in which we are fetching data from two different hierarchy.
A B C D E F G H I J K
L 1 2 3....................4 5
M .............................
N .........................
O .......................
P
The report matrix look like the above one. The elements A,B,C are coming from one hierarchy and D,E,F,G,H,I,J,K are coming from other hierarchy. But i have created one data set to fetch the values for the report. But while creating the column group I am getting both two diff fields so I am not able to use it in single Column group and I want to use only one column group.
Can anybody help me out to solve this issue?
I use data model as the datasource of my report. I want to create a new field basing on a date type field.
So I create a new field with function like this 'DATEADD(MONTH, 3, #8/18/2009#)', but there is always a error message box said 'the arguments to the following function are not validATEADD'
How should I write this function?
I'm trying to get a SQL 7 and 6.5 DB to interact, but while there is no problem in SQL7, I cannot create a table called "Public" or a field called "Primary"!!
Does anyone know why this might be and if so where I might get a list of any other "invalid" names??
Thanks in advance,
Damon
Hi,
I want to execute some queries inside a stored procedure, get the data and create from them some strings with formatted parts (bold, italic, underline, different color fonts) and save them in a text field. These strings are going to be displayed in some DBRichText control in a database application built with Delphi. Is there an easy way to create these complex rtf formats in MSSQL
or some ideas regarding this problem?
Best Regards,
Manolis Perrakis
I want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now:
Code Block
SELECT AutoID,
CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId
FROM NavisionReplication.dbo.Tbl_Mars_Calender
ORDER BY Mars_Calender_Year DESC
what am i doing wrong?
Greetings,
I have several reports for which the user has asked to have an optional muti-value parameter. They want to be able to select zero, one, many, or all values in the parameter list. The parm list is created through a query and the values are not static.
I would like to allow the user to leave the muti-value field empty if they want to allow all values to appear on the report. I've read some discussion about populating a multi-value default with the same query that produces the multi-value list values - presto, everything is selected. However, this is not a desirable solution for me because I "echo" the users parameter selections in the report heading. Selecting all values (and some parms have a lot of values) would cause the "parm feedback" section to grow large and unreadable.
In short, I don't want to tell the user they have to select everything when they really want to select nothing.
Is there any way to have a muti-value parm that won't insist the user select one or more values?
Thanks,
BCB
Hello All.
You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.
And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.
My below script shows error. I have following records in my TempWeekFile
Week_No WeekCnt
10 1
11 2
12 3
13 4
How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.
---------------------------------------------------------
if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
drop table WeeklySalesToThird_Month
Declare @WeekCount int
Declare @Cnt int
Declare @WeekNo varchar(2)
Select @WeekCount = count(*) from TempWeekFile
Select @WeekCount
Select @Cnt=1
CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] (
[Grp] [varchar] (30) NULL ,
[SubGrp] [varchar] (30) NULL ,
while @Cnt<=@WeekCount
Begin
select @WeekNo = Week_No from TempWeekFile
where WeekCnt=@Cnt
[Week_@WeekNo] [Money] NULL
@Cnt=@Cnt+1
End
) ON [PRIMARY]
GO
--------------------------------------------------------