Query help for setting an indicator

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 2005 Forums

Transact-SQL (2005)

Query help for setting an indicator

Author

Topic

maktech.r
Starting Member

3 Posts

Posted - 2009-06-21 : 11:26:15

I have a query with 25 columns and 15 joins involved in it from different Sql tables to match the table structure in warehouse where I have this indicator as one of the column and this is set using other columns in the same query. There are 3 levels of filtering the result set to set this indicator field as Y and rest all will be set to N.

The columns involved in setting this are:

Primary indicator(PI), Primary Indicator Separator(PS), Date1, Date2, Date3.
Here PI is not null column but not unique one and PS is not null and unique. I am using an example below to explain how to set the IND field.

PI PS DATE1 DATE2 DATE3

198 198-1 01/01/2008 01/01/2006 01/05/2008
198 198-2 02/02/2009 01/01/2007 03/20/2009
198 198-3 04/04/2007 12/12/2009 09/25/2009
234 234-1 01/21/2009 01/02/2005 04/12/2009
234 234-2 04/02/2010 03/10/2006 05/19/2009
234 234-3 04/12/2000 04/02/2010 04/12/2010
234 234-4 01/23/2008 01/22/2006 02/10/2008
234 234-5 03/18/2007 03/10/2007 03/19/2007
234 234-6 05/22/2005 04/02/2010 04/12/2010

First level of filtering involves finding the max(DATE1) group by PI and max(DATE2) group by PI. From both these maximum dates we need to find the max(ie. Max of these two max's).
In our example Max of DATE1 would be
PI PS DATE1
198 198-2 02/02/2009
234 234-2 04/02/2010
Max of DATE2 would be
PI PS DATE1 DATE2
198 198-3 04/04/2007 12/12/2009
234 234-3 04/12/2000 04/02/2010
234 234-6 05/22/2005 04/02/2010

Then we need to find the Max of these filtered set of records and the result will be
PI PS DATE1 DATE2
198 198-3 04/04/2007 12/12/2009
234 234-2 04/02/2010 03/10/2006
234 234-3 04/12/2000 04/02/2010
234 234-6 05/22/2005 04/02/2010
For PI as 198 we have only one record and we can set it as Y and remaining records with the PI number would be N. But in the case of 234 we have 3 records with same date 04/02/2010. Then we need to bring the second level of filtering.

In the second level of filtering we need to take the first level of filtered results and consider the DATE3 column and look for Max(DATE3) group by PI which filters our result set to:
PI PS DATE1 DATE2 DATE3
234 234-3 04/12/2000 04/02/2010 04/12/2010
234 234-6 05/22/2005 04/02/2010 04/12/2010

As we still have 2 records the we go for 3rd level of filtering which is considering these 2nd level of filtered result set and checking for Max(PS) which scales down to just one record.
PI PS DATE1 DATE2 DATE3
234 234-6 05/22/2005 04/02/2010 04/12/2010

Here we set the IND as Y for only this record and N for remaining which should look like below:
PI PS DATE1 DATE2 DATE3 IND

198 198-1 01/01/2008 01/01/2006 01/05/2008 N
198 198-2 02/02/2009 01/01/2007 03/20/2009 N
198 198-3 04/04/2007 12/12/2009 09/25/2009 Y
234 234-1 01/21/2009 01/02/2005 04/12/2009 N
234 234-2 04/02/2010 03/10/2006 05/19/2009 N
234 234-3 04/12/2000 04/02/2010 04/12/2010 N
234 234-4 01/23/2008 01/22/2006 02/10/2008 N
234 234-5 03/18/2007 03/10/2007 03/19/2007 N
234 234-6 05/22/2005 04/02/2010 04/12/2010 Y

I hope everybody can understand my requirement and help me figure out how to solve this. Thanks for helping.

Mak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 11:16:14

[code]
SELECT * INTO #Temp
FROM
(
SELECT PI, PS, DATE1 AS DateVal1,DATE3 AS DateVal2
FROM YourTable
UNION ALL
SELECT PI, PS, DATE2,DATE3
FROM YourTable
)t

UPDATE t
SET t.IND=CASE WHEN t1.PI IS NOT NULL THEN 'Y' ELSE 'N' END
FROM YourTable t
LEFT JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY PI,PS,DateVal2 ORDER BY PI,PS,DateVal2) AS Seq,
DENSE_RANK() OVER (PARTITION BY PI ORDER BY DateVal1 DESC,DateVal2 DESC,PS DESC) AS Rnk,*
FROM #Temp
)t1
ON t.PI=t1.PI
AND t.PS=t1.PS
AND t1.Seq=1
AND t1.Rnk=1
[/code]