consecutive months

[chip12]

I need to select all merchants via the merchant-id that have process for 12 consecutive months. Processing means that we have received payments via the merchant 's credit card. The processing information is held in the processing date column and is reported in days not months.

I am not sure how to group the info in that column by month then retrieve all merchants that have had 12 consecutive months processing. Below is the data. you will see that the first merchant has 3 consecutive months of processing. The second merchant misses a month. any help would be appreciated

I have written the code to get me to the data below. If possible, I need code to tell me if a merchant has processed for a minimum of 12 consecutive months


Merchant_id Processing date
876yu 03/05/2005
876yu 03/07/2005
876yu 04/05/2005
876yu 04/06/2005
876yu 05/07/2005
7645h 08/02/2005
7645h 08/03/2005
7645h 08/04/2005
7645h 10/06/2005
7645h 11/06/2005

 

[GingerR]

Hi. I made a table called MerchantCharges with fields

MerchantID
ProcessingMonth

I made a query with this SQL:

SELECT MerchantCharges.MerchantID, CDate(Month([ProcessingDate]) & "/01/" & Year([ProcessingDate])) AS ProcessingMonth
FROM MerchantCharges
GROUP BY MerchantCharges.MerchantID, CDate(Month([ProcessingDate]) & "/01/" & Year([ProcessingDate]))
ORDER BY CDate(Month([ProcessingDate]) & "/01/" & Year([ProcessingDate]));

That first query is named MerchantChargeMonths. If you run it you'll see that it changes the dates to months like

5/1/05
6/1/05
7/1/05
etc, so they can be grouped.

Next I made a second query: it uses the first query twice, comparing MerchantID's that have both a month and the next month. If this happens 11 times, then they have 12 consequtive months. If it happens 12 or more times, they have 13 or more consequtive months. If you want ONLY 12 and not more than 12, change the >=11 to just =11.

SELECT MerchantChargeMonths.MerchantID
FROM MerchantChargeMonths INNER JOIN MerchantChargeMonths AS MerchantChargeMonths_1 ON MerchantChargeMonths.MerchantID = MerchantChargeMonths_1.MerchantID
WHERE (((MerchantChargeMonths.ProcessingMonth)=DateAdd("m",-1,[MerchantChargeMonths_1]![ProcessingMonth])))
GROUP BY MerchantChargeMonths.MerchantID
HAVING (((Count(MerchantChargeMonths.MerchantID))>=11));

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html