Transact SQL :: Group Results Into Single String

Jun 4, 2015

I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row.

Current Query:

select
t.Name 'Task',
d.FirstName + d.LastName 'User'
from [dbo].[Tasks_TemplateAssignTo] a join
Task_Template t on a.template_id = t.ID join
Doctor d on d.id = a.provider_id

Results from query above:

TaskUser
Call CustomerJohn Smith
Call CustomerBetty White
Call CustomerTammy Johnson
Order suppliesGreg Bullard
Order suppliesJosephine Gonzalez

Expected Results:

TaskUser
Call CustomerJohn Smith, Betty White, Tammy Johnson
Order SuppliesGreg Bullard, Jospehine Gonzalez

View 4 Replies

Transact SQL :: String Manipulation Single To Multiple Rows

Nov 7, 2015

The recipe preparation instructions are stored in a table by RecipeID. The prep instructions are in a single VARCHAR(MAX) column and look something like this:

1. Boil Water
2. Add noodles
3. Add cheese sauce
4. Stir well

Now they want this single VARCHAR(Max) column broken into 2 columns - Step and Prep Instruction like this: Boil WaterAdd noodlesAdd cheese sauceStir well.I figure I can use the appearance of a number followed by a period and a space to determine the existence of a new row. How would I accomplish this in T-SQL?

View 11 Replies View Related

Transact SQL :: Limit A Query Results When All Of Line Items Under Group Meet Certain Condition

Oct 1, 2015

I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data

TestCaseID TestCaseDescription BugID BugStatus
1 TestCase1 1 Closed
2 TestCase1 2 Open
3 TestCase2 11 Closed
4 TestCase2 12 Closed
5 TestCase2 13 Closed

How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.

View 3 Replies View Related

Transact SQL :: GROUP Dynamically Generated Date And String Expressions

May 17, 2015

I have below SQL. When I run it I get the 'Each GROUP BY expression must contain at least one column that is not an outer reference' error. The date and string expressions are generated dynamically and need to be grouped upon if possible. What am I missing?

INSERT INTO tblStaffPayrollHistory (StaffID, FromDate, ToDate, PayrollNo, EventID)
SELECT DISTINCT tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID
FROM tblStaffBookings INNER JOIN tblEvents ON tblStaffBookings.EventID = tblEvents.ID
WHERE ...
GROUP BY tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID

View 2 Replies View Related

Not A Single-group Group Function

Feb 12, 2015

I have a code like this:

SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation

[code]....

So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....

View 1 Replies View Related

DB Design :: Partition With Single File Group Or Multiple File Group?

May 19, 2015

partition with single file group or multiple file group which one best.

we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.

View 9 Replies View Related

Get Only Single Row Results Per Id?

May 26, 2007

Hi, is it possible to make an sql query that has an Outer Join but return only one row of results max per id.
For example i have an Articles table, and a PicturesForArticles table.
The Articles table has an id field(aid), a title field(aTitle) and a content field(aContent).
And the PicturesForArticles table has an id field(pid), a PicPath filed and a field linking it to the articles table(aid)
Obviously the PicturesForArticles field stores pictures for the articles, and article can have a multiple number of pictures, or no pictures at all.
So i want to make a query that will return all of the Articles fields and a picture for each article. Even if the article has many pictures i only want to get a single row for each aid(Articles Id), and if there are no pictures for that article the picture fields will be null.
Is there any way to do this, to only return on row of results for each aid?
Thanks

View 4 Replies View Related

Transact SQL :: Search And Return String After Searched String

Sep 1, 2015

Is there way to search for the particular string and return the string after that searched string

SalesID
Rejection reason
21812

[code]....

The timeout period elapsed hence disqualified

View 3 Replies View Related

How To Group Multiple Row As Single Row

May 30, 2008

eg say.

i have a table emp

data
----
id name phone no
1 smith 423-422-5226
1 smith 414-255-5252
2 george 511-522-2525
2 george 524-522-2428
........
........

i need output as

1 smith 423-422-5226, 414-255-5252
2 george 511-522-2525, 524-522-2428
.....

can u any one help me

View 1 Replies View Related

2 Group By In A Single Query

Jul 20, 2005

Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai

View 2 Replies View Related

Integration Services :: Two Results From Single Task

Jul 22, 2015

I have a table with email addresses and CC_Flag.

Email     | CC_Flag
xxxx           0
yyyy           1
zzzz           1

Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag..Is it possible to fill two variables from a single SQl Task,.

View 4 Replies View Related

Select 1 Row From Each Group In A Single Table

Aug 19, 2006

Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario:
Table A:
_____________
BidID - Int identity
AuctionID - int
BiderName - varchar(50)
bidAmount - money
______________________
Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly.
BidID   AuctionID   BiderName   BidAmount
1            4005            joeblow         100.00
2            4005            janedoe         101.00
3            4005            joeblow         107.00
4            4006            joeblow         100.00
5            4006            janedoe         105.00
6            4006            joeblow         106.00

I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be
3            4005            joeblow         107.00
6            4006            joeblow         106.00
Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right.
Thanks in advance for your help.

View 4 Replies View Related

Get Value Of A Single Record Instead Of Aggregated Value With GROUP BY

Oct 12, 2007

How to get

Code Block

a record value instead of aggregated value with GROUP BY?

Assume that I have a PRODUCT_COMMENT table defined as below. It logs
the multiple comments for products. A product may have multiple
comments logged at different time.

Code Block

CREATE TABLE [dbo].[PRODUCT_COMMENT](
[COMMENT_ID] [int] IDENTITY(1,1) NOT NULL,
[PRODUCT_ID] [int] NOT NULL,
[COMMENT] [nvarchar](50) NULL,
[UPDATED_ON] [datetime] NOT NULL,
CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED
(
[COMMENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID])
REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID])
GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT]

I would like to use the following SQL statement to get the latest
comments for all products.

Code Block

SELECT PRODUCT_ID, COMMENT, UPDATED_ON
FROM PRODUCT_COMMENT
GROUP BY PRODUCT_ID
HAVING UPDATED_ON = MAX(UPDATED_ON)

But this leads to the following error:

Code Block

Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause
because it is not contained in either an aggregate function or the
GROUP BY clause.

Is there a way to do that?

Thanks!

View 5 Replies View Related

Transact SQL :: How To Get String Array In String Variable

Jul 28, 2015

I have a string variable and following data.

Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'

when I assign values in @ServiceID in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.

View 8 Replies View Related

Assistance With Insertering Query Results Into A Single Table Row.

Feb 14, 2008

I have a table that was created as follows
create table call_summary (

row_date smalldatetime,
[700] int,
[730] int,
[800] int,
[900] int)

I have a query that returns the following
date interval (int) calls (int)
2008-01-07 00:00:00 700 0
2008-01-07 00:00:00 730 0
2008-01-07 00:00:00 800 0
2008-01-07 00:00:00 830 9
2008-01-07 00:00:00 900 8

I am looking for a way to get my table mentioned above to look like this
row_date [700] [730] [800] [830] [900]
2008-1-7 0 0 0 9 8

does anyone have any slick ideas on how I can accomplish this task?

View 3 Replies View Related

UPDATE With Results From GROUP BY

Jun 13, 2007

Hi,

I'm trying to calculate some leagues for a website i run usuing some quite complicated querys. As the leagues are calculated with such somplicated criteria, i've had to build the leagues inot a temporary table.

What i need to be able to do is update the temp table with the results of a group by query. This gives me an error and the only way i have found to achieve this is to insert the results of a group by into another temp table, and then use that to perfom the update.

Is there something i'm missing, or is this the only way to achieve my goal ?

Thanks in advance.

View 3 Replies View Related

Help With SQL Group By Please (results Returned Into / Shown In C#.Net)!

Oct 16, 2006

Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOfAs i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)Thanks in advance!

View 7 Replies View Related

Select Top N Results In A Group; Count(*)&<=n

Jan 18, 2003

Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region)
ProductID is the primary key.

I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.

I tried the follwoing, but get incorrect results.

select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region
group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price

However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.

select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID
group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price

I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?

View 2 Replies View Related

How To Put Serial Numbers For Results In Group

Apr 4, 2012

I need to put the serial numbers for results in group in SQL Server 2000. Please see below:

My input:
procedureid procname
1 A
1 B
2 A
2 B
2 C
2 D
3 A
3 B
3 C

Output I need:
procedureid procname serial_num
1 A 1
1 B 2
2 A 1
2 B 2
2 C 3
2 D 4
3 A 1
3 B 2
3 C 3

Here is my table:

create table po(
procedureid int,
procname varchar(10),
)
insert into po values (1,'A')
insert into po values (1,'B')

[Code] ....

View 11 Replies View Related

SQL 2012 :: Group By In Sub Query To Get Desired Results

Jun 15, 2015

I have to use a Group By in Sub query that will give me my desired results.

SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W

The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.

View 8 Replies View Related

Group By Query Insert The Results Into Different Table

Aug 20, 2007

I am trying to do a select statement and input the result to a different table how can this be done in one step? Now I am just coping to excel and importing back in this is a real pain.

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

View 5 Replies View Related

Group By And Count(*) A Single Column Returing Two Counted Values

Feb 26, 2008

I am trying to count a column field in a single table and return two count values as one record set using group by.

field1 = group by (department) nvarachar
field2 = count (closed) datetime

I have tried using derived tables with no luck getting the desired result.

field2 is a datetime field as indicated I want a count for two conditions

1. WHERE field2 is null
2. WHERE field2 is not null

End Results would like this
======
Department | OpenItems | ClosedItems
Department1 | 32 | 24
Departmnet2 | 87 | 46
Department3 | 42 | 76

=======

I got it *almost* working with derived tables, but the group by function was not putting the department as one single row. I was getting multiple rows for departments.

I realize this is probably a simple answer and I am making this a lot harder than it actually is....

Any suggestions?

View 3 Replies View Related

Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns

Feb 12, 2007

I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.

What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.

1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.

The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.

After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.

Hopefully someone can get me pointed in the right direction! Thanks for your help!

View 12 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 Replies View Related

Group Results Into Custom Date Ranges/Calendar

Nov 29, 2007

Hello,

I really hope that someone can help me or at least point me in the right direction. I am selecting a set of data and using the date values across the X axis. However the needs exists to group these by week, but these weeks are not the normal weeks, for - they exist as follows the month starts on the first Monday of a month, for example December 2007 starts on Monday the 3rd and the week ends on the 6th of December a so on till the fact that the last week of the month December 2007 starts on Monday the 31st and ends on January the 6th is there any way that I can create a group that could group the datetime values together in this way,

This is not best achieved in SSRS where should I be creating these groups. Any help would really be appreciated.

Many Thanks

Olaf Dedig

View 1 Replies View Related

Trying To Group Report Results By Year And Month By Other Variables

May 28, 2008

Below is the code. It gives 2 errors.
1 @StartDate must be defined (it is a report parameter)
2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field)
Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5))
DECLARE @sowners nvarchar(5)
DECLARE @mydate datetime
DECLARE @sstatus nvarchar(10)
DECLARE @sjtype nvarchar(5)
DECLARE @chardate nvarchar(7)
DECLARE @Jmonth Int
DECLARE @Jyear Int
DECLARE @Jcharyear nvarchar(4)
DECLARE @Jcharmonth Nvarchar(2)
DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype
FROM Requirements
WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate))
OPEN JobCur
FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Jyear = (Year(@mydate))
SET @Jmonth = (Month(@mydate))
SET @Jcharyear = CAST(@Jyear,4)
SET @Jcharmonth = CAST(@Jmonth,2)
IF Len(Jcharmonth) = 1
BEGIN
SET Jcharmonth = '0' + Jcharmonth
END
IF @Jmonth >= 10
BEGIN
SET @chardate = @Jyear + '/' + @Jmonth
END
IF @Jmonth < 10
BEGIN
SET @chardate = @Jyear + '/0' + @Jmonth
END
INSERT INTO @t (Owner, cdate, status, jtype)
VALUES (@sowners,@chardate,@sstatus,@sjtype)
FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype
END
CLOSE jobcur
DEALLOCATE jobcur
BEGIN
SELECT * FROM @t
END

View 6 Replies View Related

Single Quote In A String

Mar 9, 2006

Hi,

i am trying to add a single quote to a string. This is a must because i am making a full select statement in which i need the single quote to compare values. Obviously this breaks my string invalidating my query.

ej:

SELECT avg(tabla.ip_trend_value) as valor, FLOOR(Cast(tabla.ip_trend_time AS FLOAT)) as tiempo
FROM TESTLAB5.dbo.CE02_L21_916AI31_43 tabla, TESTLAB5.dbo.CE02_L21_916XI31_4 t2
WHERE t2.ip_trend_value = 'Alimentacion Digestores' and t2.ip_trend_time = tabla.ip_trend_time
group by FLOOR(Cast(tabla.ip_trend_time AS FLOAT))

and this will become something like this.

SELECT @TableName = 'TESTLAB5.dbo.'+@TableName
SELECT @SQL = 'SELECT avg(tabla.ip_trend_value), FLOOR(Cast(tabla.ip_trend_time AS FLOAT)) FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' tabla, TESTLAB5.dbo.CE02_L21_916XI31_4 t2'
SELECT @SQL = @SQL + ' WHERE t2.ip_trend_value = '@NombreVar'and t2.ip_trend_time = tabla.ip_trend_time'
SELECT @SQL = @SQL + ' group by FLOOR(Cast(ip_trend_time AS FLOAT))'

the @NombreVar is the equivalence of 'Alimentacion Digestores'.

is there something i can add or change to make it work ?

View 2 Replies View Related

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.

Dec 20, 2007

I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

DECLARE @login char(20), /*This sets the rep for the query.*/
@record_date datetime, /*This is the date that we want to run this for.*/
@RWPY decimal(18,2), /*This is the required wins per year.*/
@OCPW decimal(18,2), /*This is the opportunities closed per week.*/
@OACW decimal(18,2), /*This is opportunities advanced to close per week.*/
@TOC decimal(18,2), /*This is the total number of opportunities in close.*/
@OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/
@TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/
@OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/
@TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/
@OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/
@TOI decimal(18,2), /*This is the total number of opportunities in interview.*/
@OCW decimal(18,2), /*This is the opportunities created per week.*/
@TOA decimal(18,2) /*This is the total number of opportunities in approach.*/

SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

SELECT loginname,
CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
app_time AS [Approach Average Time],
app_perc_adv AS [Approach Perc Adv],
CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
int_time AS [Interview Average Time],
int_perc_adv AS [Interview Perc Adv],
CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],
dem_time AS [Demonstrate Average Time],
dem_perc_adv AS [Demonstrate Perc Adv],
CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],
neg_time AS [Negotiate Average Time],
neg_perc_adv AS [Negotiate Perc Adv],
CAST(@TOC AS decimal(18,1)) AS [Opps In Close],
cls_time AS [Close Average Time],
cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date

Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

CREATE TABLE #pipelinehist (
glusftboid int IDENTITY(1,1) NOT NULL,
record_date datetime NOT NULL,
loginname char(20) NOT NULL,
app_new float NOT NULL,
app_time float NOT NULL,
app_perc_adv float NOT NULL,
int_time float NOT NULL,
int_perc_adv float NOT NULL,
dem_time float NOT NULL,
dem_perc_adv float NOT NULL,
neg_time float NOT NULL,
neg_perc_adv float NOT NULL,
cls_time float NOT NULL,
cls_perc_adv float NOT NULL,
target_deal money NOT NULL,
annual_quota money NOT NULL,
weeks int NOT NULL
) ON [PRIMARY]

INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View 3 Replies View Related

Transact SQL :: Merging Every 3 Rows Into A Single Row

Aug 18, 2015

I have 2 columns (ID, Msg_text) in a table where i need to combine every 3 rows into single row. What would be the best option i have? I know by using 'STUFF' and 'XML PATH' i can convert all the rows into a single row but here i'm looking for every 3 rows into a single row.

View 3 Replies View Related

Transact SQL :: Two Different Outputs From Single Query?

Jul 22, 2015

I have a table with email addresses and CC_Flag.

Email     | CC_Flag
xxxx           0
yyyy           1
zzzz           1

Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag. Is it possible to fill two variables from a single SQl Task.

View 8 Replies View Related

Transact SQL :: REPLACE Single Quote

Oct 24, 2008

I'm cleaning up a column in my table and getting rid of special characters.The only think I can't get rid of with the REPLACE function is single quotes.I'm doing aUPDATE TableSET Column = REPLACE(Column,'''','') --that's four single quotes then two single quotesBut the single quotes in my column wouldn't go away.I know that

DECLARE @string varchar(50) = 'test''s strings'SET @string = REPLACE(@string,'''','')SELECT @string

View 19 Replies View Related

How To INSERT A String That Contains Single-quotes?

Dec 28, 2006

My code results in SQL statements like the following one - and it gives an error because of the extra single-quotes in 'it's great':
UPDATE Comments SET Comment='it's great' WHERE UserID='joe' AND GameID='503'
Here's the error I get when I try this code in SQL Server:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near 's'.Msg 105, Level 15, State 1, Line 1Unclosed quotation mark after the character string ''.
I need to know how I can insert a string such as 'it's great' - how do I deal with the extra quotes issue? is there a way to ecape it like this 'it/'s great' ? This doesn't seem to work.
Here's the code that generates the SQL. I'm using a FCKeditor box instead of a TextBox, but I got the same error when I was using the TextBox:
string strUpdate = "UPDATE Comments SET Comment='";strUpdate = strUpdate + FCKeditor1.Value;//strUpdate = strUpdate + ThisUserCommentTextBox.Text;strUpdate = strUpdate + "' WHERE UserID='";strUpdate = strUpdate + (string)Session["UserID"];strUpdate = strUpdate + "'";strUpdate = strUpdate + " AND GameID='";strUpdate = strUpdate + Request.QueryString["GameID"];strUpdate = strUpdate + "'";
SqlConnection myConnection = new SqlConnection(...);SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);
try{myCommand.Connection.Open();myCommand.ExecuteNonQuery();}catch (SqlException ex){ErrorLabel.Text = "Error: " + ex.Message;
}finally{myCommand.Connection.Close();}

I'm using SQL Server 2005 and ASP.NET 2.0
Much thanks

View 5 Replies View Related

Insert A String With Single Quotes

Aug 28, 1998

Hi,

How to insert a string value with quotes in it in SQL Server?

This is not working:

insert table_name values(`abc`d`)

I tried to put escape in front of `, still failed.

Thanks in advance.

-Jenny Wang

View 2 Replies View Related