Combine months and years in Query (XP)

Topic

New Reply

This may have been asked and answered in a more articulate manner, but I can’t find it. Sorry.

Have any of you come across a situation where your user wants to compare a length of time in a chart (or query) where the time span is not constant? In this case the user wants to compare the length of time a person was with the company before certain events occurred. I created a nice pareto chart providing what he asked for ( a graph comparing length of service at time of event), but not what he wanted.

Instead of a graph showing 0, 1, 2, 3, 4 etc. years (which is what he originally asked for) he wants a graph of events at:
: 0-3 months,
: >3 6 9 <=12 months then
: one year increments after the first 12 months

Does that make sense? Is it possible to create a query to accomplish? Am I not seeing the forest for the trees?

Thanks in advance for any ideas.

Reply | Quote

Replies

Can you use something like the query in the attached database? It’s not clear to me how, but that’s up to you…

Reply | Quote

WOW! That was awesome! The [duration] column in the query was exactly what I was seeking. I don’t know how you do it, but I am glad you do.

THANKS HANS!

Reply | Quote

WOW! That was awesome! The [duration] column in the query was exactly what I was seeking. I don’t know how you do it, but I am glad you do.

THANKS HANS!

Reply | Quote

Hans,

What you suggested worked great and allowed me to give him what he asked for. Of course, once he saw the results, it was not what he wanted. Using the query allowed showing that the problems were not just the first 12 months, but in fact roll through the second year. I have used the following SQL to accomplish what he asked for (i.e. first 12 months by quarter, then by year):

SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff(“yyyy”,[Date1]+1,[Date2])+(Format([Date1]+1,”mmdd”)>Format([Date2],”mmdd”)) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<1,([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y") AS Duration
FROM tblDates;

I have tried understanding how you were able to break the first year down by quarters, and then modifying the query to go out 24 months by quarter, then annually thereafter, but to no avail. Would you mind terribly modify the above query to accomplish the 24 instead of 12 month comparison. Hopefully, I can see by example what I am not understanding.

Thank you.

Ken

Reply | Quote

Does this do what you want?

SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff(“yyyy”,[Date1]+1,[Date2])+(Format([Date1]+1,”mmdd”)>Format([Date2],”mmdd”)) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<1,([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",IIf([Years]<2,"1 y " & ([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y")) AS Duration
FROM tblDates;

Reply | Quote

Hans,

Almost. It does exactly what I want for 0-12 months (0-3m, 4-6m, 7-9m, and 10-12 m). For months 12-24, instead of “1 y 0-3 m” I was trying to get “13-15 m”, and so on through 24 months, then continue with 3 y, 4 y, 5 y, etc.

Does that make sense?

Thanks,

Ken

Reply | Quote

The SQL then becomes

SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff(“yyyy”,[Date1]+1,[Date2])+(Format([Date1]+1,”mmdd”)>Format([Date2],”mmdd”)) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<2,((12*[Years]+[Months])3)*3 & "-" & (((12*[Years]+[Months])3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y") AS Duration
FROM tblDates;

If you look at the expression for Duration:

IIf([Years]<2,((12*[Years]+[Months])3)*3 & “-” & (((12*[Years]+[Months])3)*3+3) & ” m”,[Years] & “-” & ([Years]+1) & ” y”)

and analyze it part by part, you should be able to see how it works, and add further tweaks if desired.

Reply | Quote

Hans,

Thanks for the lesson. I shall put it to good use, and hopefully remember how to use in the future.

Reply | Quote

Hans,

Thanks for the lesson. I shall put it to good use, and hopefully remember how to use in the future.

Reply | Quote

The SQL then becomes

SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff(“yyyy”,[Date1]+1,[Date2])+(Format([Date1]+1,”mmdd”)>Format([Date2],”mmdd”)) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<2,((12*[Years]+[Months])3)*3 & "-" & (((12*[Years]+[Months])3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y") AS Duration
FROM tblDates;

If you look at the expression for Duration:

IIf([Years]<2,((12*[Years]+[Months])3)*3 & “-” & (((12*[Years]+[Months])3)*3+3) & ” m”,[Years] & “-” & ([Years]+1) & ” y”)

and analyze it part by part, you should be able to see how it works, and add further tweaks if desired.

Reply | Quote

Hans,

Almost. It does exactly what I want for 0-12 months (0-3m, 4-6m, 7-9m, and 10-12 m). For months 12-24, instead of “1 y 0-3 m” I was trying to get “13-15 m”, and so on through 24 months, then continue with 3 y, 4 y, 5 y, etc.

Does that make sense?

Thanks,

Ken

Reply | Quote

Does this do what you want?

SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff(“yyyy”,[Date1]+1,[Date2])+(Format([Date1]+1,”mmdd”)>Format([Date2],”mmdd”)) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<1,([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",IIf([Years]<2,"1 y " & ([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y")) AS Duration
FROM tblDates;

Reply | Quote

Hans,

What you suggested worked great and allowed me to give him what he asked for. Of course, once he saw the results, it was not what he wanted. Using the query allowed showing that the problems were not just the first 12 months, but in fact roll through the second year. I have used the following SQL to accomplish what he asked for (i.e. first 12 months by quarter, then by year):

SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff(“yyyy”,[Date1]+1,[Date2])+(Format([Date1]+1,”mmdd”)>Format([Date2],”mmdd”)) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<1,([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y") AS Duration
FROM tblDates;

I have tried understanding how you were able to break the first year down by quarters, and then modifying the query to go out 24 months by quarter, then annually thereafter, but to no avail. Would you mind terribly modify the above query to accomplish the 24 instead of 12 month comparison. Hopefully, I can see by example what I am not understanding.

Thank you.

Ken

Reply | Quote

Can you use something like the query in the attached database? It’s not clear to me how, but that’s up to you…

Reply | Quote