How to add cost percentage to product

Question

How to add cost percentage to product

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

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 2

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

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)

BuilderBits SSCertifiable Points: 6071 More actions · Answer 3

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!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 4

Hey Toni,

Is the w_low, w_hi, h_low, h_hi, for better performance?

Erik

Dam again!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 5

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!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 6

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!

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 7

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

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)

toniupstny Hall of Fame Points: 3986 More actions · Answer 8

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?

BuilderBits SSCertifiable Points: 6071 More actions · Answer 9

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!

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 10

AFCC Inc. Com (2/17/2009)
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.

You keep saying "loop"... that's "Death by SQL". Please read the following article...

http://www.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

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)

BuilderBits SSCertifiable Points: 6071 More actions · Answer 11

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