MS SQL Server :: T-SQL (SS2K8) :: Only One Record From Table With Multiple Records?

T-SQL (SS2K8) :: How To Change Multiple Records In One Record

Apr 14, 2015

View 8 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Get One Row On A Table With Multiple Records

Apr 24, 2014

I have a table called TBLCataloghi

I have multiple records with colunms codpro and codcat equal

They differ only by a date called catalog.datfin

I'd like to select all rows but with the same codpro,codcat, obtaining ONLY the row with MIN () field datfin

Field datfin is a date..

Ex. codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 01/01/2010

codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 10/07/2014

I'd like to read both records but in SELECT obtain only the record with datfin MIN (01-10-2010)

I did the query but i was not able to do nothing of good. I obtain all times both records...

SELECT catalog.codpro AS CodProdotto,
catalog.codcat AS CodiceCatalogo,
MIN(catalog.datfin)

FROM pub.catalog

WHERE catalog.codcat = 'MK'

GROUP BY catalog.codpro,catalog.codcat ,catalog.datfin

View 2 Replies View Related

T-SQL (SS2K8) :: Copy Master Record And Its Detail Records

Aug 26, 2014

I have been asked to give the users the ability to copy a set of records within the database. The current setup is

Master Table: JSA1
JSAID: (PK, int, not null)
JOBTITLE(nvarchar(200), null)
PlantNumber(int, not null)

Detail Table: tblSteps
STEPID (PK, int, not null)
JSAID (FK, int, not null)
StepNo (int, null)
BasicSteps (nvarchar(200), null)
DBPhoto(varbinary(max), null)

The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.

I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.

View 4 Replies View Related

T-SQL (SS2K8) :: Better Method To Detect Record With Multiple / All 0s?

Feb 17, 2015

Better method to detect records with one or more 0's, other than hard coding all of the potential values. For example, I need to detect account duplicates where the duplicate contains multiple/all 0's in the "MasterAccount" column:

DECLARE @Account TABLE(AccountNo varchar(3), MasterAccount varchar(10) )
INSERT INTO @Account
SELECT 'ABC','0000000ABC' UNION
SELECT 'DEF','0000000DEF' UNION
SELECT 'ABC','0000000000' UNION ---- unwanted duplicate
SELECT 'DEF','00000' UNION ---- unwanted duplicate

[Code] ....

Is there a better method than this?

View 5 Replies View Related

T-SQL (SS2K8) :: Returning Multiple TOP Records?

Jul 10, 2014

Here is my setup: I have the following tables -

tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account

I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.

People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.

So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.

But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?

Straight TOP by itself won't do it.
GROUP BY by itself won't do it.

View 9 Replies View Related

T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

I have a table with about half a million records, each representing a patient in my county.

Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.

I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.

I can see what I want to accomplish by using ROW_NUMBER, thus:

SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank

I see the numbers in the RecNumber column falling behind the RRank as I scan down the results

My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:

UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);

but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).

View 5 Replies View Related

T-SQL (SS2K8) :: Multiple Child Records Per Row Dynamically?

May 21, 2015

I have two tables:

tblServer
tblInstance

tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.

I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.

I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:

with serverInfo as
(
select
srv.srvType as Type
,srv.srvName as ServerName
,srv.srvIP as ServerIP
,ins.instNetName as InstanceNetName

[Code] .....

View 3 Replies View Related

Displaying Multiple Records From One Record

Jan 26, 2007

Hi there,
I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.
Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?
User tableUserid      Username
1             Bob2             Luke3             John
Store procedure resultCompanyname         Username
Acrm Corp               NullMaxwellarm Ltd        Null
Regards,

View 1 Replies View Related

Combining Multiple Records Into One Record

Dec 20, 2007

Hi All,

I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record,
Table1 has the primary key
id
--
1
2
3
4
Table2 has the follwing records
id year subject
-----------------
1 2000 English
1 2002 French
2 2004 English
2 2005 English
2 2006 English
3 2007 French
I want the result to be like this
id 2000 2001 2002 2003 2004 2005 2006 2007
-----------------------------------------------------------
1 English null French null null null null null
2 null null null null English English English null
3 null null null null null null null English

Appretiate your assistance

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

T-SQL (SS2K8) :: Most Updated Records From Multiple Join Query

Mar 28, 2014

i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).

View 5 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records Each Containing Null Value For One Of Fields

Apr 11, 2014

I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:

Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,

[Code] ....

View 5 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records From A Single Variable?

Apr 24, 2014

I have two tables. Table 1 has column "job", table 2 has column "job" and column "item". In table table 2 there are multiple "items" for each "job"

I would like to insert all of the "items" into table 1, based on a join table1.job = table2.job

View 7 Replies View Related

Insert Multiple Records - First Record Is Insterted Twice: Why?

May 5, 2008

Hi and thanks for any advice.Right now I have a for loop that inserts multiple records. The first record is inserted into the database and I am not sure why. Here is the code I am using - Dim intPhotoKeyID As Integer
Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc "
InsertCmd += ") values " & _
"(@OriginalName, @GalleryID, @PhotoDesc " & _
") "
InsertCmd += "; SELECT CAST(scope_identity() AS int);"

Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString)
Dim MyCommand = New SqlCommand(InsertCmd, DBConnection)
MyCommand.Connection.Open()
MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150))
MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int))
MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text))
myCount = myCount & i 'Is zero for first two records
MyCommand.Parameters("@OriginalName").Value = "Orig" & i
MyCommand.Parameters("@GalleryID").Value = 5

MyCommand.Parameters("@PhotoDesc").Value = MyCommand.CommandText & myCount

Try
intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar())
MyCommand.Connection.Close()
Catch Exp As Exception
Response.Write(Exp)
'ResultsLabel.Text = Exp.ToString()
End Try
DBConnection.Close() Below is the whole procedure which either uploads an image or directory of images and resizes them. Then gets the meta data from the image. Then creates an entry into the database for each image. If I am dealing with 4 images then 4 images are uploaded to the new gallery folder. But 5 entries are added to the database. Thanks again for any help, Jennifer Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)

If ListDirectories.SelectedValue.Length > 0 Or fileUpEx.HasFile Then
Dim i As Integer
Dim selectedDirectory As String = ListDirectories.SelectedValue
Dim photoDescription As String
Dim photoAuthor As String
Dim photoTitle As String = ""
Dim photoName As String
Dim myFiles As String()
Dim fileCount As Integer

If multiUpload.Visible = True Then
myFiles = Directory.GetFiles(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/")
fileCount = myFiles.Length - 1
Else
fileCount = 0
End If

'Get last photo order from Photo table
Dim PhotoMaxOrder As Integer = 0
Dim DS As DataSet ' DataSet object
Dim SQL As String = "SELECT MAX(PhotoOrder) FROM Photos WHERE GalleryID=" & Request("ID").Trim
Dim connString As String = ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString
Dim sqlDA = New SqlDataAdapter(SQL, connString)
DS = New DataSet
sqlDA.Fill(DS, "Photos")
If Not DS.Tables("Photos").Rows(0).Item(0) Is System.DBNull.Value Then
If DS.Tables("Photos").Rows.Count > 0 Then
PhotoMaxOrder = Convert.ToInt32(DS.Tables("Photos").Rows(0).Item(0)) + 1
End If
Else
PhotoMaxOrder = 1
End If

For i = 0 To fileCount

If multiUpload.Visible = True Then
photoName = Right(myFiles(i), InStr(StrReverse(myFiles(i)), "/") - 1)
Else
photoName = fileUpEx.PostedFile.FileName
End If

If InStr(photoName, ".jpg") > 0 Then
Dim MyPhoto As Bitmap

If multiUpload.Visible = True Then
MyPhoto = New Bitmap(myFiles(i))
Else
MyPhoto = Bitmap.FromStream(fileUpEx.PostedFile.InputStream)
End If
'testFile = myFiles(i)
Try 'Get photo description
Dim Make As PropertyItem = MyPhoto.GetPropertyItem("270")
Dim ascii As Encoding = Encoding.ASCII
photoDescription = ascii.GetString(Make.Value, 0, Make.Len - 1)
Catch ex As Exception
photoDescription = ""
End Try

Try 'Get photo author
Dim Make As PropertyItem = MyPhoto.GetPropertyItem("315")
Dim ascii As Encoding = Encoding.ASCII
photoAuthor = ascii.GetString(Make.Value, 0, Make.Len - 1)
Catch ex As Exception
photoAuthor = ""
End Try

Dim photoXmpData As String = GetXmpXmlDocFromImageStream(MyPhoto)

If Not photoXmpData = "" Then
photoTitle = GetXmpXmlNode(photoXmpData)
End If

'insert photo record into photo table

Dim intPhotoKeyID As Integer
Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc "
InsertCmd += ") values " & _
"(@OriginalName, @GalleryID, @PhotoDesc " & _
") "
InsertCmd += "; SELECT CAST(scope_identity() AS int);"

Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString)
Dim MyCommand = New SqlCommand(InsertCmd, DBConnection)
MyCommand.Connection.Open()
MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150))
MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int))
MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text))
myCount = myCount & i 'Is zero for first two records
MyCommand.Parameters("@OriginalName").Value = "Orig" & i
MyCommand.Parameters("@GalleryID").Value = 5

MyCommand.Parameters("@PhotoDesc").Value = MyCommand.CommandText & myCount

Try
intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar())
MyCommand.Connection.Close()
Catch Exp As Exception
Response.Write(Exp)
'ResultsLabel.Text = Exp.ToString()
End Try
DBConnection.Close()

'check photo width and height
Dim NewFilePath As String = Server.MapPath("/appscode/galleries/photos/g" & Request("ID").Trim & "/") & "p" & intPhotoKeyID.ToString & ".jpg" ' & photoName
lblMessage2.Text = lblMessage2.Text & InsertCmd
If MyPhoto.Width.ToString = "200" Or MyPhoto.Height.ToString = "200" Then
'just copy the image
If multiUpload.Visible = True Then
File.Copy(myFiles(i), NewFilePath, True)
Else
fileUpEx.SaveAs(NewFilePath)
End If
Else
'resize image
Dim NewSize As System.Drawing.Size = New System.Drawing.Size(200, 200)
ResizePicture(MyPhoto, NewFilePath, NewSize) 'and save it
End If
MyPhoto.Dispose()
GC.Collect()
End If

Next
If Not myFiles Is Nothing Then
If myFiles.Length > 0 And multiUpload.Visible = True Then
'delete directory that was just processed
Directory.SetCurrentDirectory(Server.MapPath("."))
Directory.Delete(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/", True)
ListDirectories.Items.Remove(selectedDirectory)
'lblMessage2.Text = "Your file(s) have been added."
End If
Else
'lblMessage3.Text = "Your file has been added."
End If

Else
lblMessage2.Text = "Please select a folder."
End If

lblMessage3.Text = myCount
End Sub

View 8 Replies View Related

SP Return Multiple Records For A Single Record

May 3, 2007

I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic

and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4

I need to get back something like this

TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4

that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client

this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this

TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray

but I am not sure how this idea could be implemented using the capabilities of SQL.

my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.

any ideas in how to make this better ?

View 8 Replies View Related

Easiest Way Of Combining Multiple Fields From Different Records Into One Record?

Jul 20, 2005

I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!

View 5 Replies View Related

T-SQL (SS2K8) :: Selecting Data From Table With Multiple Conditions On Multiple Columns

Apr 15, 2014

I am facing a problem in writing the stored procedure for multiple search criteria.

I am trying to write the query in the Procedure as follows

Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3

I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.

View 4 Replies View Related

SQL Server 2012 :: Multiple Records Queried Into Single Record?

Jan 27, 2014

I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.

People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801

[code]....

Results

PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom

View 8 Replies View Related

SQL Server 2008 :: Combining Multiple-same Records With Different Values Into One Record

Jun 25, 2015

I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example: Multiple records with the same record ID:

RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302
1380900 XZ01 12 1/6/2015 L405
1380900 XZ01 12 1/6/2015 P302 1004

INTO a single record:

RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302 L405 1004

View 2 Replies View Related

Transact SQL :: Use FOR XML To Return Multiple Child Records Within Each Parent Record

Aug 7, 2015

I have a single complex query.

SELECT
Col1, -- Header,
Col2, -- Header,
Col3, -- Detail
Col4, -- Detail
Col5, -- Detail
FROM
TableName;

The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -

SELECT
Cols
FROM
Table Names
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL

This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -

<Header >
<detail 1>
</detail 1>
<Detail 2>
</Detail 2>
<detail 3>
</detail 3>
</Header>

View 2 Replies View Related

T-SQL (SS2K8) :: Pulling Records From A Table Between Date Ranges In Another Table

Mar 17, 2014

This seems simple enough but for some reason, my brain isn't working.

I have a lookup table:

Table A: basically dates every 30 days

1/1/2014
2/3/2014
3/3/2014
4/3/2014

I have Table b that has records and dates created assocated with each record

I want all records that fall between the 1st 30 days to have an additional column that indicates 30

union

records with additional column indicating 60 days that fall between the 30 and 60 day

union

records with additional column indicating 90days that fall between the 60 and 90 day mark.

Is there an easy way to do this?

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

Automatically Adding Records To Child Table When Record Added To Parent Table

Aug 19, 2006

In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks

View 1 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID key1 key2 key3 key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

T-SQL (SS2K8) :: Identify Columns Which Will Create Unique Record In A Table

Sep 15, 2014

I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.

View 4 Replies View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

HOW TO: Delete All Records From A Table Where A Child Record From Another Table Does Not Exist.

Mar 17, 2008

I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.

Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime

Child Table: TBL_VERIFICATION
PK: Serial Number

Thanks,
Sean

View 1 Replies View Related

T-SQL (SS2K8) :: Filter Records In Table?

Mar 6, 2014

I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.

MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013

I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.

MachineA 5/7/2011
MachineB 5/5/2010

Select Distinct would almost do it, but I need each unique machine that has the latest date.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Get Distinct Records From Table

Apr 14, 2014

Suppose I have 2 table

1)Main
2)History

Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate

IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id

i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table

table structure
main table
MAIN_SKUDEDUCTIBLE_amtmodel_idcatagory
1100100phone
2150101phone
3200109smartphone
4100202smartphone
History table
History_idMAIN_SKUDEDUCTIBLE_amtmodel_idcatagoryModifiedDate
11150100phone4/14/2014
21200101phone4/13/2014
34109202smartphone4/14/2014
44101202smartphone4/13/2014
52200101phone4/13/2014
63100109smartphone4/12/2014

View 3 Replies View Related

T-SQL (SS2K8) :: Delete Table Records With Backup?

May 2, 2014

using below query

DELETE FROM Table_name
WHERE Date_column < GETDATE() - 30

am able to delete old records morethan 30 days, but i want to results to be saved in file.

before deleting i want to a craete a file and save the to be deleted records.

View 7 Replies View Related

T-SQL (SS2K8) :: Export Records Of A Table In XML Format

Jun 5, 2014

I need to export the records of a table in xml format.

create table ##prova
( Valuta varchar(2),
Misura float
)
insert into ##prova values ('EU',1000)
insert into ##prova values ('$',2000)

The final result must be something like this:

<root>
<obs id=”0”>
<dim name=”Valuta” value=”EU” />
<dim name=”Misura” value=”1000” />
</obs>
<obs id=”0”>
<dim name=”Valuta” value=”$” />
<dim name=”Misura” value=”2000” />
</obs>
</root>

View 2 Replies View Related