Transact SQL :: Concatenation Of Multiple Fields
Oct 29, 2015
I have the folloiwng fields:
ProbationLengthYears AS Decimal
ProbationLengthMonths AS Decimal
ProbationLengthDays AS Decimal
Sample Data:
ProbationLengthYears = 2.00
ProbationLengthMonths 0.00
ProbationLengthDays 0.00
I have successfully concatenated and converted all three fields into one field as Follow:
SELECT
CONVERT(varchar(10),CAST(cc2.ProbationLengthYears AS INT)) + ' Year(s)' + ' ' +
CONVERT(varchar(10),CAST(cc2.ProbationLengthMonths AS INT)) + ' Month(s)' + ' ' +
CONVERT(varchar(10),CAST(cc2.ProbationLengthDays AS INT)) + ' Day(s)'
The result is:
2 Year(s) 0 Month(s) 0 Day(s)
Ideally I would like the result to be as follow because the probation months and days are 0:
2 Year(s).
View 4 Replies
ADVERTISEMENT
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Dec 5, 2015
I have a table where I need to concatenate all values into one field separated by a comma. If the field is null display a blank value. This is my table structure and example output
Create Table #read
(
id int
,field1 varchar(100)
,field2 varchar(100)
,field3 varchar(100)
,field4 varchar(100)
[code]...
View 5 Replies
View Related
Oct 26, 2015
I have a table that exists of Serial, MSDSID and other fields.
What I need to check is to see if there are Serial numbers that multiple MSDSID numbers.
So if I have
Serial MSDSID
001 23
002 24
003 25
004 23
005 26
006 24
The results would be
Serial MSDSID Count
001 23 2
004 23 2
002 24 2
006 24 2
View 12 Replies
View Related
Aug 27, 2007
I have a table:
batch_date numeric (8,0)
batch_nmbr numeric (5,0)
batch_ctr numeric (4,0)
col4...
col5...
col6...
I am trying to select data using something like:
select col1, col2
FROM
tablename
WHERE
(BATCH_DATE +
BATCH_NMBR +
BATCH_CTR) >= (:BATCH-DATE
+ :BATCH-NMBR
+ :BATCH-CTR)
this is a mssql cursor embedded in a microfocus cobol program.
I do not get predictable results.
View 1 Replies
View Related
Jun 4, 2015
I have the following 2 fields that are sourced from an Excel spreadsheet
DocNumber - a 10 digit number
PostingRow - a number between 1 and 999
I would like to produce a new column that is a concatenation of these two fields, but the PostingRow needs to be a 3 digit number eg. 1000256153-001 ....
View 7 Replies
View Related
Oct 5, 2004
Table1
ID,Description
1,"Vowels"
2,"Others"
Table2
ID,Table1ID,Letter
1,1,"A"
2,2,"L"
3,2,"K"
4,1,"U"
5,1,"E"
6,2,"G"
7,1,"I"
How can I query the two tables to come up with a resulting table of:
ID,Description,Letter
1,"Vowels","A, U, E, I"
2,"Others","L, K, G"
View 7 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Aug 14, 2015
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
View 3 Replies
View Related
Oct 27, 2015
I have a table that has Serial numbers and MSDSID numbers and many other fields in the table.
What I need to figure out is if the table contains a distinct Serial number with different MSDSIDs.
So If I have in the table
Serial MSDSID Date Size...
001 20 1/1/2015 5
002 21 1/1/2015 3
001 22 2/1/2015 1
003 21 3/1/2015 1
004 23 1/15/2015 5
003 22 1/20/2015 6
004 23 2/21/2015 5
002 21 4/25/2015 4
I would like the results to show:
Serial Count
001 2 the count is 2 because Serial 001 has an MSDSID of 20 and 22
002 1 the count is 1 because Serial 002 only has MSDSID 21
003 2 the count is 2 because Serial 003 has an MSDSID of 21 and 22
004 1 the count is 1 because Serial 002 only has MSDSID 23
It would be even better if the results just showed where the count is greater than 1.
View 5 Replies
View Related
Sep 21, 2015
I am trying to JOIN table on datetime column truncated to full minutes with other table, where I store time intervals (in order to have all minutes in result even when there was no event in main data table).
SELECT
dateadd(minute, datediff(minute, 0, StartTime), 0) as StartTimeMinute
,ApplicationName
,COUNT(*) as Requests
FROM dbo.Profiler as p INNER JOIN dbo.MinuteIntervals as i ON i.TIMEVALUE = p.StartTimeMinute
WHERE EventClass IN (10,12)
GROUP BY dateadd(minute, datediff(minute, 0, StartTime), 0), ApplicationName
ORDER BY StartTimeMinute
How to join on p.StartTimeMinute? I do understand why this doesn't work, as p.StartTimeMinute is calculated in my query, so JOIN cannot find it in original p table.
View 5 Replies
View Related
Sep 24, 2015
I need to query a table in Database 1 then, using a primary key, pull the columns from a table in Database 2.
The query I'm using in db_1 works:
SELECT * FROM db_1.table_1 WHERE (hcpcs_cd = '20552' AND srvc_units_num>'1 ' AND claim_dos_yr >'2012')
But what I really need are the columns associated with these records in db_2.table_1. There is a primary key in both tables.
Ultimately what I need is all of the columns in db_2.table_1 and the hcpcs_cd, srvc_untis_num, and claim_dos_yr from db_1.table1
View 11 Replies
View Related
Oct 14, 2015
I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it..
The date is DOB and it's datetime but I just need to extra the getdate() from it.The open time is OPENHOUR and OPENMINThe close time is CLOSEHOUR and CLOSEMIN
so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN
And the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN
How can I get the total minutes for the check?
View 2 Replies
View Related
Jul 18, 2015
use Northwind
Go
select
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date
, dbo.Customers.CustomerID
, dbo.Customers.CompanyName
, dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
View 9 Replies
View Related
May 22, 2015
Im doing a report on total sales, however my statement below will return values that are equal to both fields ONLY.For example I want to do a query using two text boxes 'from' and 'to 'and count the total sales between the product dates 'Veh_Tyres_Date' and Veh_Parts_Date and 'Veh_Tyres Price' and Veh_ Parts Price'. however it works but if for example I do a search for 01/05/2015 from 31/05/2015 it will not return anything if the second field doesnt contain a sales date between that period.
SELECT tblVehicles.Veh_Parts, tblVehicles.Veh_Parts_Date, tblVehicles.Veh_Tyres, tblVehicles.Veh_Tyres_Date
FROM tblVehicles
WHERE (((tblVehicles.Veh_Parts_Date) Between [Enter From Date] And [Enter To])
AND ((tblVehicles.Veh_Tyres_Date) Between [Enter From Date] And [Enter To]));
View 4 Replies
View Related
Oct 16, 2015
I am working on a query that is quite complex. I need the query to return as part of the fields a field that will contain the total percentage of tickets in a version.The query is below
select cat.name as name,count(distinct bug.id) as numberOfBugs,cast(count(bug.id) * 1000.0 / sum(count(bug.id) * 10.0) over() as decimal(10,2))/100 AS qnt_pct, vers.version, dateadd(s,vers.date_order,'1/1/1970') as "Release_Date"
from mantis_bug_table bug
INNER JOIN mantis_category_table cat on cat.id = bug.category_id
LEFT OUTER JOIN mantis_project_version_table vers on vers.project_id = vers.project_id and vers.version = bug.version
[code]....
View 12 Replies
View Related
Sep 1, 2015
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66
Jaime KRiSH 5/1/2015 1043.66
Jaime KRiSH 5/1/2015 1043.66
Jaime KRiSH 5/1/2015 4150.66
Jaime KRiSH 5/3/2015 4150.66
Peter Jason 5/1/2015 321.02
Peter Jason 5/1/2015 321.02
Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66
2 Jaime KRiSH 5/1/2015 1043.66
2 Jaime KRiSH 5/1/2015 1043.66
3 Jaime KRiSH 5/1/2015 4150.66
4 Jaime KRiSH 5/3/2015 4150.66
5 Peter Jason 5/1/2015 321.02
6 Peter Jason 5/1/2015 321.02
7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
View 4 Replies
View Related
Sep 25, 2015
I want change all field in database to new datatype.I want change data from Small Integer to Integer but there are the relation in each table.
View 3 Replies
View Related
Mar 2, 2007
I've done some simple sql's for searching a field using Like,But this one is different.
I am adding a param named @searchText
I would like to bring back all records in all the fields listedbelow that has that string in the field...
WHERE a.manufacturer = b.manufacturerIDAND a.location = c.locationIDAND a.Status = d.statusIDAND a.EquipmentType = e.IDAND a.calLab = f.ID AND a.testTechnology = g.id AND (c.locationID = @location OR @location = 0)
So, each line/field above I want to search for the string and includein the dataset.
Anyone can point me in the right direction?
Thanks,
Zath
View 7 Replies
View Related
Sep 24, 2007
How can I get two fields to appear side by side in the DropDownList? Ex. FName and LName
Thanks
View 1 Replies
View Related
Mar 20, 2001
Please point me in the direction of a tutorial that will do help me do the following:
The database has been previously created and the users first & last names are in the respective columns. There is also a column that should contain the full name of the user but as such it only contains <NULL>. Is there a way to pull the first name and last name from the db then write both names to column3?
Thanks - Sharon
View 5 Replies
View Related
Sep 29, 2005
I have a table that tracks some dates, and I am looking for an SQL statemet that will check all of the fields to see if a prespecified data range is true. For exampe you enter a start and end date and then then the SQL statement would check about 12 different fields to see if any of the dates are within that range. I am trying to use an or statement, but to no avail. So if you have any help it would be greatly appreciated.
View 1 Replies
View Related
Jun 12, 2014
I have two issues I'm trying to deal with in my code.
1. I'm trying to group by first and last name, which are in two different columns
2. I'm trying to take an average of pay per miles.
Neither of these is working well. Actually, neither is working at all.
This is the code!
SELECT
OD.DriverID,
(W.FirstName + W.LastName AS 'Driver'),
DATEADD(dd,(DATEDIFF(dd,0,O.ReadyTimeFrom)),0) AS Date,
DATENAME(dw,O.ReadyTimeFrom) AS DayOfTheWeek,
Count(OD.OrderID) AS 'Total Orders',
SUM(O.Distance) AS OrderMiles,
[Code] ....
View 2 Replies
View Related
Sep 6, 2005
Hi all,I'm running into a road block, and I know I've done this before. I'mgetting fields from two tables, and I need to do a count of similaritems with it showing some extra info.Here's my fields:Log.LogId - IntLog.LogDispatcherID - IntOfficer.OfficerID - IntOfficer.OfficerFirstName - VarcharOfficer.OfficerLastName - VarcharI can get the info I need without a count with this:select a.LogID,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDBut when I try to add a count and group-by it errors out:select Count(a.LogID) as LogCount,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDGroup By a.LogIDI've done this before, but this isn't working. It's giving the error"it is not contained in either an aggregate function or the GROUP BYclause" for each field other then LogID.How can I do this? I want output similar to this:LogCountLogDispatchIDOfficerName334Tom Jones422John Smith.... EtcThanks for any suggestions or ideas...Sam Alex
View 2 Replies
View Related
Sep 22, 2005
How can I update each record in a table, based on a value in another tablewith a single SQL statement?For example, suppose I have the following two tables:Table1Name Something Color-----------------------------------------John GHAS BlueJohn DDSS BlueJohn EESS BluePaul xxxx RedRingo HJKS RedRingo FFFS RedSara hjkd PurpleSara TTHE PurpleJimi sdkjls GreenTable2Name Color------------------------John ?Paul ?Ringo ?Sara ?Jimi ?How can I update the color field in table 2 to correspond with the colorfield in table1 (so I can normalize the db and delete the color field fromtable1)?I know I could open table2 and loop through within my app; just wonderingabout a single SQL statement that would do it. I need a similar technique inother places as part of my app.Thanks,Calan
View 5 Replies
View Related
Oct 27, 2006
Hi All,
I am trying to do something like this
Select * from Table 1 where (field1, field2, field3) IN (select field1, field2, field3 from table2 where fieldx = 'rt')
This can be done in oracle. Is there any equivalent in SQL Server ?
Can anybody please help !
Thanks.
PN
View 3 Replies
View Related
Oct 27, 2006
Hi All,
I am trying to do something like this
Select * from Table 1 where (field1, field2, field3) IN (select field1, field2, field3 from table2 where fieldx = 'rt')
This can be done in oracle. Is there any equivalent in SQL Server ?
Can anybody please help !
Thanks.
PN
View 3 Replies
View Related
Sep 10, 2015
I have two tables. Status and Fourhistory tables.
Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.
Below i have given some possible sample scenarios which i face and the third table contains the expected output.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
)
[Code] .....
View 10 Replies
View Related
Aug 4, 2015
I have imported a whole bunch of tables. Most of them have an ID (int) column. Is there a way to set the ID columns across all tables to auto increment Primary Keys in bulk?
View 11 Replies
View Related
Nov 4, 2015
Setup:
Windows Server 2003 R2 - Enterprise - SP2 - 32 Bit
SQL Server 2014 Express - 32 Bit
Problem: I have a calculated field on a PO table that adds up item prices on an Item table to get the total PO value. This works as expected until there are at least 10 rows in the PO table. From the 10 row on the calculated field stops working and only shows 0.
I have experienced this before and it seems like calculated fields break on the 10th row of a table and onward.
My PO table
CREATE TABLE [dbo].[PO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Quote_Number] [varchar](max) NULL,
[Customer] [varchar](max) NULL,
[CustomerPO] [varchar](max) NULL,
[PO_Received_Date] [datetime] NULL,
[Total_PO_Value] [decimal](18, 2) NULL,
[Code] ....
View 10 Replies
View Related
Jul 22, 2006
I have multiple textboxes in a page. How do i make them insert their values to multiple fields on multiple tables using a button.
View 1 Replies
View Related
Nov 14, 2013
I have a am doing some date calcs () . The situation is that I have a Move date (date a customer moved to a new home). I want to calculate their sales for the following 0-3 months after the move (month 0 being the move month). I have the month and year of the move (MthStart, YrStart), and I am adding 3 to MthStart to get the MthEnd of that 0-3 month period. I will then find sales BETWEEN YrStart&MthStart AND YrEnd&MthEnd (there is a YrMth field in the sales table)
Of course, for MthStarts 10, 11, and 12, the ends are 13, 14, and 15. So for these, I need to subtract 12, and increment the YrEnd by 1.
I am wondering if there is a way to update both the MthEnd and YrEnd fields at one time instead of separate SETs (or maybe I am just thinking about this the hard way to begin with). Is there a way to update both in a single CASE statement like WHEN MthEnd> 12 THEN MthEnd-12 AND YrEnd+1?
Code:
CREATE TABLE #myTable (
YrStart INT,
MthStart INT,
YrEnd INT,
MthEnd INT);
[code]....
View 4 Replies
View Related