March 17, 2016 at 8:03 am
If I have the following example tables:
CREATE TABLE [dbo].[Status]
(
[StatusID] [INT] NOT NULL ,
[StatusDesc] [VARCHAR](20) NULL ,
[DatePassed] [DATETIME] NULL ,
CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED ( [StatusID] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON [PRIMARY];
CREATE TABLE [dbo].[JobStatus]
(
[JobId] [INT] NULL ,
[JobDesc] [VARCHAR](10) NULL ,
[StatusId] [INT] NULL ,
[DatePassed] [DATETIME] NULL
)
ON [PRIMARY];
ALTER TABLE [dbo].[JobStatus] WITH CHECK ADD CONSTRAINT [FK_Job_Status] FOREIGN KEY([StatusId])
REFERENCES [dbo].[Status] ([StatusID]);
ALTER TABLE [dbo].[JobStatus] CHECK CONSTRAINT [FK_Job_Status];
INSERT INTO [Status]
( StatusID, StatusDesc )
VALUES ( 1, 'FirstState' ),
( 2, 'SecondState' ),
( 3, 'ThirdState' );
INSERT INTO dbo.JobStatus
( JobId, JobDesc, StatusId, DatePassed )
VALUES ( 1, 'Job1', 1, GETDATE() ),
( 1, 'Job1', 2, GETDATE() + 1 ),
( 2, 'Job2', 1, GETDATE() + 2 ),
( 2, 'Job2', 2, GETDATE() ),
( 2, 'Job2', 3, GETDATE() ),
( 3, 'Job3', NULL, NULL );
I wish to be able to see all the values for the status, even when there is no match for a job. I.E. (dates changed from above due to laziness of typer!)
JobDesc StatusDesc DatePassed
Job1 FirstState 1/1/2016
Job1 SecondState 1/1/2016
Job1 ThirdState Null
Job2 FirstState 1/1/2016
...
Job3 FirstState Null
Job3 SecondState Null
Job3 ThirdState Null
March 17, 2016 at 8:16 am
So you want to match every job with every status? Just use a CROSS JOIN.
John
March 17, 2016 at 8:24 am
John Mitchell-245523 (3/17/2016)
So you want to match every job with every status? Just use a CROSS JOIN.John
To elaborate on this, it sounds like you're looking for something like:
SELECT JS.JobDesc,
S.StatusDesc,
DatePassed=MAX(CASE WHEN S.StatusId=JS.StatusId THEN JS.DatePassed END)
FROM JobStatus JS
CROSS JOIN
[Status] S
GROUP BY JS.JobDesc,S.StatusDesc
ORDER BY JS.JobDesc ASC
Cheers!
March 17, 2016 at 8:26 am
I thought that, and it works for where there is a job with no status, but where a job has one or two status's then it joins the wrong status together.
March 17, 2016 at 8:27 am
Jacob Wilkins (3/17/2016)
John Mitchell-245523 (3/17/2016)
So you want to match every job with every status? Just use a CROSS JOIN.John
To elaborate on this, it sounds like you're looking for something like:
SELECT JS.JobDesc,S.StatusDesc,
DatePassed=MAX(CASE WHEN S.StatusId=JS.StatusId THEN JS.DatePassed END)
FROM JobStatus JS
CROSS JOIN
[Status] S
GROUP BY JS.JobDesc,S.StatusDesc
ORDER BY JS.JobDesc ASC
Cheers!
Ah! That works. I needed the Max Case section!
thank you!
March 17, 2016 at 8:35 am
Actually, the best approach is to CROSS JOIN your missing Jobs table with your status table and then LEFT JOIN to the job status table. That way you don't have to do a GROUP BY to eliminate extraneous joins like status 1 joining 3.
SELECT j.JobID, j.JobDesc, s.StatusDesc, js.DatePassed
FROM Jobs j
CROSS JOIN Status s -- I really prefer plurals for table names
LEFT JOIN JobStatus js
ON j.JobID = js.JobID
AND s.StatusID = js.StatusID
If you don't have a Jobs table, then your data is improperly normalized.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 17, 2016 at 8:42 am
drew.allen (3/17/2016)
Actually, the best approach is to CROSS JOIN your missing Jobs table with your status table and then LEFT JOIN to the job status table. That way you don't have to do a GROUP BY to eliminate extraneous joins like status 1 joining 3.
SELECT j.JobID, j.JobDesc, s.StatusDesc, js.DatePassed
FROM Jobs j
CROSS JOIN Status s -- I really prefer plurals for table names
LEFT JOIN JobStatus js
ON j.JobID = js.JobID
AND s.StatusID = js.StatusID
If you don't have a Jobs table, then your data is improperly normalized.
Drew
Thank you. Another good approach. I do have a jobs table. I left it out of the question for clarity of the two problem tables.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply