Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE [dbo].[table1](
- [col1] [varchar](50) NOT NULL,
- [col2] [varchar](50) NOT NULL,
- [col3] [int] NULL,
- [col4] [varchar](50) NOT NULL,
- [col5] [varchar](6) NULL,
- [col6] [smallint] NOT NULL,
- [col7] [char](6) NULL,
- CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
- (
- [col2] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- CREATE TABLE [dbo].[table2](
- [col1] [varchar](50) NOT NULL,
- [col8] [nvarchar](50) NULL,
- CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED
- (
- [col1] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- CREATE TABLE [dbo].[table3](
- [col9] [varchar](50) NOT NULL,
- [col10] [int] NOT NULL
- CONSTRAINT [PK_table3] PRIMARY KEY CLUSTERED
- (
- [col9] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- CREATE TABLE [dbo].[table4](
- [col11] [smallint] IDENTITY(1,1) NOT NULL,
- [col2] [varchar](50) NULL,
- [col1] [varchar](50) NULL,
- [col10] [int] NULL,
- [col12] [nvarchar](50) NULL,
- [col13] [int] NULL,
- [col7] [char](6) NULL,
- [col14] [varchar](6) NOT NULL,
- [col15] [money] NULL,
- CONSTRAINT [PK_table4] PRIMARY KEY CLUSTERED
- (
- [col11] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- CREATE TABLE [dbo].[table5](
- [col16] [money] NULL,
- [col2] [varchar](50) NULL,
- [col17] [money] NULL,
- ) ON [PRIMARY]
- CREATE UNIQUE CLUSTERED INDEX [UCX_table5_col2] ON [dbo].[table5]
- (
- [col2] ASC
- )
- CREATE TABLE [dbo].[table6](
- [col18] [varchar](50) NOT NULL,
- [col19] [varchar](50) NOT NULL,
- [col20] [bigint] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_table6] PRIMARY KEY CLUSTERED
- (
- [col20] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- CREATE TABLE [dbo].[table7](
- [col21] [uniqueidentifier] NOT NULL,
- [col22] [varchar](50) NOT NULL,
- [col23] [varchar](50) NOT NULL,
- CONSTRAINT [PK_table7] PRIMARY KEY CLUSTERED
- (
- [col21] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- ;
- WITH a6
- AS ( SELECT cdc.col2
- ,maxLimit.col11 AS col15Rule
- ,maxLimit.col15
- FROM table1 AS cdc
- JOIN table2 AS cdt
- ON cdt.col1 = cdc.col1
- LEFT JOIN table3 AS ahcr
- ON ahcr.col9 = cdt.col1
- OUTER APPLY ( SELECT TOP 1
- rules.col11
- ,rules.col15
- FROM ( SELECT clr.col11
- ,clr.col15
- ,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 )
- FROM table4 AS clr
- WHERE ( clr.col2 IS NULL
- OR clr.col2 = cdc.col2
- )
- AND ( clr.col1 IS NULL
- OR clr.col1 = cdc.col1
- )
- AND ( clr.col10 IS NULL
- OR clr.col10 = ahcr.col10
- )
- AND ( clr.col12 IS NULL
- OR clr.col12 = cdt.col8
- )
- AND ( clr.col13 IS NULL
- OR clr.col13 = cdc.col3
- )
- AND ( clr.col7 IS NULL
- OR clr.col7 = cdc.col7
- )
- AND ( clr.col14 IS NULL
- OR clr.col14 = cdc.col5
- )
- AND clr.col15 IS NOT NULL
- ) AS rules
- WHERE rules.Priority = 1
- ) maxLimit
- WHERE cdc.col6 = 4
- )
- SELECT a1.[col2] AS [col22]
- ,a4.[col2] AS [col23]
- FROM [dbo].[table1] a1
- INNER JOIN [dbo].[table5] a2
- ON a1.[col2] = a2.[col2]
- INNER JOIN [dbo].[table6] a3
- ON a2.[col2] = a3.[col18]
- INNER JOIN [dbo].[table1] a4
- ON a3.[col19] = a4.[col2]
- INNER JOIN [dbo].[table5] a5
- ON a4.[col2] = a5.[col2]
- INNER JOIN a6
- ON a6.col2 = a4.col2
- WHERE COALESCE(a5.[col17], a5.[col16], 0) < ISNULL(a6.[col15], 0)
- AND NOT EXISTS ( SELECT 1
- FROM [dbo].[table7] a7
- WHERE a1.[col2] = a7.[col22]
- AND a4.[col2] = a7.[col23] )
- AND a1.col2 = '1234123412341234';
Add Comment
Please, Sign In to add comment