SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need to create a derived table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

72 Posts

Posted - 09/12/2013 :  15:58:23  Show Profile  Reply with Quote
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()

Edited by - nietzky on 09/12/2013 16:02:52

ScottPletcher
Constraint Violating Yak Guru

USA
437 Posts

Posted - 09/12/2013 :  16:07:34  Show Profile  Reply with Quote
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


Edited by - ScottPletcher on 09/12/2013 16:07:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/12/2013 :  16:24:40  Show Profile  Reply with Quote

;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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nietzky
Yak Posting Veteran

72 Posts

Posted - 09/13/2013 :  10:37:01  Show Profile  Reply with Quote
Thank you both, I am good now.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/15/2013 :  06:32:20  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000