October 20, 2010 at 3:55 pm
I have a table of checks, each of which has a Status of 'Open' or 'Void'. Below are some sample data.
CKIDStat
101Open
102Open
103Open
104Void
105Void
106Open
107Open
108Open
109Void
110Open
I would like to create a CTE that would include a column for each group of checks, as shown below.
CKIDStatGrp
101Open1
102Open1
103Open1
104Void2
105Void2
106Open3
107Open3
108Open3
109Void4
110Open5
Thank you for any help.
October 20, 2010 at 4:22 pm
A serial update might work for this, but the implementation would require me to see the underlying DDL for the table and indexing, specifically the clustered index.
For the proper rules to doing something like this, do a search for 'Quirky Update' here on SSC and you'll find a very detailed article on how to do something like that.
EDIT: found it: http://www.sqlservercentral.com/articles/T-SQL/68467/
Other than that, you'll have to make data-islands of the data and then perhaps setup a row_number() over on the subset. I'll have to chew on that one a while.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 20, 2010 at 4:42 pm
It's slightly more complicated than just using a CTE...
DECLARE @tbl TABLE
(
ckid INT,stat VARCHAR(10)
)
INSERT INTO @tbl
SELECT 101 ,'Open' UNION ALL
SELECT 102 ,'Open' UNION ALL
SELECT 103 ,'Open' UNION ALL
SELECT 104 ,'Void' UNION ALL
SELECT 105 ,'Void' UNION ALL
SELECT 106 ,'Open' UNION ALL
SELECT 107 ,'Open' UNION ALL
SELECT 108 ,'Open' UNION ALL
SELECT 109 ,'Void' UNION ALL
SELECT 110 ,'Open'
SELECT *
FROM @tbl
;WITH cte AS -- row number per group
(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY ckid) r1,
ROW_NUMBER() OVER(ORDER BY ckid)-
ROW_NUMBER() OVER(PARTITION BY stat ORDER BY ckid ) AS ROW
FROM @tbl
),cte2 AS -- row number per group range, ordered by ckid
(
SELECT MIN(r1) mi,MAX(r1) ma,stat,ROW, ROW_NUMBER() OVER(ORDER BY MIN(r1) ) r2
FROM cte
GROUP BY stat,ROW
)
SELECT ckid ,cte.stat,cte2.r2
FROM cte
INNER JOIN cte2 ON r1>=mi AND r1<=ma
ORDER BY r1
October 20, 2010 at 4:52 pm
Thank you both for your help, Craig for the interesting article which I have started reading, and Lutz for the solution I needed.
gmrose
October 20, 2010 at 5:42 pm
Lutz, that is wild. I like it. Thanks. :w00t:
Have you done that on huge recordsets? Does it perform well?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 20, 2010 at 6:12 pm
Craig Farrell (10/20/2010)
Lutz, that is wild. I like it. Thanks. :w00t:Have you done that on huge recordsets? Does it perform well?
No, I haven't. And I probably won't either.
Such requirements and some rather large tables actually call for the quirky update from my point of view.
I don't expect the CTE to perform anywhere near the quirky update. Mainly because of the triple sort operation, the aggregation and the join on a range. But it should outperform any loop.
So, why did I posted this solution anyway?
#1: You already posted the link to Jeffs article.
#2: I did pretty much what you've already described. Just using T-SQL over English ;-):-D
#3: gmrose specifically asked for a cte solution and
#4: it was some kind of an exercise for me to do it using a non-quirky-update method.
October 20, 2010 at 6:24 pm
After I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been running for over 30 minutes so far and hasn't finished yet.
Thanks anyways.
gmrose
October 20, 2010 at 9:12 pm
Looks like it's time for a quirky update solution:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
-- This primary key is crucial. If your table doesn't have a clustered index
-- on this, then you will have to dump the data to a temp table (along with
-- the PK columns of the table), use this PK, then update the real table
-- by joining this temp table back to the real table by the PK columns.
CREATE TABLE #temp (ckid INT PRIMARY KEY CLUSTERED, stat varchar(10), Grp int);
INSERT INTO #temp (ckid, stat)
SELECT 101 ,'Open' UNION ALL
SELECT 102 ,'Open' UNION ALL
SELECT 103 ,'Open' UNION ALL
SELECT 104 ,'Void' UNION ALL
SELECT 105 ,'Void' UNION ALL
SELECT 106 ,'Open' UNION ALL
SELECT 107 ,'Open' UNION ALL
SELECT 108 ,'Open' UNION ALL
SELECT 109 ,'Void' UNION ALL
SELECT 110 ,'Open';
-- declare and initialize variables needed in the update statement.
DECLARE @Sequence int, -- for safety check
@stat varchar(10), -- to hold stat column from last row
@grp int, -- current grp number
@ckid int; -- for anchor column
SET @Sequence = 1;
SET @grp = 1;
/*
This form of the UPDATE statement has some rules for proper usage.
See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
for a complete discussion of how this works, and all of the rules for utilizing it.
If you don't follow ALL the rules, you WILL mess up your data.
*/
WITH SafeTable AS
(
-- generate table with a sequence column in clustered index order
-- in order to verify that update is happening in the correct order
SELECT ckid,
stat,
grp,
Sequence = ROW_NUMBER() OVER (ORDER BY ckid)
FROM #temp
)
UPDATE t
-- verify in proper sequence order; if not, throw an error so nothing is updated
SET @grp = grp = CASE WHEN Sequence = @Sequence THEN
CASE WHEN stat <> @Stat THEN @grp + 1
-- if you have to separate this by account numbers also, then you will need
-- a when clause to handle it here also.
-- different stat --> increment grp number
ELSE @grp END -- same stat --> same grp number
ELSE 1/0 END, -- not in proper sequence order, so throw an error
@Sequence = @Sequence + 1,
@stat = stat, -- get the current value to compare to in next row
@ckid = ckid -- anchor column
FROM SafeTable t WITH (TABLOCKX) -- lock table
OPTION (MAXDOP 1); -- prevent parallelism!
select * from #temp;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 20, 2010 at 10:50 pm
Wayne, in ur solution, you are basing your order over ckid column (Sequence = ROW_NUMBER() OVER (ORDER BY ckid) )
What if ckid is not in a proper order ? what the OP wants is to maintain the open-void in tact right.. so if you row_number it over ckid, then would that open-void combo be maintained ?
P.S: i am still learning the QU, so please pardon my ignorance..
October 21, 2010 at 8:55 am
gmrose (10/20/2010)
After I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been running for over 30 minutes so far and hasn't finished yet.Thanks anyways.
gmrose
How many rows does your table have? And what indexes?
Please post complete DDL for the table in question.
Edit: the actual execution plan would be great, too...
October 21, 2010 at 10:22 am
LutzM (10/21/2010)How many rows does your table have? And what indexes?
Please post complete DDL for the table in question.
Edit: the actual execution plan would be great, too...
This table currently has almost 414,000 rows. Here is the DDL for the table:
USE [MyDatabase]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 10/21/2010 08:48:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable](
[AcctFundCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctFundId] DEFAULT (''),
[ACHTrans] [bit] NOT NULL CONSTRAINT [DF_MyTable_ACHTrans] DEFAULT (0),
[AddrCity] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrCity] DEFAULT (''),
[AddrForeignStateCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrCountry] DEFAULT (''),
[AddrName] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorName] DEFAULT (''),
[AddrStateCode] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorState] DEFAULT (''),
[AddrStreet] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorStreet] DEFAULT (''),
[AddrZip] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorZip] DEFAULT (''),
[AmtCheck] [money] NOT NULL CONSTRAINT [DF_MyTable_AmtTotal] DEFAULT (0),
[BankAcctId] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BankAcctId] DEFAULT (''),
[BankCheckId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_CheckId] DEFAULT (''),
[BankCheckSort] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrSortValue] DEFAULT (''),
[DateCancelled] [datetime] NULL,
[DateCheck] [datetime] NULL,
[DateTimeCreated] [datetime] NOT NULL CONSTRAINT [DF_MyTable_DateTimeCreated] DEFAULT (getdate()),
[DateTimeEdited] [datetime] NULL,
[DateTimePrinted] [datetime] NULL,
[EmpId] [int] NOT NULL CONSTRAINT [DF_MyTable_PersonId] DEFAULT (0),
[OrgId] [smallint] NOT NULL CONSTRAINT [DF_MyTable_OrgId] DEFAULT (0),
[PayeeId] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_StudentId] DEFAULT (''),
[RegisterId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_RegisterId] DEFAULT (''),
[RegisterIdCancel] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_RegisterIdCancel] DEFAULT (''),
[Status] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_ReqStatusCode] DEFAULT (''),
[UserIdCreated] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdCreated] DEFAULT (''),
[UserIdEdited] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdEdited] DEFAULT (''),
[UserIdPrinted] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdPrinted] DEFAULT (''),
[VendorAddrId] [tinyint] NOT NULL CONSTRAINT [DF_MyTable_VendorAddrId] DEFAULT (0),
[VendorId] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorId_1] DEFAULT (''),
[VendorPayrollCheckType] [tinyint] NOT NULL CONSTRAINT [DF_MyTable_VendorPayrollCheckType] DEFAULT (0),
[PrintedCheckNum] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BatchId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BatchId] DEFAULT (''),
[DateCleared] [datetime] NULL,
[AcctResourceCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctResourceCode] DEFAULT (''),
[AcctProjectYearCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctProjectYearCode] DEFAULT (''),
[BatchIdCancel] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BatchIdCancel] DEFAULT (''),
CONSTRAINT [PK_MyTable_CheckId] PRIMARY KEY NONCLUSTERED
(
[BankCheckId] ASC,
[OrgId] ASC,
[BankAcctId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [MyDatabaseData4]
) ON [MyDatabaseData4]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Fund Id when only single funded checks are allowed' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctFundCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'True if this was part of an ACH Bank Transaction' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'ACHTrans'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee City Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrCity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Country Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrForeignStateCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee State Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrStateCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Street Address' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrStreet'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Zip Code' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrZip'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total Check Amount' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AmtCheck'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Bank Account Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankAcctId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankCheckId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Sort value used when printing checks' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankCheckSort'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date this check was cancelled' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCancelled'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Date' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCheck'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation Date and Time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimeCreated'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last changed Date and Time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimeEdited'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Print date and time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimePrinted'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee Id value when this check is associated with an Employee' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'EmpId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Organization''s Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'OrgId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Id value when this check is associated with a one time vendor' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'PayeeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Register this appeared on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'RegisterId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cancel Register Id this check appeared on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'RegisterIdCancel'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Status Code value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'Status'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation User Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdCreated'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last changed by User Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdEdited'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User Id of the user who printed the check' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdPrinted'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Address Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorAddrId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Payroll Check Type (1 - Deduction, 2 - Contribution, 3 - Both Deduction & Contribution, 4 - ACH Check to cover direct deposits)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorPayrollCheckType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a check # that some foreign system printed for our check record' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'PrintedCheckNum'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Batch Id value associated with the printing of checks' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BatchId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Datetime the check cleared the bank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCleared'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Resource Code that paid the most for this check' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctResourceCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Project Year component value that paid the most in combination with the AcctFundCode, AcctResourceCode' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctProjectYearCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'BatchId that this check was cancelled on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BatchIdCancel'
USE [MyDatabase]
GO
CREATE CLUSTERED INDEX [IX_MyTable_BankAcctId] ON [dbo].[MyTable]
(
[BankAcctId] ASC,
[OrgId] ASC,
[DateCheck] DESC,
[BankCheckId] DESC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]
CREATE NONCLUSTERED INDEX [IX_MyTable_BatchId] ON [dbo].[MyTable]
(
[BatchId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]
CREATE NONCLUSTERED INDEX [IX_MyTable_BatchIdCancel] ON [dbo].[MyTable]
(
[BatchIdCancel] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]
CREATE NONCLUSTERED INDEX [IX_MyTable_CheckNum] ON [dbo].[MyTable]
(
[PrintedCheckNum] ASC,
[DateTimeCreated] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]
CREATE NONCLUSTERED INDEX [IX_MyTable_Status] ON [dbo].[MyTable]
(
[BankAcctId] ASC,
[Status] ASC,
[OrgId] ASC,
[DateCancelled] ASC,
[DateCheck] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]
CREATE NONCLUSTERED INDEX [IX_MyTable_VendorId] ON [dbo].[MyTable]
(
[OrgId] ASC,
[VendorId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable_CheckId] PRIMARY KEY NONCLUSTERED
(
[BankCheckId] ASC,
[OrgId] ASC,
[BankAcctId] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]
Here is the current execution plan:
DECLARE @tmpChecks TABLE
(
OrgCheck char(15),
OrgId smallint,
CheckNum int,
Status char(1),
AmtCheck money,
CheckDate char(10),
RegisterId char(12)
)
INSERT INTO @tmpChecks
SELECT CAST(OrgId as char(3))+PrintedCheckNum as OrgCheck,
OrgId, PrintedCheckNum as CheckNum, Status,
AmtCheck = CASE WHEN Status = 'V' THEN 0 ELSE AmtCheck END,
CONVERT(char(10), DateCheck,101) as CheckDate, RegisterId
from MyTable WITH (NOLOCK)
WHERE
ISNUMERIC(PrintedCheckNum) = 1
order by 1
;WITH cte1 AS -- row number per group
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY OrgCheck) AS CkRow,
ROW_NUMBER() OVER(PARTITION BY Status ORDER BY OrgCheck ) AS StatRow,
ROW_NUMBER() OVER(ORDER BY OrgCheck)-
ROW_NUMBER() OVER(PARTITION BY Status ORDER BY OrgCheck ) AS DiffRow
FROM @TmpChecks
),cte2 AS -- row number per group range, ordered by OrgCheck
(
SELECT MIN(CkRow) as MinRow,MAX(CkRow) as MaxRow, Status, DiffRow,
ROW_NUMBER() OVER(ORDER BY MIN(CkRow)) AS CheckGroup
FROM cte1
GROUP BY Status, DiffRow
)
SELECT cte1.OrgCheck, cte1.OrgId, cte1.CheckNum, cte1.Status, cte1.AmtCheck,
cte1.CheckDate, cte1.RegisterId, cte2.CheckGroup
FROM cte1
INNER JOIN cte2 ON cte1.CkRow>=cte2.MinRow AND cte1.CkRow<=cte2.MaxRow
ORDER BY cte1.OrgCheck
I don't mean for this to turn into a large task for anyone on the forum. Thank you again for any suggestions.
gmrose
October 21, 2010 at 12:56 pm
That explains the bad performance pretty much...
You're using a table variable just like I did for my demo version. :blush: My fault. I'm sorry.
A table variable is a bad option to use since it's usually populated without proper indexing causing a table scan instead of a index seek. Furthermore, there are no statistics available for the query optimizer to come up with the best query plan. Instead QO will assume that the table varialbe will have only one row.
I thought you would replace the table variable with your original table, but I didn't mention it.
Now that I see you're using a computed column as a replacement of ckid, I'd recommend you make the following changes (+ change the table name in the CTE).
CREATE TABLE #tmpChecks
(
OrgCheck char(15) PRIMARY KEY CLUSTERED,
OrgId smallint,
CheckNum int,
Status char(1),
AmtCheck money,
CheckDate char(10),
RegisterId char(12)
)
INSERT INTO #tmpChecks
SELECT CAST(OrgId as char(3))+PrintedCheckNum as OrgCheck,
OrgId, PrintedCheckNum as CheckNum, Status,
AmtCheck = CASE WHEN Status = 'V' THEN 0 ELSE AmtCheck END,
CONVERT(char(10), DateCheck,101) as CheckDate, RegisterId
from MyTable WITH (NOLOCK)
WHERE
ISNUMERIC(PrintedCheckNum) = 1
order by OrgCheck
I'd be curious to know the timing of this code compared to the previous solution...
Again, I'm sorry...
October 21, 2010 at 1:24 pm
ColdCoffee (10/20/2010)
Wayne, in ur solution, you are basing your order over ckid column (Sequence = ROW_NUMBER() OVER (ORDER BY ckid) )What if ckid is not in a proper order ? what the OP wants is to maintain the open-void in tact right.. so if you row_number it over ckid, then would that open-void combo be maintained ?
P.S: i am still learning the QU, so please pardon my ignorance..
Not a problem - it would only be a problem when you stop learning - and even then, it's YOUR problem! :-D:-P;-):w00t:
In the original post, the OP shows the expected results in ckid order, and the grp number changes whenever the stat changes. That is why there is a clustered index on the ckid column. The ROW_NUMBER() ... ORDER BY clause MUST identical to the clustered index columns (including the sort direction).
The ROW_NUMBER() ORDER BY is a neat trick that Paul White came up with, and it was enhanced by Tom Thompson. It essentially guarantees that the update occurs in the proper order, or not at all. Tom's enhancement covers the (theoretical) possibility that the sequence number is properly generated in a linear fashion, while the variable assignment (that's being carried row-to-row) isn't.
In the solution I posted, I alluded to the possibility of needing an additional field to do this properly. I'm assuming that there would be an account number. This would require:
1. changing the clustered index (and the ROW_NUMBER()) to include this column.
2. changing the logic of the variable assignment to handle when the account number changes.
I've found that the best way of learning is by practicing. You don't get rid of c.u.r.s.o.r.s. by writing more of them; you learn the QU (and other set-based practices) by doing things with them. So, let's make a couple of minor modifications to the specification. Let's include the account number, and change the specs so that whenever the account number changes, the grp restarts at one. All other specs remain the same. The following code has the sample data and expected results.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (acct_nbr INT,
ckid INT,
stat varchar(10),
Grp int);
INSERT INTO #temp (acct_nbr, ckid, stat)
SELECT 1, 101 ,'Open' UNION ALL
SELECT 1, 102 ,'Open' UNION ALL
SELECT 1, 103 ,'Open' UNION ALL
SELECT 1, 104 ,'Void' UNION ALL
SELECT 1, 105 ,'Void' UNION ALL
SELECT 1, 106 ,'Open' UNION ALL
SELECT 1, 107 ,'Open' UNION ALL
SELECT 1, 108 ,'Open' UNION ALL
SELECT 1, 109 ,'Void' UNION ALL
SELECT 1, 110 ,'Open' UNION ALL
SELECT 2, 101 ,'Open' UNION ALL
SELECT 2, 102 ,'Open' UNION ALL
SELECT 2, 103 ,'Open' UNION ALL
SELECT 2, 104 ,'Void' UNION ALL
SELECT 3, 105 ,'Void' UNION ALL
SELECT 3, 106 ,'Open' UNION ALL
SELECT 3, 107 ,'Open' UNION ALL
SELECT 4, 108 ,'Open' UNION ALL
SELECT 4, 109 ,'Void' UNION ALL
SELECT 4, 110 ,'Open';
-- expected output:
SELECT acct_nbr = 1, ckid = 101 , stat = 'Open', grp = 1 UNION ALL
SELECT 1, 102 ,'Open',1 UNION ALL
SELECT 1, 103 ,'Open',1 UNION ALL
SELECT 1, 104 ,'Void',2 UNION ALL
SELECT 1, 105 ,'Void',2 UNION ALL
SELECT 1, 106 ,'Open',3 UNION ALL
SELECT 1, 107 ,'Open',3 UNION ALL
SELECT 1, 108 ,'Open',3 UNION ALL
SELECT 1, 109 ,'Void',4 UNION ALL
SELECT 1, 110 ,'Open',5 UNION ALL
SELECT 2, 101 ,'Open',1 UNION ALL
SELECT 2, 102 ,'Open',1 UNION ALL
SELECT 2, 103 ,'Open',1 UNION ALL
SELECT 2, 104 ,'Void',2 UNION ALL
SELECT 3, 105 ,'Void',1 UNION ALL
SELECT 3, 106 ,'Open',2 UNION ALL
SELECT 3, 107 ,'Open',2 UNION ALL
SELECT 4, 108 ,'Open',1 UNION ALL
SELECT 4, 109 ,'Void',2 UNION ALL
SELECT 4, 110 ,'Open',3;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 21, 2010 at 5:26 pm
Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor adjustments to exclude about 100,000 unneeded rows. Now the script runs as desired.
For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.
gmrose
October 21, 2010 at 6:49 pm
gmrose (10/21/2010)
Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor adjustments to exclude about 100,000 unneeded rows. Now the script runs as desired.For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.
gmrose
Yes, this is indeed a real improvement. Out of curiosity, how many records are you processing? And did you try out the script that I posted?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply