How To Get Results In One Row From Multiple Record

Jul 19, 2013

I have following situation -

DECLARE @table1 TABLE
(
Stuid INT NOT NULL,
schoolcd varchar(10),
enterDate Datetime,
entercd char(10)
)

INSERT @Student VALUES
(4648, ‘20130515’, ‘892’,’30’),
(4648, ‘20130103’, ‘764,’10’)

SELECT STATEMENT will give me following -

Stuid schoolcd entercd enterDate
46488923005/15/2013
46487641001/03/2013

Desired output is one row for two or more records for same stuid
464889230 05/15/20137641001/03/2013xxxxxxx/xx/xxxx

How can I accomplish that?

View 5 Replies

Representation Multiple Results As One Record.

Jan 29, 2008

Hi,

Here is my sample table creation and insertion script.
I want represent as a summary-result using just one record.
How can I ?
Please note the below red color text.

create table policy
(
id int not null,
name nvarchar(50) not null,
constraint pk_policy primary key(id)
);

create table localhost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_localhost primary key(policy_id,id)
);

create table remotehost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_remotehost primary key(policy_id,id)
);

create table rate
(
policy_id int not null,
inbound int not null,
outbound int not null,
constraint pk_rate primary key(policy_id)
);

-------------
insert into policy values(0,N'policy0');

insert into localhost values(0,0,0xC0A80101,0xC0A80101,24);
insert into localhost values(0,1,0xC0A80A01,0xC0A80B01,0);

insert into remotehost values(0,0,0xACA80101,0xACA80101,0);
insert into remotehost values(0,1,0xACA80A01,0xACA80B01,0);
insert into remotehost values(0,2,0xACA80C01,0xACA80C01,24);

insert into rate values(0,1000,2000);

-------------
select * from policy;
select * from localhost;
select * from remotehost;
select * from rate;

-- result of policy table
0 policy0

-- result of localhost table
0 0 0xC0A80101 0xC0A80101 24
0 1 0xC0A80A01 0xC0A80B01 0

-- result of remotehost table
0 0 0xACA80101 0xACA80101 0
0 2 0xACA80C01 0xACA80C01 24

-- result of rate table
0 1000 2000

------------------------------------------------------
desired result set
id name localhost remotehost rate
-- ---------- ---------------------------------------------------------------- ------------------------------------------- -----------------
0 policy0 (192.168.1.1/24),(192.168.10.1~192.168.11.1) (172.168.1.1),(172.168.12.1/24) 0,1000,2000

descriptin of desired result set :
0. key value is id column and is referenced by each policy_id column.
1. id and name column is the same as policy table's.
2. localhost and remotehost columns are intigration of ip_begin,ip_end,prefix.
3. ip_begin and ip_end should be converted dotted presention from numeric format.
4. if prefix greater than 0, it should be displayed using '/'.
5. if ip_begin and ip_end are equal, show just one ip.
6. if the two ips are different from each other, they separated by '~'.
7. rate field is packed divided by ','.

View 4 Replies View Related

T-SQL (SS2K8) :: Nested Stored Procedure - Multiple Results Of Record Sets Coming

Oct 1, 2014

I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.

I have 2 problems in that

1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.

I have attached the scripts also, the line i described in step1 is

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

Multiple Record Insertion For Each Record From Source

Jan 10, 2008

Hi,

How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.

thanks.

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

Add New Record - Weird Results

Jul 20, 2005

I have written a generic script in asp to add records to a table. Thescript works fine with one table but in the other tables it updatesthe first record in the table with the values for the new record andadds a new record with all null values?!? Here is the script:adOpenKeyset=1adLockOptimistic=3Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")'INSERT******************************************* ******************'Open connection to sub-tableif action = "insert" thencnnFormToDB.Open "SELECT top 1 * FROM " &subtable,"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta",adOpenKeyset,adLockOptimisticcnnFormToDB.AddNewelsecnnFormToDB.Open "SELECT top 1 * FROM " & subtable & " WHERE ID = " &ID, "DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,adLockOptimisticEnd Ifif not cnnFormToDB.eof thencnnFormToDB.MoveFirstend if'DELETE******************************************* ********************if action = "delete" thencnnFormToDB.DeletecnnFormToDB.Closeelse'Build 2nd SQL StringFor i=0 To Ubound(aFields)cnnFormToDB(aFields(i)) = aValues(i)Next'Insert record into sub-tablecnnFormToDB.UpdateThe even weirder thing is I know that values in aFields and aValuesare OK because this test script I wrote for one of the tables worksjust fine:adOpenKeyset=1adLockOptimistic=3Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")cnnFormToDB.Open "SELECT top 1 * FROM FlightsDirect","DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,adLockOptimisticcnnFormToDB.AddNewcnnFormToDB("fkCity") = 198cnnFormToDB("fkDepartureAirport") = 159cnnFormToDB("ValidFrom") = "17/09/2003"cnnFormToDB("ValidTo") = "15/10/2003"cnnFormToDB("fkType") = 1cnnFormToDB("ReturnFlight") = 1cnnFormToDB("fkReturnAirport") = 184cnnFormToDB("Price") = yyyyyycnnFormToDB("fkATOL") = 5346cnnFormToDB.UpdateAny suggestions appreciatedThanksAlison

View 1 Replies View Related

SQL Server 2014 :: Duplicate Record Results On 2 One To Many Tables?

Feb 1, 2015

I have 3 Tables

TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]

A query returns duplicate results from TableB as well as TableC

TableB and TableC have nothing in common and should not interfere with each other.

with TwoTables as (
select
a.QuickSpec as QuickSpecId,

[Code].....
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]

View 1 Replies View Related

SQL 2012 :: Limiting Query Results With 3 Criteria For Each Record

Mar 20, 2015

I am trying to write a query that gives me the personal records from speed skaters on e.g. the 500 mtrs. I do this with the query:

SELECT cdsDistance AS Distance
, prsFirstName
, prsLastName
, min(crtFinalTime) AS MinTime
FROM tb....... INNER JOIN etc..
GROUP BY cdsDistance, prsFirstName, prsLastName
ORDER BY min(crtFinalTime)

In itself this works fine. However, there are complicating factors. Sometimes a speed skater has multiple PRs, meaning the he/she has the same fastest time more than once.

If these times are achieved on multple days, the 1st date is the official PR. (meaning: "Min of racedate")
If they are raced on the same day the 1st race is the PR (meaning: "Min of distancenumber")

Changing the code to:

SELECT cdsDistance AS Distance
, prsFirstName
, prsLastName
, MIN(crtFinalTime) AS MinTime
, MIN(cdsStartDate) AS RaceDate
, MIN(cdsDistanceNumber) AS DistanceNumber

FROM tb.......
GROUP BY cdsDistance, prsFirstName, prsLastName
ORDER BY min(crtFinalTime)

This gives me the wrong outcome because it gives me the "MIN" of every field, and they are not necessarily on the same row.

An option would be to calculate min(crtFinalTime), if for a person there is more than 1 result, calculate min of date, and then (if there is still more than 1 row) min of distancenumber.

Seems complicated, and I have the feeling there must be a better way (apart from: how to get this code)

Stacking subqueries in the FROM statement seems like a option be costly (time wise). There are more than 10 million rows (and growing) to run through.

As an example a few times:

DistanceFirst nameLast name Time Date Distance nr.
500 Yuya Oikawa 34.49 201311155
500 Yuya Oikawa 34.49 201311153
500 Yuya Oikawa 34.49 201311172

Yuya has 3 best times (34.49), 15-11-2013 is the 1st date, then distance nr 3 is the 1st distance raced. Therefore the 2nd row is the only row I would like to get in my endresult.

View 4 Replies View Related

SQL Server 2012 :: How To Hide Specific Record(s) From Results

Nov 2, 2015

I have written a SQL statement.There is a table called customer.It contains all customer data with customerid as PK.There is another table called logs and it contains customerid as foreign key and it contains a field to keep more than 90 days older user accounts.That field name is "Checked"

What I need get all records from these 2 tables and remove/hide more than 90 days older customers from record set.See my illustration.

I have written this code but I dont understand how to remove more than 90 days older user from result (because customer table doesnt contain a record called "Checked")

SELECT *
FROM [dbo].[Customers],[dbo].[VIESLog] WHERE [dbo].[VIESLog].[Checked] < DATEADD(day, -90, GETDATE())

View 6 Replies View Related

DTS Package With Multiple Results

Feb 28, 2008

Hi everyone, I have a DTS package on my SQL Server database that returns several different results. Does anybody know how I would access any one particular part of the return. I am trying to make it so that there is only one run to the database for the information, but am not sure how to get just the parts I want out of the DTS package. for Example, here is my DTS Package:
Select @InvoiceNo as InvoiceNo, '02/01/08' as PaymentDate, 1000.00 as PaymentAmt, 500.00 as RemainingAmt, 'X31235X' as ReceiptNumber
Declare @Results Table ( InvoiceNo char(10), Amt float)
Insert Into @Results Values('SNJ0100001',100.00)Insert Into @Results Values('SNJ0100002',200.00)Insert Into @Results Values('SNJ0100003',300.00)
Select * from @Results
How would I ask for just the results from the @Results section and then somewhere else on my page I will want to return just the results from the @InvoiceNo section?? I would normally call the package from my code-behind and I know how to do that, just not to pull a particular section. Here is what I have so far on that section:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="hsp_rpt_AR_ReceiptStatus" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter ControlID="TextBox1" Name="InvoiceNo" PropertyName="Text" Type="String" /></SelectParameters></asp:SqlDataSource>
Thanks for any helpRandy

View 1 Replies View Related

Combining Multiple Results Into One Row

Mar 6, 2006

Hey guys, here is my issue, i'm trying to combine multiple columns of data into one row. For example, I have a temp table:

create table #Custom_Address
( patient_idchar(10)null,
episode_idchar(3)null,
custom_address varchar(100) null
)

As you can tell, the 'custom_address' column is going to house the results of the combination.

I am trying to INSERT (combine) using this statement:

INSERT #Custom_Address
select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address
FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id
WHEREcc.coverage_plan_add1 > 0
OR cc.coverage_plan_add2 > 0
OR cc.coverage_plan_city > 0
OR cc.coverage_plan_st > 0
OR cc.coverage_plan_zip > 0
GROUP BY cm.patient_id, cm.episode_id

I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.

Then I do my update:

UPDATE#ClaimMaster
SET #ClaimMaster.custom_address = ca.custom_address
FROM #ClaimMaster
JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id

However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.

If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?

As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.

View 5 Replies View Related

Put Multiple Results In One Field

Jul 23, 2005

Hi all,I need to put multiple results in one field, but not sure how. Here'ssome sample code:select a.name,a.accountnum,a.ordernum,(select itemid from items where items_ordernum = a.ordernum) asItems[color=blue]>From clients a[/color]There's multiple instances of 'itemid' in 'items' with the criteria Igave, so this generates an error. I want to instead have the itemnumbers listed as just a list seperated by commas, so the results mightbe this:Name AccountNum OrderNum ItemsJohn Smith 12345 0000234 1233, 1333, 4322Mike Jones 43223 0000023 4322, 543And so forth. Is there someway to loop through this sub-query and makethe results become a list?Thanks --rlangly

View 2 Replies View Related

Multiple Rows Of Data Into 1 Row Results?

Dec 10, 2003

Is there a way to get multiple rows of data to show in 1 row of results?
I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.

I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).

Thanks!

View 5 Replies View Related

Search And Results Using Multiple Columns

Feb 10, 2004

I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)

I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.

I'm lost on both the search and the results code.

I'd like to have a drop down for the search like this:

<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>

Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>

Any ideas on how to make this work would be greatly appreciated.

View 3 Replies View Related

How To Combine Results From Multiple Records Into One

Apr 19, 2004

Hello,

I have a table which has the following structure:

ID MessageText
001 Hello
001 There
001 Working
003 See
003 you
003 Next
003 Time

How to build a query or store procedure to return result like this:

ID MessageText
001 Hello There Working
003 See you Next Time

Your help/advice is greatly appreciated.

Thanks, Ficisa

View 14 Replies View Related

Multiple Results In One Column MS SQL 2000

Jun 11, 2008

Hi all,

I'm totally new and been reading the forums to help me and I think I've done ok so far getting the results I need from basic SQL selects and stuff but now I seem to have hit a wall and I realize I need help from someone who really knows their SQL.

I'm using Microsoft SQL 2000.

I have a database with columns

Product_ID
Product_Name
Product_Association

The Product_ID column is the unique ID key. Product_Name is the name of the product. And Product_Assocation represents search terms.

The column Product_Assocation has for example the values

nature*leaves*leaf*tree*trees*abstract*green

for one product and

food*dinner*table*eat*eating*restaurant*cuisine*plate*dinner*knife

for another.

I need to do 2 things basically.

1. How do I select the product based on ProductID and display each word independently in a recordset?

For example how can I Select from Table where Associate = 'each word and list them' for Product_ID = '1'

2. I have to allow the user to "search" for a word in the column so I need to create an SQL statement that goes someting like

Select * from Table where searchresult = 'search word for example tree which is a word seperated by one of the * in the Product_Association column' and that the recordset returns the table row.

I am sure these both probably sound like silly and simple questions and if they are I am sorry guys. I am trying my best. I am a newbie and no point pretending otherwise. Hopefully someone with greater SQL knowledge out there can assist me because I have tried all I know but can't seem to make any headway.

I am using ASP not .net or php if that makes any difference? I'm not sure that it does as I think it is about the SQL Select statement and how to construct them?

Thanks for any help. Its really appreciated.

Kind regards, Sam

View 8 Replies View Related

Max Datetime Returning Multiple Results

Oct 23, 2013

I have a query running without issue on around 100,000 results however i have a couple where my MAX aggregate is returning two results for a MAX(date time) and i cannot figure out why. I have paired down the query to try and work it out but still don't know.

My query now looks like this (i have remove most columns)

SELECT STATUS, RunOn, sort, T_ID
FROM dbo.Results
WHERE (RunOn IN
(SELECT MAX(RunOn)
FROM dbo.Results
group BY T_ID) AND T_ID = 21405)

ORDER BY sort

I added the T_ID = 21405 to restrict the results to the problematic results, if i set T_ID to any other test result i get one value based on the latest RunOn, but for some reason with this T_ID i get two values, the latest one and one slightly older... The date time looks ok, i just can't work out why the latest and one older result are returned !

The original query was grouping by more items but i removed those to try and work out what is going one, as mentioned for 99.9% of results it works perfectly, but for this one i don't know.

View 10 Replies View Related

Displaying Results From Multiple Tables

Apr 17, 2008

Hi,

I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format:
Customer Name Address
----------------------------------------------------------
ABC Add1
ABC Add2

XYZ Add1
XYZ Add2

Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report).
Please give me a solution to this.

Shyam

View 4 Replies View Related

Multiple Rows To One Column Results

Nov 8, 2006

I want to display the top 5 count of areacodes for each name
I
want to combine all the results for the areacodes into one column for
each user. like a csv. I tried my code below but the results just
return the same 5 of areacodes for all names on each area code row with
each callername. like
joe blow 123,456,755,312,465,567,555
bill jones 123,456,755,312,465,567,555

I just want the top 5 for each particular name.
I tried reading a few articles on putting multiple colums in one row and i could not figure out what i am missing!
Thanks

DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,myareacodes int)
declare @MyAreaCodes varchar(50)
INSERT @Stage
(
CallerName,
AreaCode,
Calls,
theDate
--myAreacodes

) --This is where the area code data comes from I can get it to display in multiple colums, but -- I want the area codes to be on one line for each name
SELECT CallerName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0) as myDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BY CallerName,
SUBSTRING(TargetNum, 2, 3),
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
ORDER BY CallerName,
COUNT(*) DESC

-- Get Calls
SELECT s.CallerName,
s.AreaCode,
s.Calls,
s.theDate--,myareacodes
FROM @Stage s
INNER JOIN (
SELECT CallerName,
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM @Stage
GROUP BY CallerName

HAVING (CallerName = 'name1') OR
(CallerName = 'name2')
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BY callername,Calls desc
--
set @MyAreaCodes =''
--
SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCode,'') + ',' from @Stage

--
SELECT CallerNAme,@MyAreaCodes AS MyAreaCodes from @stage
Group By CallerName

View 1 Replies View Related

Combine Multiple Results Of Subquery

Nov 14, 2006

Table users:
userid, name, added...

Table groups
groupid, groupname...

Table groupadmins:
userid, groupid

The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'

If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users

then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?

View 4 Replies View Related

Multiple Results From One Stored Procedure

May 17, 2007

If I have multiple selects statements in one stored procedure. How do I access the different results in a report in Reporting Services? Where the first select is the detail lines of my report and the second select is just a field for a my header? Or am I going about this wrong with putting it all the results I want into one stored procedure for one report?

Example stored procedure:

ALTER PROCEDURE [dbo].[proc_DepositsByOfficer]

As

SELECT MASTER_DSC.APP

, MASTER_DSC.BRANCH

, qlkpOfficer.strName

, MASTER_DSC.DSC_OFFICER_49

, qlkpBranchName.strDescrS

, MASTER_DSC.DSC_CUR_BAL_21

, Case MASTER_DSC.APP WHEN 1 Then DSC_CUR_BAL_21 End AS DDA_BAL

, Case MASTER_DSC.APP WHEN 2 Then DSC_CUR_BAL_21 End AS SAV_BAL

, Case MASTER_DSC.APP WHEN 3 Then DSC_CUR_BAL_21 End AS CD_BAL

, MASTER_DSC.DSC_INT_RATE_45

, Case When [DSC_CUR_BAL_21]>0 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End As ANN_EXP

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=1 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_DDA

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=2 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_SAV

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=3 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_CD

, Case MASTER_DSC.APP WHEN 1 Then 1 End AS DDA_Count

, Case MASTER_DSC.APP WHEN 2 Then 1 End AS SAV_Count

, Case MASTER_DSC.APP WHEN 3 Then 1 End AS CD_Count

, qlkpApplicationCode.strDescrS AS strApplCode

FROM

MASTER_DSC

INNER JOIN qlkpApplicationCode ON MASTER_DSC.APP=qlkpApplicationCode.dblReference

LEFT JOIN qlkpOfficer ON MASTER_DSC.DSC_OFFICER_49=qlkpOfficer.intID

LEFT JOIN qlkpBranchName ON MASTER_DSC.BRANCH=qlkpBranchName.dblReference

WHERE

MASTER_DSC.DSC_CUR_BAL_21<>0

ORDER BY

MASTER_DSC.BRANCH;

SELECT dbo.fBankName() AS BankName;

View 7 Replies View Related

Transact SQL :: Pivot On Multiple Results

Nov 9, 2015

I have a table similar to below:

itemID | part
1         | A
1         | B
2         | A
2         | A
2         | A
3         | C

I need the table to look like the following:

itemID | part1 | part2 | part 3
1         | A        | B       | null
2         | A        | A       | A
3         | C        | null    | null

There will _never_ be more than three parts to an item, and it does not matter what order they are in.

I cannot get pivot to work for me.

View 2 Replies View Related

Incorrect Results From FTS On Multiple Columns

Apr 24, 2006

I'm writing a FTS query which needs to search on two different columns.

E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.

I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?

Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?

Shailesh Patel...

View 3 Replies View Related

Multiple Row Value In One Record

Jun 11, 2008

SOURCE TABLE
ID SEQ COM VARCHAR(10)
1 1 I
1 2 am
1 3 doctor
2 1 I
2 2 am nurse
DESTINATION TABLE --Each comm field is varchar(20)
ID_COM1___COM2______COM3
1__I______am_________doctor
2__I______am nurse____NULL

Please Help

View 6 Replies View Related

Join Results From Multiple EXEC Calls

Apr 25, 2006

I would like to make inner join with results from different exec %procedureName% calls.

thanks.

View 3 Replies View Related

Returning Multiple Results From A Cursor Into A Table

Sep 7, 2007

The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...

But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.

In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...

/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/

SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection);
sda.Fill(dt);

... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).

So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?

Any help is greatly appreciated

View 5 Replies View Related

Transact SQL :: If Statement Matches Multiple Results

Jun 2, 2015

If it possible to have an if statement match multiple results, as to not have to use the OR multiple times.

Example: I want to say, if Description equals red or blue or green or yellow or orange or black or white or pink, without having to use OR and OR and OR. Description can match 10 different values.

View 9 Replies View Related

Multiple Table Record

Oct 13, 2007

I have two table and there are releationsheep between them.
Topic:
topicId, topicName, topicDate
Reply:
replyId,topicId,replyName,replyDate
I want to insert two table at the same time.
Topic: 1,example,10/21/2007
Reply:1,1,example, 10/21/2007

View 1 Replies View Related

Multiple Record Insert

Dec 9, 2002

I am trying to do an update to a database with the code below, the code will work in generating a single record (if i exclude the IF statement) but i need it to create multiple new records based on the IF statement (or something similar) i have in the code. It needs to create updates for every record that has a parentguid that matches the one specified in the code.
the idea is, the hierachy structure in the tables are:

Location1
--> Part1
--> part2 etc
Location2
--> part1 etc

i want it so that when i input a new part it updates to all the locations. The locations all have the same parentguid (the one specified in the code)
With regards to the stored procedure it executes, this stored procedure creates the entry. I don't really want to change the stored procedure because other functions reference and use it.

My code:

DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID2 VARCHAR(50)
select @parentguid2 = parentguid from dsdba.itemgroups

if @PARENTGUID2 = '8CF850AD-2026-411B-AABE-BF1584624EB3'
BEGIN

SET @PREFIX = 'JUST'

SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS WHERE CODE = @PREFIX

SET @CODE = @PREFIX + '02'
SELECT @GUID = NEWID()

EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, 'JMB', @PARENTGUID, 1

END

:confused:

View 1 Replies View Related

Insert Multiple Record

Mar 3, 2004

can anyone help to explain how to insert multiple record into one/two table?

ex:lets say when user specify start date and end date, then we need to created and insert the record on that duration.

and how to do with insert the record weekly or monthly?until the end of the date!

View 1 Replies View Related

Spreading SELECT Query Results Over Multiple Pages

Dec 20, 2006

Hello.Lets say that there is a table with N rows. now, i want to display the table's data on a web page. one way is to select the whole table and add each row's data to the webpage (something like SELECT * FROM TABLE1).going this way will create a huge page. i want to speared the results over multiple pages - excatly as this forum spread each forum messages over multiple pages.
for this purpose, i need to query Y rows each time, for example, if my table has 20 rows, and say that i want that each page will display 5 rows, then i need to query 5 rows each time. the first 5 rows for the first page, the next 5 rows for the second page and so on...
is there any way to achieve it using an SQL query?
the simplest way is to select the whole table and manaually filter the results. but this way will become slow as the table grows with data... and i dont want to select rows which i wont display anyway.
any suggestions?
Thanks.

View 5 Replies View Related

Sort Results By Rank Using FREETEXT On Multiple Columns

Apr 20, 2008

Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description

can anyone give any code snippets?

View 1 Replies View Related