October 11, 2012 at 1:55 pm
I have a list of Statuses
A
B
C
D
E
F
G
H
I
and list of Phases
1
2
3
4
5
A,B fall under 1
C,D,E fall under 2
F falls under 3
G,H fall under 4
I fall under 5
once it goes into Phase 3, it cannot go back to Phase 2 again, even if the status is reverted to E. i.e if it goes to a higher Phase it cannot go down again even if the status is changed to a Phase that doesn't fall under that.
I will have Subjects with different statuses at different times. I have to calculate their Phase using their statuses. A Subject can skip Statuses and jump to final status at once too. then its Phase is 5 cos its status is I.
For Example.
my data in my table is like this
SubjectName----->Status---> Date
Subject1---->A----->10/01/2012
Subject1---->B----->10/02/2012
Subject1---->D----->10/02/2012
Subject1---->I----->10/03/2012
Subject2---->A----->10/02/2012
Subject2---->G----->10/04/2012
Subject2---->C----->10/05/2012
Subject2---->I----->10/06/2012
I want to show my result set as
Subject Name---->Status---->Phase---->Date
Subject1---->A---->1---->10/01/2012
Subject1---->B---->1---->10/02/2012
Subject1---->D---->2---->10/02/2012
Subject1---->I----->5---->10/03/2012
Subject2---->A---->1---->10/02/2012
Subject2---->G---->4---->10/04/2012
Subject2---->C---->4---->10/05/2012
Subject2---->I---->5---->10/06/2012
Please help me with the sql code.
All of them are Varchars (Status and Phase even though they look like Integers).
Please don't ask me to change my Datamodel.. Thanks everyone in advance.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
October 11, 2012 at 2:10 pm
Looks like homework, but tell us what approach you've taken. What didn't work? What have you tried? How far are you?
Rob
October 11, 2012 at 2:14 pm
Please help me with the sql code.
All of them are Varchars (Status and Phase even though they look like Integers).
Please don't ask me to change my Datamodel.. Thanks everyone in advance.
What sql code? What are you trying to do? Do you want constraints for this?
I won't ask you to change your datamodel but it seems that you know is less than ideal if you have to mention it.
If you really want some help you need to explain what you want help with and you need to post ddl and sample data. Take a look at the first link in my signature about best practices.
_______________________________________________________________
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/
October 11, 2012 at 2:20 pm
Not actually homework.
I have tried this using a case statement, but when ever the status changes the Phase also keeps changing, so I am checking if there is a way other than writing a case statement for actually doing this.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
October 11, 2012 at 2:22 pm
Sean Lange (10/11/2012)
Please help me with the sql code.
All of them are Varchars (Status and Phase even though they look like Integers).
Please don't ask me to change my Datamodel.. Thanks everyone in advance.
What sql code? What are you trying to do? Do you want constraints for this?
I won't ask you to change your datamodel but it seems that you know is less than ideal if you have to mention it.
If you really want some help you need to explain what you want help with and you need to post ddl and sample data. Take a look at the first link in my signature about best practices.
I am trying to mean the Script. Sorry for that.
I have tried using a Case Statement for this, but my Phase keeps changing when I do that.
any help with that?
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
October 11, 2012 at 2:29 pm
a4apple (10/11/2012)
Sean Lange (10/11/2012)
Please help me with the sql code.
All of them are Varchars (Status and Phase even though they look like Integers).
Please don't ask me to change my Datamodel.. Thanks everyone in advance.
What sql code? What are you trying to do? Do you want constraints for this?
I won't ask you to change your datamodel but it seems that you know is less than ideal if you have to mention it.
If you really want some help you need to explain what you want help with and you need to post ddl and sample data. Take a look at the first link in my signature about best practices.
I am trying to mean the Script. Sorry for that.
I have tried using a Case Statement for this, but my Phase keeps changing when I do that.
any help with that?
What is the script supposed to do? I can't help with no ddl and sample data to work with.
_______________________________________________________________
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/
October 11, 2012 at 2:32 pm
CREATE TABLE Status
(
StatusName Varchar(10)
)
CREATE TABLE Phase
(
PhaseName Varchar(10) NOT NULL
)
CREATE TABLE Subject_status
(
SubjectName Varchar(10) NOT NULL,
StatusName Varchar(10) NOT NULL,
Date [Date] NOT NULL
)
INSERT INTO Status VALUES ('A')
INSERT INTO Status VALUES ('B')
INSERT INTO Status VALUES ('C')
INSERT INTO Status VALUES ('D')
INSERT INTO Status VALUES ('E')
INSERT INTO Status VALUES ('F')
INSERT INTO Status VALUES ('G')
INSERT INTO Status VALUES ('H')
INSERT INTO Status VALUES ('I')
INSERT INTO Phase VALUES ('1')
INSERT INTO Phase VALUES ('2')
INSERT INTO Phase VALUES ('3')
INSERT INTO Phase VALUES ('4')
INSERT INTO Phase VALUES ('5')
As per business rule,
Status A and B fall under Phase 1
Status C, D and E fall under Phase 2
Status F falls under Phase 3
Status G and H fall under Phase 4
Status I falls under Phase 5
Insert into Subject_status Values ('Subject1','A','10/01/2012')
Insert into Subject_status Values ('Subject1','B','10/02/2012')
Insert into Subject_status Values ('Subject1','D','10/02/2012')
Insert into Subject_status Values ('Subject1','I','10/03/2012')
Insert into Subject_status Values ('Subject2','A','10/02/2012')
Insert into Subject_status Values ('Subject2','G','10/04/2012')
Insert into Subject_status Values ('Subject2','C','10/05/2012')
Insert into Subject_status Values ('Subject2','I','10/06/2012')
Need to write a SQL Statement so that it will show Phase in my result set as follows
Subject Name---->Status---->Phase---->Date
Subject1---->A---->1---->10/01/2012
Subject1---->B---->1---->10/02/2012
Subject1---->D---->2---->10/02/2012
Subject1---->I----->5---->10/03/2012
Subject2---->A---->1---->10/02/2012
Subject2---->G---->4---->10/04/2012
Subject2---->C---->4---->10/05/2012
Subject2---->I---->5---->10/06/2012
(Arrows are just to differentiate the columns)
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
October 11, 2012 at 3:00 pm
Thanks for ddl and data. Makes this a lot easier.
Your data structures don't allow for anything like a join because there is nothing to tie a status to a phase. I won't ask you to change them but I will highly recommend it if it is possible.
I modified your ddl to use temp tables for testing. Anything other than Subject_status is not helpful because you can't tie these together.
The following produces the output as you described.
CREATE TABLE #Status
(
StatusName Varchar(10)
)
CREATE TABLE #Phase
(
PhaseName Varchar(10) NOT NULL
)
CREATE TABLE #Subject_status
(
SubjectName Varchar(10) NOT NULL,
StatusName Varchar(10) NOT NULL,
Date [Date] NOT NULL
)
INSERT INTO #Status VALUES ('A')
INSERT INTO #Status VALUES ('B')
INSERT INTO #Status VALUES ('C')
INSERT INTO #Status VALUES ('D')
INSERT INTO #Status VALUES ('E')
INSERT INTO #Status VALUES ('F')
INSERT INTO #Status VALUES ('G')
INSERT INTO #Status VALUES ('H')
INSERT INTO #Status VALUES ('I')
INSERT INTO #Phase VALUES ('1')
INSERT INTO #Phase VALUES ('2')
INSERT INTO #Phase VALUES ('3')
INSERT INTO #Phase VALUES ('4')
INSERT INTO #Phase VALUES ('5')
Insert into #Subject_status Values ('Subject1','A','10/01/2012')
Insert into #Subject_status Values ('Subject1','B','10/02/2012')
Insert into #Subject_status Values ('Subject1','D','10/02/2012')
Insert into #Subject_status Values ('Subject1','I','10/03/2012')
Insert into #Subject_status Values ('Subject2','A','10/02/2012')
Insert into #Subject_status Values ('Subject2','G','10/04/2012')
Insert into #Subject_status Values ('Subject2','C','10/05/2012')
Insert into #Subject_status Values ('Subject2','I','10/06/2012')
select * from #Status
select * from #Phase
select * from #Subject_status
select *,
Case
when StatusName IN ('A', 'B') then 1
when StatusName IN ('C', 'D', 'E') then 2
when StatusName = 'F' then 3
when StatusName = 'G' then 4
when StatusName = 'I' then 5
end as Phase
from #Subject_status ss
drop table #Status
drop table #Phase
drop table #Subject_status
_______________________________________________________________
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/
October 11, 2012 at 4:39 pm
Sean Lange (10/11/2012)
select *,
Case
when StatusName IN ('A', 'B') then 1
when StatusName IN ('C', 'D', 'E') then 2
when StatusName = 'F' then 3
when StatusName = 'G' then 4
when StatusName = 'I' then 5
end as Phase
from #Subject_status ss
ject_status
Subject1A2012-10-011
Subject1B2012-10-021
Subject1D2012-10-022
Subject1I2012-10-035
Subject2A2012-10-021
Subject2G2012-10-044
Subject2C2012-10-052
Subject2I2012-10-065
This actually doesn't work because Phase changes from 4 to 2 in Subject 2 in the result set. Like I said, Once the Phase reaches the higher level like 4 it shouldn't come back to 2. I wrote the same exact case statement but I am unable to do the Phase level not being changed from higher level to lower.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
October 11, 2012 at 8:58 pm
a4apple (10/11/2012)
This actually doesn't work because Phase changes from 4 to 2 in Subject 2 in the result set. Like I said, Once the Phase reaches the higher level like 4 it shouldn't come back to 2. I wrote the same exact case statement but I am unable to do the Phase level not being changed from higher level to lower.
I don't believe you can accomplish this without first adding a Phase column to the Subject_Status table and then designing a high performance trigger to enforce the business rule to automatically assign the correct Phase including the rule that the Phase shall never decrement.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2012 at 9:16 pm
Jeff Moden (10/11/2012)
a4apple (10/11/2012)
This actually doesn't work because Phase changes from 4 to 2 in Subject 2 in the result set. Like I said, Once the Phase reaches the higher level like 4 it shouldn't come back to 2. I wrote the same exact case statement but I am unable to do the Phase level not being changed from higher level to lower.
I don't believe you can accomplish this without first adding a Phase column to the Subject_Status table and then designing a high performance trigger to enforce the business rule to automatically assign the correct Phase including the rule that the Phase shall never decrement.
Ugh! Never mind. I just saw the test data setup. No need for the trigger because all the "old data" is present.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2012 at 10:08 pm
I believe the following will do as you ask. I call it a "data smear" (because it "smears" the largest value "down" through the returned rows) for lack of a better term.
Ordinarily, this would be a very ineffecient "Triangular Join" but it'll be only about twice as slow as a very high performance "Quirky Update" if you add the following index (assuming you already have some sort of a PK on the table). The caveat here is that this index will split extents frequently on inserts which could cause timeouts on a busy system. If that's the case, we'll try something else.
CREATE INDEX Composite01 ON dbo.Subject_Status (StatusName,SubjectName,[Date])
SELECT ss.SubjectName, ss.StatusName, ss.Date,
PhaseName =
--== Nested SELECT determines whether to use the current StatusName or a previous larger one
-- from the CROSS APPLY.
CASE (SELECT CASE WHEN ca.StatusName > ss.StatusName THEN ca.StatusName ELSE ss.StatusName END)
WHEN 'A' THEN '1'
WHEN 'B' THEN '1'
WHEN 'C' THEN '2'
WHEN 'D' THEN '2'
WHEN 'E' THEN '2'
WHEN 'F' THEN '3'
WHEN 'G' THEN '4'
WHEN 'H' THEN '4'
WHEN 'I' THEN '5'
ELSE 'ERROR'
END
FROM dbo.Subject_Status ss
CROSS APPLY ( --===== Finds "highest" status by SubjectName and Date
SELECT TOP 1
hi.StatusName
FROM dbo.Subject_Status hi
WHERE hi.SubjectName = ss.SubjectName
AND hi.[Date] <= ss.[Date]
ORDER BY hi.StatusName DESC
) ca
ORDER BY SubjectName, Date, PhaseName
;
Here are the results using the test data setup previously given on this thread...
SubjectName StatusName Date PhaseName
----------- ---------- ----------------------- ---------
Subject1 A 2012-10-01 00:00:00.000 1
Subject1 B 2012-10-02 00:00:00.000 2
Subject1 D 2012-10-02 00:00:00.000 2
Subject1 I 2012-10-03 00:00:00.000 5
Subject2 A 2012-10-02 00:00:00.000 1
Subject2 G 2012-10-04 00:00:00.000 4
Subject2 C 2012-10-05 00:00:00.000 4
Subject2 I 2012-10-06 00:00:00.000 5
(8 row(s) affected)
If I had a bit more time, I would have built the rules into a table so that you wouldn't actually have to change the code if the rules changed.
Also, I'm assuming that your status "names" and phase "names" will always be sortable as they currently are. If not, then you should probably go to the table driven solution with a "SortOrder" or "Priority" column in both the status and phase tables and key off of that.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2012 at 8:56 am
Jeff Moden (10/11/2012)
a new "Modenism" called the data smear!
sweet!
thanks Jeff!
Lowell
October 12, 2012 at 1:20 pm
Jeff Moden (10/11/2012)
I believe the following will do as you ask. I call it a "data smear" (because it "smears" the largest value "down" through the returned rows) for lack of a better term.Ordinarily, this would be a very ineffecient "Triangular Join" but it'll be only about twice as slow as a very high performance "Quirky Update" if you add the following index (assuming you already have some sort of a PK on the table). The caveat here is that this index will split extents frequently on inserts which could cause timeouts on a busy system. If that's the case, we'll try something else.
CREATE INDEX Composite01 ON dbo.Subject_Status (StatusName,SubjectName,[Date])
SELECT ss.SubjectName, ss.StatusName, ss.Date,PhaseName =
--== Nested SELECT determines whether to use the current StatusName or a previous larger one
-- from the CROSS APPLY.
CASE (SELECT CASE WHEN ca.StatusName > ss.StatusName THEN ca.StatusName ELSE ss.StatusName END)
WHEN 'A' THEN '1'
WHEN 'B' THEN '1'
WHEN 'C' THEN '2'
WHEN 'D' THEN '2'
WHEN 'E' THEN '2'
WHEN 'F' THEN '3'
WHEN 'G' THEN '4'
WHEN 'H' THEN '4'
WHEN 'I' THEN '5'
ELSE 'ERROR'
END
FROM dbo.Subject_Status ss
CROSS APPLY ( --===== Finds "highest" status by SubjectName and Date
SELECT TOP 1
hi.StatusName
FROM dbo.Subject_Status hi
WHERE hi.SubjectName = ss.SubjectName
AND hi.[Date] <= ss.[Date]
ORDER BY hi.StatusName DESC
) ca
ORDER BY SubjectName, Date, PhaseName
;
Here are the results using the test data setup previously given on this thread...
SubjectName StatusName Date PhaseName
----------- ---------- ----------------------- ---------
Subject1 A 2012-10-01 00:00:00.000 1
Subject1 B 2012-10-02 00:00:00.000 2
Subject1 D 2012-10-02 00:00:00.000 2
Subject1 I 2012-10-03 00:00:00.000 5
Subject2 A 2012-10-02 00:00:00.000 1
Subject2 G 2012-10-04 00:00:00.000 4
Subject2 C 2012-10-05 00:00:00.000 4
Subject2 I 2012-10-06 00:00:00.000 5
(8 row(s) affected)
If I had a bit more time, I would have built the rules into a table so that you wouldn't actually have to change the code if the rules changed.
Also, I'm assuming that your status "names" and phase "names" will always be sortable as they currently are. If not, then you should probably go to the table driven solution with a "SortOrder" or "Priority" column in both the status and phase tables and key off of that.
Wow.. Works Like a Charm.. Thank you very much Jeff..
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
October 12, 2012 at 1:24 pm
CELKO (10/12/2012)
You need a course in basic data modeling. There is no such entity as a “status” or “phase” in RDBMS. Those are attribute properties and are part of a data element name -- “<something>_status” such as marriage, employment, etc. Using “status_name” is absurd because both of those are attribute properties. Attributes should be in columns in tables.You have no keys, no constraints, DATE is a reserved word, too vague and an attribute property (a trifecta of incorrect design). Oh, you used the wrong date display format, too. We use CHECK() constraints for short, invariant lists of attribute values.
Here is my guess at what you meant to post:
CREATE TABLE Subjects
(subject_name VARCHAR(35) NOT NULL,
screwup_status CHAR(1) NOT NULL,
PRIMARY KEY (subject_name, screwup_status),
screwup_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);
INSERT INTO Subjects
VALUES ('Subject1', 'A', '2012-10-01'),
('Subject1', 'B', '2012-10-02'),
('Subject1', 'D', '2012-10-02'),
('Subject1', 'I', '2012-10-03'),
('Subject2', 'A', '2012-10-02'),
('Subject2', 'G', '2012-10-04'),
('Subject2', 'C', '2012-10-05'),
('Subject2', 'I', '2012-10-06');
CREATE TABLE Screwup_Phases
(screwup_phase CHAR(1) NOT NULL,
CHECK (screwup_phase IN ('1', '2', '3', '4', '5')),
screwup_status CHAR(1) NOT NULL
CHECK (screwup_status IN
('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I')),
PRIMARY KEY (screwup_phase, screwup_status));
Now put the business rules in a table. This could also be put into a a persistent VIEW using a row constructor if it is constant.
INSERT INTO Screwup_Phases
VALUES ('1', 'A'), ('1', 'B'),
('2', 'C'), ('2', 'D'), ('2', 'E'),
('3', 'F'), ('4', 'G'),
('4', 'H'), ('5', 'I');
>> Need to write a SQL Statement so that it will show Phase in my result set as follows
SELECT S.subject_name, S.screwup_status, P.screwup_phase, S.screwup_date
FROM Screwup_Phases AS P, Subjects AS S
WHERE P.screwup_status = S.screwup_status;
Subject1A12012-10-01
Subject2A12012-10-02
Subject1B12012-10-02
Subject2C22012-10-05
Subject1D22012-10-02
Subject2G42012-10-04
Subject1I52012-10-03
Subject2I52012-10-06
Hi Celko,
I totally understand you have great command and knowledge over sql server. People who need help here need suggestions, not answers like yours. I have seen all your posts complaining about others stuff. Why do you want to do it. If you can help, do it or else just laugh at the thread and walk away. Please donot keep posting you screwed your database, you are wrong, it will die etc., If you don't like the design, ignore it. I just placed "Donot change my datamodel" keeping you in mind.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply