Ideas for duty drawback query

[Shal2]

Hi All,

I am trying to create a report, for which I need suggestions to write the query.

I have a table1 which is shown below. Below that are month1 and month2 which are the reports I want to create, I have left the last column empty, which should be filled.

If you see the table1, for the same "style1" dutypaid value is different(3,1,2). I want to use up the highest paid first for the report. Let's say "Style1" "50" qty has been exported, for that I want to use "3" as duty paid first, then there will be 150 qty remaining in the table1 for me to use in the future months. How can I do this? Should I have another column which subtracts the Qty once used up? When it becomes zero, how can I select the next highest paid. What is the best way to do this, stored procedure, simple query with TOP and asc and desc order, cursors??? or DTS packages?

Any suggestion is helpful.



Table1
-----------------------------------------------
Style QtyImported DutyPaid
-----------------------------------------------
Style1 200 3
Style1 300 1
Style1 250 2
Style2 300 2
Style3 200 3
Style3 100 4
------------------------------------------------



Month 1 Report
------------------------------------------------------
Style QtyExported Dutydrawback(dutypaid*QtyExp)
-------------------------------------------------------
Style1 50
Style2 40
Style3 250
----------------------------------------------------
Month 2 Report
----------------------------------------------------
Style QtyExported Dutydrawback
-----------------------------------------------------
Style1 40
Style3 20
------------------------------------------------------


Thank you,
Shal

 

[Shal2]

Hi All,
Since I got no response to this question, I am providing more details hoping that someone might give me ideas to work this one out.

The SP below works well, only problem with this is I might have more than one row for same barcode and style with different duty paid. So in the "update statement", I don't want to select just any row, but the highest paid first and then the next, so to move through the rows I think I need to use cursors, also once I use up the quantity, I would like to update the JKMT table column quantityremaining to Quantity - Quantity used up (td.Quantity). And once the quantityremaining becomes zero I want to move to the next row with the same barcode and style but the next highest duty paid. Below is sample data and Stored procedure.


JKMT result set in the update query.
Barcode dutypaid Qty Qtyremaining
20120603 1.2825 20 20
20120603 1.3012 33 33
20120610 1.2793 30 30
20120610 1.2994 56 56




CREATE procedure MSRS_Dutydrawbacktest
AS
CREATE TABLE #DutyDrawBack (

Barcode VARCHAR(35),
Style VARCHAR(35),
Description VARCHAR(75),
Country VARCHAR(35),
ImportNo VARCHAR(35),
Lodgement VARCHAR(35),
Tariff VARCHAR(35),
Quantity INT,
DutyPaidPerUnit MONEY,
DutyDrawBack MONEY
)


INSERT INTO #DutyDrawBack

select
td.Barcode,
g.Style,
g.Description,
g.Country,
0 as ImportNo,
0 as Lodgement,
0 as Tariff,
sum(IsNull(td.quantity,0)) as Quantity,
0 as DutyPaidPerUnit,
0 as DutyDrawBack

from
ManifestMaster mm JOIN Manifestchild mc ON
mm.manifestid=mc.manifestid JOIN tranheader th on
mc.ParcelInvoiceNo = th.documentnum INNER JOIN TranDetail td ON
td.documentnum = th.documentnum and
td.trandate = th.trandate and
td.type = th.type
inner join barcode b on
td.barcode = b.barcode
inner join garment g on
b.style = g.style

where
td.Trandate between '04/22/2004' and '04/27/2004' and
th.type = 'SALE' and
th.branch = 'Australia Mail Order' and
td.barcode != 'Freight'

group by
td.barcode,
g.Style,
g.Description,
g.Country

order by
td.barcode

UPDATE #DutyDrawBack
SET #DutyDrawBack.DutyPaidPerUnit = ddb.DutyPaidPerUnit, #DutyDrawBack.ImportNo = ddb.ImportNo, #DutyDrawBack.Lodgement = ddb.Lodgement,
#DutyDrawBack.Tariff = ddb.Tariff

FROM ( Select


JKMT.DutyPaidPerUnit as DutyPaidPerUnit,
JKMT.ImportNo as ImportNo,
JKMT.Lodgement as Lodgement,
JKMT.Tariff as Tariff,
JKMT.Barcode as Barcode
From
JK_Mondiale_Tariff JKMT
Where JKMT.QuantityRemaining IS NOT NULL

) ddb
where #DutyDrawBack.Barcode = ddb.Barcode

SELECT *
FROM
#DutyDrawback
ORDER BY Style,Barcode
DROP TABLE #DutyDrawback
GO

Thank you,
Shal

 

[tektipdjango]

For me you're not explaining clearly the problem

You said :
If you see the table1, for the same "style1" dutypaid value is different(3,1,2). I want to use up the highest paid first for the report. Let's say "Style1" "50" qty has been exported, for that I want to use "3" as duty paid first, then there will be 150 qty remaining in the table1 for me to use in the future months. How can I do this?


What means "Style1" "50" qty has been exported?

Is it in another table as qtyExported by style, or is it input another way by the user, and which way?

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[Shal2]

Hi,

We have two tables, Trandetail (td) which has all the barcodes and styles we export. From this I get the quantity we export for a date range. We have another table JKMT which has all the information about all the barcodes and styles we have imported.
In my stored procedure, to create a report, I insert all the barcodes we export for a date range from table "td". Now for this I want to load up dutypaid from "JKMT" table using update statement. But in JKMT table we might have imported same barcode several times and each might have different duty paid (shown in the result set in second post). Also we might have imported different quantity depending on demand. So once I use up the highest paid row quantity, I will have to move to the next row, again once used up next one and so on. For this I have a Qtyremaining column in the JKMT table which can be decremented as being used up and once reaches zero I can move to the next one, only thing is I don't know how to write a cursor to do this one.

Please let me know if I am not clear.
Thank you,
Shal

 

[tektipdjango]

If I Understand well you have

1- JKMT table which is loaded at the beginning by qties you received for different styles (barcoded) :

Barcode dutypaid Qty Qtyremaining
20120603 1.2825 20 20
20120603 1.3012 33 33
20120610 1.2793 30 30
20120610 1.2994 56 56
at the beginning the Qty=Qtyremaining

2- Trandetail table giving the qty exported you have selected for a period

Barcode Qty date
20120603 8 04/23/2004


And you want to get in the JKMT table the new situation

Barcode dutypaid Qty Qtyremaining
20120603 1.2825 20 20
20120603 1.3012 33 [highlight]25[/highlight]
20120610 1.2793 30 30
20120610 1.2994 56 56

the row was selected because the duty paid was the highest for the barcode

if you have a new export like

Barcode Qty date
20120603 30 04/23/2004


And you want to get in the JKMT table the new situation

Barcode dutypaid Qty Qtyremaining
20120603 1.2825 20 [highlight]15[/highlight]
20120603 1.3012 33 [highlight]0[/highlight]
20120610 1.2793 30 30
20120610 1.2994 56 56

Am I right?

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[donutman]

Shal2, I think I understand your question. However, answering it has more to do with application design, then SQL. Nor are you giving us the info necessary to directly answer your question. The only table that you are providing a small window into it's structure is JK_Mondiale_Tariff. Yet you have ManifestMaster, Manifestchild, tranheader, TranDetail, barcode and garment.
I assume that the code that you provided is intended to ONLY be used for generating a report and it will not update an existing record in one of the above tables? Otherwise I don't think you will have an audit trail.
The good news is that I think you can do what you want to do but I question the logic of using the highest paid duty first without regard to the date of the import that created that duty. Another words the duty drawback that you compute could be based upon an import duty that was paid after the export was made! It gets even worse if you consider that this once-in-a-blue-moon report may get printed twice in such a way that the duty drawbacks that utilized particular import duty amounts for the first report now get re-used in the next report!
You have to decide exactly what this database is for. Is it for an accounting program where you will maintain an audit trail or is it just a wharehouse of import/export transaction data.
Bottom line, I can't help without the structure of all relevant tables with sample data for each. And because the relationship names do not appear to be consistent please include info regarding primary keys and foreign keys.
-Karl

 

[donutman]

Sorry there's been two posts since I composed my post.
-Karl

 

[Shal2]

Hi all,

Thank you for all the replies.

Tektipdejango you have clearly expressed what I am trying to do exactly. Is it possible ? If yes how?
Along with updating the JKMT table I want the duty paid amount in the barcode export table, for the report. Now let's say I had a third export of the same barcode, since the qty remaining has become zero, I want to select the next highest duty paid, which is the first row..

Thank you,
Shal

 

[Shal2]

Hi Tektipdejango,
I now see that you have already used the first row since we were 5 Qty short, yes you are absolutely correct that is what I want to do. Now in the report for dutydraw back for the second export the value should be (25*1.3012+ 5*1.2825)

Karl,
We will have all the information we need about the import in the JKMT table. And we only export after they have been imported. So we won't have a date problem situation as dutydrawbacks are done long after import.(I think we have seven years time here, I mean NZ). Also Customs don't care about highest paid and date and all, as long as they can link our exports and imports(I have been told so atleast).So it is to our advantage that we use the highest paid first. We might export only a fraction of what we import. So the least paid may be used for local sales.
And yes I want both the report and audittrail as the report it being generated as the JKMT table is used only for producing the reports. Where as TranDetail table has data about our transactions(Sales and export). Other tables like barcode and Manifest are all for other information which is not needed to solve this.
Thank you
Shal

 

[donutman]

Shal, rather than using an update to adjust an amount remaining, I would create a new table, DutyDrawBacks. Let it become a transactional table (like the others). Insert the amounts applied to draw backs as you compute them. That way you can earmark each computation with a reference to the original imports to which they apply. If a single export of sufficient quantity exceeds the amount remaining for the highest cost, then that transaction may have several rows in this new table associated with it. DutyDrawBack should also have a field to persist the date on which you have applied this draw back.
By doing it this way you maintain an audit trail and you lessen the possibility that your database gets into an "out of balance" situation. You will also be able to prove the accuracy of your reports by reference to the detailed transaction that support each entry.
-Karl

 

[tektipdjango]

donutman's drawbackTable is a good solution but it needs, as in Shal2's sample, to completely know how the drawback is calculated, thet is, how are "exported" the different dutyChargedItems.
eg in my sample above, the first export of 8 pieces will give one drawback record, and the second one of 30, two records.
But Shal2, what will happen if after the second export, you receive a new import with a dutypaid higher than the remaining qty at 1.2825. The following export will continue to finish the remaining qty or will there be two 'remaining qties together?

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[Shal2]

I am a bit confused now, I was thinking, we will do dutydraw back for each month, let's say for Jan 2004, I get all the barcodes we have exported. Then run the report only once for january. At that time, the stored procedure will use up whatever information we have in the import table JKMT.
After that if have new import for the same barcode with another dutypaid value, it will be stored.
Then when we run the report for february month, it will be a fresh start, the stored procedure has to go through all the records in the JKMT and use up the highest paid first again if the qtyremaining is not zero.
Karl, I will think about the Dutydrawback table solution and see what I can do.
Thank you,
Shal