Help with Projected Plan Amounts

Project Plan Tables

I have an office yealy plan table, that is mostly static. Will I need to make this table a fact table and break this yearly plan number down by day using the time dimension to use it to calculate against all the facts in my fact table?

I have a fact table with all my daily submitted $ amounts per day, per office. I have a static "yearly office plan" table with my $$ per office per year. The thing is this "yearly office plan amount" could change based on business or sales and marketing decisions. I use this plan amount along with my fact table to base many mdx calculations against actuals vs plan, per day, per month etc.

FactPolicyTable hasolicyNumber(FK), DistributionChannelCode(FK), OfficeCode(FK), TimeKey(FK) and Face_amt, current_day_amt, lost_amt, prior_submitted_amt, pending_amt

DimOfficeTable has OfficeCode(key) and additional office information

PlanOffice table has keys of Plan_Office_Code(FK from OfficeCode), Plan_Office_Year, Plan Office Annual_Amt

The PlanOffice Annual Amt is what I need help with, how could I best implement this? all indications are I would have to break it down by day and move it into the FactPolicyTable or make it a fact table with a FK TimeKey in it. By the way this amount does not really relate to the PolicyNumber key in the fact table either.

I hope this gives you more to work with to help with my issue.

Or

Can I make this yearly plan amount a KPI? would the KPI still need to be broken down by day? can I use the KPI in to do calculations against other values in FactPolicy table?

What would be the best way to implement this Plan Value?

I hope I explained enough for this to make sense?

Well, the PlanOffice table could be a fact table for another measure group, which relates to the date dimension at a different granularity (ie. year) than does the FactPolicyTable (day). For example, in Adventure Works, the Sales Targets measure group relates to the Date dimension at the Calendar Quarter granularity, but [Measures].[Sales Amount Quota] gets allocated down to the month level in the cube MDX script.

Help with Projected Plan Amounts

Project Plan Tables

I have an office yealy plan table, that is mostly static. Will I need to make this table a fact table and break this yearly plan number down by day using the time dimension to use it to calculate against all the facts in my fact table?

I have a fact table with all my daily submitted $ amounts per day, per office. I have a static "yearly office plan" table with my $$ per office per year. The thing is this "yearly office plan amount" could change based on business or sales and marketing decisions. I use this plan amount along with my fact table to base many mdx calculations against actuals vs plan, per day, per month etc.

FactPolicyTable hasolicyNumber(FK), DistributionChannelCode(FK), OfficeCode(FK), TimeKey(FK) and Face_amt, current_day_amt, lost_amt, prior_submitted_amt, pending_amt

DimOfficeTable has OfficeCode(key) and additional office information

PlanOffice table has keys of Plan_Office_Code(FK from OfficeCode), Plan_Office_Year, Plan Office Annual_Amt

The PlanOffice Annual Amt is what I need help with, how could I best implement this? all indications are I would have to break it down by day and move it into the FactPolicyTable or make it a fact table with a FK TimeKey in it. By the way this amount does not really relate to the PolicyNumber key in the fact table either.

I hope this gives you more to work with to help with my issue.

Or

Can I make this yearly plan amount a KPI? would the KPI still need to be broken down by day? can I use the KPI in to do calculations against other values in FactPolicy table?

What would be the best way to implement this Plan Value?

I hope I explained enough for this to make sense?

Well, the PlanOffice table could be a fact table for another measure group, which relates to the date dimension at a different granularity (ie. year) than does the FactPolicyTable (day). For example, in Adventure Works, the Sales Targets measure group relates to the Date dimension at the Calendar Quarter granularity, but [Measures].[Sales Amount Quota] gets allocated down to the month level in the cube MDX script.

Help with multi Join or multi tier select.

Hello,

I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables.
PlanID is the primary in Plan and

PlanProviderProviderLocationLookups
--------------
PlanIDProviderIDProviderIDLookupType
PlanNamePlanIDProviderStatusLookupKey
RegionIDLastName...LookupValue
...FirstName...

Given a PlanID I want all the Providers with a ProviderStatus = 0

I can get the query to work just fine if there are records but what I
want is if there are no records then I at least want one record with
the Plan information. Here is a sample of the Query:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
left outer JOIN Provider p ON pln.PlanID = p.PlanID
left outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey
WHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

I know the problew the ProviderStatus on the Where clause is keeping
any records from being returned but I'm not good enough at this to
another select.

Can anybody give me some suggestions?

Thanks

DavidTry moving the predicate "AND PL.providerstatus = 0" into the ON clause:

FROM Plans AS PLN
INNER JOIN Lookups L3
ON L3.LookupType = 'REGN'
AND PLN.regionid = L3.lookupkey
LEFT OUTER JOIN Provider AS P
ON PLN.planid = P.planid
LEFT OUTER JOIN ProviderLocation AS PL
ON P.providerid = PL.providerid
AND PL.providerstatus = 0
LEFT OUTER JOIN Lookups AS L1
ON L1.lookuptype = 'PLRM'
AND PL.reasonmain = L1.lookupkey
LEFT OUTER JOIN Lookups AS L2
ON L2.lookuptype = 'PLX1'
AND PL.reasonsub = L2.lookupkey
WHERE PLN.planid = '123456789'

--
David Portas
SQL Server MVP
--|||No that didn't work becase then it all the providers ... and I think
only the locations with with providerstatus = 0.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RpOdnffKDN6GPVfdRVn-sQ@.giganews.com>...
> Try moving the predicate "AND PL.providerstatus = 0" into the ON clause:
> FROM Plans AS PLN
> INNER JOIN Lookups L3
> ON L3.LookupType = 'REGN'
> AND PLN.regionid = L3.lookupkey
> LEFT OUTER JOIN Provider AS P
> ON PLN.planid = P.planid
> LEFT OUTER JOIN ProviderLocation AS PL
> ON P.providerid = PL.providerid
> AND PL.providerstatus = 0
> LEFT OUTER JOIN Lookups AS L1
> ON L1.lookuptype = 'PLRM'
> AND PL.reasonmain = L1.lookupkey
> LEFT OUTER JOIN Lookups AS L2
> ON L2.lookuptype = 'PLX1'
> AND PL.reasonsub = L2.lookupkey
> WHERE PLN.planid = '123456789'|||I understood that you wanted to include rows from Plans which didn't have
corresponding rows from ProviderLocation - in which case they won't have a
ProviderStatus. It may be easier to understand your requirements if you post
DDL, sample data INSERTs and show your required result based on that sample
data. (http://www.aspfaq.com/5006)

--
David Portas
SQL Server MVP
--|||>> I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables. <<

Mind posting some DDL? When see data element names as poorily written
as "LookupType", "LookupKey" and "LookupValue", it is a pretty sure
sign that the basic schema design is wrong. To be is to be something
in particular and those names imply that you have a "One True Lookup
Table" (OTLT) flaw. Yes, like many diseases or disasters, it is
common enough to have a name! Google it; I wrote a column on it in
INTELLIGENT ENTERPRISE magazine.|||[posted and mailed, please reply in news]

David Logan (ibflyfishin@.yahoo.com) writes:
> I can get the query to work just fine if there are records but what I
> want is if there are no records then I at least want one record with
> the Plan information. Here is a sample of the Query:
> SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
> p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
> pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
> l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
> pl.InvalidData
> FROM Plans pln
> INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
> AND pln.RegionID = l3.Lookupkey
> left outer JOIN Provider p ON pln.PlanID = p.PlanID
> left outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
> left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
> AND pl.ReasonMain = l1.LookupKey
> left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
> AND pl.ReasonSub = l2.Lookupkey
> WHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0
> ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum
> I know the problew the ProviderStatus on the Where clause is keeping
> any records from being returned but I'm not good enough at this to
> another select.

As David said, it is always a good idea to include CREATE TABLE and
sample data. But I think I have a guess what will work for you:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
LEFT JOIN (Provider p
JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
AND pl.ProviderStatus = 0
JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey)
ON pln.PlanID = p.PlanID
WHERE pln.PlanID = '123456789'
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

The point here is that the thing in parathensis is sort of a logical
table, and you make an outer-join to that logical table.

This is the normal way of doing things when you want to join a
left-joined table with a lookup table (should not be necessary to
left-join the lookup table). In this case it also necessary, to
exclude providers which does not have any location with status = 0.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp