Query Database for Summary 1

[shelby55]

Hi

SkipVought offered me brilliant advice last year on a project I was working on. The new fiscal year is here and I have to update the template for use with 2010/11 but want to add something else. Hopefully someone (Skip?) can help.

My worksheet takes dates from a data entry page and copies to them to another page to calculates days each month between the cases:
SELECT `'09_10$'`.F5 AS 'referral', `'09_10$'`.F6 AS 'assessment'
FROM `'09_10$'` `'09_10$'`
WHERE (trim(`'09_10$'`.F5) Is Not Null)

Then to summarize the total days per month I used:
=SUMPRODUCT((dts>=D$1)*(dts<E$1)*(dts>=IF($A2="",TODAY(),$A2)+1)*(dts<=IF($B2="",TODAY(),$B2)))

The above is for the April column only but just showing what I used.

Then a summary for the total days is:
SELECT Sum(`ReferAssess$`.`Total Days`) AS 'Total Days', Sum(`ReferAssess$`.F4) AS 'Apr-09' <etc for full year of months>
FROM `ReferAssess$` `ReferAssess$`

Now I want to create a summary for patients that don't have an assessment date per month. So if a patient had a referral date of March 10 but didn't have an assessment date until July 1 then they would show as a person awaiting assessment in March, April, May and June.

How do I do this? Thanks.

 

[SkipVought]

Shelby,

Please post a sample of data (1) that represents your problem adequately and a corresponding sample of data (2) that represents the result based on (1).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Everything works thus far but the thing I'm asking for is new so I can only post what I want it to look like.

The data entry screen looks like this:
chart number referral date assessment date
12345 4/1/09 5/30/09
45678 5/1/09
88997 6/1/09 7/30/09
55557 6/15/09 8/30/09
88833 6/17/09

The first code results in a line of data per patient showing total days from referral date to assessment date and splitting up those days per month of activity:
referral date assessment date Ttl Days 04/09 05/09 06/09
4/1/09 5/30/09 59 29 30
5/1/09 343 30 30
6/1/09 7/30/09 59 29
6/15/09 8/30/09 76 15
6/17/09 296 13

Note for above that total days is for entire year.

The summary of days looks like this:

Total Days Apr-09 May-09 Jun-09 Jul-09 etc.
833 29 60 87 123

Now I want to show # of patients per month who have a referral date and not an assessment date:
Apr-09 May-09 Jun-09 Jul-09 etc.
0 1 2 2

In this case "total" doesn't make sense because one patient could be counted many times.

Does this help? Thanks Skip!!

 

[SkipVought]

1. Please post TABLUAR EXAMPLES using TGML tags and align all values in proper columns for understanding. (do a FIND on this web page for [blue]ProcessTGML[/blue], follow the link and read up on the [tt][ignore][tt]...[/tt][/ignore][/tt] tag)

Which is more understandable?
without TGML tags
'referral' 'assessment' Total Days Jan-09 Feb-09
1/5/2009 2/6/2009 32 26 6
4/1/2009 373 0 0
4/14/2009 4/14/2009 0 0 0

or
with TGML tags AND removing TABS -- adding SPACES
[tt]
'referral' 'assessment' Total Days Jan-09 Feb-09
1/5/2009 2/6/2009 32 26 6
4/1/2009 373 0 0
4/14/2009 4/14/2009 0 0 0
[/tt]

2. Please coordinate your examples so that occurrences, counts and sums actually match in each example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

I read as you requested but I still don't know how to put in columns correctly - is the [tt] and [/tt] used per line or at the start and end of the group?

I guess I'm confused because some of the examples were for show but they are already working with the code provided. Now instead of total days per month I want total people outstanding per month i.e. have a referral date but not an assessment date.

Thanks.

 

[SkipVought]

Example:
[tt]
with TGML tags AND removing TABS -- adding SPACES
[ignore][tt][/ignore]
'referral' 'assessment' Total Days Jan-09 Feb-09
1/5/2009 2/6/2009 32 26 6
4/1/2009 373 0 0
4/14/2009 4/14/2009 0 0 0
[ignore][/tt][/ignore]
[/tt]
HINT: It often take me MORE TIME to format a table properly for viewing (removing the TABS between values and adding SPACES can be a manual tedious process, but WELL WORTH the time) than it takes to code many solutions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Right now my worksheet correctly calculates the total number of days between referral date and assessment date and those total days split out per month. Each line of information is one patient's case.

[tt]referral date assessment date Ttl Days 04/09 05/09 06/09....to end of year
4/1/09 5/30/09 59 29 30
5/1/09 343 30 30
6/1/09 7/30/09 59 29
6/15/09 8/30/09 76 15
6/17/09 296 13
[/tt]

The code for the Total Days column is:
=IF($B2="",TODAY(),$B2)-IF($A2="",TODAY(),$A2)

The code for April's column is:
=SUMPRODUCT((dts>=D$1)*(dts<E$1)*(dts>=IF($A2="",TODAY(),$A2)+1)*(dts<=IF($B2="",TODAY(),$B2)))

All the other months have code similar to April but comparing to that month's column head (which is in date format).

What I want to do now is show the number of patients per month who have a referral date but not an assessment date i.e. waiting for assessment. Total cases doesn't apply because each line is one patient who can be counted once for every month they don't have an assessment.

[tt]referral date assessment date 04/09 05/09 06/09 07/09.....to end of year
4/1/09 6/30/09 1 1 0 0
5/1/09 7/30/09 1 1 0
5/15/09 1 1 1
5/19/09 7/15/09 1 1 0
[/tt]

Any assistance greatly appreciated - thanks.

 

[SkipVought]

My table
[tt]
chart number referral date assessment date Apr-09 May-09
12345 4/1/2009 5/30/2009 1 1
45678 5/1/2009 0 1
88997 6/1/2009 7/30/2009 0 0
55557 6/15/2009 8/30/2009 0 0
88833 6/17/2009 0 0
[/tt]
The formula in D2 (Apr-09 row 2)
[tt]
D2: =IF($C2=0,SUMPRODUCT(--($B2<=D$1)),SUMPRODUCT(($C2>=D$1)*($B2<E$1)))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks very much. I actually don't have chart number in column A but was able to use my column values to get this to work.

The only issue is that I count the month of reference but not the month of assessment. For instance, in your results example for chart 12345 you show 1 case in May but they were treated in May so should only have a count in April.

How to change the formula so it's not counted in the month of assessment? Thanks.

 

[SkipVought]

[tt]
D2: =IF($B2<=D$1,1,0)-IF($C2="",0,IF(TEXT($C2,"yyyymm")<=TEXT(D$1,"yyyymm"),1,0))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks. Sorry but what is your "B" referring to - referral date or assessment date? The formula is now showing -1 in some columns but I think it might just be that the formulae is using different columns.

A = referral date, B = assessment date and C is for April column. I wasn't sure what you were doing to know how to make the edits to my table configuration.

Thanks.

 

[SkipVought]

Just insert an empty column in A. B would be your refereal date. C assessment date, D first column of formula.

Then delete the empty column and the formula will adjust.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks but still not working: showing those patients who are referred and assessed in same month as -1.

 

[shelby55]

Hi Skip

I tweaked the formula to:
=IF(TEXT($A2,"yyyymm")<=TEXT(C$1,"yyyymm"),1,0)-IF($B2="",0,IF(TEXT($B2,"yyyymm")<=TEXT(C$1,"yyyymm"),1,0))

and it appears to be working now. Thanks for helping me out!!

 

[shelby55]

HI

Sorry but still not working. Now it is adding a case to months that haven't taken place yet which I don't want it to do.

Any suggestions? Thanks very much.

 

[Gavona]

One way:
if(C$1>Today(),"",ExistingFormula)




Gavin

 

[SkipVought]

showing those patients who are referred and assessed in same month as -1.

That is why good test data contains EVERY case that real world will encounter.

You will save yourself a lot of time and aggravation if you carefully construct your test data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Good point...but still doesn't offer a solution to my problem.

 

[SkipVought]

The added logic should be...
[tt]
if
( and
(
[referral "yyyymm"]=[assessment "yyyymm"],
[referral "yyyymm"]=[Row1Date "yyyymm"]
) then 1 else ...
the existing formula
)
[/tt]
Please understand that this is a SPECIAL CASE that is not covered by the logic that defined your original requirement. This is why a clear, concise and complete requirement must be stated before a valid solution can be achieved.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks for your reply.

Unfortunately, this is still not working. I created the formula as you stated though using my columns of referral (A), assessment(B), column head (C).

=IF(AND(TEXT($A$2,"yyyymm")=TEXT($B$2,"yyyymm"),(TEXT($A$2,"yyyymm")=TEXT(C1,"yyyymm"))),1,IF(AND($B2="",TEXT(TODAY(),"yyyymm")<=TEXT(C$1,"yyyymm")),0,0))

The scenario for outstanding patients are:
= if referral and assessment month the same, won't count as outstanding
= if referral and assessment months different, will count for each month outstanding but not treatment month. So if referral is April 1, 2010 and assessment is June 15, 2010 the patient will show as outstanding for April and May but not June
= if referral date present but assessment date is blank then will calculate as outstanding until assessment date entered.

The current formula is still not taking into account the patients who are referred and assessed in same month.

Thanks.