count item as out for every month between two dates

[dsiruss]

Hi, Looking for help with a query, need to show all 12 months showing if the item was out between the two dates “ShipDate” and “ReturnDate”



Example

ID:1234 Shipped on 2/15/2013 and returned 5/15/2013

Jan Feb March April May June July August Sept Oct Nov Dec
ID:1234 Yes Yes Yes

Thanks in advance for any help you can provide...

[June7]

Maybe something like:

SELECT ID, IIf(1>=Month([Shipped]) AND 1<=Month([Returned]),"Yes","No") AS Jan FROM tablename;

Partial month treated as full month.

Complication arises if the date range crosses years. Not sure how to deal with that. Might require a custom function.

[dsiruss]

Thanks for the help, I will give it a try but looks like what I was looking for. Maybe someone can offer help on the complication arising if if the date range crosses years?

[dsiruss]

I really appreciate the help and I am getting closer, ran the query and had two lines, goal is to have a single line per asset and year.

Example
Asset 123 ships out a couple of times in the year 2014
out - 1/4/2014 In - 2/28/2014
out - 4/4/2014 In - 9/28/2014

My Goal
Single line - Asset, 123 / Year, 2014 /Jan, Yes /Feb, Yes / March, No / April, Yes / May, Yes / June, Yes / July, Yes / August, Yes / Sept, Yes / Oct, No / Nov, No / Dec, No
Is this possible?

[June7]

Try:

SELECT Asset, Year([DateOut]) AS YearOut, Max(IIf(1>=Month([DateOut]) And 1<=Month([DateIn]),"Yes","No")) AS Jan, Max(IIf(2>=Month([DateOut]) And 2<=Month([DateIn]),"Yes","No")) AS Feb, Max(IIf(3>=Month([DateOut]) And 3<=Month([DateIn]),"Yes","No")) AS Mar, Max(IIf(4>=Month([DateOut]) And 4<=Month([DateIn]),"Yes","No")) AS Apr
FROM Tablename
GROUP BY Asset, Year([DateOut]);

For crossing year (only one crossing within the date range):
SELECT Asset, Year([DateOut]) AS YearOut, Max(IIf(1>=Month([DateOut]) And 1<=IIf(Year([DateOut])<>Year([DateIn]),12,0)+Month([DateIn]),"Yes","No")) AS Jan, Max(IIf(12>=Month([DateOut]) And 12<=IIf(Year([DateOut])<>Year([DateIn]),12,0)+Month([DateIn]),"Yes","No")) AS Dec
FROM Tablename
GROUP BY Asset, Year([DateOut]);

[dsiruss]

Wow, June7 once again you have been a HUGE help, thanks!

But experiencing problem, this maybe the crossover you are referring too..
Example;
asset 123, has Start = 9/1/2013 and End = 10/25/2013
asset 123, has Start = 12/1/2013 and End = 12/15/2013
asset 123, has Start = 12/16/2013 and End = 1/30/2014 (Showing in years 2013 and 2014 as it has activity in both years)
As per the list of dates, looking to get a summary of lines as (Asset 123, Year, 2013 Sept, Yes, Oct, Yes, Nov, No, Dec, Yes) and also (Asset 123, Year, 2014 Jan, Yes, Feb, No, Mar, No,) etc..

Again, I really appreciate your help and any comments you may have, thanks!

[June7]

The multiple crossovers I meant would be like 12/16/2013 - 1/30/2015 involving 3 years.

Issue of rolling the months did occur to me but was avoiding. I think a custom function will be needed. Get back to you.

[dsiruss]

June7, Were you able to come up with any ideas regarding a work around for the Issue of rolling the months?

[June7]

I did lose track of this. Can you provide sample data? A table in post or attached spreadsheet would be enough but if you want to provide db, follow instructions at bottom of my post.