Complex T-sql Query - Apply diff rules

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)

Complex T-sql Query - Apply diff rules

Author

Topic

DavidWilson
Starting Member

1 Post

Posted - 2009-07-03 : 15:37:48

Hi Team,

Please see below is my Original Table:


KEYCOL		GENNUMB		DATE		ITERATION	Srl#		
ABC-111		833		10199		1		0
ABC-111		833		10199		1		0
ABC-111		833		10199		1		0
ABC-111		833		10199		1		0
ABC-111		833		20199		1		0
ABC-111		833		20199		1		0
ABC-111		833		30199		1		0
ABC-111		833		40199		1		0
ABC-111		833		40199		1		0
ABC-111		833		40199		1		0
ABC-111		833		50199		1		0

ABC-111		933		10199		1		0
ABC-111		933		10199		1		0
ABC-111		933		10199		1		0
ABC-111		933		10199		1		0
ABC-111		933		20199		1		0
ABC-111		933		20199		1		0
ABC-111		933		30199		1		0
ABC-111		933		40199		1		0
ABC-111		933		40199		1		0
ABC-111		933		40199		1		0
ABC-111		933		60199		1		0
					
ABC-222		833		10299		2		0
ABC-222		833		20299		2		0
ABC-222		833		20299		2		0
ABC-222		833		20299		2		0
ABC-222		833		30299		2		0
ABC-222		833		30299		2		0
ABC-222		833		40299		2		0
ABC-222		833		40299		2		0
ABC-222		833		40299		2		0
ABC-222		833		40299		2		0
ABC-222		833		50299		2		0
ABC-222		833		70299		2		0

ABC-222		933		10299		2		0
ABC-222		933		20299		2		0
ABC-222		933		30299		2		0
ABC-222		933		40299		2		0
ABC-222		933		60299		2		0
ABC-222		933		60299		2		0
ABC-222		933		70299		2		0
ABC-222		933		80299		2		0
ABC-222		933		90299		2		0
ABC-222		933		100299		2		0
ABC-222		933		110299		2		0

I am looking for a result table as below (Rule#s are mentioned just for understanding, Its not a column of Result table):


KEYCOL		GENNUMB		DATE		ITERATION	Srl#	
ABC-111		833		010199		1		2	Rule1
ABC-111		833		010199		1		3	--do--
ABC-111		833		010199		1		4	--do--
ABC-111		833		010199		1		5	--do--
ABC-111		833		020199		1		6	--do--
ABC-111		833		020199		1		7	--do--
ABC-111		833		030199		1		8	--do--
ABC-111		833		040199		1		9	--do--
ABC-111		833		040199		1		10	--do--
ABC-111		833		040199		1		11	--do--
ABC-111		833		050199		1		12	--do--

ABC-111		933		010199		1		2	Rule2
ABC-111		933		010199		1		2	Rule2
ABC-111		933		010199		1		2	Rule2
ABC-111		933		010199		1		2	Rule2
ABC-111		933		020199		1		6	Rule2
ABC-111		933		020199		1		6	Rule2
ABC-111		933		030199		1		8	Rule2
ABC-111		933		040199		1		9	Rule2
ABC-111		933		040199		1		9	Rule2
ABC-111		933		040199		1		9	Rule2	
ABC-111		933		060199		1		9	Rule3

ABC-222		833		010299		2		2
ABC-222		833		020299		2		3
ABC-222		833		020299		2		4
ABC-222		833		020299		2		5
ABC-222		833		030299		2		6
ABC-222		833		030299		2		7
ABC-222		833		040299		2		8
ABC-222		833		040299		2		9
ABC-222		833		040299		2		10
ABC-222		833		040299		2		11
ABC-222		833		050299		2		12
ABC-222		833		070299		2		13

ABC-222		933		010299		2		2
ABC-222		933		020299		2		3
ABC-222		933		030299		2		6
ABC-222		933		040299		2		8
ABC-222		933		060299		2		8
ABC-222		933		060299		2		8
ABC-222		933		070299		2		13
ABC-222		933		080299		2		13
ABC-222		933		090299		2		13
ABC-222		933		100299		2		13
ABC-222		933		110299		2		13

About Data:
The data will be divided into two sets: GENNUMB = 833 and GENNUMB = 933
The Composite Primary Key is KEYCOL+ITERATION

Rules for getting the Result Table:

Rule1: For a particular KEYCOL(ex:ABC-111), Take all records for GENNUMB=833 , ITERATION=1 (If =2,its diff set of records), generate serial#s starting from 2
Note: By appling Rule1 to original table , only the records having GENNUMB = 833 will be effected.

Then apply the following rules to get the result table:

Rule2: For a KEYCOL(ex:ABC-111) , having GENNUMB = 933, for a DATE = DATE in GENNUMB 833,
assign Serial Number to that of Min Serial# found in DATE in GENNUMB = 833

Rule3: For a KEYCOL(ex:ABC-111), having GENNUMB=933, For a DATE NOT FOUND in DATE to that of GENNUMB 833, assign serial Number to that of Min Serial# found in DATE in GENNUMB = 833 for most previous month.

I have spent 2 days of time in getting this result table but failing at some points.

There might be some other rules , still i am analysing.
I am posting this because this forum has experienced professionals as members , but not to trouble anybody.
Please help me in getting the result table by applying these rules at this point.

Thanks,
David

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-03 : 16:28:19

Something like this?

SELECT      KEYCOL,
            GENNUMB,
            DATE,
            ITERATION,
            CASE GENNUMB
                WHEN 833 THEN recID
                WHEN 933 THEN keyID
            END AS [Srl#]
FROM        (
                SELECT  KEYCOL,
                        GENNUMB,
                        DATE,
                        ITERATION,
                        ROW_NUMBER() OVER (PARTITION BY KEYCOL, GENNUMB ORDER BY DATE) + 1 AS recID,
                        RANK() OVER (PARTITION BY KEYCOL, GENNUMB ORDER BY DATE) + 1 AS keyID
                FROM    OriginalTable
            ) AS d
ORDER BY    KEYCOL,
            GENNUMB,
            DATE

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-04 : 12:50:44

im not sure how

quote:
Rule3: For a KEYCOL(ex:ABC-111), having GENNUMB=933, For a DATE NOT FOUND in DATE to that of GENNUMB 833, assign serial Number to that of Min Serial# found in DATE in GENNUMB = 833 for most previous month.

can achieve

quote:
ABC-222 933 070299 2 13
ABC-222 933 080299 2 13
ABC-222 933 090299 2 13
ABC-222 933 100299 2 13
ABC-222 933 110299 2 13

or it just typo?

Result from peso query

ABC-111   	833	10199	1	2
ABC-111   	833	10199	1	3
ABC-111   	833	10199	1	4
ABC-111   	833	10199	1	5
ABC-111   	833	20199	1	6
ABC-111   	833	20199	1	7
ABC-111   	833	30199	1	8
ABC-111   	833	40199	1	9
ABC-111   	833	40199	1	10
ABC-111   	833	40199	1	11
ABC-111   	833	50199	1	12
ABC-111   	933	10199	1	2
ABC-111   	933	10199	1	2
ABC-111   	933	10199	1	2
ABC-111   	933	10199	1	2
ABC-111   	933	20199	1	6
ABC-111   	933	20199	1	6
ABC-111   	933	30199	1	8
ABC-111   	933	40199	1	9
ABC-111   	933	40199	1	9
ABC-111   	933	40199	1	9
ABC-111   	933	60199	1	12
ABC-222   	833	10299	2	2
ABC-222   	833	20299	2	3
ABC-222   	833	20299	2	4
ABC-222   	833	20299	2	5
ABC-222   	833	30299	2	6
ABC-222   	833	40299	2	7
ABC-222   	833	40299	2	8
ABC-222   	833	40299	2	9
ABC-222   	833	40299	2	10
ABC-222   	833	40299	2	11
ABC-222   	833	50299	2	12
ABC-222   	833	70399	2	13
ABC-222   	933	10299	2	2
ABC-222   	933	20299	2	3
ABC-222   	933	30299	2	4
ABC-222   	933	40299	2	5
ABC-222   	933	60299	2	6
ABC-222   	933	60299	2	6
ABC-222   	933	70299	2	8
ABC-222   	933	80299	2	9
ABC-222   	933	90299	2	10
ABC-222   	933	100299	2	11
ABC-222   	933	110299	2	12

AMITOFO+AMEN+YA ALLAH Hope the query works

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 05:20:02

[code]
;WIth CTE (KEYCOL,GENNUMB,DATE,ITERATION,Srl#)
(
SELECT KEYCOL,GENNUMB,DATE,ITERATION,
ROW_NUMBER() OVER (PARTITION BY KEYCOL,ITERATION ORDER BY DATE)
FROM YourTable
WHERE GENNUMB = 833
)

SELECT t.KEYCOL,t.GENNUMB,t.DATE,t.ITERATION,t1.Srl#
FROM YourTable t
CROSS APPLY (SELECT TOP 1 Srl#
FROM CTE
WHERE KEYCOL =t.KEYCOL
AND ITERATION = t.ITERATION
AND GENNUMB=833
AND DATE <=DATE
ORDER BY DATE DESC)t1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources