How best to manage date/milestone-specific data + applying to continuous transactions

[Atomic Shrimp]

I know how to do this in theory, it's the practical outworking of it that I can't seem to get my head around.

There's a table of incoming transaction data, to which new records are continually being added.

A succession of sales promotion periods takes place. They are of variable duration, but one immediately succeeds the next.

The process generating the raw transaction data knows nothing about the promotion periods and so cannot store the relevant promotion period ID in the transaction record.

So how do we describe the sales promotion periods? If we store a start and finish date for each, we then have to make sure there are no gaps or overlaps

And if we just store the start date of the promotion period, how do we determine which one of the periods any given transaction fell into? (because although the transaction date is greater than the start date of the relevant period, it's also greater than the start of the previous period).


I see scenarios like this quite a lot, when people talk about normalising their data and not storing something like cost prices, which can be calculated later - but how should they be stored, and how is the right one picked out later?

 

[DCrake]

Sounds a bit like them on telly DFS - Doesn't Finish Sunday.

Can you not use the day of the year as a quantifier or the Julien number, actuallt this may be better as it will cope with crossing over into new years.

 

[Atomic Shrimp]

Sure, but the problem is that the table describing the promotion periods only contains their endpoints. How can I build a query that determines which promotion period was active for any given transaction?

 

[gemma-the-husky]

surely you dont need to store the start AND end of a promotion.

if only one promotion can be active, just store the start date of the promotion

then the price for a transaction is the one ruling at the lowest date greater/equal to the transaction date. which is just a single dmin

ie - effectively (ignoring the syntax errors)

promotion date = dmin("promotiondate","promotion recordtable","promitiondate<=targetdate")

 

[Atomic Shrimp]

surely you dont need to store the start AND end of a promotion.

if only one promotion can be active, just store the start date of the promotion

Absolutely! I expressly don't want to store both, as I would then have to manage for gaps and overlaps

then the price for a transaction is the one ruling at the lowest date greater/equal to the transaction date. which is just a single dmin

ie - effectively (ignoring the syntax errors)

promotion date = dmin("promotiondate","promotion recordtable","promitiondate<=targetdate")

That's perfect. Thanks!

 

[Atomic Shrimp]

I'm having trouble getting this to work consistently - I think it's because of date format constraints. My expression is:

DMin("[PromoCode]","[tblPromotions]","[DateEffective]<=#" & [TrDate] & '#')

Which should mean: find the Promocode for whicand it returns a value for every row, but it's not necessarily the appropriate value. I also tried:

DMin("[PromoCode]","[tblPromotions]",'#' & [TrDate] & '# - [DateEffective] >=0')

But the results are the same. For all but a couple of records, it's actually finding the promo code that has an effective date furthest in to the past.

I've also tried wrapping the date parameters with cdate(), but it makes no difference.

Stuck now. Help!

 

[Atomic Shrimp]

I cannot for the life of me get this to work. To keep things clean, I've created separate database for testing, containing the following tables:

Promotions:
PromoID	PromoStartDate	PromoName
1	01/01/2011	January Madness
2	01/02/2011	Valentines
3	01/03/2011	Mad March Hare
4	01/04/2011	April Fools
5	01/05/2011	Mayday
6	01/06/2011	Summer
7	25/07/2011	School's Out
8	01/09/2011	Back To School
9	15/09/2011	No Promo in effect
10	15/10/2011	Halloween
11	01/11/2011	Bonfire Night
12	06/11/2011	Autumn Special
13	01/12/2011	Christmas

Transactions:
ID	Transdate	TransAmount	ItemID_fK	Narrative
1	15/01/2011	£12.00		2		Should be in January Madness
2	19/01/2011	£1.00		5		Should be in January Madness
3	12/04/2011	£56.00		22		Should be in April Fools
4	19/04/2011	£5.00		6		Should be in April Fools
5	06/06/2011	£1.00		1		Should be in Summer
6	02/11/2011	£55.00		44		Should be in Bonfire Night
7	01/12/2011	£56.00		48		Should be in Christmas
8	12/12/2011	£4.00		3		Should be in Christmas
(NB the Narrative column in this Transactions table exists only to help me see at a glance whether the query expression is working)

Then I've got this query:

SELECT Transactions.*, 
DMin("[promoname]","[promotions]","#" & [transdate] & "# >= [PromoStartDate]") AS Comparison, 
DMin("[promoname]","[promotions]","#" & [transdate] & "# - [PromoStartDate] >=0") AS [Subtraction method],
DMin("[promoname]","[promotions]","#" & CDate([transdate]) & "# - [PromoStartDate] >=0") AS [Using cdate],
DMin("[promoname]","[promotions]","#" & Format([transdate],'mm/dd/yyyy') & "# - [PromoStartDate] >=0") AS [imposing mmddyyyy format], 
DMin("[promoname]","[promotions]","#" & Format([transdate],'dd/mm/yyyy') & "# - [PromoStartDate] >=0") AS [imposing ddmmyyyy format]
FROM Transactions;

Which gives these results:

ID	Transdate	TransAmount	ItemID_fK	Narrative			Comparison		Subtraction method	Using cdate		imposing mmddyyyy format	imposing ddmmyyyy format
1	15/01/2011	£12.00		2		Should be in January Madness	January Madness		January Madness		January Madness		January Madness			January Madness
2	19/01/2011	£1.00		5		Should be in January Madness	January Madness		January Madness		January Madness		January Madness			January Madness
3	12/04/2011	£56.00		22		Should be in April Fools	April Fools		April Fools		April Fools		April Fools			April Fools
4	19/04/2011	£5.00		6		Should be in April Fools	April Fools		April Fools		April Fools		April Fools			April Fools
5	06/06/2011	£1.00		1		Should be in Summer		April Fools		April Fools		April Fools		April Fools			April Fools
6	02/11/2011	£55.00		44		Should be in Bonfire Night	January Madness		January Madness		January Madness		April Fools			January Madness
7	01/12/2011	£56.00		48		Should be in Christmas		January Madness		January Madness		January Madness		April Fools			January Madness
8	12/12/2011	£4.00		3		Should be in Christmas		April Fools		April Fools		April Fools		April Fools			April Fools

I'm pretty sure this is something to do with a conflict between UK dd/mm/yyyy formatted dates and US/SQL mm/dd/yyyy dates, but I can't seem to coerce them to play nicely

 

[gemma-the-husky]

possibly this - you need to format the trdate in a SQL statement

DMin("[PromoCode]","[tblPromotions]","[DateEffective]<=#" & format([TrDate],"DD/MM/YYYY") & '#')

 

[Atomic Shrimp]

Thanks - I tried that, but I'm still getting the same (wrong) results.

 

[gemma-the-husky]

sorry try this (not sure without trying whether the target date needs UK formatting to avoid ambiguity.

this should get the lowest promotion starting on or after the targetdate. Then do a dlookup to get promoid. I would think you could do the same by using a findfirst in a recordset.

usedate = nz(dmin("promostartdate","promotions","promostartdate>= #" & targetdate & "#"),0)

if usedate>0 then useid = dlookup("promoid","promotions","promostartdate>= #" & usedate & "]#")

 

[Atomic Shrimp]

Thanks Dave - I need to do this in a query though. Not sure how to apply the above...

I think something is fundamentally broken about date handling here - I just tried checking each part of the date separately, like this:
Expr5: DMin("[promoname]","[promotions]","Day([PromoStartDate]) <= " & Day([transdate]) & " AND Month([PromoStartDate]) <= " & Month([transdate]) & " AND year([PromoStartDate]) <= " & Year([transdate]))

And I still get the same results.

 

[Atomic Shrimp]

This is my sample database... if you're interested in experimentation (I'd be interested to see whether this just works for people in other locales anyway)

 

[gemma-the-husky]

i'll have a look, but i'm UK.

 

[gemma-the-husky]

I see

a) you do need to wrap the dates in some formatting code, to avoid US/UK errors.
b) after having a go, with my original idea, I realised that rather than the dmin of dates greater than or equal to yours, you need instead to use the slightly different idea of a dmax of dates less than or equal to yours

so - I don't think you can do it in a single step - but I added a module, and an update query, and an extra field to the transactions table called promocode, to store the effective price list.

Hope this does the trick


View attachment promocodes.zip

 

[Atomic Shrimp]

Dave, that's fantastic - thanks so much.

The getpromocode function you created can be called directly in a query, so it's still possible to do in a single step. Excellent!