Best way to separate "stacked" records

  • To all -

    Now the code to create the destination table.

    Thanks so much.

    BT

    --===================Create the destination table

    /****** Object: Table [dbo].[tblDataSymScanDetail] Script Date: 06/03/2011 15:43:27 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDataSymScanDetail]') AND type in (N'U'))

    DROP TABLE [dbo].[tblDataSymScanDetail]

    GO

    /****** Object: Table [dbo].[tblDataSymScanDetail] Script Date: 06/03/2011 15:43:27 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[tblDataSymScanDetail](

    [pk] [int] IDENTITY(1,1) NOT NULL,

    [ItemUPC] [nvarchar](15) NULL,

    [RawFilePk] [int] NULL,

    [ItemDesc] [nvarchar](25) NULL

    ) ON [PRIMARY]

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblDataSymScanDetail', @level2type=N'COLUMN',@level2name=N'pk'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Item UPC' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblDataSymScanDetail', @level2type=N'COLUMN',@level2name=N'ItemUPC'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The primary key from tblDataSymRawImport' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblDataSymScanDetail', @level2type=N'COLUMN',@level2name=N'RawFilePk'

    GO

    --Insert data into the destination DB table

    SET IDENTITY_INSERT tblDataSymScanDetail ON

    INSERT INTO tblDataSymScanDetail(pk, ItemUPC, RawFilePk, ItemDesc)

    Select '1','1128400301','81','JJS APPLE PIF1$0.50' UNION ALL

    Select '2','1200010092','87','FRAPP MOCHA F1$6.69 ' UNION ALL

    Select '3','2430004120','34','NUTTY BARF1$1.25' UNION ALL

    Select '4','4369505632','54','CRSSNT PKT HF1$2.50'

    SET IDENTITY_INSERT tblDataSymScanDetail OFF

  • Lowell,

    Please see my next couple of posts. I believe I have included everything needed to create the raw and destination tables. You seem to have gotten darn close with your gessing.

    BT

  • well based on your data, see what this does for you:

    it's assuming less than 10 on the quantity, but it's close;

    the assumption of 12 char descrip doesn't work as things like 'MM OJ F1$1.59' is in the data, and it's shorter than my assumption... but a solid starting point i think:

    SELECT

    V1.* ,

    V2.*,

    DATALENGTH(V2.RawImport) As TheLen,

    SUBSTRING(V1.RawImport,CHARINDEX('#',V1.RawImport) +1,10) AS TheUPC,

    SUBSTRING(V2.RawImport,1,12) AS ItemDescrip,

    SUBSTRING(V2.RawImport,CHARINDEX('$',V2.RawImport)+1,30) AS ThePrice,

    REVERSE(V2.RawImport),

    SUBSTRING(REVERSE(V2.RawImport),CHARINDEX('$',REVERSE(V2.RawImport))+1,1)

    FROM tblDataSymRawImport V1

    INNER JOIN tblDataSymRawImport V2

    ON V1.pk + 1 = V2.pk

    WHERE CHARINDEX('#',V1.RawImport) = 1

    --must be ten digits after the #

    AND V1.RawImport LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Bill Tidwell (6/3/2011)


    When I say, "horizontal format", I mean one single record per scan. For example, in the raw-data file I get from the POS, everything is "single stacked" - the UPC number, for example, might be on row 873. The description of the item is on row 874, the quantity scanned on row 874, the scanned price on 875, etc. I need the entire scan event (not the entire transaction) needs to be in one record, in which the UPC is the first column, the item description column 2, the quantity scanned column 3, the scanned SRP column 4, etc.

    Bill

    Ah... whew! Got it. Thanks, Bill.

    I see you've got some good folks helping you since I asked my question. Now that I know what you're requirements are really, I might just join the party because I love these types of problems. Not sure how much I can add, at this point, though. You've got some heavy hitters working on this thread.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Bill, that helps tremendously.

    Now, I'm going to need your help understanding the components of the stream.

    First, I need to know where a record starts. Is it on the Date change, the register identification, or the clerk line?

    If you'll look below, you'll see I wrapped from date to date/reg. With reg being the final selection here, I'm assuming that the record goes from clerk through to register identification.

    Select '76',' 7:31AM Apr 1/11' UNION ALL

    Select '77','00-0000 001 REG 1 ' UNION ALL

    Select '78','#53677 CLERK ' UNION ALL

    Select '79','#1128400301 ' UNION ALL

    Select '80','JJS APPLE PIF1$0.50 ' UNION ALL

    Select '81','#1128400301 ' UNION ALL

    Select '82','JJS APPLE PIF1$0.50 ' UNION ALL

    Select '83','#2430004325 ' UNION ALL

    Select '84','LD BP BROWNIF1$3.00 ' UNION ALL

    Select '85','#2500005603 ' UNION ALL

    Select '86','MM OJ F1$1.59 ' UNION ALL

    Select '87','#1200010092 ' UNION ALL

    Select '88','FRAPP MOCHA F1$6.69 ' UNION ALL

    Select '89','#3700018506 ' UNION ALL

    Select '90','PRINGLES SOUF1$0.60 ' UNION ALL

    Select '91','#5410740100 ' UNION ALL

    Select '92','KIWI FRUIT 5F1$1.99 ' UNION ALL

    Select '93','CARD # ' UNION ALL

    Select '94',' ************3874' UNION ALL

    Select '95','REF# 0002' UNION ALL

    Select '96',' T O T A L $14.87' UNION ALL

    Select '97','FS ST $14.87' UNION ALL

    Select '98','EBT FSTEND $14.87' UNION ALL

    Select '99',' 7:32AM Apr 1/11' UNION ALL

    Select '100','00-0000 001 REG 1 '

    Next, in the data I quoted above, the record has a number of other UPCs on lines 83, 85, 89... I assume your result table is merely a partial result set, or was there something particular about these items that made them selectable where the rest were ignored?

    What's the starting Z60 do? Is that just the register tape/stream initializer to show a new series of records? If so, It doesn't make direct sense to me that the register identification completes a record, as the beginning of this process then only shows the 'end' of a sale transaction.

    Lines like 25 don't carry a UPC in front of them, but look like direct entry sales, like fruit. What do you want to have happen with these?

    ... I think you get the drift. If you just want us to show you how to yank out the UPC's and the line after it, that's actually pretty easy. You filter out like '%CLERK%', use anything starting with #, and use that line and join to itself on line +1 and pivot.

    What the real problem here is keeping the records associated intelligently. The more you can share with us about the business logic involved here, the more we can help you find a complete solution.


    - Craig Farrell

    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

  • Craig,

    As I mentioned in a previous post, this is an older system and, unfortunately, there is no documentation (that I can find) so we are making an educated guess about this.

    It seems to me that the date/ time line marks the beginning of a transaction. There are a number of things a cashier can do in a transaction: Log in, open the cash drawer, transfer money out of the till to the office, scan an item or items, open the cash drawer and change a $20 bill for a customer, ring in an open-department sale (this occurs for items that do not have a UPC - $1.25 entered on the 'Grocery Dept' key, etc.), etc. In the full data file (about 5,900 records) I am seeing consistency with the date/ time line marking the beginning of each transaction, regardless of transaction type.

    For transactions that are sale of merchandise, transactions are ended by:

    MDSE $9.49

    TAX2 $0.93

    T O T A L $10.42

    CASH $15.00

    CHANGE $4.58

    The 'MDSE' line shows the subtotal, then the tax then a grand total, followed by the type of tender (cash, check, credit card, food stamps, etc.) and change. The 'Change' line marks the end of the transaction.

    More later.

    BT

  • Craig,

    ...the record has a number of other UPCs on lines 83, 85, 89...

    I assume your result table is merely a partial result set, or was there something particular

    about these items that made them selectable where the rest were ignored?

    Nothing special about the items in the destination table - merely a few sample items.

    What's the starting Z60 do? Is that just the register tape/stream initializer to

    show a new series of records? If so, It doesn't make direct sense to me that the register

    identification completes a record, as the beginning of this process then only shows the 'end'

    of a sale transaction.

    The particular extraction that I am working with is an electronic journal - AKA a "Z" report. This goes back to the days of mechanical cash registers, which all had a "Z" key, used to read out sales and reset totals. Your estimation is correct - the "Z60" line merely identifies the type of output.

    Lines like 25 don't carry a UPC in front of them, but look like direct entry sales,

    like fruit. What do you want to have happen with these?

    I believe you are corrrect. We refer to this type of transaction as an "open-department ring". I would like for each open-dept. ring to have a row in the destination table. By the way - you will see lines containing what appears to be a very short UPC. These are PLU (price-lookup) numbers assigned to produce items. If you have ever used a self checkout at the grocery store, you must enter a PLU to ring up bananas, for example (PLU 4011), etc. I want to treat these just like full UPCs.

    What the real problem here is keeping the records associated intelligently.

    The more you can share with us about the business logic involved here, the more

    we can help you find a complete solution.

    Ultimately, I would like to be able to assign a unique number to each customer's ticket. A customer ticket is all records associated with each customer's order: The clerk's number, all sale events - either scans or open-dept rings, the subtotal, tax, grand total, tender type and amount and change given to the customer (if any). Each set of records comprising a customer's ticket will need to be assigned a unique number that will allow us to isolate all activity for any given ticket.

    My initial reason for initiating this post was to determine the best way to convert all these vertically-stacked records into a row-and-column table. We have gone much deeper into this project that I anticipated. I am new to this blog and the assistance I am seeing is nothing less than remarkable. Thanks so much.

    BT

  • Alright, finally got a little time to go delving into this.

    This end is end to end test code, and worked in 2k5 Express, so nothing funny's going on, though I do pull a few things out of thin air.

    Read through this end to end, then once or twice more. Make sure you understand all the moving parts involved in what I did to this, because that will let you expand on the concept. I messed around a little with your core tables, make sure to re-review the DDL for them.

    In particular, I tried to make sure you did as few passes as necessary to tackle this project. It's far from complete, I didn't even get near the payment calculations nor re-totalling to confirm accuracy, things like that. It will, however, give you the basic gist of what you're going to need to do to decompile the stream into components you can work with.

    Let me know what questions you have after you play with this a little bit. There's a lot of comments inline in the code.

    /****** Object: Table [dbo].[tblDataSymRawImport] Script Date: 06/03/2011 15:07:37 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDataSymRawImport]') AND type in (N'U'))

    DROP TABLE [dbo].[tblDataSymRawImport]

    GO

    IF OBJECT_ID('ReceiptHeader') IS NOT NULL

    DROP TABLE ReceiptHeader

    /****** Object: Table [dbo].[tblDataSymRawImport] Script Date: 06/03/2011 15:07:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    --=========Create the table================================================

    CREATE TABLE [dbo].[tblDataSymRawImport](

    [pk] [int] IDENTITY(1,1) NOT NULL,

    [RawImport] [varchar](25) NULL

    ) ON [PRIMARY]

    GO

    SET DATEFORMAT DMY

    GO

    SET ANSI_PADDING OFF

    GO

    --Insert the data into the table ==========================================

    SET IDENTITY_INSERT dbo.tblDataSymRawImport ON

    INSERT INTO dbo.tblDataSymRawImport(pk, RawImport)

    Select '1',';Z60' UNION ALL

    Select '2','#53670-6 07:14 Z 090' UNION ALL

    Select '3',' 7:14AM Apr 1/11' UNION ALL

    Select '4','00-0000 001 REG 1 ' UNION ALL

    Select '5','#53671 CLERK ' UNION ALL

    Select '6','CARD # ' UNION ALL

    Select '7',' ************6056' UNION ALL

    Select '8','MANUALLY ENTERED ' UNION ALL

    Select '9','REF# 0000' UNION ALL

    Select '10',' 7:17AM Apr 1/11' UNION ALL

    Select '11','00-0000 001 REG 1 ' UNION ALL

    Select '12','#53672 CLERK ' UNION ALL

    Select '13','#3600019586 ' UNION ALL

    Select '14','POISE PADS X-2$7.39 ' UNION ALL

    Select '15','CARD # ' UNION ALL

    Select '16',' ************3019' UNION ALL

    Select '17','REF# 0001' UNION ALL

    Select '18','MDSE $7.39' UNION ALL

    Select '19','TAX2 $0.72' UNION ALL

    Select '20',' T O T A L $8.11' UNION ALL

    Select '21','DEBIT CARD $8.11' UNION ALL

    Select '22',' 7:18AM Apr 1/11' UNION ALL

    Select '23','00-0000 001 REG 1 ' UNION ALL

    Select '24','#53673 CLERK ' UNION ALL

    Select '25','GROCERY F1$3.99 ' UNION ALL

    Select '26','GROCERY F1$11.00 ' UNION ALL

    Select '27','FOOD COUPON F-1.00 ' UNION ALL

    Select '28','#2430004120 ' UNION ALL

    Select '29','LD NUTTY BARF1$1.25 ' UNION ALL

    Select '30','#2430004130 ' UNION ALL

    Select '31','LD SWISS CK F1$1.59 ' UNION ALL

    Select '32','#2430004101 ' UNION ALL

    Select '33','LD OATMEAL CF1$1.25 ' UNION ALL

    Select '34','#2430004120 ' UNION ALL

    Select '35','LD NUTTY BARF1$1.25 ' UNION ALL

    Select '36','MDSE $19.33' UNION ALL

    Select '37','TAX1 $1.68' UNION ALL

    Select '38',' T O T A L $21.01' UNION ALL

    Select '39','CASH $25.01' UNION ALL

    Select '40','CHANGE $4.00' UNION ALL

    Select '41',' 7:19AM Apr 1/11' UNION ALL

    Select '42','00-0000 001 REG 1 ' UNION ALL

    Select '43','#53674 CLERK ' UNION ALL

    Select '44','#5000041250 ' UNION ALL

    Select '45','CARN COFMAT F1$2.59 ' UNION ALL

    Select '46','MDSE $2.59' UNION ALL

    Select '47','TAX1 $0.21' UNION ALL

    Select '48',' T O T A L $2.80' UNION ALL

    Select '49','CASH $10.00' UNION ALL

    Select '50','CHANGE $7.20' UNION ALL

    Select '51',' 7:20AM Apr 1/11' UNION ALL

    Select '52','00-0000 001 REG 1 ' UNION ALL

    Select '53','#53675 CLERK ' UNION ALL

    Select '54','#4369505632 ' UNION ALL

    Select '55','CRSSNT PKT HF1$2.50 ' UNION ALL

    Select '56','#1200000134 ' UNION ALL

    Select '57','D M DEW F1$1.59 ' UNION ALL

    Select '58','#2200000667 ' UNION ALL

    Select '59','JUICY FRUIT F2$1.09 ' UNION ALL

    Select '60','MDSE $5.18' UNION ALL

    Select '61','TAX1 $0.34' UNION ALL

    Select '62','TAX2 $0.11' UNION ALL

    Select '63',' T O T A L $5.63' UNION ALL

    Select '64','CASH $20.00' UNION ALL

    Select '65','CHANGE $14.37' UNION ALL

    Select '66',' 7:26AM Apr 1/11' UNION ALL

    Select '67','00-0000 001 REG 1 ' UNION ALL

    Select '68','#53676 CLERK ' UNION ALL

    Select '69','#3400000241 ' UNION ALL

    Select '70','HRSH ALMOND F2$0.79 ' UNION ALL

    Select '71','MDSE $0.79' UNION ALL

    Select '72','TAX2 $0.08' UNION ALL

    Select '73',' T O T A L $0.87' UNION ALL

    Select '74','CASH $5.00' UNION ALL

    Select '75','CHANGE $4.13' UNION ALL

    Select '76',' 7:31AM Apr 1/11' UNION ALL

    Select '77','00-0000 001 REG 1 ' UNION ALL

    Select '78','#53677 CLERK ' UNION ALL

    Select '79','#1128400301 ' UNION ALL

    Select '80','JJS APPLE PIF1$0.50 ' UNION ALL

    Select '81','#1128400301 ' UNION ALL

    Select '82','JJS APPLE PIF1$0.50 ' UNION ALL

    Select '83','#2430004325 ' UNION ALL

    Select '84','LD BP BROWNIF1$3.00 ' UNION ALL

    Select '85','#2500005603 ' UNION ALL

    Select '86','MM OJ F1$1.59 ' UNION ALL

    Select '87','#1200010092 ' UNION ALL

    Select '88','FRAPP MOCHA F1$6.69 ' UNION ALL

    Select '89','#3700018506 ' UNION ALL

    Select '90','PRINGLES SOUF1$0.60 ' UNION ALL

    Select '91','#5410740100 ' UNION ALL

    Select '92','KIWI FRUIT 5F1$1.99 ' UNION ALL

    Select '93','CARD # ' UNION ALL

    Select '94',' ************3874' UNION ALL

    Select '95','REF# 0002' UNION ALL

    Select '96',' T O T A L $14.87' UNION ALL

    Select '97','FS ST $14.87' UNION ALL

    Select '98','EBT FSTEND $14.87' UNION ALL

    Select '99',' 7:32AM Apr 1/11' UNION ALL

    Select '100','00-0000 001 REG 1 '

    SET IDENTITY_INSERT dbo.tblDataSymRawImport OFF

    GO

    -- We need someplace to put our findings...

    ALTER TABLE tblDataSymRawImport

    ADD TicketGroupGUID uniqueidentifier, LogicRule VARCHAR(50)

    GO

    -- We're going to need this for the cascading update.

    CREATE CLUSTERED INDEX idx_tdsri ON tblDataSymRawImport (pk)

    GO

    --Assign rules:

    UPDATE tblDataSymRawImport

    SET LogicRule =

    -- Case will short circuit, meaning that the first one found that meets the

    -- requirements will be used. Lower priority items can be inserted further down in the list.

    -- IE: If # and a number indicate a UPC, and # alone indicates something else, we can do that.

    -- Format's wrong, I can't find the right way to pick it up for an isdate.

    --CASE WHEN isdate(ltrim( rawimport)) = 1 THEN 'TicketStart'

    CASE WHEN charindex( '/', RawImport) > 0 THEN 'TicketStart'

    WHEN CHARINDEX( 'REG', RawImport) > 0 THEN 'Register Identification'

    --Note that clerk starts with a #, so leave this higher in the list.

    WHEN CHARINDEX( 'CLERK', RawImport) > 0 THEN 'Clerk Identification'

    WHEN LEFT( rawimport, 1) = '#' THEN 'UPC Row'

    -- Identify the total out rows.

    WHEN CHARINDEX( 'MDSE', Rawimport) > 0 THEN 'Total - Merchandise'

    WHEN CHARINDEX( 'Tax1', Rawimport) > 0 THEN 'Total - Tax1'

    WHEN CHARINDEX( 'Tax2', Rawimport) > 0 THEN 'Total - Tax2'

    WHEN CHARINDEX( 'T O T A L', Rawimport) > 0 THEN 'Total - Grand Total'

    WHEN CHARINDEX( 'DEBIT CARD', RawImport) > 0 THEN 'Payment Method - Debit Card'

    WHEN CHARINDEX( 'CASH', RawImport) > 0 THEN 'Payment Method - Cash'

    WHEN CHARINDEX( 'CHANGE', RawImport) > 0 THEN 'Payment Method - Change returned'

    WHEN CHARINDEX( 'CARD #', RawImport) > 0 THEN 'Payment Method - Credit Card'

    WHEN CHARINDEX( '****', RawImport) > 0 THEN 'Payment Method - Hidden Card Identifier'

    WHEN CHARINDEX( 'FOOD COUPON', RawImport) > 0 THEN 'Payment Method - Food Coupon'

    WHEN CHARINDEX( 'FOOD COUPON', RawImport) > 0 THEN 'Payment Method - Food Coupon'

    WHEN CHARINDEX( 'FS ST', RawImport) > 0 THEN 'N/A'

    WHEN CHARINDEX( 'EBT FSTEND', RawImport) > 0 THEN 'N/A'

    -- This is the last of the detections we want to do.

    WHEN CHARINDEX( '$', RawImport) > 0 THEN 'Money Row'

    ELSE 'N/A'

    END

    GO

    --SELECT * FROM dbo.tblDataSymRawImport

    GO

    -- Cascading update time.

    DECLARE @TicketGroupGUID uniqueidentifier,

    @Sequence INT,

    @pk INT

    SELECT @Sequence = 0,

    @TicketGroupGUID = NEWID()

    -- Safety table came about via discussions between Jeff Moden, Wayne Sheffield, Paul White, and Hugo Kornelis

    -- Links: http://www.sqlservercentral.com/Forums/Topic802558-203-5.aspx#bm981258

    -- http://www.sqlservercentral.com/Forums/Topic802558-203-4.aspx#bm980118

    -- Best known rules for Cascading updates... or the Quirky Update: http://www.sqlservercentral.com/articles/T-SQL/68467/

    -- The reason to go with this is it's a single pass to update the entire table, one shot.

    ;WITH SafetyTable

    AS (SELECT Sequence = ROW_NUMBER() OVER (ORDER BY pk ASC),

    pk,

    LogicRule,

    TicketGroupGUID

    FROM tblDataSymRawImport (TABLOCKX)

    )

    UPDATESafetyTable

    SET@TicketGroupGUID = TicketGroupGUID = CASE WHEN LogicRule = 'TicketStart'

    THEN NEWID()

    ELSE @TicketGroupGUID

    END,

    @pk = pk,

    @Sequence = CASE WHEN Sequence = @Sequence + 1 THEN Sequence ELSE 1/0 --Break the query

    END

    OPTION ( MAXDOP 1)

    GO

    --Clean ups go here.

    UPDATE tblDataSymRawImport

    SETRawImport = RTRIM( LTRIM(RawImport) )

    GO

    -- Clean up nonUPC'd grocer items

    UPDATEri1

    SETLogicRule = 'Grocer Row'

    FROM

    tblDataSymRawImport AS ri1

    LEFT JOIN

    tblDataSymRawImport AS ri2

    ONri1.pk -1 = ri2.pk

    AND ri2.LogicRule = 'UPC Row'

    WHERE

    ri1.LogicRule = 'Money Row'

    AND ri2.pk is null

    GO

    --SELECT * FROM dbo.tblDataSymRawImport where logicrule = 'grocer row'

    GO

    -- Now, let's get the ticket 'header'

    CREATE TABLE ReceiptHeader

    ( rhID INT IDENTITY( 1, 1) NOT NULL,

    TicketGroupGUID UNIQUEIDENTIFIER NOT NULL, -- So we can reassocate other records to the real surrogate key later

    RegisterIdentification VARCHAR(15) NULL,

    ClerkIdentification INT NULL,

    DateOfTicket DATETIME NULL)

    -- I'll leave the rest to you once we get you through the concepts

    INSERT INTO ReceiptHeader (TicketGroupGUID, RegisterIdentification, ClerkIdentification, DateOfTicket)

    SELECT

    t1.TicketGroupGuid,

    --Grab everything left of the second space

    LEFT( t2.RawImport, CHARINDEX( ' ', t2.RawImport, CHARINDEX( ' ', t2.RawImport) + 1)),

    SUBSTRING( t3.RawImport, 2, CHARINDEX( ' ', t3.RawImport)-1),

    -- Need to turn this: ' 7:14AM Apr 1/11' into this: 20110401 07:14:00AM

    '20' + RIGHT( t1.RawImport,2)

    + right( '00' + CONVERT( VARCHAR(20), Month( substring( t1.RawImport, CHARINDEX( ' ', t1.RawImport)+1, CHARINDEX( ' ', t1.RawImport, CHARINDEX( ' ', t1.RawImport)+1) - CHARINDEX( ' ', t1.RawImport)) + '1 1900')), 2)

    + RIGHT( '00' + SUBSTRING( t1.RawImport, CHARINDEX( ' ', t1.rawimport, charindex(' ', t1.rawimport)+1) + 1, CHARINDEX( '/', t1.rawimport) - CHARINDEX( ' ', t1.rawimport, charindex(' ', t1.rawimport)+1) - 1),2)

    + ' '

    + CASE WHEN CHARINDEX( 'PM', t1.RawImport) > 0 THEN LEFT( t1.RawImport, CHARINDEX('PM', t1.RawImport) - 1) + ':00PM' ELSE LEFT( t1.RawImport, CHARINDEX('AM', t1.RawImport) - 1) + ':00AM' END

    FROM

    (SELECT TicketGroupGUID, RawImport FROM tblDataSymRawImport

    WHERE

    LogicRule = 'TicketStart'

    ) AS t1

    LEFT JOIN

    dbo.tblDataSymRawImport AS t2

    ONt1.TicketGroupGUID = t2.TicketGroupGUID

    AND t2.LogicRule = 'Register Identification'

    LEFT JOIN

    dbo.tblDataSymRawImport AS t3

    ONt1.TicketGroupGUID = t3.TicketGroupGUID

    AND t3.LogicRule = 'Clerk Identification'

    OPTION ( FORCE ORDER)

    -- In general, I avoid force order until you know what it's going to affect.

    -- In this case though it's necessary to forcethe where clause to trigger first,

    -- otherwise the charindexes() could find out of bounds values

    -- and come up with an error because you cannot control order of executions in the query

    -- in any other way.

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDataSymScanDetail]') AND type in (N'U'))

    DROP TABLE [dbo].[tblDataSymScanDetail]

    GO

    CREATE TABLE [dbo].[tblDataSymScanDetail](

    [pk] [int] IDENTITY(1,1) NOT NULL,

    rhID INT NOT NULL,

    [ItemUPC] BIGINT NULL,

    --[RawFilePk] [int] NULL,

    [ItemDesc] [nvarchar](25) NULL,

    Price DECIMAL( 20,4) NULL

    ) ON [PRIMARY]

    GO

    --SELECT * FROM dbo.tblDataSymRawImport

    insert into tblDataSymScanDetail (rhid, ItemUPC, ItemDesc, Price)

    SELECT

    rh.rhID,

    CASE WHEN ri.LogicRule = 'Grocer Row' THEN -1

    WHEN ri.LogicRule = 'UPC Row' THEN RIGHT( RTRIM( ri.RawImport), LEN( ri.RawImport) -1)

    ELSE CONVERT(BIGINT, 0 )

    END AS ItemUPC,

    CASE WHEN ri.LogicRule = 'Grocer Row' THEN 'Grocery'

    WHEN ri.LogicRule = 'UPC Row' THEN CONVERT( VARCHAR(50), LEFT( riDesc.RawImport, CHARINDEX( '$', riDesc.RawImport) - 1))

    ELSE 'N/A'

    END AS ItemDesc,

    CASE WHEN ri.LogicRule = 'Grocer Row' THEN RIGHT( ri.RawImport, LEN( ri.RawImport) - CHARINDEX( '$', ri.RawImport))

    WHEN ri.LogicRule = 'UPC Row' THEN RIGHT( riDesc.RawImport, LEN( ridesc.RawImport) - CHARINDEX( '$', ridesc.RawImport))

    ELSE '' --Really, 0., but it was whining about conversions and I didn't want to force order again, and '' will convert to 0.00

    END AS Price

    FROM

    ReceiptHeader AS rh

    JOIN

    tblDataSymRawImport AS ri

    ONrh.TicketGroupGUID = ri.TicketGroupGUID

    LEFT JOIN

    tblDataSymRawImport AS riDesc

    ONri.pk + 1 = riDesc.pk

    AND ri.LogicRule = 'UPC Row'

    WHERE

    ri.LogicRule IN ( 'UPC Row', 'Grocer Row')

    GO

    SELECT * FROM tblDataSymScanDetail

    GO


    - Craig Farrell

    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

  • Craig,

    Thanks so much. I might be a couple of days before I can focus on this, since the week has reset back to Monday.

    I scanned through it and I think I get the concept from 30,000 ft, but I want to go through it line by line and understand th logic.

    I also want to load it into the database.

    Thanks again,

    Bill T

  • Bill Tidwell (6/6/2011)


    Craig,

    Thanks so much. I might be a couple of days before I can focus on this, since the week has reset back to Monday.

    I scanned through it and I think I get the concept from 30,000 ft, but I want to go through it line by line and understand th logic.

    I also want to load it into the database.

    Thanks again,

    Bill T

    My pleasure and no problem, Bill. It's a lot to consume and I know how Mondays go. 🙂


    - Craig Farrell

    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

  • Another option is:

    -- import

    truncate table dbo.Importedtable

    INSERT INTO dbo.Importedtable(data)

    Values

    ('x'),

    ('y'),

    ('#1234567891'),

    ('Descript Prod 1'),

    ('12.34'),

    ('z'),

    ('a'),

    ('#1234567892'),

    ('Descript Prod 2'),

    ('22.44'),

    ('#1234567893'),

    ('Descript Prod 3'),

    ('33.3');

    select UqDId.Id, UqDId.IRefId, UqDId.IDescription, i3.data price

    from dbo.Importedtable i3

    inner join

    (select Uqid.id, Uqid.IRefId, i2.data IDescription

    from dbo.Importedtable i2

    inner join

    (select i.id, Right(Rtrim(Ltrim(i.data)), 10) irefId

    from dbo.Importedtable i

    where Left(ltrim(i.data), 1) = '#'

    and Isnumeric(Right(Rtrim(Ltrim(i.data)), 10)) = 1

    ) Uqid

    on i2.id = uqid.id + 1

    ) UqDId

    on i3.id = UqDId.id + 2

    where Isnumeric(Rtrim(Ltrim(i3.data))) = 1

    results

    idIrefId Idescription Price

    31234567891Descript Prod 112.34

    81234567892Descript Prod 222.44

    111234567893Descript Prod 333.3

    This may not be fastest, but it has the simplest code to understand.

    It does nothing to address mistakes, but it may not show that row.

  • Craig,

    The quotations in your footer have peaked my interest. Who is Douglas Adams?

    BT

  • Bill Tidwell (6/6/2011)


    Craig,

    The quotations in your footer have peaked my interest. Who is Douglas Adams?

    BT

    http://en.wikipedia.org/wiki/Douglas_Adams

    Author to "The Hitchhiker's Guide to the Galaxy".


    - Craig Farrell

    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

  • Craig, I was not attempting to quote anyone. i was just commenting on what my answer did not cover.

    Someone else addressed the quote that it appears to remind you of.

  • m_cg (6/6/2011)


    Craig, I was not attempting to quote anyone. i was just commenting on what my answer did not cover.

    Someone else addressed the quote that it appears to remind you of.

    Huh? If you look at the bottom of my posts, there's a set of quotes by Douglas Adams, which is what Bill was asking about. It's my signature line, that's all.

    I'm not sure what you mean by your post but there has been nothing offensive by you.


    - Craig Farrell

    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

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply