February 16, 2009 at 12:15 pm
Erik. I am not sure what you mean by the % in the columns. From your sample grid (second post) it looks like you have the cost hard-coded for each combination of length and width for the blinds then two possible adders - one for oversize and another for rellease.
Unless the grid you posted is totally wrong, then this would work (I trust you can add the primary and foreign keys on your own - if not, let me know). Otherwise, please post the exact schema you will use, the test input and a statement of your expected results.
Thank you
Toni
declare @product table (prodid int identity(1,1), prodname varchar(8))
declare @prodmeasure table (prodid int,
w_low int, w_hi int, h_low int, h_hi int,
cost decimal(5,2), oversize int, rellease int)
insert into @product
select 'Blinds' union all
select 'Doors' union all
select 'Boards'
insert into @prodmeasure (prodid,w_low, w_hi, h_low, h_hi, cost, oversize, rellease)
select 1,0,12,0,36,301,0,0 union all
select 1,0,12,37,42,323,0,0 union all
select 1,13,24,0,36,281,0,0 union all
select 1,13,24,37,42,303,0,0 union all
select 1,25,30,0,36,317,0,89 union all
select 1,25,30,37,42,339,2,89
select * from @product
select * from @prodmeasure
order by w_low,w_hi
declare @orderstbl table (orderid int identity(1,1),prodid int, width int, ht int)
insert into @orderstbl
select 1,12,36 union all
select 1,12,42 union all
select 1,24,36 union all
select 1,24,42 union all
select 1,30,36 union all
select 1,30,42
select * from @orderstbl
select orderid, ot.prodid, width, ht, (cost + oversize + rellease) as final_price
from @product pr
join @prodmeasure pm on pr.prodid = pm.prodid
join @orderstbl ot on ot.prodid = pm.prodid
where ot.width between w_low and w_hi and ot.ht between h_low and h_hi
order by orderid
February 16, 2009 at 12:20 pm
AFCC Inc. Com (2/16/2009)
Ok now that I am looking at this ..I have to remember that the price is computed from the .% in the length width columns. SO my posted schema is wrong.
I do not need to have any visible prices stored because all the prices are computed from the .% of the chosen width length..
THIS IS MADNESSSSSS....
Looking at the gif chart, I'd have to say that's just not possible. For example, there is a bit of an extra charge for widths 12 or fewer inches. Then, the price actually goes down for 24 inches and starts back up from there.
--Jeff Moden
February 16, 2009 at 5:31 pm
I just downloaded a trial version of ABBYY Fine Reader and that product actually let me scan that grid and place the data into an excel file for me..
From there it is as easy as import from sql.... "Holy Molly..."
I have attached the excel document for others to see how easy it is to import grid data from a phycial paper document and scanner.
Dam again!
February 16, 2009 at 5:55 pm
Hey Toni,
Is the w_low, w_hi, h_low, h_hi, for better performance?
Erik
Dam again!
February 16, 2009 at 9:56 pm
Hey you guys I have just about got this thing down...
Just need a little more help..
What I am at now is when I scan that document in and import the data to sql from the Excel file I have the table / grid just like i the image that I post. So Now I need to get it into those width height columns.
I have got it scaled down as simple as I can with variable tables. When this is right I will move over to real tables.
**After this is working I need to figure out a way to add in the low_width and low_height
From what I can tell I need to add a dynamic sql at one location to gain access to the current WidthTO column
--Set current WidthTO ****Need dynamic sql****
SELECT @CurrentWidthTO = @ColCnt + 1 FROM @WidthRowTbl WHERE WID = @ColCnt + 1
================================================================
CREATE TABLE [dbo].[Sheet1](
[1] [nvarchar](255) NULL,
[2] [nvarchar](255) NULL,
[3] [nvarchar](255) NULL,
[4] [nvarchar](255) NULL,
[5] [nvarchar](255) NULL,
[6] [nvarchar](255) NULL,
[7] [nvarchar](255) NULL,
[8] [nvarchar](255) NULL,
[9] [nvarchar](255) NULL,
[10] [nvarchar](255) NULL,
[11] [nvarchar](255) NULL,
[12] [nvarchar](255) NULL,
[13] [nvarchar](255) NULL,
[14] [nvarchar](255) NULL
) ON [PRIMARY]
INSERT INTO dbo.Sheet1
SELECT 0,12,24,28,32,36,42,48,54,60,66,72,84,96 UNION ALL
SELECT 30,95,80,93,107,120,140,160,179,199,219,239,281,351 UNION ALL
SELECT 36,112,97,110,127,143,167,190,215,239,262,289,337,414 UNION ALL
SELECT 42,118,103,120,137,153,180,203,232,255,280,309,361,441 UNION ALL
SELECT 48,132,117,137,157,173,203,233,265,292,322,352,411,501 UNION ALL
SELECT 54,142,127,147,167,187,220,250,285,315,345,379,444,534 UNION ALL
SELECT 60,155,140,163,187,210,243,280,315,352,385,419,491,591 UNION ALL
SELECT 66,158,143,167,190,213,250,287,322,359,395,432,504,604 UNION ALL
SELECT 73,162,147,170,197,220,257,293,332,369,405,442,517,621 UNION ALL
SELECT 78,172,157,183,210,233,273,313,355,392,432,472,551,661 UNION ALL
SELECT 84,185,170,197,223,253,293,337,382,422,465,509,594,707 UNION ALL
SELECT 90,195,180,210,240,270,317,360,409,452,499,542,637,757 UNION ALL
SELECT 96,208,193,223,257,290,337,387,435,482,532,579,677,804 UNION ALL
SELECT 108,232,217,253,290,323,380,433,489,542,599,652,761,901 UNION ALL
SELECT 120,255,240,280,320,360,420,480,542,65,665,725,847,997 UNION ALL
GO
----------------------------------------------------------------------
DECLARE @ColCnt INT;
SET @ColCnt = 1; --Predetermined number of columns is 14
DECLARE @CurrentHeightTO INT, @CurrentWidthTO INT;
--
DECLARE @WidthHeightTbl TABLE(W_H_ID int identity(1,1),[1] INT,[2] INT,[3] INT, [4] INT, [5] INT,
[6] INT, [7] INT, [8] INT, [9] INT, [10] INT,
[11] INT, [12] INT, [13] INT, [14] INT)
--
DECLARE @WidthRowTbl TABLE(WID int identity(1,1),[1] INT,[2] INT,[3] INT, [4] INT, [5] INT,
[6] INT, [7] INT, [8] INT, [9] INT, [10] INT,
[11] INT, [12] INT, [13] INT, [14] INT)
--
DECLARE @HeightWidthColsTbl TABLE(Hcol INT, Wcol INT, CostCol NVARCHAR(255))
--
DECLARE @HeightColTbl TABLE( HID int identity(1,1),Hcol INT)
--
DECLARE @TotalRowCnt INT;
--
--Fill WidthHeight Table
INSERT INTO @WidthHeightTbl
SELECT * FROM dbo.Sheet1
--
--Fill Width Row
INSERT INTO @WidthRowTbl -- Fill the first row that is the WidthTO row
SELECT TOP(1)dbo.Sheet1.* FROM dbo.Sheet1
--
--Fill Height Col
INSERT INTO @HeightColTbl
SELECT [1] FROM @WidthHeightTbl;
--
SELECT @TotalRowCnt = COUNT(*) FROM @WidthHeightTbl
WHILE(@TotalRowCnt > 0)
BEGIN
--SET current heightTO
SELECT @CurrentHeightTO = Hcol FROM @HeightColTbl WHERE HID = @TotalRowCnt + 1
WHILE(@ColCnt < 15 )
BEGIN
--Set current WidthTO ****Need dynamic sql****
SELECT @CurrentWidthTO = @ColCnt + 1 FROM @WidthRowTbl WHERE WID = @ColCnt + 1
--
INSERT INTO @HeightWidthColsTbl
SELECT @CurrentHeightTO, @CurrentWidthTO,
WHERE WID = @CurrentHeightTO AND Wcol = @CurrentWidthTO
SET @ColCnt = @ColCnt + 1
END
SET @ColCnt = 1
SET @TotalRowCnt = @TotalRowCnt + 1
END
Let me know if I missed something...
I think I need to add that dynamic sql unless you guys have a better idea.
Erik
Dam again!
February 17, 2009 at 9:31 am
Come to find out the window treatment supplier has this grid in an excel format.. Oh well,, live and learn..
Did I post enough info to loop that table and group the width, height, cost data into the correct columns.?
Erik
Dam again!
February 17, 2009 at 10:56 am
AFCC Inc. Com (2/17/2009)
Come to find out the window treatment supplier has this grid in an excel format.. Oh well,, live and learn..Did I post enough info to loop that table and group the width, height, cost data into the correct columns.?
Erik
I wouldn't "loop" that table... I'd normalize it, instead... thing "NVP" table (Name/Value Pair).
--Jeff Moden
February 17, 2009 at 10:56 am
I am going to defer to Jeff. I don't think I am anywhere near the same mind set as you are on this one. I think you are now trying to just build test data?
February 17, 2009 at 11:26 am
What I am doing is creating a table that has one row, and this row contains the WidthTo columns
Then I create a HeightTable and this table contains the all the rows in the first column which is all the heights
Then I loop the table and for each row, I then loop each column for that row.
Then I am attempting to insert the matched WidthTo and HeightTo to the Cost for those two coordinates. Then insert that into the WidthToHeidthToTble.
For each row that I loop I loop each cell in that row, each cell coordinates the current cost for the current WidthTo and HeightTO coordinates.
The problem here is that I most likely got the right idea I just do not know the correct terminology to ask for help.
----------
Maybe it is that I need to take the data from a standard table and create a two dimensional table with it..
I am not sure how I would normalize this type of table..
----------------
Here is another way... I need to create a utility script that will allow me to process data from an imported excel file.
The data above from the dbo.Sheet table is the exact replica of the data from the excel file.
I need to process this imported data table so that I can use this data in the tables you guys have showed me how to create.
1. Got Table full of data
2. Need to loop each row
3. Need to loop each cell in each row
4. Need to store current WidthToCol val, HeightToCol val, and cost val for those coordinates in the tables you guys showed me how to create.
---The last most script that I posted is for testing only.... If I get that I can take that concept over to the physical data tables you guys showed me how to create.
--I know this is not kindergarten stuff here....
Thanks a lot for the help...
Erik
Dam again!
February 17, 2009 at 6:33 pm
AFCC Inc. Com (2/17/2009)
What I am doing is creating a table that has one row, and this row contains the WidthTo columnsThen I create a HeightTable and this table contains the all the rows in the first column which is all the heights
Then I loop the table and for each row, I then loop each column for that row.
You keep saying "loop"... that's "Death by SQL". Please read the following article...
http://qa.sqlservercentral.com/articles/TSQL/62867/
especially the section where it says...
"You've seen it... some poor slob posts that (s)he has a table and it has a CSV column in it. "How do you join to it?", they ask. The correct answer, of course, is to normalize the table"
Don't skimp... read the whole article and try the examples...
--Jeff Moden
February 18, 2009 at 3:05 pm
Very nice article.... I can say that I truly got something valuable from that..
I need a little help here..
I need to a way to get that Width Column value into table
CREATE TABLE [dbo].[Sheet1](
[1] [nvarchar](255) NULL,
[2] [nvarchar](255) NULL,
[3] [nvarchar](255) NULL,
[4] [nvarchar](255) NULL,
[5] [nvarchar](255) NULL,
[6] [nvarchar](255) NULL,
[7] [nvarchar](255) NULL,
[8] [nvarchar](255) NULL,
[9] [nvarchar](255) NULL,
[10] [nvarchar](255) NULL,
[11] [nvarchar](255) NULL,
[12] [nvarchar](255) NULL,
[13] [nvarchar](255) NULL,
[14] [nvarchar](255) NULL
) ON [PRIMARY]
INSERT INTO dbo.Sheet1
SELECT 0,12,24,28,32,36,42,48,54,60,66,72,84,96 UNION ALL
SELECT 30,95,80,93,107,120,140,160,179,199,219,239,281,351 UNION ALL
SELECT 36,112,97,110,127,143,167,190,215,239,262,289,337,414 UNION ALL
SELECT 42,118,103,120,137,153,180,203,232,255,280,309,361,441 UNION ALL
SELECT 48,132,117,137,157,173,203,233,265,292,322,352,411,501 UNION ALL
SELECT 54,142,127,147,167,187,220,250,285,315,345,379,444,534 UNION ALL
SELECT 60,155,140,163,187,210,243,280,315,352,385,419,491,591 UNION ALL
SELECT 66,158,143,167,190,213,250,287,322,359,395,432,504,604 UNION ALL
SELECT 73,162,147,170,197,220,257,293,332,369,405,442,517,621 UNION ALL
SELECT 78,172,157,183,210,233,273,313,355,392,432,472,551,661 UNION ALL
SELECT 84,185,170,197,223,253,293,337,382,422,465,509,594,707 UNION ALL
SELECT 90,195,180,210,240,270,317,360,409,452,499,542,637,757 UNION ALL
SELECT 96,208,193,223,257,290,337,387,435,482,532,579,677,804 UNION ALL
SELECT 108,232,217,253,290,323,380,433,489,542,599,652,761,901 UNION ALL
SELECT 120,255,240,280,320,360,420,480,542,65,665,725,847,997 UNION ALL
GO
--===== Simulate a passed parameter
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Height VARCHAR(3),
Width VARCHAR(3),
[Value] VARCHAR(2000) --The string value of the element
)
INSERT INTO @Elements
SELECT X.HeightColVal, 'Need to store current width here',
SUBSTRING(X.CurrentWidthRow,N+1,CHARINDEX(',',X.CurrentWidthRow,N+1)-N-1)
FROM(
SELECT N 'HeightColVal',(
SELECT ','+[2]+','+[3]+','+[4]+','+[5]+','+[6]+','+[7]+','+[8]+',' +
[9]+','+[10]+','+[11]+','+[12]+','+[13]+','+[14]+','
FROM dbo.Sheet1
WHERE [1] = N)'CurrentWidthRow'
FROM dbo.Tally
WHERE N < 200)X,dbo.Tally
WHERE X.CurrentWidthRow IS NOT NULL
AND
N < LEN(X.CurrentWidthRow)
AND SUBSTRING(X.CurrentWidthRow,N,1) = ','
SELECT * FROM @Elements
ORDER BY Height desc
Thanks...
Dam again!
February 19, 2009 at 12:32 pm
Hey Jeff,
Did you have any ideas for me on this one...?
Or another article that will help me?
Thanks
Dam again!
February 19, 2009 at 10:28 pm
Here is my first finish product...
Will have to just refine this over time, and maybe people on here will jump in and share their better experiences, but for now this works and my brain hurts.... 😉
===================================================================================
--
----===== Create and populate the Tally table on the fly
-- SELECT TOP 1000
-- IDENTITY(INT,1,1) AS N
-- INTO dbo.Tally
-- FROM Master.dbo.SysColumns sc1,
-- Master.dbo.SysColumns sc2
IF EXISTS
(SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[WidthHeight]'))
DROP TABLE [dbo].[WidthHeight]
------------------------------------------
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1),
Height VARCHAR(3),
Width VARCHAR(3),
[Value] VARCHAR(2000)
)
INSERT INTO @Elements
SELECT X.HeightColVal, 0,
SUBSTRING(X.CurrentWidthRow,N+1,CHARINDEX(',',X.CurrentWidthRow,N+1)-N-1)
FROM(
SELECT N 'HeightColVal',(
SELECT ','+[2]+':12,'+[3]+':24,'+[4]+':28,'+[5]+':32,'+[6]+':36,'+[7]+':42,'+[8]+':48,' +
[9]+':54,'+[10]+':60,'+[11]+':66,'+[12]+':72,'+[13]+':84,'+[14]+':96,'
FROM dbo.Sheet1
WHERE [1] = N)'CurrentWidthRow'
FROM dbo.Tally
WHERE N < 200)X,dbo.Tally
WHERE X.CurrentWidthRow IS NOT NULL
AND
N < LEN(X.CurrentWidthRow)
AND SUBSTRING(X.CurrentWidthRow,N,1) = ','
SELECT * into WidthHeight FROM @Elements
group by Height,Number,Width,Value
ORDER BY Number asc
--------------------------------
--Process the
declare @TotalRows INT;
SET @TotalRows = (SELECT COUNT(*) FROM dbo.WidthHeight)
WHILE(@TotalRows > 0)
BEGIN
UPDATE dbo.WidthHeight
SET Width =
(
SELECT Replace(SUBSTRING([VALUE],Charindex(':',[VALUE]),3),':','')
FROM dbo.WidthHeight WHERE Number = @TotalRows
),
[Value] =
(
SELECT Replace(SUBSTRING([VALUE],0,Charindex(':',[VALUE])),':','')
FROM dbo.WidthHeight WHERE Number = @TotalRows
)
WHERE Number = @TotalRows
SET @TotalRows = @TotalRows -1;
END
SELECT * FROM dbo.WidthHeight
Dam again!
February 20, 2009 at 9:31 am
Erik, for this particular table if you add an identity column to your sheet1 table you can make your WidthHeight table without loops and all. Now, there is probably a Rank/Rownum way to do this in 2005 but I did not work it that way.
------------------ add an identity column to your sheet1 table
------------------ (or could be done to a copy)
-----------------
alter table sheet1
add rowid int identity(1,1)
---------------------------------------
---- create your widthheight table from sheet1 building a row for each width/height combo
---------------------------------------
declare @WidthHeight table (row_number int Identity(1,1), value varchar(10),
width varchar(3), height varchar(3))
insert into @WidthHeight (value, height, width)
select [2],[1] height, (select [2] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
-- select * from @WidthHeight
select [3],[1] height, (select [3] from sheet1 where rowid=1) as width
from sheet1 where rowid >1 union all
select [4],[1] height,(select [4] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [5],[1] height,(select [5] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [6],[1] height,(select [6] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [7],[1] height,(select [7] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [8],[1] height,(select [8] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [9],[1] height,(select [9] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [10],[1] height,(select [10] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [11],[1] height,(select [11] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [12],[1] height,(select [12] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [13],[1] height,(select [13] from sheet1 where rowid=1) width
from sheet1 where rowid >1 union all
select [14],[1] height,(select [14] from sheet1 where rowid=1) width
from sheet1 where rowid >1
select * from @WidthHeight
Toni
February 20, 2009 at 5:50 pm
Check this awesome solution that Jonathan over at msdn helped me with.
==================================================
CREATE TABLE [dbo].[Sheet2]( [0] [nvarchar](255) NULL, [12] [nvarchar](255) NULL,
[24] [nvarchar](255) NULL, [28] [nvarchar](255) NULL, [32] [nvarchar](255) NULL,
[36] [nvarchar](255) NULL, [42] [nvarchar](255) NULL, [48] [nvarchar](255) NULL,
[54] [nvarchar](255) NULL, [60] [nvarchar](255) NULL, [66] [nvarchar](255) NULL,
[72] [nvarchar](255) NULL, [84] [nvarchar](255) NULL, [96] [nvarchar](255) NULL )
ON [PRIMARY]
INSERT INTO dbo.Sheet2
SELECT 30,95,80,93,107,120,140,160,179,199,219,239,281,351
UNION ALL SELECT 36,112,97,110,127,143,167,190,215,239,262,289,337,414
UNION ALL SELECT 42,118,103,120,137,153,180,203,232,255,280,309,361,441
UNION ALL SELECT 48,132,117,137,157,173,203,233,265,292,322,352,411,501
UNION ALL SELECT 54,142,127,147,167,187,220,250,285,315,345,379,444,534
UNION ALL SELECT 60,155,140,163,187,210,243,280,315,352,385,419,491,591
UNION ALL SELECT 66,158,143,167,190,213,250,287,322,359,395,432,504,604
UNION ALL SELECT 73,162,147,170,197,220,257,293,332,369,405,442,517,621
UNION ALL SELECT 78,172,157,183,210,233,273,313,355,392,432,472,551,661
UNION ALL SELECT 84,185,170,197,223,253,293,337,382,422,465,509,594,707
UNION ALL SELECT 90,195,180,210,240,270,317,360,409,452,499,542,637,757
UNION ALL SELECT 96,208,193,223,257,290,337,387,435,482,532,579,677,804
UNION ALL SELECT 108,232,217,253,290,323,380,433,489,542,599,652,761,901
UNION ALL SELECT 120,255,240,280,320,360,420,480,542,65,665,725,847,997
SELECT [0] as height, Width, Cost
from
(SELECT [0],[12],[24],[28],[32],[36],[42],[48],[54],[60],[66],[72],[84],[96]
FROM Sheet2) p
UNPIVOT
(Cost FOR Width IN
([12],[24],[28],[32],[36],[42],[48],[54],[60],[66],[72],[84],[96])
) as unpvt
===========================
Copy and paste that into sqlserver manager and watch the fireworks..
Dam again!
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply