Query to show repeat occurances between two dates

S

Sinoe

Registered User.
Local time
Today, 09:08
Joined
Mar 31, 2015
Messages
25
Hi Everyone,

It's been a while since I posted. I need your help! I am trying to develop a query that will provide information about alleged abuse. I want to be able to show that the same allegations occurred within 6 month of the initial allegation and to the same person. My data fields include:
1. date (incident occurred)
2. Incident Name
3. Determination date
5. Allegation name
6. Allegation code
7. Victim ID

Hope this is sufficient information.


Thanks.
 
Does SQL like this offer you any ideas?
Code: 
SELECT Count(*) As AllegationCount
FROM YourTable
WHERE VictimID = <VictimID>
   AND AllegationCode = <AllegationCode>
   AND Date <=  DateAdd("m", 6, <InitialAllegationDate>)
hth
Mark
 
Thanks so much for responding so quickly, Mark. I will try out the code as soon as I get the chance.
 
MarkK answer is short and will not
satisfy what you need to accomplish.
here is what you need:

SELECT YourTable.[Victim ID], YourTable.[Allegation Code], YourTable.ID, YourTable.Date, YourTable.[Incident Name], YourTable.[Determination Date], YourTable.[Allegation Name]
FROM YourTable
WHERE (((YourTable.[Victim ID]) In (SELECT [Victim ID] FROM [YourTable] As Tmp GROUP BY [Victim ID],[Allegation Code] HAVING Count(*)>1 And [Allegation Code] = [YourTable].[Allegation Code])) AND ((YourTable.Date)>=DateAdd("m",-6,Date())))
ORDER BY YourTable.[Victim ID], YourTable.[Allegation Code], YourTable.Date;
 
Thank you so much arnelgp. I can't wait to try this. MarkK's response was helpful but the result wasn't what I expected. I will post later about what results I get and if my original question was phased correctly.

Thanks again.
 
Thanks to Markk and arnelgp for their responsed. I am still having some trouble utilizing your solutions. As a result, I am posting a sample table of the data that I am working with in the hope of getting a query that will help me achieve my result which is to get a the number of allegations within a date range and whether the same allegations were repeated within six months of the initial determination.



Field NameData TypeAGENCY_RECCD_DATEDate/TimeAGENCY_INTERNAL_IDNumberMOTC_DI_AP_RPT_IDNumberMOTC_ASSESS_AP_RPT_IDNumberMOTC_INTAKE_AP_RPT_IDNumberMOTC_INTAKE_CNTY_CDShort TextMOTC_MAARC_CC_AP-RPT_IDNumberMOTC_VICTIM_IDNumberMOTC_SW_INDEX_IDNumberMOTC_MADI_RPMShort TextMOTC_FIRST_NAMEShort TextMOTC_LAST_NAMEShort TextMOTC_BIRTH_DTDate/TimeAGENCY_BIRTH_DATEDate/TimeMOTC_INTAKE_WG_START_DATEDate/TimeMOTC_INTAKE_WG_END_DATEDate/TimeMOTC_INTAKE_WG_REASON_ENDEDShort TextMOTC_RP_RPT_STATUS_CDShort TextMOTC_INTAKE_RP_ROLE_CDShort TextMOTC_RP_ALLEG_CDShort TextMOTC_ALLEG_CODEShort TextMOTC_ALLEG_DESCShort TextMOTC_DISP_CODEShort TextMOTC_DISP_CODEShort Text

Thanks you.
 
Can you post actual sample data to demonstrate your issue? Post 2 sets of data:

A. Starting sample data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you hope to end up with when you feed this query the data from A.
 
Thanks plog. Here is the table and sample result.


AGENCY_RECCD_DATEAGENCY_INTERNAL_IDMOTC_DI_AP_RPT_IDMOTC_ASSESS_AP_RPT_IDMOTC_INTAKE_AP_RPT_IDMOTC_INTAKE_CNTY_CDMOTC_MAARC_CC_AP-RPT_IDMOTC_VICTIM_IDMOTC_SW_INDEX_IDMOTC_MADI_RPMMOTC_FIRST_NAMEMOTC_LAST_NAMEMOTC_BIRTH_DTAGENCY_BIRTH_DATEMOTC_INTAKE_WG_START_DATEMOTC_INTAKE_WG_END_DATEMOTC_INTAKE_WG_REASON_ENDEDMOTC_RP_RPT_STATUS_CDMOTC_INTAKE_RP_ROLE_CDMOTC_RP_ALLEG_CDMOTC_ALLEG_CODEMOTC_ALLEG_DESCMOTC_DISP_CODEMOTC_DISP_CODE

Sample result


Total Substantiated Reports to RP investigated by the County Total Substantiated Repeats Within 6 Months Total Substantiated or Inconclusive Recurrences Within 6 Months State Fiscal YearsAllegation% Allg Repeats% Allg Repeats2016FE nonfiduciary200.00%00.00%2016Self Neglect2150.00%150.00%
 
1. I can barely make any sense of what you posted.

2. The sense I can make is that what you posted is not data, but field names.


Please provide me with 2 sets of data as requested prior. You can use a spreadsheet and upload it, or post it in the thread using this format:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
Sally, 4/1/2018, 76
David, 5/11/2011, 43
Carl, 3/3/2008, 11
 
your query should look something
like this:

SELECT motc_table.*
FROM motc_table WHERE (((motc_victim_id) IN (SELECT motc_victim_id FROM motc_table AS Tmp GROUP BY motc_victim_id,motc_alleg_code HAVING Count(*)>1 And tmp.motc_alleg_code = motc_table.motc_alleg_code)) AND ((motc_table.agency_reccd_date) >= DateAdd("m", -6, Date())))



'------
replace motc_table with the actual name of your table.
 
You must log in or register to reply here.

Similar threads

K
Solved Compare Dates in Two Tables
Replies
8
Views
518
Pat Hartman
P
X
When was my Worker's Last Day Off?
Replies
3
Views
383
MarkK
MarkK
A
Filter Records
Replies
1
Views
269
June7
June7
medihub_guy
Solved Searching between two dates just doesnt seem to work properly
Replies
15
Views
720
Pat Hartman
P
C
Query to find the MIN value of multiple date fields
Replies
16
Views
655
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom