SHARE
TWEET

Untitled

a guest Feb 27th, 2017 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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';
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top