Need to create a derived table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to create a derived table

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-09-12 : 15:58:23

This is a table I am working of.

NAME CON_ID TOWER TestDate TestType INCIDENT SUB_STATUS
Email 20226 REE 6/15/2013 HA A620323 R
Email 20226 REE 6/14/2013 SR A010324 G
Email 20226 REE 4/12/2014 SR A080928 R
Email 20226 REE 4/12/2014 HA A060396 R
Email 20226 REE 11/1/2013 HA A080447 R
Email 20226 REE 11/1/2013 SR A432432 R
RATT 95667 LORR 6/11/2013 HA A567656 G
RATT 95667 LORR 6/12/2013 SR A896776 G
RATT 95667 LORR 6/10/2013 SR A456579 G
RATT 95667 LORR 6/1/2013 HA A123123 G
RATT 95667 LORR 6/5/2013 HA A635455 A
RATT 95667 LORR 6/5/2013 SR A534555 A
RATT 95667 LORR 6/16/2013 HA A435655 A
RATT 95667 LORR 6/17/2013 SR A756777 A
: : :
: : :

I need to create a derived table that would show these columns onlywith distinct CON_ID values.
CON_ID Last_HA_TestDate HA_SUB_STATUS Last_SR_TestDate SR_SUB_STATUS Next_HA_TestDate Next_SR_TestDate INCIDENT

conditions for these columns I am showing here. I am looking for most efficient (fast) way of constructing this table. Not: There are many CONIDs and they keep on growing every week.

Last_HA_TestDate - WHERE TestType= 'HA' and MAX(TestDate) <=getdate()
Last_SR_TestDate - WHERE TestType= 'SR' and MAX(TestDate) <=getdate()

Next_SR_TestDate - WHERE TestType= 'SR' and MAX(TestDate)>getdate()

Next_HA_TestDate - WHERE TestType= 'HA' and MAX(TestDate)> getdate()

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-12 : 16:07:34

Below is code for the columns you gave a description/formula for.



SELECT
    CON_ID,
    MAX(CASE WHEN TestType = 'HA' AND TestDate <= GETDATE() THEN TestDate END) AS Last_HA_TestDate,
    MAX(CASE WHEN TestType = 'SR' AND TestDate <= GETDATE() THEN TestDate END) AS Last_SR_TestDate,
    MAX(CASE WHEN TestType = 'HA' AND TestDate >  GETDATE() THEN TestDate END) AS Next_HA_TestDate,
    MAX(CASE WHEN TestType = 'SR' AND TestDate >  GETDATE() THEN TestDate END) AS Next_SR_TestDate    
FROM dbo.atable
GROUP BY
    CON_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-12 : 16:24:40

[code]
;With Temp
AS
(
SELECT *,MIN(CASE WHEN TestDate > GETDATE() THEN TestDate END) OVER (PARTITION BY CON_ID) AS NextDate,
MAX(CASE WHEN TestDate < GETDATE() THEN TestDate END) OVER (PARTITION BY CON_ID) AS PrevDate
FROM dbo.aTable
)

SELECT CON_ID,
MAX(CASE WHEN TestType = 'HA' AND TestDate = PrevDate THEN TestDate END) AS Last_HA_TestDate,
MAX(CASE WHEN TestType = 'HA' AND TestDate = PrevDate THEN SUB_STATUS END) AS HA_SUB_STATUS,
MAX(CASE WHEN TestType = 'SR' AND TestDate = PrevDate THEN TestDate END) AS Last_SR_TestDate,
MAX(CASE WHEN TestType = 'SR' AND TestDate = PrevDate THEN SUB_STATUS END) AS SR_SUB_STATUS,
MAX(CASE WHEN TestType = 'HA' AND TestDate = NextDate THEN TestDate END) AS Next_HA_TestDate,
MAX(CASE WHEN TestType = 'SR' AND TestDate = NextDate THEN TestDate END) AS Next_SR_TestDate,
...
FROM Temp
WHERE (TestDate = PrevDate
OR TestDate = NextDate)
GROUP BY CON_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-09-13 : 10:37:01

Thank you both, I am good now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-15 : 06:32:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources