September 5, 2012 at 11:20 am
Hello Everyone.
I need your suggestions on the following.
I have a 3 Tables
1) Iss (IssID, subject, description created_on)
2) jou (jouID, IssID, user_id, created_on)
3) details (detailsID, jouID, key, old_value, new_value)
Iss
---
IssID subject desc created_on
1 abc NULL 2012-04-30
2 def NULL 2012-08-31
jou
---
JouID IssID user_id created_on
1 1 1 2012-05-18
2 1 8 2012-06-15
details
------
detailsID jouID key old_value new_value
1 1 status New Assign
2 2 status Assign Accept
We are planning to do a new table instead of all this.
Iss
---
IssID subject desc Accepted_date Assigned_Date
1 abc NULL 2012-06-15 2012-05-18
2 def NULL NULL NULL
Please suggest me a way to do this.
Thanks
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
September 5, 2012 at 11:33 am
Maybe something like this...
CREATE TABLE dbo.YourNewTable
(
lssID INT IDENTITY(1,1),
[subject] VARCHAR(50),
[desc] VARCHAR(50),
Accepted_date DATE,
Assigned_date DATE
)
But seriously, what is the reason for this? If you turn 3 tables into one, you are going to be backing off whatever level of normalization you currently have. Is this driven by a business need, or is it someones lack of understanding how to create views of the data when there are multiple tables with one to many and many to many relationships. (And I don't intend for that to sound condescending, because it's not meant to be.). Maybe if you tell us more of what's going on you would get some good suggestions.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 5, 2012 at 11:41 am
Greg Snidow (9/5/2012)
Maybe something like this...
CREATE TABLE dbo.YourNewTable
(
lssID INT IDENTITY(1,1),
[subject] VARCHAR(50),
[desc] VARCHAR(50),
Accepted_date DATE,
Assigned_date DATE
)
But seriously, what is the reason for this? If you turn 3 tables into one, you are going to be backing off whatever level of normalization you currently have. Is this driven by a business need, or is it someones lack of understanding how to create views of the data when there are multiple tables with one to many and many to many relationships. (And I don't intend for that to sound condescending, because it's not meant to be.). Maybe if you tell us more of what's going on you would get some good suggestions.
I am actually not looking for the create table, we are doing a datawarehouse design.. so normalization doesn't matter. I am looking for a sql code that will actually tell me, if I have my status changed the data gets updated if I run it.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
September 5, 2012 at 12:18 pm
I am looking for a sql code that will actually tell me, if I have my status changed the data gets updated if I run it.
now, i could be wrong but in case your version supports it change data capture seems to be a good way to go for starts,
September 5, 2012 at 12:20 pm
a4apple (9/5/2012)
I am looking for a sql code that will actually tell me, if I have my status changed the data gets updated if I run it.
I'm not sure what exactly what you are asking, can you be a little more descriptive? Also, see below for how to post DDL for relavent objects and sample data. If you do this for all three tables, and be very specific as to what you are looking for, I'm sure you will get an answer.
USE tempdb
GO
IF OBJECT_ID('tempdb..lss','u') IS NOT NULL
DROP TABLE tempdb..lss
GO
CREATE TABLE tempdb..lss
(
lssID INT,
[subject] VARCHAR(20),
[description] VARCHAR(20),
created_on DATE
)
GO
INSERT INTO tempdb..lss
VALUES
(1,'abc',NULL,'2012-04-30'),
(2,'def',NULL,'2012-08-31')
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 6, 2012 at 4:55 am
The following query should do it for you:
Select a.IssID, a.subject, a.dessc,
MAX(Case When c.new_value = 'Accept' Then b.created_on Else NULL End) As Accepted_Date,
MAX(Case When c.new_value = 'Assign' Then b.created_on Else NULL End) As Assign_Date
From Iss As a FULL OUTER JOIN Jou As b ON a.IssID = b.IssID
FULL OUTER JOIN Details As c ON b.JouID = c.jouID
Group By a.IssID, a.subject, a.dessc
Hope this is what you were looking for.
September 6, 2012 at 12:14 pm
CREATE TABLE Iss
(
id BIGINT,
name Varchar(50)
)
CREATE TABLE Jou
(
id BIGINT,
iss_id BIGINT,
created_on DATETIME
)
CREATE TABLE Jd
(
id BIGINT,
jou_id BIGINT,
VARCHAR(10),
old_value varchar(10),
new_value varchar(10)
)
CREATE TABLE
(
id BIGINT,
name VARCHAR(50)
)
INSERT INTO (id, name) values (1,'Phase')
INSERT INTO (id, name) values (2,'Type')
INSERT INTO (id, name) values (3,'key3')
INSERT INTO Iss(id, name) values (1, 'abcd')
INSERT INTO Iss(id, name) values (1, 'efgh')
INSERT INTO jou(id, iss_id, created_on) VALUES (1, 1, 2012-08-01)
INSERT INTO jou(id, iss_id, created_on) VALUES (2, 1, 2012-08-15)
INSERT INTO jou(id, iss_id, created_on) VALUES (3, 1, 2012-08-26)
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (1,1, 1, 'status', NULL, 'Assign')
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (2,1, 1, 'assigned', NULL, '20') --> 20 is the user( dats a different table)
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (3,2, 1, '1', 'QA1', 'QA2')
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (4,2, 1, '2', 'Small', 'Big')
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (5,3,1, 'Assign', 'Complete')
My result set should look like this
issidPhase Accepted_on user
1Complete2012-08-26 20
Please let me know thanks..
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
September 6, 2012 at 12:15 pm
vinu512 (9/6/2012)
The following query should do it for you:
Select a.IssID, a.subject, a.dessc,
MAX(Case When c.new_value = 'Accept' Then b.created_on Else NULL End) As Accepted_Date,
MAX(Case When c.new_value = 'Assign' Then b.created_on Else NULL End) As Assign_Date
From Iss As a FULL OUTER JOIN Jou As b ON a.IssID = b.IssID
FULL OUTER JOIN Details As c ON b.JouID = c.jouID
Group By a.IssID, a.subject, a.dessc
Hope this is what you were looking for.
almost close, please see my requirement below.. I now gave the DDL and DML also..
Thank you
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
September 6, 2012 at 12:40 pm
a4apple (9/6/2012)
CREATE TABLE Iss(
id BIGINT,
name Varchar(50)
)
CREATE TABLE Jou
(
id BIGINT,
iss_id BIGINT,
created_on DATETIME
)
CREATE TABLE Jd
(
id BIGINT,
jou_id BIGINT,
VARCHAR(10),
old_value varchar(10),
new_value varchar(10)
)
CREATE TABLE
(
id BIGINT,
name BIGINT
)
INSERT INTO (id, name) values (1,'Phase')
INSERT INTO (id, name) values (2,'Type')
INSERT INTO (id, name) values (3,'key3')
INSERT INTO Iss(id, name) values (1, 'abcd')
INSERT INTO Iss(id, name) values (1, 'efgh')
INSERT INTO jou(id, iss_id, created_on) VALUES (1, 1, GETDATE()-5)
INSERT INTO jou(id, iss_id, created_on) VALUES (2, 1, GETDATE()-3)
INSERT INTO jou(id, iss_id, created_on) VALUES (3, 1, GETDATE()-1)
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (1, 1, 'status', NULL, 'Assign')
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (1, 1, 'assigned', NULL, '20')
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (2, 1, '1', 'QA1', 'QA2')
INSERT INTO Jd(id, jou_id, , old_value, new_value) Values (2, 1, '2', 'Small', 'Big')
My result set should look like this
issidPhaseAccepted_onuser
1Complete2012-09-05 11:13:32.39720
Please let me know thanks..
Your ddl and dml don't match. Your table Key (horrible name but that is another topic) has two columns, both BigInt. Then you insert an int and a string. If you change that definition to be varchar(10) your script will work.
However I can't begin to understand how you get your output based on the data in these tables. It seems you need provide a LOT more detailed information about what you are trying to do. Should all the data in these tables be transformed into that single row you said you want as output?
_______________________________________________________________
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/
September 6, 2012 at 2:59 pm
Sean Lange (9/6/2012)
[I can't begin to understand how you get your output based on the data in these tables. It seems you need provide a LOT more detailed information about what you are trying to do. Should all the data in these tables be transformed into that single row you said you want as output?
I can buy the fact that we are not going to produce the same dates, since the values in table jou are dependent on when we run it. However, I agree, I don't see how we can get anything having a status of complete. Unless, of course, we use the crystal ball to infer, for example, that id 1's having a 'status' key value going from NULL to 'Assign', and the 'assigned' key changing from NULL to 20 means it is complete. Maybe it is complete from the perspective of the person whose sole job it is to make sure all tasks are assigned to someone, then he's done. I suspect what is missing from the desired output is a column for the key value. In that case, I would probably make the assumptions the keys mentioned above are complete.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 6, 2012 at 4:38 pm
Greg Snidow (9/6/2012)
Sean Lange (9/6/2012)
[I can't begin to understand how you get your output based on the data in these tables. It seems you need provide a LOT more detailed information about what you are trying to do. Should all the data in these tables be transformed into that single row you said you want as output?I can buy the fact that we are not going to produce the same dates, since the values in table jou are dependent on when we run it. However, I agree, I don't see how we can get anything having a status of complete. Unless, of course, we use the crystal ball to infer, for example, that id 1's having a 'status' key value going from NULL to 'Assign', and the 'assigned' key changing from NULL to 20 means it is complete. Maybe it is complete from the perspective of the person whose sole job it is to make sure all tasks are assigned to someone, then he's done. I suspect what is missing from the desired output is a column for the key value. In that case, I would probably make the assumptions the keys mentioned above are complete.
Hi,
I have edited the post, can you please see now and suggest me a solution?
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
September 6, 2012 at 6:40 pm
a4apple (9/6/2012)
Hi, I have edited the post, can you please see now and suggest me a solution?
Not quite there yet. Your values list includes 6 values into table jd, which only has 5 columns by your DDL. I am assuming the third integer value in the values lists is supposed to be iss_id? And maybe you forgot to include that column in the table DDL?
It would be very helpful if you would explain how the data get populated in the tables, and what the relationship between them is. It seems fairly straight forward if we make some assumptions, but if you want a solid solution, we should not be making any assumptions. We all want to help, but you can help us to help you by giving more info.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 7, 2012 at 5:57 am
CELKO (9/6/2012)
And you need a number that is greater than the number of electrons in the universe!
That's a fairly unprovable assertion in my opinion, as the known substance of the universe is constantly changing.
http://www.huffingtonpost.com/2010/12/01/number-of-stars-in-universe_n_790563.html
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 7, 2012 at 7:12 am
Here is my take on what you need to do. You should read the article at the first link in my signature, the one about best practices. Keep in mind that we are all volunteers, we can't see your screen and we have no knowledge of your project. Once you have put together the scripts you think will work, test them out and make sure they actually will work. Then you need to provide a clear description of what you want for output.
We are now into the second day and nearly 20 posts trying to work on your problem and sadly we can't even put together a table with data yet to start to work on your issue.
_______________________________________________________________
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 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply