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

Transact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?

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

Transact SQL :: Concatenate Multiple Fields Into One

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

Transact SQL :: How To Find Duplicates For Multiple Fields

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

Numeric Concatenation Failure On 4 Fields

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

Integration Services :: Derived Column Transformation - Concatenation Of Two Fields

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

Multiple Row Result Concatenation

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

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

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

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

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

Transact SQL :: To Show Multiple Column In Multiple Rows

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

Transact SQL :: How To Find Duplicates For Different Fields

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

Transact SQL :: JOIN On Calculated Fields

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

Transact SQL :: Selection Criteria In DB1 - Need Fields In DB2

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

Transact SQL :: Get Check Time From Separate Fields

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

Transact SQL :: Use Field Alias In Calculated Fields?

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

Transact SQL :: Return Values That Are Equal To Both Fields ONLY

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

Transact SQL :: Query To Return As Part Of The Fields

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

Transact SQL :: Create Unique ID Based On Existing Fields

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

Transact SQL :: Change Data Type In All Fields In Database

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

Search Multiple Fields Using Like

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

Dropdownlist With Multiple Fields

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

Merging Multiple Fields

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

Checking Multiple Fields At Once

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

GROUP BY With Multiple Fields

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

Getting Count With Multiple Fields

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

Updating Multiple Fields

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

Help ! Multiple Fields Using IN Condition....

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

Help ! Using Multiple Fields With IN Condition !

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

Transact SQL :: Merging Two Tables Using Effectivestart And Effectiveend Date Fields

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

Transact SQL :: Set Fields To Auto Increment Primary Keys In Bulk

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

Transact SQL :: Calculated Fields Stop Working When There Are 10 Or More Rows In A Table

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

Inserting To Multiple Fields Using A Button

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

Possible To UPDATE Multiple Fields Conditionally?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved