Pulling Info From The Current Row Of One Dataset Into Another
Sep 6, 2007
This is day 1 of me ever using RS so forgive ignorance of the topic.
Here's the basic scenario:
Input parameter from the user is an Account name.
For that account name I want to list Addresses of locations under the account, and then for each of those, I want to list received shipments from the address.
So Ideally the result is:
Account Name
Address 1
Shipment 1
Shipment 2
Shipment 3
Address 2
Shipment 1
Shipment 2
Address 3
Shipment 1
So, I am able to list the addresses just fine, but the next step is getting the stuff from the shipments table based on the currently running address? I'm probably saying that poorly. What I need to know is how do I structure the dataset/report to accomplish this. I suppose I could combine the query for the shipments with the query for the addresses, but that seems suboptimal to me.
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
Just recently I noticed something that I consider very strange on a SQL7.0 server with SP1.
In [Server -> Management -> Current Activity -> Process Info] I see a great number of rows(connections) around 70 or so that all have the same net_address value. However, the nt_loginame is different on each.
Q1: How can this be? Q2: Isn't the net_address really the MAC of the network card?
Also, on a test SQL7.0 server with SP1 where the connections are far less (around 15 or so), I noticed the same problem. Luckily, because the connection count is low I was able to go around to each machine where the nt_loginame was used to log on and found that the net_address shown in [Current Acivity] didn't match any of these machines. Why?
Dear AllI have problem with my database server which running SQL server 2000.The server running very slow. The worst case, to save a record requiredmore than 20-30 seconds.Since this problem, I usually monitoring Process Info from EnterpriseManager (Management - Current Activity), and I found a misteriousprocess as follow :1. User: SystemAccessTo: MasterStatus: BackgroundCommon: Task ManagerWaiting: >438 Million2. User: SystemAccessTo: MasterStatus: BackgroundCommon: Task ManagerPhysical IO: > 510003. User: Administrator (Join domain)Database: MSDBStatus: SleepingCommon: Awaiting CommandApp: SQL Agent Alert EngineCPU Usage: > 16 MillionAnybody know about these condition? Does it normal?ThanksMichael
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore , lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore from (select group_name, Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know. What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in. This is the relationship of the data. I have at the highest group, an industry code, then a customer, then the part and then the fiscal year. What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC 001 - Signposts M12345 Part 1 2006 5 6 2 2007 0 3 1
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
I've this query SELECT t1.ID, t1.Date, t1.Time, t1.VALUE FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045 So, i will get ID | Date (this is datetime) | Time (this is integer) | Value -------------------------------------------------- 204 | 8/1/2005| 2359 | 90 205 | 8/1/2005| 2250 | 99 206 | 8/1/2005| 1950 | 88 ... ... 207 | 8/7/2005| 1845 | 77 208 | 8/7/2005| 2255 | 77 209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value -------------------------------------------------- 204 | 8/1/2005| 2359 | 90 205 | 8/1/2005| 2250 | 99 ... ... 207 | 8/7/2005| 1845 | 77
I only have this query, SELECT t1.ID, t1.Date, t1.Time, t1.VALUE FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows. Table 2: Large table with CLOB, 10,000,000 rows
select CLOB from table2 where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
Hi, I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0. if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in if @Status=0 even if I pass @status=0. What am I doing wrong? Any help is appreciated.
ALTER PROCEDURE [dbo].[MyStorProc]
(
@Status smallint,
@RowCount int = NULL,
@FacilityId numeric(10,0) = NULL,
@QueueID numeric (10,0)= NULL,
@VendorId numeric(10, 0) = NULL
)
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
If @Status = 0
BEGIN
SELECT ...... END If @Status = 1 BEGIN SELECT...... END
i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer. but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.
I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.
Ok, to start, i have read SO many posts on this site, and othe sites, and i cannot get this to work.
What i want to do is: 1. Login 2. show an image from a SQL database, based on that login.
I have the image path stored in the database, and i can easily get it to display on the screen, but it doesnt show the correct image for the login, it shows them all. (all images based on that column).
I know i have to put a WHERE at the end of my SELECT statement, but everything i have tried doesnt work.
When i do the query builder, and i put in the value, it shows exactly what i want, based on the login's username.
Ok so every week I have to download a file containing in excess of 40000 products. I then have to change the category ids to suit my system. Is there a script or something that I could create so I just run it each week to do a search and replace and then loop until all category ids have been changed.
Table = ecommstore Column = section
I would need to change say from 84 to 1 then 86 to 2, 87 to 3 etc etc
I have had an unusual request and I thought to ask youreal smart folks out there for a hand. I am working with a non microsoft application that writes links to an html file. I need to extrapolate the link and pull it into a database table on our MS-SQL 2005 server. I believe that this woould best be done with a stored procedure.
I guess that I will get the files and save them on a given directory on the W2K3 Enterprise server and run the proceedure there.
Good Morning, I'm not quite sure this is the right place to post this, but i'll try anyway. I have a website where I allow people to create their own layouts in HTML, and in the HTML I have them placeing markers (eg. |1| ) where they would like to place something from a database. All the HTML is stored in a SQL Server DB, and when I pull it out, it's fine, except it won't allow me to Replace any of the markers with values from the DB. Here's and example line of HTML that's held in the DB:<table width='50%' border='1'><tr><td>|0|</td><td>|1|</td></tr> and when I do a strHtml.Replace("|0|", dReader["somefield"].ToString()) or even just a strHtml.Replace("|0|", "Test") it never changes it. Anyone have any ideas? Thanks, Deepthought
In my code behind file I have this function: 1 public void getAllSystems() 2 { 3 MTConnection con = MTConnection.CreateConnection(new Credential(new Context("ClearQuest - Boise", "Version 2.0", Micron.Application.Context.Environments.Production), "DSSPROD", Credential.DataSourceTypes.MSSQL, Credential.DataSourceProviders.Odbc)); 4 5 string sqltest = " select T1.dbid,T12.systemname AS 'ParentSystem',T1.systemname AS 'SupportSystem',T1.systemstate,T1.features,T3.name,T4.stakeholder," + 6 " T4.fldcolumn_1,T1.systemwebpage,T1.description from ( ( ( ( ( ( ( ( CQ_ADMIN.micronsystem T1 INNER JOIN" + 7 " CQ_ADMIN.microngroup T6 ON T1.primarysupportgroup = T6.dbid ) INNER JOIN CQ_ADMIN.securitygroups T7 ON T1.secuirtygroup" + 8 " = T7.dbid ) LEFT OUTER JOIN CQ_ADMIN.parent_child_links T12mm ON T1.dbid = T12mm.child_dbid and 16778862 =" + 9 " T12mm.child_fielddef_id ) LEFT OUTER JOIN CQ_ADMIN.micronsystem T12 ON T12mm.parent_dbid = T12.dbid )" + 10 " LEFT OUTER JOIN CQ_ADMIN.parent_child_links T3mm ON T1.dbid = T3mm.parent_dbid and 16804095 =" + 11 " T3mm.parent_fielddef_id ) LEFT OUTER JOIN CQ_ADMIN.feature_1 T3 ON T3mm.child_dbid = T3.dbid ) LEFT OUTER" + 12 " JOIN CQ_ADMIN.parent_child_links T4mm ON T3.dbid = T4mm.parent_dbid and 16804263 = T4mm.parent_fielddef_id )" + 13 " LEFT OUTER JOIN CQ_ADMIN.stakeholder T4 ON T4mm.child_dbid = T4.dbid ) where T1.dbid <> 0 and ((T6.abbreviation =" + 14 " 'Test ES Eng Software' and T1.systemstate = 'Active')) and ((T7.dbid in (select parent_dbid from" + 15 " CQ_ADMIN.parent_child_links where parent_fielddef_id = 16778767 and child_dbid in (select child_dbid from" + 16 " CQ_ADMIN.parent_child_links where parent_fielddef_id = 16777328 and parent_dbid = 33664106)and T12.systemname IS NOT" + 17 " NULL ))) order by T3.name ASC,T1.systemname ASC"; 18 19 System.Data.DataSet ds = con.ExecuteDataset(CommandType.Text, sqltest); 20 21 foreach (DataRow drCQ in ds.Tables[0].Rows) 22 { 23 string parent_system = drCQ["ParentSystem"].ToString(); 24 string child_system = drCQ["SupportSystem"].ToString(); 25 //Response.Write(child_system + "<B><I>'S PARENT IS</I></B> " + parent_system + "<BR />"); 26 } 27 } I would like to pull the values of parent_system and child_system into my main page but I don't know how to do that. Is it even possible?Thanks,Xana.
hi im making a page where i want a single line at a time to be pulled from my MS Database, basically at the moment i have a list of questions, but the page is displaying all the questions from my database, i only want it to pull out 1 and then if the user clicks the true button then it goes to another page and displays another question?
I need to pull a report that gives me the desired results as show below. I first show the data that is in the table, then below that is the desired result.
What I am trying to do is pull every employee's most recent 3 appraisal Effective Dates within the last 4 years where Rating is different than X.
Sorry I don't reall know how to explain this using code because I seem to be having a block right now. Not sure where to start really.
Any help would be greatly appreciated!! Thanks again!
Records in tblAPPRAISALS Table:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5 A | 1/10/2003 | 5 A | 1/10/2002 | 5 B | 1/10/2006 | 5 B | 1/10/2005 | 5 B | 1/10/2004 | X B | 1/10/2003 | 5 C | 1/10/2006 | 4 C | 1/10/2005 | 5 C | 1/10/2004 | 5
My current SQL:
SELECT DISTINCT TOP 3 Employee, Appr_Eff as [Appr Effective], Rating_Of_Rcd as [Rating] FROM tblAPPRAISALS WHERE Appr_Eff >= DateAdd("YYYY", -4, Date()) AND Rating_Of_Rcd <> "X" Order by 1, 2 DESC
Results from this Query:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5
Desired Results:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5 B | 1/10/2006 | 5 B | 1/10/2005 | 5 B | 1/10/2003 | 5 C | 1/10/2006 | 4 C | 1/10/2005 | 5 C | 1/10/2004 | 5
using remote admin program and i need to pul the entire DB onto my local maachine to do some trial runs. I have a static IP so i thought I could just DTS the how system to my computer. Can any one verify that this is the best way of suggest another? Thanks Matt
FROM Edina_Class class LEFT JOIN Edina_Names names ON names.claimno = class.claimno INNER JOIN Edina_Address addr ON class.claimno = addr.claimno where addr.isactive = 1
What I want is to pull back the unique names, so, if the use only had 1 name, name2-4 would be blank, if the person had 3 names name4 would be blank.
Since "name1" is in the address table, I can pull that one back easy, but what is happening, is the code is taking the first name is finds in the name table and filling in names2 - names4 with it.
I have no idea how to fix the person besides recreating the name to have all of the names per user in 1 record opposed to individual records in the names table.
SQL Server 2000 - Backend Access 2000 Runtime - Front End Connecting via ODBC
I have read loads and loads of examples and looked at other sources of info for help on this but am struggleing big time.
I have multiple users working in the same database table. The problem is they often get an error message about the record has been changed and would they like to save the changes to clipboard etc etc.
I basicly want to implement pessimistic locking for my tables. So once a user has started to edit that record nobody else can get to it.
Code: SELECT P.ID, P.QTY, T_DATE FROM PARTS P INNER JOIN INVENTORY I ON P.ID = I.PART_ID WHERE P.QTY > 0 AND I.CODE = 'R' GROUP BY P.ID, P.QTY, I.T_DATE HAVING MAX(T_DATE) !> DATEADD(MONTH,-12,GETDATE())
[Code] ....
I am trying to make the sql only pull records where the highest date stored is older than a year ago.
So, based on today's date 12/21/2012:
if the highest (max) T_DATE is 3/12/2012 => don't pull that record if the highest (max) T_DATE is 11/30/2011 => pull that record into the recordset
the statement is off, it is pulling records that should not be included.
I am trying to pull subscription for a database but I always get the error message ... Error 15004: Name cannot be null What exactly have I done wrong Plaese help Thanks Nneka Echebe
Hello, I am new here and to sql so please bare with me.
I am trying to create 2 select statements from the same field. The field is a job_description field that sometimes contains PO#'s. One select statement needs to extract the PO# if it has been written in the field and the other needs to extract all data that is not the PO#
Here is some example of the data:
New shirts for 2007 Magazine cover for may edition Way to go postcards PO# 45687 PO#879
For the first select statement I need to capture just the PO#. the result for the above examples would be:
NULL NULL 45687 879
For the second select statement I need everything but the PO#. The result would be:
New shirts for 2007 Magazine cover for may edition Way to go postcards NULL
So far I have put together a basic idea for the PO# part:
(SELECT SUBSTRING (job_description , (SELECT patindex('%PO#%', job_description)+3),10)WHERE job_description LIKE '%po#%')
If i could figure out a way to only return numeric data from the above i think it will work.
As for the second select statement i am at a loss on how to select everything but the PO#.
