June 17, 2016 at 10:37 am
Hello and thank you for reading.
I have multiple steps in a process, each with a Followup Date (anything _F) and a completed date (anything _C). I need to unpivot all Followup steps and Completed steps while still allow null values in 1 on the unpivot columns. If both completed and Followup are NULL drop from output
[Code="sql"]
CREATE TABLE [dbo].[#Activity_Tracking](
[ID] [INT] NOT NULL,
[Step1_C] [datetime] NULL,
[Step1_F] [datetime] NULL,
[Step2_C] [datetime] NULL,
[Step2_F] [datetime] NULL,
[Step3_C] [datetime] NULL,
[Step3_F] [datetime] NULL,
[Step4_C] [datetime] NULL,
[Step4_F] [datetime] NULL
)
INSERT INTO [#Activity_Tracking] VALUES (1,'2016-06-01',NULL,'2016-06-02','2016-06-01','2016-06-05','2016-06-04', '2016-06-08',Null);
INSERT INTO [#Activity_Tracking] VALUES (2,NULL,'2016-06-01',NULL,'2016-06-04','2016-06-05','2016-06-04', '2016-06-08',Null);
INSERT INTO [#Activity_Tracking] VALUES (3,'2016-06-03',NULL,'2016-06-02','2016-06-01','2016-06-05',NULL, '2016-06-08','2016-06-08');
INSERT INTO [#Activity_Tracking] VALUES (4,'2016-06-04','2016-06-04','2016-06-02',NULL,'2016-06-05',NULL, '2016-06-08',Null);
[/Code]
With Output like:
[Code="sql"]
IDStepCompleted DateFollowup Date
1Step16/1/2016NULL
1Step26/2/20166/1/2016
1Step36/5/20166/4/2016
1Step46/8/2016NULL
2Step1NULL 6/1/2016
2Step2NULL 6/4/2016
2Step36/5/20166/4/2016
2Step46/8/2016NULL
3Step16/3/2016NULL
3Step26/2/20166/1/2016
3Step36/5/2016NULL
3Step46/8/20166/8/2016
4Step16/4/20166/4/2016
4Step26/2/2016NULL
4Step36/5/2016NULL
4Step46/8/2016NULL
[/Code]
I've tried unpivoting both and setting them equal, but I only get output when both are not null.
I've tried Cross Apply on both, but I get data that does not exist
Working on Cross Join, as I'm posting, not sure if that's the answer though. I know the answer is out there, I can't be the first person who has wanted to compare followup and completed dates before 🙂
Any help would be greatly appreciated!
David92595
June 17, 2016 at 10:48 am
Cross apply is the way to go. Check the following article which explains how to do it.http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Here's an example. Be sure to understand it and ask questions that you might have.
SELECT at.ID,
u.Step,
u.CompletedDate,
u.FollowupDate
FROM #Activity_Tracking at
CROSS APPLY( VALUES('Step1', Step1_C, Step1_F),
('Step2', Step2_C, Step2_F),
('Step3', Step3_C, Step3_F),
('Step4', Step4_C, Step4_F))u(Step,CompletedDate,FollowupDate);
June 17, 2016 at 11:59 am
something is still off. None of the result show a followup with a NULL completed date.
I'm running all this from a sub query, not sure how that would matter, but it may be important...
June 17, 2016 at 12:02 pm
David92595 (6/17/2016)
something is still off. None of the result show a followup with a NULL completed date
Can you elaborate?
Using the sample data, I get the expected results you posted. Is there something missing in the sample?
June 17, 2016 at 12:13 pm
never mind, I figured fixed it.
I removed the sub query, as it was no longer needed, and used actual column Names instead of aliases. It's now worked. Not sure which one fixed it.
Thank you for your help!
June 17, 2016 at 7:00 pm
Hope someone checks this thread again,
I've expanded my query from a simple test to the real use of the query and I'm running into trouble with the following error: incorrect syntax near 'at'
my real problem is I have several tables joined in my from statement, when I add my cross apply statement i get the error. If I only use the cross apply statement against one other table, it work.
should I use a CTE or temp table?
basic example:
SELECT A.Id, B.Name, C.State, D.Step, D.FollowupDate, D.CompletedDate
from
A left outer join on A.ID = B.ID
Inner Join C on A.ID = C.ID
at Cross Apply (Values
('step1', step1_F, Step1_C)
('step2', step2_F, Step2_C)
('step3', step3_F, Step3_C)
('step4', step4_F, Step4_C)) as D(Step,FollowupDate, CompletedDate)
June 17, 2016 at 9:46 pm
Do you know what "at" was being used for in the first query?
June 28, 2016 at 6:23 am
Sorry for the delayed response.
I'm not sure I completely understand your question, cross apply is new to me, but I'll do my best.
Before "at" was being applied to view I created. However, I have had to to overhaul aspects on my front end based on new project information and joining tables make more sense then a view contain more information then is needed. If I use the view I'm updating a view of 10+ tables, when I only need 3-4.
Hope this makes sense.
June 28, 2016 at 6:52 am
It makes sense, but I found no relation to the question.
The APPLY operator is similar to a JOIN but with additional capabilities. It allows you to reference columns from tables previously used in the FROM clause.
Now, let's compare the code.
SELECT at.ID,
u.Step,
u.CompletedDate,
u.FollowupDate
FROM #Activity_Tracking at
CROSS APPLY( VALUES('Step1', Step1_C, Step1_F),
('Step2', Step2_C, Step2_F),
('Step3', Step3_C, Step3_F),
('Step4', Step4_C, Step4_F))u(Step,CompletedDate,FollowupDate);
SELECT A.Id, B.Name, C.State, D.Step, D.FollowupDate, D.CompletedDate
from
A left outer join on A.ID = B.ID
Inner Join C on A.ID = C.ID
at Cross Apply (Values
('step1', step1_F, Step1_C)
('step2', step2_F, Step2_C)
('step3', step3_F, Step3_C)
('step4', step4_F, Step4_C)) as D(Step,FollowupDate, CompletedDate)
Can you spot the errors and differences? Maybe if we format it the same way, the 3 errors will become easier to spot.
SELECT A.Id,
B.Name,
C.State,
D.Step,
D.FollowupDate,
D.CompletedDate
FROM A
LEFT OUTER JOIN ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID at
CROSS APPLY (VALUES ('step1', step1_F, Step1_C)
('step2', step2_F, Step2_C)
('step3', step3_F, Step3_C)
('step4', step4_F, Step4_C)) as D(Step,FollowupDate, CompletedDate)
Remember, you need to understand the code that you're using, that's why I'm not giving you the full answer and want you to find it.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply