Ok, I have three tables worth of data. They basically hold data that is keyed into a system for potential clients and include stuff like names, weights, classes of weight, addresses etc.
The main table that holds data on a record by record basis is set up like this:
This table contains the batches (or collections of the data above). Basically a collection of bills is called a batch, so if a batch has 18 bills in it, those 18 bills are keyed and assigned the prospect_batch_id above.
This is the table that contains the various weights and classes for the individual records in the first table (tbl_prospect):
Tbl_prospect_clwt
Prospect_clwt_id (pk, unique)
Prospect_id(unique)
Class
weight
Generally without worrying about the multiple classes and weights it's easy to just join all three tables and pull the fields I need, but now that I have to get multiple classes and weights I have no clue how to actually grab them and delimit them with a comma.
I think I'm only going to be working on tbl_prospect and tbl_prospect_clwt, and if I put a filter in place for the prospect_batch_id I can see the individual record in tbl_prospect as well as the multiple classes and weights in the clwt table, as seen here:
So record 18 of that batch is comprised of two shipments, one that is class 100/weight 1623 and one that is class 70/weight 438, just not sure how to actually grab both of those for each record and delimit them.
Hi,I have table which has the following values :ID SEQ Text1 1 A2 1 B3 2 C4 2 D5 2 E6 2 F7 3 GThe result should be :1 AB2 CDEF3 GCould somebody help me with this? I could use an cursor but the tablecould be large and i want a fast solution.Thanx in advance...Hennie
there can be more than one Parentobject so I want to concatenate them so I have them on one line
for example
901 Joe Dow 901 Jane Dow
I want one line - | 901 | Joe Dow, Jane Dow
I found something similar as below but I'm getting dups like
901 |Joe Dow , Joe Dow 901 | Jane Dow, Jane Dow
DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values SELECT DISTINCT ParentObject, (SELECT STUFF( (SELECT @Delimiter + s1.[View As] FROM Signatures s2
I have a query that produces seperate rows for people, but I want to combine them into one place. I tried doing this in SQL but apparently it's not very easy in SQL Server. You need to loop through a table using cursors. I'm not quite that advanced with SQL Server and was wondering if there might be an easier way just using SSRS.
In other words I have a table as such:
1 John Smith 2 Jane Doe 3 Matthew Jones
And I'd like to create one textbox that contains the following:
I have a table and a view. Let's call the Table dbo.EmpPers and the view dbo.codes
EmpPers holds personal information and Codes holds all the codes for the company, naturally.
There is a field in Emppers called 'EthnicID'. It a number say for example 1, 2, 3 ect...
'Codes' has a field called CodDesc. It's a varchar for example White, Hispanic, African American. ect...
What is the best way to match up the description with the code? Say for all Persons who have have the Hispanic Code '2', I want to add the description to it in the select statement.
We have a table in DB2, which has a column1 (Data type: - decimal (10, 0)) and column2 (Data type: - varchar (70)), I need to concatenate these two columns into one while displaying output
Procedure I followed:--
Tried to convert column1 to varchar using cast function so that I could concatenate both columns. However I ended up in getting the following error message
Hi! Need help with this one: I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date.
378 9/05
388 9/05
4/05
1/06 606
1/06 646
76 5/05
100 1/05
118 8/05
129 8/05
9/05 342
05/3 123
1/07
4/06 164 The problem is that I need to get each value alone (to separate columns), in example: Value Date 378 09/2005 388 09/2005 0 04/2005 ... 606 01/2006
and so on... In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone.
I will appreciate any good ideas, Thanks in advance, Aldo.
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:
id metric_id metric_type_id metric_name 1 80 2 Fiscal Enterprise Value Historic Year 1 2 81 2 Fiscal Enterprise Value Current Fiscal Year 3 82 2 Fiscal Enterprise value Forward Fiscal year 1 4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2 5 101 3 Calendar Enterprise value Historic Year 1 6 102 3 Calendar Enterprise Value Current Fiscal Year 5 103 3 Calendar Enterprise value Forward Year 1 6 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_details
id metric_type_id metric_type_name 1 1 Raw 2 2 Fiscal 3 3 Calendar 4 4 Calculated
The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:
Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.
Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,
metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally
metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).
As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:
-- Dummy year variable to make it easier to use MONTH() function -- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc... DECLARE @DUMMY_YEAR VARCHAR(4) SET @DUMMY_YEAR = 1900;
with temp(security_id, metric_id, value) as ( select ml.security_id, ml.metric_id, ml.value from metrics_ladder ml where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...) -- only consider securities with fiscal year end not equal to december and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec') ) insert into @calendar_averages select temppivot.security_id -- Net Income ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103] ,NULL as [104] -- Share Holders Equity ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107] ,NULL as [108] -- Capex -- Sales -- Accounts payable etc... .. .. from temp pivot ( sum(value) for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...) ) as temppivot inner join company_details cd on temppivot.security_id = cd.security_id
********* END SQL *********
The result then needs to be unpivoted and stored in metrics_ladder.
And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
I recieved a SQL Server table that was supposed to have just the firstname in a field, but actually has firstname and middle name. Example David Michael Carol Anne Is there a way in a query to look for the blank space and separate the names?
Hello there,How can i take data out of my database, put them into a textbox and then separate with a comma..An example:----------------------------------| column Email || mail1@email.com || mail2@email.com || mail3@email.com |----------------------------------Put them into a textbox and separate with a , (comma)-------------------------------------------------------------------------------------| mail1@email.com, mail2@email.com, mail3@email.com |-------------------------------------------------------------------------------------Anybody who know how I can do that? :S
On the time of installation SQL Server asking me where I wont to locate the DATA files and the PROGRAM files. It’s giving to me choice to put database AND log files on one disk and program files on separate. But what about to separate LOG and DATA files. I have RAID1 especially created on F: drive for LOG files and RAID 5 on E: for DATABASE files. When I have to separate that if not on the time of installation? How I can do that?
I have a table which has fields called defid, datavalue, pid etc. I have to create a new table which should have fields: CustID, datavalue[SocSec]{note:this is the value in d.datavalue when defid is 1004), datavalue[FirstName]{note: this is the value in d.datavalue when defid is 1006}, datavalue[LastName]{note: this is the value in d.datavalue when defid is 1007}, [FullName] {note: this is the value I should get by joining the datavalues (lastname and firstname) when defifis 1006 and 1007) ie. ex: FirstNameLastName.
COULD ANYONE PLEASE HELP ME ON THIS. DATA QUERY is as follows: Thank you in advance.
SELECT DISTINCT c.CustID, d.datavalue, f.name FROM dbo.Customers c INNER JOIN dbo.Date7 d ON d.pid = c.root INNER JOIN dbo.fielddisc f ON f.defid = d.defid WHERE c.separatedate BETWEEN @StartDate AND @EndDate AND c.DateTab = 7 AND d.defid IN (1004, 1006, 1007, 1009) AND c.CustID = 10 AND c.root = 8472 ORDER BY c.root
This is probably for most of you a very basic question. My goal is to define in a seperate instance from application all infos related to DataSets and DataAdapters that are needed when connecting to a specific database. Why?
First, I would like to be able to have in a single instance everything needed when hooking to a SQL Server Express database and in an other everything needed when hooking to a SQL Server CE database. Then, from application, I would like to be able following a few basic configuration steps to choose from one or the other, or even both in specific cases.
Second, I want to be able to seperate Database related stuff from application so that I can re-use everything from any other application when needed.
I've read several books, articles on the subject and would like to have your opinion as several approaches were illustrated through them. What would you use for this:
hi alli have a web application on a web server.and another windows application on another computer.there are databases on each of them.how can i send and receive data between these two applications?please reply me as soon as possibleregards
I'm getting ready to install SQL Server 2005 Enterprise for the first time and I have a question about the directory location of the log files and the data files. I have 3 RAID arrays on my server, 1 for the OS, 1 for SQL Logs, and 1 for SQL Data.
Here's my issue. I want to install the logs on the Log array and the SQL data on the SQL data array, however, during the installation I can't find anything that allows me to select certain directories!
I receive a file that will have hyphens between data items such as 123-aed-edr-45r-ui9 1-ed3-45r-rrr-98u I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
I am writing a stored procedure to prepare some reports.
The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).
When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?
alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status] @Salesmen nvarchar(50), @QuoteStatus nvarchar(50) AS BEGIN SET NOCOUNT ON;
I am using Reporting Services in the following way. The application implements a three tier Architecture viz Web Server, Application Server and Database Server. Reporting Services 2005 is installed on the Database Server.
The application uses ReportViewer Control which is part of an ASPX page deployed on the Web Server. For ReportViewer Control to fetch data from Reporting Services using RS API, we have to set ReportServerUrl and Report Path. In this scenario, the web server cannot communicate to the database server directly. The web server is in the DMZ and it can only communicate with the Application Server which is in a separate Network.
How can I use Report Viewer Control to fetch data from Reporting Services which is on the Database Server.
I have 1 table that is just a list of feeds. A, B, C, D etc (15 rows in total) and each feed has other information attached to it such as Full name, location etc etc. I am only interested in the Feed column.
Each feed then has a corresponding data table which contains a list of records. Eg Feed A data is contained in TableA, Feed B data is contains in TableB and so on.
Basically what I need is a combined table that shows the list of Feeds in the 1st Column ( So A, B, C, D…..) and then a second column which counts the records from each separate data table corresponding to that feed.
So the end result would look something like this:
Feed------No of Records A----------4 (from TableA) B----------7 (from TableB) C----------8 (from TableC) D----------1 (from TableD)
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
LEFT JOIN Status As S on S.LoanID = L.LoanID LEFT JOIN Borrower B on B.LoanID = L.LoanID LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID where S.PrimStat = '1' and B.Deceased = '0'
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.
E.g. If I have the following data:
Main input: Key Name --- ---- 1 Steve 2 Jamie 3 Donald
