Got a really early start today arriving on site at 6:30am. My current task is to output sales transaction history to three XML documents. The history is a compilation of daily sales from 300 retail stores that are stored on iSeries in a DB2 table.
After some discussion with the IT Manager it was decided that we would add records to a new file during nightly processing. This file will mimic a file currently in the process except for added status and date/time stamp fields for use in the MidRetail processing.
I first have to determine how I can identify sales based on three categories; regular, promo and markdown. The file does not contain proper coding to identify these types of sales, if it did this would be to easy.
One clue I am given is that markdown’s all end with 99 cents and there is a discount code but not consistent. The first question is how do I get at the number to the right of the decimal place. Should be easy I should be able to do this mathematically but do not know the formula. I want to be able to do this with SQL and did not take long to find answers like Floor() and PARSENAME but not exactly what I am looking for. I went over all the BIF’s again and did not see any silver bullets. Thought this was going to be easy, not!
Then I found an obscure post on a forum about CAST function. It was not exactly what I needed but showed promise. After tinkering around with the function taking care of the positive/negative potential issue I was able to come up with the following, vnew is the selling price and vold is the orginal price:
abs(cast(vnew as int)- vnew)
Now armed with something that will work I created these three statements:
//Regular
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where abs(cast(vnew as int)- vnew) <> .99
order by vstr, vdte, vcls, vven, vsty, vclr;
//Promo
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where vnew <> vold
order by vstr, vdte, vcls, vven, vsty, vclr;
//Markdown
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where abs(cast(vnew as int)- vnew) = .99
order by vstr, vdte, vcls, vven, vsty, vclr;
I add sum(vqty) and Group by to the equation I have my daily sales summed to store, date, class, vendor, style and color. I add size and I’ll have my summary to the size. I am sure the weekly summary to the color level will be easy.
After analyzing the data a few different ways and counting up records to make sure nothing missed I took my results to the IT Manager. Looks good and we can move forward.
Tomorrow I look into how to add these statements in my RPGLE program to create XML documents.
~Richard
Sales types -
ReplyDeleteRegular sales = price new(cents) not equal .99 and price new equal price old
Markdown sales = price new(cents) equal .99 and price new equal price old
Promo sales = price new not equal price old
Try the MOD function.
ReplyDeleteFor example,
select mod(123.45,1) from sysibm/sysdummy1
returns .45
Tried the MOD, works in interactive but not through program. I am missing something. Thanks!
ReplyDelete