yingchai (2/8/2012)

---

Hi JonFox,

I had tried execute your sql in my fact table which contains 123591 records.

The performance is quite good actually...took around 2 seconds to complete...but the results seems...

JonFox (1/21/2012)

---

How wedded are you to the schema of your base fact table? Is this a work in progress that you can make some changes to, or is it already...

Hi JonFox,

I had tried execute your sql in my fact table which contains 123591 records.

The performance is quite good actually...took around 2 seconds to complete...but the results seems not...

Hi JonFox,

Based on two periods that I had loaded in the fact table, I can estimate that the fact table has around 150,000++ record. I would say that the database...

Hi,

Apologized for my mistake. The output in the views should look like this:

('CMP1000', 'PM1',2011,11,100),

('CMP1001', 'PM1',2011,11,101),

('CMP1002', 'PM1',2011,11,102),

('CMP1003', 'PM1',2011,11,103),

('CMP1001', 'PM1',2011,12,111),

('CMP1002', 'PM1',2011,12,112),

('CMP1003', 'PM1',2011,12,113),

('CMP1000', 'PM1', ...

Hi ChrisM@home,

I am currently using this sql script below to check whether the TB, AR or AP file is loaded into the database by organization and would like to enhance...

Hi fahey.jonathan,

Based on your sample data here:

('CMP1000', 'PM1',2011,11,100),

('CMP1001', 'PM1',2011,11,101),

('CMP1002', 'PM1',2011,11,102),

('CMP1003', 'PM1',2011,11,103),

('CMP1001', 'PM1',2011,12,111),

('CMP1002', 'PM1',2011,12,112),

('CMP1003', 'PM1',2011,12,113),

('CMP1002', 'PM1',2012,01,122),

('CMP1003', 'PM1',2012,01,123)

The desired output should be:

('CMP1000', 'PM1',2011,11,100),--Row A

('CMP1001', 'PM1',2011,11,101),--Row B

('CMP1002', 'PM1',2011,11,102),

('CMP1003', 'PM1',2011,11,103),

('CMP1001', 'PM1',2011,12,111),

('CMP1002', 'PM1',2011,12,112),

('CMP1003', 'PM1',2011,12,113),

...

Hi fahey.jonathan,

Let's make sure I have this right. You have an "OriginalFact" table and you want to populate data into a "DerivedFact" table, using the "prior" month's data if "this"...

Hi manjuke,

Thanks for the reply. But your solution seems not so flexible as it is just comparing period 06 and 09. In my actual fact table, it contains period 01...

Thanks ChrisM@home! 🙂

Hi ChrisM@home,

There is still one last final problem. After I apply the group by aggregate, the company "9006000 ISZL Consortium" does not appear in the result as this company does...

Hi ChrisM@home,

I had tried your script and it indeed simplify a lot of things. But it shows some duplication results if an organization has TB, AR and AP loaded in....

Hi ChrisM@home,

Removing the WHERE clause does not help. It still throws the same error...

Msg 8152, Level 16, State 14, Line 9

String or binary data would be truncated.

The statement has been...

Hi JonFox,

I am putting this issue aside first as I need to rush for other modules...will let you know once I found some workaround on this performance issue...

TQ.

Hi Usman Butt,

I think I had figured out a way for this problem.

select *, organization as subgroup_org from org_hrchy_details

where organization in (select distinct parent from org_hrchy_details)

union

select *, parent + '-'...