SQL Server 2012 :: Get Unmatched And Matched Values From Two Tables
Apr 10, 2015
I have two tables category and lu_category.
The category table has columns [CategoryId], [CategoryName], [TotalCategoryRiskScore] and the lu_category has columns [CategoryId], [CategoryName].
I want a sql query that will list all values from lu_category table and category table and if a categoryid is not available in lu_category table but available in category table,
i need that too in the result.
Below is the screenshot of the data and my desired output
If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101) then output should come as 'Mobile/c' (this is for f.parent_id=101)
If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id
Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache
I have a table called employee which identifies each employee by the field 'empId'. In this table there are 2 fields called 'Fname' and 'Lname' - I have another table called 'Training' that I imported from Lotus Notes that holds all the training taken by the employees.
Unfortunately, the only identifying field that imported is called 'Name' and Just contains the First Name, a space and the Surname
Joe Smith Susan Jones
I am at a loss to find a way of joining these tables. I need to put the field 'empId' into the Training Table. Is this possible and what about the case where there may be more than 1 Susan Jones?
Please, any help or suggeestions would be greatly appreciated.
I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this
Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.
And this query produces wrong results, its giving me match and also non match records.
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.
SELECT nvl(field1, 0), field2, field3, nvl(field4, 0), nvl(field5, 0), nvl(field6, 0) FROM TBL1 ------------------------------------------- Result: Record count 3000.
Only field3 is populated everything else is null.
select field3 from tbl1 group by field2 - record count = 48
I am having problems joining these two tables and returning the correct values. The issue is that i have a work order table and a revenue table. I only want to return the sum of the revenue when the revenue comes after the work order date. That is simple enough, but it gets tricky when there are multiple work orders for the same ID. for those instances, we only want the sum of the revenue if it shows up post the work order date and if it is before any other work order date. So ID 312187014 should only have the 9-5 revenue from below, not the 7/7 or 8/6 revenue because the 8/7 work order date is after those revenue dates and thus will not have any revenue tied to it because there is a 9/3 work order that ties to the 9/5 revenue. Additionally the 412100368 ID has a 7/7 work order that ties to the 7/26 revenue, and the 8/7 work order will tie to the 8/23 and 9/20 revenue
--===== Create the test table with
CREATE TABLE #workorder ( Id varchar(20), wo varchar(10), wodate datetime, amount float ) GO CREATE TABLE #revenue
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
Hi! I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error
Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.
For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
order by PackTypeWhat I need is if the PackType is Deposit and need to multiply the number of records by 35, If the PackType is Monthly I need to multiply the records by 25 and if the PackType is Single I need to sum the values in the price column.
I am very new to MsSQL having only used Access before. I am trying to find records in the second select statement that aren't in the first select statement. Using the
Have been working on it for 15 hours now and still can't find a way to do it. Thanxs in advance
@_MemberIdint
AS BEGIN SET NOCOUNT ON; Begin SELECT dbo.Tbl_01035_Tour_Players.TourTypeId, dbo.Tbl_01035_Tour_Players.MemberId, dbo.Tbl_01030_Tour_Schedule.Sch_TourId, dbo.Tbl_01030_Tour_Schedule.Sch_TourStartDate, dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate, dbo.Tbl_01030_Tour_Schedule.Sch_TourGame FROM dbo.Tbl_01035_Tour_Players INNER JOIN dbo.Tbl_01030_Tour_Schedule ON dbo.Tbl_01035_Tour_Players.TourId = dbo.Tbl_01030_Tour_Schedule.Sch_TourId WHERE (dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate >= GETDATE()) and dbo.Tbl_01035_Tour_Players.MemberId = @_MemberId End
Begin SELECT Tbl_01020_Tour_Types.TourTypeId, Tbl_01020_Tour_Types.TourGame, Tbl_01020_Tour_Types.TourDescr, Tbl_01020_Tour_Types.TourDaysDur, Tbl_01020_Tour_Types.TourMinPot FROM Tbl_01020_Tour_Types CROSS JOIN Tbl_01035_Tour_Players WHERE (NOT (Tbl_01020_Tour_Types.TourTypeId = db.Tbl_01035_Tour_Players.TourTypeID)) End
I have two tables:TableA and TableB, both of the two tables have two fields: c_IDA char(10) and c_IDB char(10); A import text file includes the ID data, the data will insert into TableB only when the ID existed in TableA The line in the import text file like this: 00000023450000012345
in the text file: 1-10 is the c_IDA and 11-20 is the c_IDB
In the Derived Column transformation, set the column name IDA as expression: SUBSTRING(LINE,1,10) set the column name IDB as expression: SUBSTRING(LINE,11,10)
In the followed Lookup transfornation, I created the reference table with a sql: Select c_IDA,c_IDB from TableA, the IDA and IDB in the pipeline linked to the reference table's c_IDA and c_IDB, i then setup the error output to another log file.
The problem is even though the ID existed in the TableA, the Lookup always generate the error out put, that means the ID not been found in the TableA at all.
In the sample above, if i run the sql in the SSMS: Select * from TableA where c_IDA = '0000002345' and c_IDB = '0000012345'
I have the following code and trouble reading values of Bank Accounts. If i remove the line it says "xmlns="http://applications.apch1.com/webservice/schema/" then i my query is working. But i cant remove this becasue that is what i will get response from a web service. All the records are stored in the database with this line included.
DECLARE @MyXML XML SET @MyXML = '<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <return xmlns="http://applications.apch1.com/webservice/schema/"> <CustomerID> A8339 </CustomerID> <BankAccounts>
I have an existing function and need to alter function to give result of the values multipiled until its parent is reached.need two seperate functions for city and amt columns..need to also display the parent-description
I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.
Here's some test data and code which shows the principle:
CREATE TABLE #t ( ref INT , id INT , who VARCHAR(10) , checkin DATE
[Code] ....
The result set is:
ref 1 who1 2 who2 3 who3 4 who4 123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol 456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL
Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?
I have two tables, a dates table and a values table. They are joined on the date column.The date table has a range, say from today as far as 20 days from now, incrementing by 1 day each row.The values table may have a row for a day, and may not. If the day has a value I want to display that value.If the day does not have a value in the values table I want to display the last known value.
I think this can be done with windowing functions in a set based manner but have not been able to work it out. I have done it procedurally but im not happy with that at all, and really want to see if this is possible in a set based manner.Below is some simplified code to allow testing with sample data.
create table DimDate ( DateCol date ) create table TotalsData ( DateCol date
SELECT * INTO TEMP FROM (SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X
I need to extract the date that the value in CODE column changes to another code for each value of CATEGORY and if there is no change, to record the original CODE value and its startdate for each CATEGORY.
I have 2 queries where I select all accounts with bill code in 'bmonit' example ('12','39','124','1FA') then I also have a Select where the same account might have additional services, that are not in example ('12','39','124','1FA') and for these accounts I need to just put a 'Y' if stop_date is null.
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd) SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 1 ELSE 0 END AS isFirstVisit, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 0 ELSE 1 END AS isRepeatVisit FROM#Visits
The following t-sql 2012 works fine in sql management studio. However when I place it in a .net 2010 web form application, I am told the sql does not work when the parameter values are null. Thus can you tell me what I can change in the sql below that will accept null as 3 possible input values?
I am trying to reconcile two different databases. Each database holds customer information and some of this information is represented as drop down tables. Here is how it is linked
Attribute name Attribute Values Customer Color 8, 0 8, 1, 1234, 6 892, 14, 1234, 6, John, Doe red 8, 1 8, 2, 1234, 6 blue 8,2
Database 2 Sample
Attribute name Attribute Values Customer Color 5, 0 5, 3, 1234, 6 892, 14, 7434, 5, John, Doe red 5, 3 5, 4, 1234, 6 blue 5,4
Within a database, the ATTID and Attcode link the attribute and attribute value tables and they link to the Customer table with CustID and Cust Code. One Customer may have multiple entries within the Attribute value table
The Customer tables in each database are linked by the DBID and DBCode values. The other ID's are not necessarily the same across databases.
The object is to compile a list of Customers where these attribute values vary between databases. There are approximately 56,000 Customers in the customer table and 710,000 entries in the attribute values table.
My attempts to do this have produced cross joins with millions of values returned
I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.
SELECT 89 AS Val, 100 AS RoundVal UNION ALL SELECT 329, 1000 UNION ALL SELECT 6329, 10000 UNION ALL SELECT 43299, 100000 UNION ALL SELECT 155329, 1000000
I have a report that I am running in visual studio 2010, that gets its data from a few different stored procedures in a SQL 2012 Database. The variables are Date and Office Code. We currently have 6 different office codes. One of the Stored Procedures gets call information for each office. With the report we can select any single office or any combination of offices to compile data.
The problem I have is two of these share phone information, so when you select either one or both of them the same data gets returned. So for example we have office codes of AAAAA, BBBBB, CCCCC, DDDDD, EEEEE, and FFFFF. Now AAAAA, and BBBBB share phone information so if you select office code AAAAA, the phone info that is returned is for AAAAA, and BBBBB, and visa versa. So I am not sure how to accomplish that either in the report or in the stored procedure.
I have a set of data in which i have a product number going through 6 stages and each stage has a date. Since the each stages are in columns, I have created a unpivot query to transpose the columns into rows.
The unpivot query is working fine however I am getting duplicate values in the result. For each productnumber there must be only 6 results however i am getting 24 rows for each product number due to duplication.
I have attached the code and the source data for reference
SET NOCOUNT ON; DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10)) INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8' SELECT * FROM @items
-- table with categories
SET NOCOUNT ON; DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10)) INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE' SELECT * FROM @categories
--table where categories are assigned to master categories
SET NOCOUNT ON; DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT) INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107 SELECT * FROM @master_categories
-- items-categories assignment table
SET NOCOUNT ON; DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT) INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11
[code]....
So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.In the above example it would be only the ITEM 1 (id=10) that is returned:
1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally
2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally
3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1
3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2
CREATE TABLE A (ID INT IDENTITY (1,1)) CREATE TABLE B (ID INT, EMPID VARCHAR(10)) INSERT INTO A DEFAULT VALUES GO 5 INSERT INTO B VALUES (1,'E23') INSERT INTO B VALUES (1,'E24') INSERT INTO B VALUES (2,'E23')
from the above code i would like to get output like
Question in review today is Creating a Report showing the FName, LName of all Employees not Specified in a region; I would assume "No Value to be Null" Correct?
Or is there another way for me to do this?
This is what I have so far...What am I missing that this is not showing me results?
Select Firstname, Lastname, Region From Employees WHERE Region LIKE 'null%';
I have two tables I am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one: InstitutionID, InstName ------------------------ 1 School Alpha 2 School Beta 3 School Charlie 4 School Delta
Table two: InstitutionOversightID, InstitutionID, Type ------------------------------------------------ 1 1 Accreditation 2 1 Verifcation 3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList ----------------------------------------------- 1 School Alpha Accreditation, Verification, Old System 2 School Beta null 3 School Charlie null 4 School Delta null
