March 27, 2014 at 9:21 am
I have 2 related tables Case c and Subcase s (c.Case ID = s.Case ID: Case columns: CaseID Resolution
Subcase Columns: Case ID Subcase ID SerialNumber
Some CaseIDs do not have subcases. How to add a dummy values based on the resolution:
If resolution = Void than SubcaseID = 'Void'
if resolution = onsite than Subcase ID = 'onsite'
if resolution = parts than SubcaseID = 'parts'
March 27, 2014 at 9:27 am
Golden_eye (3/27/2014)
I have 2 related tables Case c and Subcase s (c.Case ID = s.Case ID: Case columns: CaseID ResolutionSubcase Columns: Case ID Subcase ID SerialNumber
Some CaseIDs do not have subcases. How to add a dummy values based on the resolution:
If resolution = Void than SubcaseID = 'Void'
if resolution = onsite than Subcase ID = 'onsite'
if resolution = parts than SubcaseID = 'parts'
Not totally sure what you mean here. Are you wanting to create rows in the SubCase table when a Case has no SubCase? Is this permanent or during a query? The questions can go on and on here because you didn't provide much information.
Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 9:29 am
Correct, I want to create rows in the SubCase table when a Case has no SubCase. This is permanent, not during a query.
March 27, 2014 at 9:32 am
Golden_eye (3/27/2014)
Correct, I want to create rows in the SubCase table when a Case has no SubCase. This is permanent, not during a query.
Then you will need to insert these rows.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 9:37 am
Sean Lange (3/27/2014)
Golden_eye (3/27/2014)
Correct, I want to create rows in the SubCase table when a Case has no SubCase. This is permanent, not during a query.Then you will need to insert these rows.
Yes, I know. My question is how? 🙂
March 27, 2014 at 9:39 am
Golden_eye (3/27/2014)
Sean Lange (3/27/2014)
Golden_eye (3/27/2014)
Correct, I want to create rows in the SubCase table when a Case has no SubCase. This is permanent, not during a query.Then you will need to insert these rows.
Yes, I know. My question is how? 🙂
Find Case rows that have no SubCase. I can't provide any kind of detail here because you didn't give me anything to work with. I suggested previously that you should read the first article in my signature.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 9:54 am
I think you're looking for LEFT OUTER JOIN, something like this:
SELECT
c.CaseID, c.Resolution,
COALESCE(CAST(s.CaseID AS varchar(10)), 'DUMMY-' + c.Resolution) AS SubCaseID
--, s.SerialNumber
FROM [Case] c
LEFT OUTER JOIN Subcase s ON
s.CaseID = c.CaseID
Edits: Added CAST() of "s.CaseID" and "'DUMMY' + " based on later post of OP.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 27, 2014 at 10:47 am
Here the scripts:
Create table tmp.[Case]
(
CaseID varchar (50),
Resolution varchar(50),
);
INSERT INTO tmp.[Case] (CaseID, Resolution)
VALUES ('4647018364','Void')--no subcase available
,('4647018265','Parts')--no subcase available
,('4647018368', 'Onsite')--no subcase available
,('4647018630', 'Transferred')
,('4715206787', 'Admin');
Create table tmp.[Subcase]
(
CaseID varchar (50),
SubcaseID varchar (50),
Activity varchar (50),
Summary varchar(50),
);
INSERT INTO tmp.[Subcase] (CaseID,SubcaseID, Activity, Summary)
VALUES ('4647018630', '4647018630-461','K2','Case was transfered to the partner query')
,('4715206787','4715206787-461','N4','Administrative solution found');
These are the initial 2 tables with foreign key Case ID.
I am updating these 2 tables weekly with a script from other 2 source tables and the condition for updating Subcase is
Update.........
......
from
gcss.Subcase S
,GCSS.[Case] c
where
s.Case_ID = c.Case_ID;
At the end I would like to have in Subcase table:
CaseIDSubcaseIDActivitySummary
46470186304647018630-461K2Case was transfered to the partner query
47152067874715206787-461N4Administrative solution found
4647018364 'DUMMY-VOID' 'N/A' 'N/A'
4647018265 'DUMMY-PARTS' 'N/A' 'N/A'
4647018368 'DUMMY-ONSITE' 'N/A' 'N/A'
I hope I explained it well :hehe:
March 27, 2014 at 12:10 pm
Yeah this is pretty much exactly what Scott already posted.
I changed the name Case to MyCase because I hate working with reserved words and typing brackets around table names.
insert SubCase
select c.CaseID, 'DUMMY-' + UPPER(c.Resolution), 'N/A', 'N/A'
from MyCase c
left join SubCase sc on c.caseID = sc.CaseID
where sc.CaseID is null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 1:26 am
Sean Lange (3/27/2014)
Yeah this is pretty much exactly what Scott already posted.I changed the name Case to MyCase because I hate working with reserved words and typing brackets around table names.
insert SubCase
select c.CaseID, 'DUMMY-' + UPPER(c.Resolution), 'N/A', 'N/A'
from MyCase c
left join SubCase sc on c.caseID = sc.CaseID
where sc.CaseID is null
Hi Champion,
Your proposal would not work, becauseof
left join SubCase sc on c.caseID = sc.CaseID
where sc.CaseID is null
this is simply excluding everthing.
March 28, 2014 at 7:22 am
Golden_eye (3/28/2014)
Sean Lange (3/27/2014)
Yeah this is pretty much exactly what Scott already posted.I changed the name Case to MyCase because I hate working with reserved words and typing brackets around table names.
insert SubCase
select c.CaseID, 'DUMMY-' + UPPER(c.Resolution), 'N/A', 'N/A'
from MyCase c
left join SubCase sc on c.caseID = sc.CaseID
where sc.CaseID is null
Hi Champion,
Your proposal would not work, becauseof
left join SubCase sc on c.caseID = sc.CaseID
where sc.CaseID is null
this is simply excluding everthing.
Huh? What do you mean it is excluding everything?
You said you want to insert rows into SubCase for any row in Case where there is no row in SubCase. That is exactly what the code I posted does.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply