June 3, 2011 at 3:15 pm
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
June 3, 2011 at 3:22 pm
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
June 3, 2011 at 3:30 pm
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
June 3, 2011 at 3:50 pm
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
June 3, 2011 at 4:53 pm
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.
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
June 4, 2011 at 6:48 am
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
June 4, 2011 at 7:22 am
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
June 5, 2011 at 9:44 pm
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://qa.sqlservercentral.com/Forums/Topic802558-203-5.aspx#bm981258
-- http://qa.sqlservercentral.com/Forums/Topic802558-203-4.aspx#bm980118
-- Best known rules for Cascading updates... or the Quirky Update: http://qa.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)
)
UPDATE SafetyTable
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
SET RawImport = RTRIM( LTRIM(RawImport) )
GO
-- Clean up nonUPC'd grocer items
UPDATE ri1
SET LogicRule = 'Grocer Row'
FROM
tblDataSymRawImport AS ri1
LEFT JOIN
tblDataSymRawImport AS ri2
ON ri1.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
ON t1.TicketGroupGUID = t2.TicketGroupGUID
AND t2.LogicRule = 'Register Identification'
LEFT JOIN
dbo.tblDataSymRawImport AS t3
ON t1.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
ON rh.TicketGroupGUID = ri.TicketGroupGUID
LEFT JOIN
tblDataSymRawImport AS riDesc
ON ri.pk + 1 = riDesc.pk
AND ri.LogicRule = 'UPC Row'
WHERE
ri.LogicRule IN ( 'UPC Row', 'Grocer Row')
GO
SELECT * FROM tblDataSymScanDetail
GO
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
June 6, 2011 at 6:40 am
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
June 6, 2011 at 10:08 am
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. 🙂
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
June 6, 2011 at 1:54 pm
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
id IrefId Idescription Price
3 1234567891 Descript Prod 1 12.34
8 1234567892 Descript Prod 2 22.44
11 1234567893 Descript Prod 3 33.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.
June 6, 2011 at 2:40 pm
Craig,
The quotations in your footer have peaked my interest. Who is Douglas Adams?
BT
June 6, 2011 at 2:50 pm
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".
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
June 6, 2011 at 4:41 pm
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.
June 6, 2011 at 5:00 pm
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.
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