COUNT values in Dimension table per Row in Fact Table

Question

Post reply

COUNT values in Dimension table per Row in Fact Table

justusroux

SSC Rookie

Points: 32
More actions
February 3, 2010 at 6:46 am

#142144

Hi All
I have a data set that has a FROM_Date & TO_Date per row:
FILE_NO, FROM_Date, TO_Date
101, 1101, 1105
102, 1103, 1108
103, 1102, 1104
I have a seperate Dimension table that shows all my dates:
Date_Key, Day_of_Week
1101, 7
1102, 1
1103, 2
1104, 3
1105, 4
etc
Now my problem is that I need to find a count of 7 per FILE_NO between FROM_Date & TO_Date.
So my data should look like:
FILE_NO, FROM_Date, TO_Date, NUM_of_7's
101, 1101, 1105, 1
102, 1103, 1108, 1
103, 1102, 1104, 0
I thought of doing a loop/while per row, but not sure if this would be best practice.
Any suggestions help would be great!
Regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Hi sir,

Please post some sample data along with schema as given in the following Jeff's post

http://www.sqlservercentral.com/articles/Best+Practices/61537/

That would help us a lot..

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

ok fine, i created some sample data and table

if object_id('dataset') is not null drop table dataset

create table dataset( file_no int , fromD int , toD int)

insert into dataset

select 101, 1101, 1105

union all

select 102, 1103, 1108

union all

select 103, 1102, 1104

union all

select 104, 1108, 1110

if object_id('dim') is not null drop table dim

create table dim( fromd int , countr int)

insert into dim

select 1101, 7

union all

select 1102, 1

union all

select 1103, 2

union all

select 1104, 3

union all

select 1105, 4

union all

select 1106, 4

union all

select 1107, 4

union all

select 1108, 7

now for your request,

select dataset.file_no, dataset.fromd , dataset.tod

, case dim.countr when 7 then 1 else 0 end as Tot_Count

from dataset inner join dim on dataset.fromd = dim.fromd

Please inform if thats what you are looking for...

justusroux SSC Rookie Points: 32 More actions · Answer 3

Perfect!

Thanks

Apoligies for the incorrect posting of data, will keep that in mind for the future.

Regards

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

ColdCoffee

SSC-Dedicated

Points: 39972

February 3, 2010 at 9:07 am

#1112994

😎 🙂