SQL Server 2012 :: Joining 2 Selects Where A Value Not In Values?
Aug 12, 2014
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.
OrderID ControlName 1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table)) 1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table)) 1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table)) 2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table)) 2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))
Child2 Table
PackageID Product Color1 Color2 Color3 1 Pant Red Green Blue 1 Shirt Blue Pink Purple 1 Gown Blue Black Yellow 1 T Shirt Red Green White 2 Tie Red Green White 2 Socks Red Green White 2 Bow Red Green White
We want to have result like
OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow
1 1 ABC Red Blue White x x Blue 2 2 Bcd x x x Green Red
I have tried
;with mycte as ( select ms.OrderID,ms.PackageID ,ms.CustomerName , Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),''),'Row','') rowNum ,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum From child1 c inner join MasterTable ms on c.Orderid=ms.orderid)
[code]....
it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record
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.
I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.
p.item=i.item where 1001099548=1001099550 84162359=84198545 84532300=84532293 47547523=47547951 305545A3=87433653 87444977=87444975
left side coming from p.item = right side coming from c.item.
Here are my statements.
--#1 OPEN PO's SELECT p.item ,(p.qty_ordered-p.qty_received) as POQtyRemaining ,i.item ,i.qty_on_hand ,p.po_num
I am editing a pre-existing view.This view is already bringing data from 40+ tables so I am to modify itwithout screwing with anything else that is already in there.I need to (left) join it with a new table that lists deposits and thedates they are due. What I need is to print, for each record in theview, the due date for the next deposit due and the total of allpayments that they will have made by the next due date.So this is how things are. I join the table and it obviously bringsmultiple records for each record (one for each matching one in the newtable). I need, instead, to be able to make out what due date I shouldprint (the first one that is GETDATE()?) and the total of deposits upto that date.Now, payments can be either dollar amounts or percentages of anotheramount in the view. So if it's an amount I add it, if it's a % Icalculate the amount and add it.Example:for group X of clients...Deposit 1 due on oct 1: $20Deposit 2 due on oct 15: $30Deposit 3 due on nov 15: $40Deposit 4 due on nov 30: $50for group Y of clients...Deposit 1 due on Oct 30: $200Deposit 2 due on Nov 30: $300Deposit 3 due on Dec 30: $400So when if I execute the view today (Nov 7th) each client from group Xshould have:Next Due Date: nov 15. Total: $90 (deposit 1 + deposit 2 + deposit 3)Group Y should have:Next Due Date: Nov 30, total: $500 (Deposit 1 + deposit 2)And so on.
I have a table (can't change the schema of it since it is part of an off the shelf app ) that has columns for individuals which I need to extract several pieces of information, essentially Phone, Email Address, etc. See U1 - U6
What is a better way to return this information rather than multiple joins?
I have a object variable named "UserList" which gets populated through execute sql task - stored procedure.I wish to concatenate all the values in "UserList" using ssis script task - in lang vb.Output should be like (User1,User2,User3,....)
I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.
I think I got all my create table statements are correct.
I need to Find the number of agents for each supplier that has at least one agent. The result should be tuples of the form (sid, sName, number of agents)
-Select Sid, sName, count(Aid) from Agent A join Supplier S on (S.Sid = A.Sid) group by S.Sid, S.sName, Aid; But it gives me this error: no such column: A.Sid
Im thinking I might have a problem with my create table statement and/or primary key statements?
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.
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB; GO SET NOCOUNT ON; IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
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.
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