Transact SQL :: Aggregate 2 Dates And Concatenate Into New String

May 21, 2015

Have this table

ACCOU  NAME      NAME TODATE                            ID     EDUCAT    EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL  2018-02-11 00:00:00.000

Need to get it like

ACCOU  NAME      NAME TODATE                            ID     EDUCAT    EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL  2018-02-11 00:00:00.000

In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.

View 11 Replies


ADVERTISEMENT

Transact SQL :: Concatenate String Using Conditions?

Sep 19, 2015

I have a SQL table like this

col1      col2      col3
1           0          0
1           0          1
1           1          1
0           1          0

I am expecting output as 

col1      col2       col3      NewCol
1           0          0             SL
1           0          1             SL,PL
1           1          1             SL,EL,PL
0           1          0             EL

condition if col>0 then SL else '',  if col2>0 EL else '', if col3>0 PL else ''

View 5 Replies View Related

Transact SQL :: BETWEEN Using String Dates Doesn't Work In Some Cases

Sep 21, 2015

I put this series of select statements to verify that the BETWEEN statement is working properly. I should always get “Between” below.

SELECT
CASEWHEN'1/1/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
SELECT
CASEWHEN'1/31/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END

[Code] .....

View 4 Replies View Related

Aggregate/Concatenate Strings In A Script Component?

Sep 12, 2007

Hi--

I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.

I have data coming from an Excel Spreadsheet that looks like this:

CustNumb Invoice
1 a
1 b
2 c
3 d
3 e
3 f

I would like an output that looks like this:

CustNumb Invoice
1 a, b
2 c
3 d, e, f


I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.

Thanks for any help or ideas...
Hilary

View 10 Replies View Related

Aggregate Function To Concatenate Columns Data Into A Single Row

Aug 9, 2007

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid

Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22

Any advise would be much appreciated.

View 6 Replies View Related

How To Get Aggregate Start And End Dates

Oct 8, 2004

Hi, I'm a bit lost on this SPROC, hoping somebody can jumpstart my brain a bit please.

What I have is tbl_Classes, with a row for every class that's been taught at a school. Each class was taught on certain dates, stored in tbl_Class_Dates. tbl_Class_Dates stores EVERY day the class met, not just the start and end dates. However, in this one case, I want to list class history only showing start and end date... something like:

SELECT Class_ID,
MIN(Class_Date) AS 'Start_Date',
MAX(Class_Date) AS 'End_Date'
FROM tbl_Class_Dates
WHERE ...
GROUP BY Class_ID, Class_Date
HAVING Class_Date > @SomeCutoffDate


Problem is, when I run the above format, it still gives me EVERY class row, and the MAX and MIN functions don't seem to work...

I think I'm on the wrong track here. Incidentally, the old PROC is using a CURSOR and looping through, getting the start and end date and storing it all in a temp table. I'm hoping to improve on that efficiency.

Thanks very much for any help, or let me know if I can clarify better. Cheers!

- james

View 9 Replies View Related

Is There An Easier Way? Two Aggregate Fields From One Based On Dates

Oct 19, 2007

When I was looking at this I knew that I've done this same issue before without using temp tables at all but I need a push to to jar it loose.

I have a table like this:







Balance
Date

1
200
2/14/2000

2
350
2/14/2000

3
32
2/14/2000

2
723
2/14/1998

3
354
2/14/1998





1
321
2/14/2000

2
673
2/14/1998

3
581
2/14/2000

2
574
2/14/1998

3
50
2/14/2000

1
10
2/14/2000

And essentially need this.










Total Balance Before 1/1/2000
Total Balance After 1/1/2000

1
0
531

2
1970
350

3
354
663



Right now I'm splitting it into two temp tables and then joining them together.

Temp Table 1 has the sum of the balance grouped by field1 before 1/1/2000 and Temp table 2 has the after 1/1/2000 criteria.

Table 1 gets updates with field1 values from table 2 that aren't already there. And then the balance field after 1/1/2000 is merged in.

Utimately this will be used in a SPROC for a Multivalued SSRS report.

View 3 Replies View Related

Transact SQL :: Concatenate IDs Into One Row For Each Name

May 13, 2015

I have the below problem:

DECLARE @t TABLE
(
ID int, 
Name nvarchar(255)
)
INSERT INTO @t
SELECT 1, 'Raven'

[Code] ....

Returns the below:

ID     Name
1       Raven
4       Raven
43     Jack

Any names which are appearing twice I would like to have only one row so concatenate the IDs into one row for each Name, like below:

ID         Name
1, 4      Raven
43        Jack

Is this possible?

View 4 Replies View Related

Concatenate String

Oct 25, 2004

Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'

Is it possible.
Please help :)

View 3 Replies View Related

How To Concatenate A String To A Int Value

Mar 5, 2007

I hope this is the correct place to ask this...

I would like to concatenate a String value to an Int value using an SQL statement. At the moment it reads like this:

SELECT 'website.com/shop/product.cfm?ProductID=' + Products.ProductID AS Product_URL

But unfortunately I am getting the error:
"Conversion failed when converting the varchar value 'website.com/shop/product.cfm?ProductID=' to data type int."

Any idea how to get around this at all just using an SQL query statement?

View 2 Replies View Related

Concatenate SQL String...

Feb 20, 2008

Hi all I need some help in concatenatng a string in T-SQL. Having used the Command Microsoft Access inside the 'SQL View' window and typed the following it worked perfectly.






Code Snippet

UPDATE tblValidUsers SET blocked_users = blocked_users + 'name_123@hotmail.com;' WHERE userid='Onam'

However attempting the same command in T-SQL I get the following error:

Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals text.

Reason for having this command is I want to be able to add something to the end of the field "blocked_users" without actually overwriting the fields contents.

So for instance if I had the items: "Item1, Item2, Item3" in blocked_users and I updated it with "Item4" then the value "Item4" would be added to the end thus the use of "+" is used to concatenate. Is there a way of doing this in T-SQL?

Thanks for the help, Onam

View 6 Replies View Related

Transact SQL :: Concatenate Numbers (not Add Them)

Nov 6, 2010

Let’s say in one field there is the "year" as an integer 2010, and in another field is the "month" as an integer 11. How can you concatenate them and not add them?

Essentially the result I'm looking for based on the example would be this: 201011 but I still want this to be an integer and not a string.

View 16 Replies View Related

Concatenate String In Query

Apr 22, 2008

Hi,

As I build a record set in an SP I need to add in a string containing a list derived from a second query. I need the results of the sub query to be presented as a single string in the first query, separated by a single space.

I have no idea how to do this in t-sql, and am doing it on the web server at the moment, but becase the dataset is quite large, I'm getting 20 - 40 second processing times which is far too long.

I have found reference to the xp_sprintf function but this is not supported by my host, so not a solution.

I'm no expert in t-sql, so I imagine there's a way somewhere, and would be grateful for any advice available.

regards,

NEIL

Neil

View 5 Replies View Related

Convert String To Hex And Concatenate With

Sep 4, 2014

To start, I am NOT a SQL programmer. I have to do some minimal SQL administration (DB Creation, Backups, Security) on spatial databases that are for the most part managed by a 3rd party program. My experience with T-SQL is mostly simple tasks (i.e. Select and Update statements)..However I have been requested to calculate an ID Field using the values of two other fields. Per the request, I need to convert one field to Hex and concatenate with the second field.

ex. Field 1 + Field 2(hex string) = Field 3
Field 1 = 'FF02324323'
Field 2 = 'Smith Creek'
Field 3 = 'FF02324323536D69746820437265656B'

Field 1 VarChar(10) (Code)
Field 2 VarChar(65) (Common Name)
Field 3 VarChar(max) (ResourceID)

Spent half the day searching and have tried various forms of CAST, CONVERT, fn_varbintohexstr and others but have unable to come up with the correct combination to get what I need.

View 3 Replies View Related

Concatenate A String Problem

Oct 18, 2006

Hi to all:
In my package, I have in OLE DB SOURCE a statement:

DECLARE @CMonth as smalldatetime
SET @CMonth = '11/1/2006'
select day(@CMonth)+month(@CMonth)+year(@CMonth) as ID_Month

and in OLE DB DESTINATION, I have ID_Month column as a char(10).

I want to be result as concatenated string 1112006, but I receive 2018 (As a result of calculation)
Can anybody help me? Thank you

View 3 Replies View Related

Transact SQL :: Concatenate Display Name Using XML PATH

Oct 29, 2015

I need to concatenate the DisplayName column with distinct UserName and SysName using XML path

 SELECT [sysName], [User_Name],[DisplayName] FROM
 (SELECT v_Add_Remove_Programs.DisplayName0 AS [DisplayName], v_Add_Remove_Programs.Publisher0 AS [Publisher]
, v_R_System_Valid.Netbios_Name0 AS[sysName],v_R_System_Valid.User_Name0 AS [User_Name],v_Add_Remove_Programs.Version0 as [Version]
FROM [Offline].[dbo].v_Add_Remove_Programs
JOIN  [Offline].[dbo].v_R_System_Valid ON [Offline].[dbo].v_Add_Remove_Programs.ResourceID = [Offline].[dbo].v_R_System_Valid.ResourceID)

[code]....

View 3 Replies View Related

Transact SQL :: Concatenate Description In 2 Rows

Oct 7, 2015

 I have a requirement .Where i might have multiple rows one of the row will have description and second row might be related description but wiht routning number null .I want to concatenate these rows

Id
Routingnum
Ddesc
1
101
A

[code]....

View 8 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

Turn Hex Into Char String And Concatenate

Feb 16, 2006

I've been trying to return hex data in a way that can be concatenated.
I need the actual hex info (e.g. 0x6E3C070) as displayed
since it contains info about the path to a file.
So I can turn it into D:6E3C7 to get the path to the file.
In searching around I have come across a way to do this but can't figure out how to get it to run through a column and either display or insert into a table multiple results.

-Here's the user function that converts an integer into a hex string-

CREATE FUNCTION udf_hex_string (@i int)
RETURNS varchar(30) AS
BEGIN
DECLARE @vb varbinary(8)
SET @vb = CONVERT(varbinary(8),@i)
DECLARE @hx varchar(30)
EXEC master..xp_varbintohexstr @vb, @hx OUT
RETURN @hx
END
GO


-Using this table-

create table
XPages
(PageStoreId int null,
HexString varchar (30) null,
VolumePath varchar (60) null, )


--'PageStoreId' contains the data that needs to be converted into the editable hex string
--'HexString' is where I'd like it to go so I can parse it later.

--I can run the below select and get the hex string. But am stuck on how to run a select or update that would run through the 'XPages.PagestoreId'
column and insert the hex string into the 'XPages.Hexstring' column. 'XPages.PagestoreId' could have 100's of entries that need to be converted and placed in the relevant the 'XPages.Hexstring' column.


SELECT dbo.udf_hex_string(1234)



Thanks

View 3 Replies View Related

Concatenate String And Pass To FORMSOF?

Jul 23, 2005

The following query works perfectly (returning all words on a list called"Dolch" that do not contain a form of "doing"):SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "doing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)However, what I really want to do requires me to piece two strings together,resulting in a word like "doing". Any time I try to concatinate strings toget this parameter, I get an error.For example:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "do' + 'ing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)I have also tried using & (as in "do' & 'ing") and various forms of singleand double quotes. Does anyone know a combination that will work?FYI, in case this query looks goofy because of the unused "CombinedLexicons"table, it is because the end result should be a working form of thefollowing...Figuring out the string concatination is just a step toward this goal:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')WHERE (dbo.CombinedLexicons.vchWord IS NULL)Thanks!

View 2 Replies View Related

Concatenate String Then Convert To Datetime

Jan 23, 2008

I've been trying to do the following:





Code Snippet
if year(@SomeDateTime) <> @SomeYear

set @SomeDateTime= convert(datetime, @SomeYear+ '1231', 112)


If variable @SomeDateTime evaluates to 20080101 and @SomeYear = 2006, I wish that my variable @SomeDateTime becomes 20061231 (December 31st).

The way it's written now, it doesn't work... @SomeDateTIme evaluates to 1908-11-12.... ?!





View 6 Replies View Related

Looping Through Temporary Table To Concatenate A String

Mar 27, 2007

I have a large table that looks like this.
(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))
1, 1, p12, 1, p23, 2, p34, 2, p45, 3, p56, 3, p67, 4, p78, 5, p19, 5, p210,5, p83
i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.
can anyone help?
i have to use temporary tables. (not cursors-with cursors i know how to di it, but i want with temporary table)
thanks in advance

View 1 Replies View Related

Concatenate A String Within A Loop From A Temp Table

May 11, 2006

I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

View 9 Replies View Related

Transact SQL :: Convert Int To Time And Concatenate It With Datetime Field?

Sep 16, 2015

I have a datetime field for e.g 2015-09-15 00:00:00.000 and an integer field for e.g.100809.

The integer field is actually a time value as in hours,minutes,seconds. For e.g. the value

100809 means
10-hours
08-minutes
09-seconds

I need to combine the datetime and the int field to get the output as below

2015-09-15 10:08:09

View 7 Replies View Related

Transact SQL :: How To Concatenate Year / Month And Day Into Actual Date

Jan 10, 2012

I am working with a SQL Server database that was set up to store the year, month, and day in separate columns, rather than use a single column for the date. I plan to change this so that we store dates and times using the datetime data type. Until then, for now, I need to write transact-SQL select statements to concatenate the year, month and day to create a date that can be displayed in the results window in the format yyyy/mm/dd.

I can't seem to find any built-in function in SQL Server that will let me do this. Of course, using Excel or Access, I can use the DATE() function to reconstruct the date into yyyy/mm/dd. Is there a way to reconstruct the date into yyyy/mm/dd in SQL Server? 

View 7 Replies View Related

How Can I Concatenate A String To A Int Variable? Need Correct Combination Of Quotes!

May 26, 2008

Hi,
I need to concatenate a string with an int variable on a stored procedure; however, i looks like i am lost in single and double quotes. Does any one know the right comination of quotes for this please? My Code is below:
 1 @Price int
2
3 DECLARE @SqlPrice varchar(50)
4
5 if (@Price is not null)
6
7 set @sqlPrice = 'AND price' + '' > '' + '' + @Price + ''
8

 

View 4 Replies View Related

Transact SQL :: Aggregate In Subquery

Oct 22, 2015

(select SUM(sales.Total) from sales where StudentHist.Curdate = max(sales.curdate))  AS 'Balance'Iam trying to write a subquery to  calculate the total amount of sales until the Curdate  in studenthist equals the Curdate in sales table..how to write this query??

View 11 Replies View Related

Transact SQL :: Aggregate Last 12 Months

Sep 2, 2015

I have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month). 

For example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015. 

DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123','P123','1/31/2015',10)
INSERT INTO @TEMP VALUES ('C124','P124','2/28/2015',20)
INSERT INTO @TEMP VALUES ('C125','P125','3/31/2015',30)
INSERT INTO @TEMP VALUES ('C126','P126','4/30/2015',40)

[Code] ....

the output for the above example should be
C123 P123 7/31/2015
 70+10=80

is this possible?

View 10 Replies View Related

Aggregate String Concatenation

Oct 3, 2005

I have the following data:

create table TempTable(name varchar(50), value varchar(50))
insert into temptable values ('A', 'one')
insert into temptable values ('A', 'two')
insert into temptable values ('A', 'three')
insert into temptable values ('B', 'four')
insert into temptable values ('B', 'five')

and i would like the following output:

'A', 'one, two, three'
'B', 'four, five'

any ideas on how to accomplish this in Sql Server 2000?

thx in advance..

View 1 Replies View Related

Transact SQL :: Group By With Non-aggregate Columns

Aug 5, 2015

How can I aggregate this result into 1 row? (I got it from a UNION ALL)

Article         Assort1    Assort2
50095811    K1             NULL
50095811    NULL          K3

I would like to have

Article         Assort1    Assort2
50095811    K1             K3

View 3 Replies View Related

Transact SQL :: Using Aggregate Function With Subquery?

Aug 6, 2015

SSMS does not like mine!  THis is the error that I receive:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

And this is my syntax:

Select
employeeID
,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) +
COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End)
FROM production
GROUP BY employeeID
ORDER BY employeeID

View 6 Replies View Related

Transact SQL :: Working Of Aggregate In Sub-query Using Exists?

Aug 4, 2015

When i am running  below snippet execution plan is showing constant scan instead of referring subquery table.

I want to know  how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.

select count(*) from A  where exists (select count(1) from B where A.a=B.a)

execution plan has to show  scan or seek for subquery. Surprisingly, output is coming as expected.

View 8 Replies View Related

Transact SQL :: Create Sequential Aggregate Of A Column Using Sum?

Aug 17, 2015

I need to create a sequential aggregate of a column using sum in tsql.

I want a column that will sum up all hours work by transaction date.

for example

Monday my hours work will be 8 and therefore my total transaction hours will be 8.

Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to  16.

Wed my total hours work will be 5 and I want the total hours of my total transaction hours to  21. etc.

Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table

View 3 Replies View Related







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