Guest User

Untitled

a guest
Feb 27th, 2017
262
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.77 KB | None | 0 0
  1.  
  2. CREATE TABLE [dbo].[table1](
  3. [col1] [varchar](50) NOT NULL,
  4. [col2] [varchar](50) NOT NULL,
  5. [col3] [int] NULL,
  6. [col4] [varchar](50) NOT NULL,
  7. [col5] [varchar](6) NULL,
  8. [col6] [smallint] NOT NULL,
  9. [col7] [char](6) NULL,
  10. CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
  11. (
  12. [col2] ASC
  13. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  14. ) ON [PRIMARY]
  15.  
  16.  
  17.  
  18.  
  19.  
  20. CREATE TABLE [dbo].[table2](
  21. [col1] [varchar](50) NOT NULL,
  22. [col8] [nvarchar](50) NULL,
  23. CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED
  24. (
  25. [col1] ASC
  26. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  27. ) ON [PRIMARY]
  28.  
  29.  
  30.  
  31. CREATE TABLE [dbo].[table3](
  32. [col9] [varchar](50) NOT NULL,
  33. [col10] [int] NOT NULL
  34. CONSTRAINT [PK_table3] PRIMARY KEY CLUSTERED
  35. (
  36. [col9] ASC
  37. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  38. ) ON [PRIMARY]
  39.  
  40.  
  41.  
  42. CREATE TABLE [dbo].[table4](
  43. [col11] [smallint] IDENTITY(1,1) NOT NULL,
  44. [col2] [varchar](50) NULL,
  45. [col1] [varchar](50) NULL,
  46. [col10] [int] NULL,
  47. [col12] [nvarchar](50) NULL,
  48. [col13] [int] NULL,
  49. [col7] [char](6) NULL,
  50. [col14] [varchar](6) NOT NULL,
  51. [col15] [money] NULL,
  52. CONSTRAINT [PK_table4] PRIMARY KEY CLUSTERED
  53. (
  54. [col11] ASC
  55. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  56. ) ON [PRIMARY]
  57.  
  58.  
  59. CREATE TABLE [dbo].[table5](
  60. [col16] [money] NULL,
  61. [col2] [varchar](50) NULL,
  62. [col17] [money] NULL,
  63. ) ON [PRIMARY]
  64.  
  65. CREATE UNIQUE CLUSTERED INDEX [UCX_table5_col2] ON [dbo].[table5]
  66. (
  67. [col2] ASC
  68. )
  69.  
  70.  
  71. CREATE TABLE [dbo].[table6](
  72. [col18] [varchar](50) NOT NULL,
  73. [col19] [varchar](50) NOT NULL,
  74. [col20] [bigint] IDENTITY(1,1) NOT NULL,
  75. CONSTRAINT [PK_table6] PRIMARY KEY CLUSTERED
  76. (
  77. [col20] ASC
  78. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  79. ) ON [PRIMARY]
  80.  
  81.  
  82.  
  83.  
  84. CREATE TABLE [dbo].[table7](
  85. [col21] [uniqueidentifier] NOT NULL,
  86. [col22] [varchar](50) NOT NULL,
  87. [col23] [varchar](50) NOT NULL,
  88. CONSTRAINT [PK_table7] PRIMARY KEY CLUSTERED
  89. (
  90. [col21] ASC
  91. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  92. ) ON [PRIMARY]
  93. ;
  94.  
  95.  
  96.  
  97. WITH a6
  98. AS ( SELECT cdc.col2
  99. ,maxLimit.col11 AS col15Rule
  100. ,maxLimit.col15
  101. FROM table1 AS cdc
  102. JOIN table2 AS cdt
  103. ON cdt.col1 = cdc.col1
  104. LEFT JOIN table3 AS ahcr
  105. ON ahcr.col9 = cdt.col1
  106. OUTER APPLY ( SELECT TOP 1
  107. rules.col11
  108. ,rules.col15
  109. FROM ( SELECT clr.col11
  110. ,clr.col15
  111. ,Priority = ROW_NUMBER() OVER ( ORDER BY clr.col2 DESC, clr.col1 DESC, clr.col10 DESC, clr.col12 DESC, clr.col13 DESC, clr.col7 DESC , clr.col14 DESC )
  112. FROM table4 AS clr
  113. WHERE ( clr.col2 IS NULL
  114. OR clr.col2 = cdc.col2
  115. )
  116. AND ( clr.col1 IS NULL
  117. OR clr.col1 = cdc.col1
  118. )
  119. AND ( clr.col10 IS NULL
  120. OR clr.col10 = ahcr.col10
  121. )
  122. AND ( clr.col12 IS NULL
  123. OR clr.col12 = cdt.col8
  124. )
  125. AND ( clr.col13 IS NULL
  126. OR clr.col13 = cdc.col3
  127. )
  128. AND ( clr.col7 IS NULL
  129. OR clr.col7 = cdc.col7
  130. )
  131. AND ( clr.col14 IS NULL
  132. OR clr.col14 = cdc.col5
  133. )
  134. AND clr.col15 IS NOT NULL
  135. ) AS rules
  136. WHERE rules.Priority = 1
  137. ) maxLimit
  138. WHERE cdc.col6 = 4
  139. )
  140. SELECT a1.[col2] AS [col22]
  141. ,a4.[col2] AS [col23]
  142. FROM [dbo].[table1] a1
  143. INNER JOIN [dbo].[table5] a2
  144. ON a1.[col2] = a2.[col2]
  145. INNER JOIN [dbo].[table6] a3
  146. ON a2.[col2] = a3.[col18]
  147. INNER JOIN [dbo].[table1] a4
  148. ON a3.[col19] = a4.[col2]
  149. INNER JOIN [dbo].[table5] a5
  150. ON a4.[col2] = a5.[col2]
  151. INNER JOIN a6
  152. ON a6.col2 = a4.col2
  153. WHERE COALESCE(a5.[col17], a5.[col16], 0) < ISNULL(a6.[col15], 0)
  154. AND NOT EXISTS ( SELECT 1
  155. FROM [dbo].[table7] a7
  156. WHERE a1.[col2] = a7.[col22]
  157. AND a4.[col2] = a7.[col23] )
  158. AND a1.col2 = '1234123412341234';
Add Comment
Please, Sign In to add comment