Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.I should give some more info to make the analysis easier...

My raw data looks like this

Date Type
20040101 Meal
20040101 Meal
20040101 Meal

I want the SQL to add a Count column and the Fact table data should look like this (one count per day).

Date Type Count
20040101 Meal 1
20040101 Meal 0
20040101 Meal 0

The only way I have been able to come close is to have a count of '1' for each line item, or to have to sum all the meals and have only one line per day. Is this possible to create without having to sum?

Thanks. Sorry I wasn't more clear.|||I still don't understand what you are trying to do. To get a count of meals, why don't you just
SELECT COUNT(*) From Table WHERE Type = 'Meal'

Do you want to count the number of days that have one meal so that if a single day has multiple meals, it just counts as one:
SELECT COUNT(*) From Table WHERE Type = 'Meal' GROUP BY datepart(year, Date), datepart(dayofyear, Date)|||Yes, I want to count one meal per day, however I still want the other line items to show as zero, because they have costs.

I'll try your SQL, but won't it drop the other 2 line items?

Thanks.|||select date expression
, count(*) as numberofmeals
, sum(costs)/count(*) as averagecostofmeal
from yourtable
group by date expression|||another solution :
select Date, Type into ##temp from yourtable group by Date, Type;
select Type,count(type) from ##temp group by type;

Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.This is tough to help you do. The problem is that we (or I at least) don't understand your schema, so I'm not sure what you've got stored how.

One thing that I'd strongly suggest is to avoid using reserved words like "Count". You can use them, but it makes everything more work. In this case, I'd suggest using meal_count because it avoids the collision with a reserved word and it is more meaningful to some poor bozo like me that might try to help you!

-PatP|||This does not help. I was only giving you an example, I am using the 'Count' for the column title only, and am returning the same rows if I change the column title. I want to edit the SQL so I only count the number of days they had a meal charged.

Thanks.|||I'm sorry, I was trying to explain that I didn't know enough about your problem to help. If you can post the CREATE TABLE statements for your tables, and the SELECT statement (all of it), then I could get a lot closer. As it is, I don't know enough to give you anything more than guesses.

-PatP

Help With a Calculated Member Calculation

Hello,

I have a fact file that includes a measure called Credit Hours. I also have a dimension called DimTerm what includes time data for two terms: 05Fall and 06Fall. My goal is to get a number change between 05 and 06 terms. So I created a calculated member with the logic below but my percentage change is always %0.00. Maybe someone knows a way of doing this through MDX which would allow me to connct to Term values?

([Measures].[Credit Hours] - [Measures].[Credit Hours]) / [Measures].[Credit Hours]

Thanks!

Take a look at the ParallelPeriod function. Your calculated member will look something like

([Credit Hours] - ([Credit Hours], ParallelPeriod(...))) / [Credit Hours]

Help using Lookup Transformation

Hi All,

I am doing something really simple and it doesnt work, may be I am missing something, What I am trying to accomplish is to load a fact table using lookup transaformation, however my source data was different from the data in my dimension (or the datatype ) I had to use a data conversion task before my lookup , so the data flow is something like this source -> Data Conversion -> Lookup -> destination , I am getting an error at my lookup task where it says the "[Lookup [82]] Error: Row yielded no match during lookup". and then it just fails. I know for sure that there has to be matching data. donno what is it that I am missing.

Thanks

Things to look out for...

Trailing spaces on either side of the lookup (source data or lookup data). If one has trailing spaces and the other doesn't, it won't match.

CaSE. The lookups are case sensitive.|||

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

|||

db_guy wrote:

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

Redirect the errors (error output, red arrow) to a flat file source, OLE DB destination, or even a Row Count Transformation, and add a data viewer to inspect the data that does not match.|||

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

|||

db_guy wrote:

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

Add a data viewer to the red arrow connecting the lookup to the row count. (Double click on it and select data viewers, then add a new data viewer)|||

Hi,

I have attached data viewers and one thing I noticed is they behave in a weird way, sometimes they have 0 records and sometimes they have data, donno why.I have three lookups that I use , and then finally they all load the fact table

Thanks

|||Connect all lookup error flows to a Union All component, and then to a row counter. Add the data viewer between the union all and the row counter. This will let you see all of the lookup error outputs.

Or, just hook each lookup up to their own row counter and add data viewers there. You're data is getting caught up somewhere.|||

I think using the data viewers I have narrowed down the problem thanks for that, however I have another question how do I do lookup where I have a join which is something like this

INNER JOIN dbo.Dim_Time T WITH (NOLOCK)

ON SUBSTRING(T.MonthYear,1,3) = SUBSTRING(s.Fiscal_Time,1,3)

AND SUBSTRING(T.MonthYear,6,2) = SUBSTRING(s.Fiscal_Time,6,2)

The problem is I was using derived column to create/replace the stage column using the substring function , however I cannot do that twice because derived column cannot be used twice for the same column, any suggestions?

Thanks

|||

Where did you get that you can't use a derived column twice for the same column?

If you are having a problem with your lookup you can always use a Source / merge join transform, or you can go to the advanced tab of the Lookup transform and select enable memory restriction and type in your exact query with parameters and line it up that way (This is much slower than using it in the regular lookup mode)

If you were to give more info we could probably tell you the exact steps needed to complete this process.

(also, if your original question was answered, remember to mark it as such, along with all other applicable answers)