September 10, 2012 at 7:05 pm
CREATE TABLE Issue
(
id int not null identity(1,1) primary key,
name varchar(10)
)
CREATE TABLE Jrn
(
id int not null identity(1,1) Primary Key,
issue_id int,
created_dt datetime
)
ALTER TABLE jrn
ADD Foreign Key (issue_id) References Issue(id)
CREATE TABLE details
(
id int not null identity(1,1) primary key,
jrn_id int,
prop_key varchar(10),
old varchar(10),
new varchar(10)
)
ALTER TABLE details
ADD Foreign Key (jrn_id) References jrn(id)
Create table Status
(
id int not null identity(1,1) primary key,
name varchar(20)
)
Create table users
(
id int not null identity(1,1) primary key,
name varchar(20)
)
INSERT INTO issue(name) values('Issue1')
INSERT INTO issue(name) values('Issue2')
INSERT INTO issue(name) values('Issue3')
Insert into jrn(issue_id, created_dt) values(1, '04/01/2012')
Insert into jrn(issue_id, created_dt) values(1, '04/22/2012')
Insert into jrn(issue_id, created_dt) values(1, '04/28/2012')
Insert into jrn(issue_id, created_dt) values(2, '04/01/2012')
Insert into jrn(issue_id, created_dt) values(2, '04/23/2012')
insert into status(name) values('New')
insert into status(name) values('Assign')
insert into status(name) values('Inprogress')
insert into status(name) values('Complete')
Insert into users(name) values('Alex')
Insert into users(name) values('Sophia')
Insert into details(jrn_id, prop_key, old, new) values(1, 'status_id', 1, 2)
Insert into details(jrn_id, prop_key, old, new) values(2, 'user_id', NULL, 2)
Insert into details(jrn_id, prop_key, old, new) values(2, 'status_id', 2, 3)
Insert into details(jrn_id, prop_key, old, new) values(3, 'status_id', 3, 4)
Insert into details(jrn_id, prop_key, old, new) values(4, 'status_id', 1, 2)
Insert into details(jrn_id, prop_key, old, new) values(5, 'status_id', 2, 3)
This is my table structure and I have data like this.
MY Final Result Should look like this
SELECT 1 as IssueID, 'Complete' as 'Status', '2012-04-01' as Created_date,'2012-04-01' as Assigned_Date, '2012-04-22' as InprogressDate, '2012-04-28' as 'Completed Date', 'Sophia' as [User]
UNION
SELECT 2, 'Inprogress', '2012-04-01', '2012-04-01', '2012-04-23', NULL, NULL
Thanks in advance
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
September 10, 2012 at 7:44 pm
The test data setup you provided is great. Could you also describe what you want done with it? The result set doesn' give many hints as to what you're trying to do. Thanks
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2012 at 8:13 pm
Jeff Moden (9/10/2012)
The test data setup you provided is great. Could you also describe what you want done with it? The result set doesn' give many hints as to what you're trying to do. Thanks
Jeff, I think this post is the same as this one... http://www.sqlservercentral.com/Forums/Topic1354775-392-1.aspx
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 10, 2012 at 11:06 pm
Jeff Moden (9/10/2012)
The test data setup you provided is great. Could you also describe what you want done with it? The result set doesn' give many hints as to what you're trying to do. Thanks
Hi Jeff,
By using my data in these tables, I want to create a final destination table that would look like the Select Statement I have used at the end.
Basically it should capture the changes in data based on the details table but the dates come from created_dt field from Jrn Table based on status change. which means Accepted Date column should be populated only if the details table has an entry for that change otherwise NULL should be populated.
I hope you got my question.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
September 11, 2012 at 4:01 am
CELKO (9/10/2012)
We do not use IDENTITY for a key in RDBMS. Why do you think that the count of physical insertion attempts to one disk on one machine is an attribute of an entity in a valid data model? You ave a magical Kabbalah number called “id” that can be an issue, a squid, Lady Gaga, etc. That is magic and not RDBMS.
Most of experts here do use IDENTITY for a primary keys in SQL Server database where it's appropriate.
Mr. Celko doesn't, but it entirely his personal problem. It does look like he is afraid of Lady G and have some issues with Kabbalah magics :hehe:
Why do the data elements change names from table to table? Status is not an entity; it is an attribute property, such as “martial_status” “employment_status”, etc. An SQL programmer would have a column something lie this ...
It depends. Sometime it's good enough to have it as attribute. Sometimes you want to denormalize it into own entity.
I use the following approach when making a decision for status:
For simple cases like 2-4 different, pretty constant statuses (usually having more technical meaning than business, but still required some text description) I use simple table attribute with CHECK. Examples:
Record status (technical) : Open, Closed
Progress status: Not-started, Started, Completed, Failed
For cases which require more flexibility, eg. the probability of status description changed or new state added, I will have an entity:
Marital status: Single, Married, Divorced, Widowed (new statuses added recently: Partnership, Friendship, Life-enemies)
Job status: Employed, Not-employed, Self-Employed. - Here business may want to add some more statuses any time eg. Split Employed to Employed Full-time and Employed Part-time, add Temporary Employed, Never want to be Employed, Not-employable.:hehe:
September 11, 2012 at 12:01 pm
Hi, I need to get status names as columns and their respective dates as rows. I know its pivoting, but asking you guys for more insight.
Thanks
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
September 11, 2012 at 1:02 pm
a4apple (9/11/2012)
Hi, I need to get status names as columns and their respective dates as rows. I know its pivoting, but asking you guys for more insight.Thanks
I think you need to do a little more than that, namely fully understand the data. For example, your expected output includes a column for 'Created_date', yet you do not provide a status of 'created'. Are you going to make an inference on this value based on it's existing in a table somewhere? Also, what happens if a user_id changes? Anyhow, this will give you your desired output, with the exception of the created_date, as I'll leave that for you to decide how you want to handle. I can think of about 10 ways this will fail depending on your real data, but it should be at least enough to get you started.
WITH Pvt AS
(
SELECT
issue_id,[created] ,[assign],[Inprogress],[Complete]
FROM
(
SELECT
a.issue_id,
a.created_dt,
f.name AS status_new
FROM jrn a INNER JOIN details b
ON a.id = b.jrn_id
CROSS APPLY
(
SELECT name FROM status f
WHERE id = b.new AND b.prop_key = 'status_id'
) f
) t1
PIVOT
(
MIN(created_dt) FOR status_new IN ([created],[assign],[Inprogress],[Complete])
) AS pvt
)
SELECT
a.issue_id,
c.name AS [Status],
a.created AS Created_dt,
a.assign AS Assign_dt,
a.inprogress AS Inprogress_dt,
a.Complete AS Complete_dt,
b.
FROM PVT a LEFT OUTER JOIN
(
SELECT
a.issue_id,
c.name AS
FROM jrn a INNER JOIN details b
ON b.jrn_id = a.id INNER JOIN users c
ON b.new = c.id
WHERE b.prop_key = 'user_id'
) b
ON a.issue_id = b.issue_id LEFT OUTER JOIN
(
SELECT
a.ISSUE_ID,
b.name
FROM
(
SELECT
a.issue_id,
MAX(b.new) AS status_id
FROM jrn a INNER JOIN details b
ON b.jrn_id = a.id
GROUP BY a.issue_id
) a INNER JOIN status b
ON a.status_id = b.id
) c
ON a.issue_id = c.issue_id
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 11, 2012 at 1:07 pm
Also...have...to...(fighting it)...agree...with...Celko. The 'id' column in every table was a bit annoying to work with. If you have any ability to provide some input with respect to column names, you might want to give more descriptive names for the id columns.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 11, 2012 at 3:21 pm
Ok, I had some free time so I messed with this some more. This one eliminates one of the joins to the pivot table to get the current status. Again, caveats abound here. For example, does a 'complete' status *always* have a higher id value than 'in progress'? Does the status ever revert back to a previous value? Perhaps if you try it, identify any errors, and submit some sample data that re-creates those errors, we would be better able to help you with it.
WITH Pvt AS
(
SELECT
issue_id,[Created],[Assign],[Inprogress],[Complete],[User]
FROM
(-- This part creates an easily PIVOTable table
-- the only part missing is the status which you
-- still have to do a join to get.
SELECT
a.ISSUE_ID,
ISNULL(f.name,'User') AS report_col,
CASE WHEN b.prop_key = 'status_id' THEN CONVERT(VARCHAR,a.CREATED_DT,101)
WHEN b.prop_key = 'user_id' THEN e.name
ELSE NULL
END AS report_val
FROM jrn a INNER JOIN details b
ON a.id = b.jrn_id
OUTER APPLY
(
SELECT name FROM users e
WHERE id = b.new AND b.prop_key = 'user_id'
) e
OUTER APPLY
(
SELECT name FROM status f
WHERE id = b.new AND b.prop_key = 'status_id'
) f
) t1
PIVOT
(
MIN(report_val) FOR report_col IN ([created],[assign],[Inprogress],[Complete],[User])
) AS pvt
)
SELECT
a.issue_id,
b.name AS [Status],
a.created AS Created_dt,
a.assign AS Assign_dt,
a.inprogress AS Inprogress_dt,
a.Complete AS Complete_dt,
a.
FROM PVT a LEFT OUTER JOIN
(
SELECT
a.ISSUE_ID,
b.name
FROM
(
SELECT
a.issue_id,
MAX(b.new) AS status_id
FROM jrn a INNER JOIN details b
ON b.jrn_id = a.id
GROUP BY a.issue_id
) a INNER JOIN status b
ON a.status_id = b.id
) b
ON a.issue_id = b.issue_id
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 12, 2012 at 9:18 am
Greg Snidow (9/11/2012)
The 'id' column in every table was a bit annoying to work with.
It's even more annoying when you need it and it's not there. 😉
Don't overlook the serious performance advantages for having an "ID" column in virtually every table can have. The biggest advantage is that make the perfect choice for clustered indexes especially for large tables that take a lot of inputs on a regular basis. "ID" columns meet all of the criteria for good clustered indexes... Unique, Narrow, and "ever increasinng". You also have to remember that the clustered index, whatever it is, becomes an integral part of every non-clustered index you end up with whether you want it to be or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2012 at 9:24 am
I agree with your reasons Jeff, but I don't see any reason why are there only id columns instead of issue_id, jrn_id, details_id, etc.
September 12, 2012 at 9:28 am
Jeff Moden (9/12/2012)
Greg Snidow (9/11/2012)
The 'id' column in every table was a bit annoying to work with.It's even more annoying when you need it and it's not there. 😉
Don't overlook the serious performance advantages for having an "ID" column in virtually every table can have. The biggest advantage is that make the perfect choice for clustered indexes especially for large tables that take a lot of inputs on a regular basis. "ID" columns meet all of the criteria for good clustered indexes... Unique, Narrow, and "ever increasinng". You also have to remember that the clustered index, whatever it is, becomes an integral part of every non-clustered index you end up with whether you want it to be or not.
Oh, I could not agree with you more, Jeff. All I meant was that it is confusing when every table has a column named 'id', as opposed to 'table_name_id', or something to that effect. I have one in all of my tables. I guess it's just what one is used to seeing.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 12, 2012 at 9:49 am
Greg Snidow (9/12/2012)
Jeff Moden (9/12/2012)
Greg Snidow (9/11/2012)
The 'id' column in every table was a bit annoying to work with.It's even more annoying when you need it and it's not there. 😉
Don't overlook the serious performance advantages for having an "ID" column in virtually every table can have. The biggest advantage is that make the perfect choice for clustered indexes especially for large tables that take a lot of inputs on a regular basis. "ID" columns meet all of the criteria for good clustered indexes... Unique, Narrow, and "ever increasinng". You also have to remember that the clustered index, whatever it is, becomes an integral part of every non-clustered index you end up with whether you want it to be or not.
Oh, I could not agree with you more, Jeff. All I meant was that it is confusing when every table has a column named 'id', as opposed to 'table_name_id', or something to that effect. I have one in all of my tables. I guess it's just what one is used to seeing.
I guess it's a pure misunderstanding between peers. Jeff, same as myself and many others, defends using IDENTITY "ID" column for PK/Clustered Indexes. I guess most of people here do understand that using just a pure "ID" word as column name is not good practice as it easily became very annoying when you see it in every table. Even MS is following common sense here, it does name columns as object_id, column_id, principal_id, assembly_id etc. in its "sys" tables.
So, there is no arguing for the above. I believe that what OP have posted here was a simplified example of the real structures he has.
However, there is a number of people (at least I know one here), who tells now and then to everyone, that IDENTITY is the evil and never used by SQL developers for defining table keys. The "one I know", doesn't clarify that it is just his own opinion, he says that as a "fact", when it's clearly not. He says that natural keys should always be used, but once I've asked him to tell me what is the natural key for the people? If I have international database what can I use as a Natural Key for People or let say for Companies from different countries? I still don't have the answer. And I don't think I will ever have one as there is no such thing. Even DNA is not 100% unique (that is why forensic DNA-result doesn't confirm "match" or "no-match", it does only gives the probability of them).
September 12, 2012 at 2:22 pm
Luis Cazares (9/12/2012)
I agree with your reasons Jeff, but I don't see any reason why are there only id columns instead of issue_id, jrn_id, details_id, etc.
Agreed. That's why I had "ID" in quotes. They should be appropriately named. I follow the general rule of tablenameID and I never pluralize table names. I always name the tables after what one row conntains. For example, I would have a "Company" table and a "CompanyID" column instead of a "Companies" table and a "CompaniesID".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 125 total)
You must be logged in to reply to this topic. Login to reply