Projecting Future Month's Potential Charges

[pnn44794]

Hello:

I have a need to be able to project potential future costs based on a decommission date of an application being missed.  For example, if an application is scheduled for decommission sometime in February and that does not happen, what is the cost for April 2017 to January 2018?  April 2017 (201704) through to January 2018 (201801) is not currently present in the data pulled from the database.  To be clear, if an app is decommissioned at some point in January 2017, we would still be billed in February 2017 for January.  If still live in February, billed in March and billing would stop with April's bill.  So, I need to add a +2 to the calculated Charge Month.

Let's say App A is due for decommission in February 2017 and is indeed decommissioned in February 2017, there's no additional charges for 2017 AFTER March 2017 because we're still charged for February (think electric bill where you're not billed for the current month until the next month because there's no way to predict what the current usage is until that month is over and  / or billing period is over) with the March bill.   If App A were to miss being decommissioned in February 2017, the bill should be the same in April 2017 because we're now into March which would have a charge amount we would see with April's billing (April billing for March usage) because there was partial usage in March.

The fields in play are:  "Charge Month" in the format of YYYYMM, "Charge Amount" in dollars, "Decommission Date" in the form of mm-dd-yyyy, the future months which don't exist yet in the format of YYYYMM.

As an example:

Charge Month = 201702, Decommission Date = 06-15-2017, Charge Amount (for current month of analysis, i.e. 201702) = $100

Result:

I would have columns from 201704 thru columns 201801 for Charge Month as part of the straight table showing a Charge Amount of $100 for each Month from Charge Month = 201708 through 201801 based on a Decommission Date of 06-15-2017 ( or any other date in June).  The dollar amount would be based on the current months charges, meaning in this case, the current month is 201702 at $100.

Does this make sense?  I know it's long winded and my apologies for that.

[pnn44794]

Hello Deran:

Ok. I’ll make that change as I would like to pull in some history. Right now, I’m having some additional challenges. I should have provided this information from the beginning. My fault for not doing so. I believe I mentioned that I’m loading the data from a database, right?

I have to join 3 tables together in order to get all the data points needed. I can do this just fine running a query from Toad. I’m also, based on those joins, pulling in additional fields.

Any ideas? Thanks in advance.

[Anonymous]

Hi Perry,

Your code above has a mix of QV and SQL code so QV will not understand the commands.

Assuming the select statement runs fine on TOAD without errors and to rule out the access issue,

start a fresh qvw and add in your connection string.

Then paste in the code as is:

- Code in bold is the exact SQL statement and regular text is QV code. SQL statement is letting QV know we want to run sql code.

- This will bring in all data returned by the sql

- When debugging you can load top/first 10 records as you would normaly do in TOAD - Just add it in the correct position after the SQL keyword below.

Fact:

Load *;

SQL

SELECT a."APP_ALLOCATION",

    a."APP_CNT4INV",

    a."APP_ENVIRONMENT",

    a."APP_ID",

    a."APP_INSTANCE_ID",

    a."APP_NAME",

    a."APP_RESOLVER",

    a."APPLICATION_IDS",

    a."APPLICATION_NAMES",

    a."ASSET_ENVIRONMENT",

    a."BUSINESS_LOAD",

    a."CAF_CRITICAL",

    a."CC_ALLOCATION",

    a."CCB_INFRA_CAT_L1",

    a."CCB_INFRA_CAT_L2",

    a."CCB_INFRA_CAT_L3",

    a."CCB_LOB",

    a."HOST_INV_NO",

    a."HOST_NAME",

    a.INFRA4APP,

    a."INVENTORY_NO",

    a."OVERALL_RISK_RATING",

    a."RECORD_OWNER (SUPPORT_TEAM)",

    a."SITE_NAME",

    a."SEAL_APP_LOB",

    a."SEAL_APP_LOB_NEW",

    a."SEAL_TGO",

    b."CHARGE_AMOUNT",

    b.ORG_CC,

    b.ORG_DESCRIPTION,

    b.ORG_SORT_CODE,

    b.PLATFORM,

    b.[PPG_L2 (FAMILY)],

    b.[PPG_L3 (PSET)],

    b.[PPG_L4 (OFFERING)],

    b.PPGUNITS,

    b.PRICE,

    b.[PRODUCT (AVAILABILITY)],

    b.[PROVIDER_L2 (TOWER)],

    b.[SCE (FINANCIAL_CODE)]

FROM sources.common."IR_TAMS_APPS_COMMON_FIELDS_BASE_V" a

left join sources.common."IR_MARS_CHARGES_V" b on b.HOST_NAME = a.HOST_NAME

left join sources.common.IR_SEAL_APP_DETAILS_V c on c.APP_ID = a.APP_ID

WHERE ((  = '201609' or  = '201610' or  = '201611' or  = '201612') or  like '2017%') and ORG_SORT_CODE like 'K%';

Run this  just to bring in the data and then take it from there.

Hopefully this helps!

[pnn44794]

Sorry Deran, but I'm not sure where left(chargemonth,4) as Year should go in order to capture any 2018 decommissions.  Does it just need to go in one place?  Is there any calculation or other manipulation I need to do with it?  Thanks in advance.

[Anonymous]

Hi,

The code above is just to test the connection. Have you tried it as above? Did it run and return the expected rows?

[pnn44794]

I have not tried it yet, but I will and hopefully today.  I was actually responding to one of your earlier responses where I had asked about including  / capturing any 2018 planned decommissions.  I've been working on an alternative to pulling directly from the database as a temporary measure.  I'll explain what I've done in about 30 minutes with another post.  I currently have a load running which hopefully will work.  The database pull is the preferred method though. 

[Anonymous]

Ok cool.

Depending on how often your refresh is going to be and the amount of records you are pulling in I would suggest creating a Qv data loader to extract and transform then save to a qvd. Qvds will allow you to hit the dB less especially during testing. Qvds also load way faster than constantly pulling from the dB which will save you lots of dev time. You should also look at loading a subset of data to your discretion to bed down the logic and then test it on the full load.

Once you have created a data loader process, you can work on your application and load faster without pulling from the dB.

Are you able to pm?

[pnn44794]

Hello Deran:

I will definitely try this as pulling from the database is the preferred method over a spreadsheet.  In the meantime....

Before I got your suggestion above for trying out the just the SQL code in a new qvw, I had gone down another path which is:

1. Run the SQL code in Toad and get the results
2. Export the results to a csv file
3. Added the additional fields I mentioned before to the appropriate sections of the code
4. Perform the load

This appears to be working, but I do get a Field Not Found <ChargeYearMonth> error when I run the load.  I say it appears to be working because the data loads into the table fine and I do have Charge Month, so it looks like it's working, but I haven't fully validated it yet.  Any ideas why ChargeYearMonth would not be found?  The error message comes up during the load for the Data section (Data << DataTemp) and after all rows have been fetched.

[pnn44794]

I'm not sure what you mean by "Are you able to pm?"  What's pm?  Sorry if I'm being dense, but I don't know what pm is. 

[Anonymous]

Its most probably the naming of the fields are inconsistent.

Its difficult to point out without looking at the script.

PM is private message via the community site. I have email notification setup for this to reduce the emails i receive

[pnn44794]

Gotcha. Thanks. I’ll respond via PM.