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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

75 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
Aged Yak Warrior

USA
550 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
52326 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

75 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
52326 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  
 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.09 seconds. Powered By: Snitz Forums 2000