Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

T-SQL Help Needed Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 7:05 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1357081
Posted Monday, September 10, 2012 7:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1357089
Posted Monday, September 10, 2012 8:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,566, Visits: 2,392
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.
Post #1357093
Posted Monday, September 10, 2012 11:06 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1357135
Posted Monday, September 10, 2012 11:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 1,945, Visits: 3,008
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.

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:

job_status CHAR(12) DEFAULT 'new' NOT NULL
CHECK (job_status)
IN('new', 'assign', 'in progress', 'complete')

You did not get the date format correct.

CREATE TABLE Issues
(issue_id INTEGER NOT NULL PRIMARY KEY,
issue_name VARCHAR(10) NOT NULL);

INSERT INTO Issues
VALUES(1,'issue1'), (2, 'issue2'), (3, 'issue3');

CREATE TABLE Journal
(issue_id INTEGER NOT NULL
REFERENCES Issues (issue_id),
journal_date DATE NOT NULL,
PRIMARY KEY (issue_id, journal_date));

INSERT INTO Journal(issue_id, journal_date)
VALUES(1, '2012-04-01), (1, '2012-04-22), (1, '2012-04-28),
(2, '2012-04-01), (2, '2012-04-23);

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
user_name VARCHAR(20) NOT NULL);

INSERT INTO Users VALUES(1, 'Alex'),(2, 'Sophia');

The next table is a total disaster, filled with meta data and worse. It makes no sense in RDBMS. Can you fire the moron that die this to you?

CREATE TABLE Something_Details
(kabbalah_nbr INTEGER NOT NULL PRIMARY KEY,
journal_id INTEGER NOT NULL
REFERENCES Journal(journal_id),
wacko_meta_data VARCHAR(10) NOT NULL,
old_something VARCHAR(10) NOT NULL,
new_something VARCHAR(10) NOT NULL);

INSERT INTO Something_Details
VALUES(42, 1, 'status_id', 1, 2),
(43, 2, 'user_id', NULL, 2),
(44, 2, 'status_id', 2, 3),
(45, 3, 'status_id', 3, 4),
(46, 4, 'status_id', 1, 2),
(47, 5, 'status_id', 2, 3);

I tried to find one right thing in this schema; I failed.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1357141
Posted Tuesday, September 11, 2012 4:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:50 AM
Points: 2,856, Visits: 5,124
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



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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1357256
Posted Tuesday, September 11, 2012 12:01 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1357618
Posted Tuesday, September 11, 2012 1:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,566, Visits: 2,392
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.[user]
FROM PVT a LEFT OUTER JOIN
(
SELECT
a.issue_id,
c.name AS [USER]
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.
Post #1357639
Posted Tuesday, September 11, 2012 1:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,566, Visits: 2,392
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.
Post #1357643
Posted Tuesday, September 11, 2012 1:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 3,667, Visits: 8,006
Have you tried this article? It might help you with what you're trying to accomplish.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1357646
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse