I have been assigned to write a report that queries 3 tables. Table Store, Store_ID unique index, Cust table Cust_ID non unique index, CUST_Store_ID relates to Store Table, Table Emp, Emp_ID non unique index, Emp_Store_ID relates to Store Table
The problem is that the Employes and the customer don't relate to each other in any way. There can be stores with no Employees and there can be stores with no Customers. There are employees and customers listed multiple times in thier respective tables, and the multiple listings need to show on the report. A result set may look like this.
And by correctly, I mean the way it *should* match, of course.I've got 2 data sources, using a left outer join, matching 2 columns. Whatever is the right side of my table is never matching and returning data. Here's my basic setup:OLE DB Source 1 (table1) Sel Name Order JoinKeyno UniqueID 1 yesyes Column1 0 no ...
OLE DB Source 2 (table2) Sel Name Order JoinKeyyes ID 1 yesyes Columns 0 no ...
There is a link (arrow) between UniqueID and ID, and the join type is "Left Outer Join". When I execute the statement "SELECT * FROM Table1 LEFT JOIN TABLE2 ON TABLE1.UniqueID = Table2.ID", the data returns correctly to me. What am I missing with the properties I've set above with the merge join?
How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.
I am checking for all parishes with no associated drivers. Currently I only have 2 of such.
The regular left join
select parish.name, driver.fname from parish left join driver on driver.parish=parish.name
Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours FROM dbo.tblEventStaffRequired;
and SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked FROM tblStaffBookings GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null drop table #left_table; if OBJECT_ID('tempdb..#right_table') is not null drop table #right_table; create table #left_table
i have one task in which i have to match some attributes(required for creating a new databse) with the exiting database, are these attributes present in exisisting database, if yes how many , and how many are not,pls do reply
Basically without going into too much detail, our company gets databases arriving and put onto our systems which have been made my other organizations with no guarantee of what the primary key is or if this is one at all.
I should probably give my main problem in an example for clarity:
Currently I have a .csv file full of data that needs to be put into say TableA. However I do not know if TableA has a primary key or not, or if the file that needs to be inserted into TableA contains duplicate data. I have the importer sorted that does this if you ignore the problem of duplicate data, however what I would like is an MS SQL query that does the following (but I cannot figure it out):
Assuming we are reading through the file line-by-line and a check is performed each time:
1.If there is a line with a primary key in the file that matches a primary key in TableA in the database update that row in the database with the line in the file.
2. If there is no primary key on the table and there is an exact data match between the line in the file and a row in the database then update it.
3. If neither 1 or 2 are successful then just insert the data.
Obviously the potential lack of a PK here makes things a lot more convoluted.
I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.
Does anyone have any suggestions how i would go about doing this?
Thanks in advance Humate
quote:Originally posted by Michael Valentine Jones
It takes real skill to produce something good out of a giant mess.
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.
How can i write a query to do this? Examples are as below.
Table 1 Sales Sale DateSale Amt 1Jun-141200 2Oct-142400 3Dec-14600 4Feb-1512000
I have a report in which I have a sub-report linked to the main report by a parameter. When there is no data for the sub-report, no data displays on the main report as expected.
Now the users are asking if there is no data in the sub-report if I can instead have "None" printed on the main report. I tried adding a label to the sub-report, but since none of the data from the main report links to the sub-report, nothing is still displayed. I then put a label on the Main report and placed it behind (set to back) on the main report. This works great when there is no data, but if there is data the label still prints and the report looks awful.
I cannot think of a way to accomplish what they have asked for.
I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.
If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact
If name, phone and email all provided, and source the same then update to new values.
If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and phone on SourceContact and name and/or Phone is blank in Contact then update
If name and email on SourceContact and name and/or email is blank in Contact then update
If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource
If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email
If Contact_fk is 0 then it is a new contact and just insert it.
IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact]; CREATE TABLE [dbo].[SourceContact] ( [SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED, [ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''), [ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),
Microsoft released an introductory tutorial for data mining in September 2007, and I was attempting to match the results on: http://msdn2.microsoft.com/en-us/library/ms169911.aspx
I was not able to match the results, so then I went "under the hood" for the tables, and discovered that my copy of AdventureWorksDW has truncated values in the Education field of the ProspectiveBuyer table. I had wanted to connect "EnglishEducation" from the Mining Model to "Education" in the "Input Table" to see if the additional linkage would affect the prediction results (the wizard does not automatically link these fields because they technically do not have the same name).
1) Is everybody's "Education" field in the "Prospective Buyers" table populated with truncated values? (which may be intentional since it is a sample dataset) -- compare to "EnglishEducation" field in the vTargetMail table
2) Am I the only one getting 0.50580 for all rows when I use "Decision Tree" (per the tutorial instructions)? By contrast, I was able to see a variance in expression values for clustering and naive Bayes.
My guess: everybody has a truncated "Education" field, and there is something different in how the data mining model was run under the Decision Tree option to yield the tutorial's numerical results for "expression".
Problem: I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data. The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.). There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.
Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.
Questions: Is this possible using Data mining? If yes, which algorithm is recommended?
Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?
I have a table ("MyData") with string columns that have nvarchar data that looks like this:
ColA -------- 42/90 78/109
I plan to do a mathematical grouping on the numerator (eg: 0-10,11-20,21-30, etc... ), So I need to grab the numerator and turn it into an int. For reasons I can't get into, I have to do this in pure T-SQL.
So my question: How would I write a select statement that regex pattern matches "^([0-9]+)/" on ColA and returns integers instead of text?
E.g. THis Query:
SELECT [magic t-sql syntax] as Converted_ColA from MyData
I'm importing floor machine data from SQL7 into SQL2005 using SSIS. I import the SQL7 data into a SQL2005 master table and then attempt to match the import data with the data in a current table for either update or insert of new machines. The SQL2005 master table was imported from a SQL2000 database. When I run the first Lookup import, it does not recognize the PK matches between the SQL7 import and SQL2005 master and imports all the SQL7 as new machines. The first Lookup branches to a second lookup that checks for changes in the SQL2005 master. When I run the package a second time, the second Lookup treats all the records as updates when it gets to the second Lookup, but should treat these as found. Any suggestions as to why this process is not working properly would be appreciated. Is there a way I can embed a picture of the process from SSIS in this post? Thanks
Our division has approximately 300 employees. We have an annual shift bid where seniority is calculated using Date of Hire. If 2 or more employees share the same Date of Hire then we fall back to Date of Application. Currently the SSRS report does a very simple query and shows all the employees in order of their Date of Hire. If they match then it sorts the matching Date of Hire entries alphabetically by the employee name. It then becomes the task of the scheduler to locatethe entries with the same Date of Hire and manually look up the employees' dates of applications then sort them accordingly and re-write the report.
Goal: Convert the manual process into an automatic process by modifying the current SSRS report. Data: The dataset is "DivDir" which contains the following fields: "EmpName", "DofHire", & "AppDate".
EmpName DofHire AppDate Adam ...... 12/2/1996 11/15/1996 Bob ..... .... 1/16/1997 12/27/1996 Charlie ....... 1/16/1997 12/12/1996 Dan ...... ... 4/11/2001 3/22/2001
In the above example I want the SSRS report to list the employees in this order: Adam, Charlie, Bob, Dan.How do I do this programmatically using SSRS?
I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock] AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock FROM tab_productdetails INNER JOIN tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file. Let's explain a little more:
I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.
Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.
What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?
Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows? Thanks for your help!
Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
I need joining 2 tables and putting the result into a new table.
Table export has the following columns: Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k
Table cc_split has the following columns: Company, Fun_Account, CC_Account, x, y, Split
The new table called export2 has exactly the same values as export i.e. : Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k
So what I need to join from export and split into export2 are company, cc_account (that shall go into account column of export2 together with the account of export) and the amount, which shall be ( split column * amount column of export), the rest values shall be the same.
So what I have so far is
Code: INSERT INTO EXP_DATA_2(PERIOD, ACTUALITY, COMPANY, CURRENCY, ACCOUNT, AMOUNT, EXTDIM1, EXTDIM2, EXTDIM3,EXTDIM4,JOURNAL_TYPE,C_COMPANY, COMPANY,C_DIM, TRANAMOUNT, TRANCURR,REGION, TRANSF_DATE) SELECT COMPANY, CC_ACCOUNT, SPLIT FROM EXP_DATA INNER JOIN CC_SPLIT ON EXP_DATA.COMPANY=CC_SPLIT.COMPANY AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT WHERE EXP_DATA.PERIOD IN ('1102', '1103', '1104')]
So at the end basically table exp_data 2, shall have the same values as exp_data but the account column shall include the same values of account column of exp_data plus the values of cc_account of cc_split table. And also the amount column shall be the multiplication of the column split from cc_split * amount of exp_data
I have realised that I need an alias, maybe a for exp_data and b for cc_split.
i have 2 tables not connected in any way but both have orderid filed (same filed). In one table this filed (and onther one) are keys, the second table dose not hace a key at all. The same order_id CAN repeat itself in each table. When i try to join the tables (some rows just in one table and some in both): Select tab1.name, tab1.orderid, tab2.sku from tab1 inner join tab2 on tab1.orderid=tab2.orderid The result i get is duplicate. each row is multiple. What I'm doing wrong?
Table One is an older database and has the column employee id, which would always consist of first letter of the last name an underscore and a numeric value. So for example, data looks like
employeeID R_12345678 S_5555555 T_777777 U_7777
Our new data structure simply removed the letter & underscore.
employeeID 12345678 5555555 777777 7777
Now my question is, how could I join on oldDB.employeeID to newDB.employeeID since the data is very similar, but not exactly the same?
I have three tables that I am trying to join in order to produce some report output. I have tried numerous ways, but they all don't give the results I need to provide.
Below is the table creation, some inserts for data and the output I am looking for.
Detail_Acct_By_Day: EMPName | Acct_Num | Channel | Medium | Date | Rev $ | Clicks | Impressions BobSmith | X0000005 | US | Net | 2008-05-11 | 500.50 |240 | 500 BobSmith | X0000005 | US | Cable | 2008-05-11 | 23.75 | 0 | 0
My latest query, which doesn't work is: select S.EmpName, S.acct_num, R.RevenueDate as Date, coalesce(R.Channel, P.channel)as channel, coalesce(R.medium, P.medium), GrossRev, coalesce(P.clicks, ''), coalesce(P.impressions, '') FROM SalesAcct AS S left outer join Revenue as R on S.acct_num = R.acct_num left outer join Perf as P on S.acct_num = P.acct_num
Yields two rows...but the Medium duplicates and the Clicks and Impressions also get duplicated when they should be zero.
BrokersCode BrokerName 100 John 101 muller 102 ABC
Now my requirement is to show broker Name (John, Muller, ABC) against their codes in table Booking. i tried with left outer join, but this is not working after join in result it shows more rows in booking table as original. (No of records in booking table should be same after join as before, the thing is only to show brokers names) .
I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.
I have created a common table expression. The first query is meant to select a particular months worth of data. The second query is meant to select the following months data but only for the subscribers (MSISDN's) that appeared in the first month.
The query returns result for month 1 but ignores the second month. If I remove the last inner join to the CTE (INNER JOIN Test_Table tt ON tt.MSISDN = B.SUBSCRIBER_NUMBER) then I get a full list for both months but obviously this isn't limited to those MSISDN's in the first month.
The second issue that I'm having is that I can't select a sample of say only a 1000 MSISDN's from the first month...
WITH Test_Table (Report_Month, MSISDN, SUB_SEGMENT) AS ( Select R.Report_Month, R.MSISDN, DV.SUB_SEGMENT
