I think I understand what I did wrong.

Between Purchases and Usage is maybe an "allocation" table, where I allocate one or more "blocks" of ingredient to each "BakeEvent" (just another...

Drew,

Okay, sorry, I'll bash that bit out in a little. I hope.. Just a lot going on right now.

Oh, I think I get it. You're doing a running total of Purchased and subtracting a running total of Used. Easy enough. Where does the UnitPrice come into this? that's...

Drew,

yes, I did. Sorry, just been doing other stuff.  I'll go look at it and add questions to that one. Because I know I'm going to be confused.

Sorry about that!

Thanks,

Pieter

Where is your question? Are you trying to determine if ProcessDate is between StartDate and EndDate?

look up windowing functions. new since 2012 at the latest.

if it's only one or two values, then I probably wouldn't use a lookup table. It would be simpler to just use a CASE statement, but if there are lots...

thanks Martin. I'll probably just get a used copy. If I don't like it, I'm only out like $10.

Where's your question?

Are you asking if there's a standard format for "AML" files? Are they XML? Is there an example of one somewhere?

Martin,

have you read Joe Caserta's book on ETL (from the Kimball collection)? ISBN: 978-0764567575

I'm just trying to get my head around doing the ETL etc and building proper dimensional models...

Okay, I got the "explode Starter ingredients" bit working, so now it's getting easier!

CREATE VIEW [dbo].[vwBaseIngredientRecipe]
AS
WITH cteRecipe(RecipeID, RecipeName, IngredientID, IngredientName, RecipeWeight)
AS (
SELECT 
r.RecipeID
,r.[Recipe Name]
,i.IngredientID
,i.IngredientName
,ExpandedRI.RecipeIngredientWeight

FROM
/* non-Starter ingredients */
(SELECT...

GENERATE_SERIES (Transact-SQL) - SQL Server | Microsoft Learn

Or of course you could search around on here. I know there are folks here that have built TVFs to generate series...

• This reply was modified 1 years, 4 months ago by pietlinden.

<checking calendar>

Nope, still 2024.

Drat!

Okay, thanks for looking at this. Thought I was just missing something. Maybe I'm not. The PowerQuery source just isn't nearly as functional as I had hoped it would be....

• This reply was modified 1 years, 5 months ago by pietlinden.

I think the PQ Source is a dead end. It's crazy buggy. Either that or I'm doing something incredibly stupid in SSIS, because if I use the PQ Source, SSIS...