AppGini Community Forums

A place where AppGini users can exchange ideas and help each other.
https://forums.appgini.com:443/phpbb/

REmove Duplicates - Keeping most recent

https://forums.appgini.com:443/phpbb/viewtopic.php?f=8&t=3582

Page 1 of 1

REmove Duplicates - Keeping most recent

Posted: 2020-04-20 18:47
by jangle
Hello forum,

I have a table where people insert a status, total people and total people out each day.

I need to have a way to create a summary table that removes duplicates and only keeps the most recent submittal. I know I can pull out into excel, but I wanted to try and automate the is AP.

Through filters maybe????

Thanks in advance

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-20 18:55
by pbottcher
Hi,
can you explain how your table looks like? What does "most recent" mean? Based on which information?

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-20 20:03
by jangle
Hi,

The table has 10 fields, one of which is organization name (fire department). One other is date submitted. We are tracking members off so each dat an organization would submit the form. Let say 10 total - 5 out. Lets say the next day when they submit it is 10 total three out (2 came back) if I sum up all from the organization I would have 20 and 8 out when really in is 210 and 3 as the most recent submittal. So I would like a way to sure, then csv download the most recent only.

Hope that makes sense...

Thanks for the help.

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-20 21:21
by pbottcher
Hi,

not sure how you want to get the result presented, but I would create a "report" table as a copy of your input table and then replace it with a view in the database to directly reflect only the required information.

Something like

SELECT t1.*
FROM TABLENAME t1
WHERE t1.ID = (SELECT t2.ID
FROM TABLENAME t2
WHERE t2.ORGANISATION = t1.ORGANISATION
ORDER BY t2.DATE DESC
LIMIT 1)

Replace TABLENAME with your tablename, ID with your PK (if different), ORGANISATION, DATE with the respective fields of your table.

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-20 21:44
by jangle
Thanks pböttcher

Let me try that's.... I think, if I can get it right that that would work...

Would I out that code in hooks?????

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-21 02:54
by jangle
Disregard last question. I see now to create the view in the database.

Hope it works!!

Thanks

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-21 13:40
by jangle
Does this look right, it does not produce a result???

Code: Select all

SELECT * FROM Workforce_Impact_Survey WHERE workforce_impact_survey.ID = (SELECT workforce_impact_report.ID FROM workforce_impact_report WHERE workforce_impact_report.Fire_Department = workforce_impact_survey.Fire_Department ORDER BY workforce_impact_report.`date_submitted` DESC LIMIT 1)
Thanks!!

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-21 17:53
by pbottcher
Hi,

can you post the definition of your tables. Which is the common key between both?

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-21 19:08
by jangle
This is the original table

Code: Select all

CREATE TABLE `workforce_impact_survey` (
  `ID` int(10) UNSIGNED NOT NULL,
  `date_submitted` datetime DEFAULT NULL,
  `Fire_Department` int(10) UNSIGNED NOT NULL,
  `Total_operational_Workforce` int(5) NOT NULL,
  `workforce_out` int(5) NOT NULL,
  `Percentage_Out` decimal(3,0) DEFAULT NULL,
  `County` int(10) UNSIGNED DEFAULT NULL,
  `Region` int(10) UNSIGNED DEFAULT NULL,
  `Total_Positive` varchar(40) NOT NULL,
  `IAFF_Local` int(10) UNSIGNED DEFAULT NULL,
  `Address` int(10) UNSIGNED DEFAULT NULL,
  `Email` varchar(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This is the copy I made that I want to make the view.

Code: Select all

CREATE TABLE `workforce_impact_report` (
  `ID` int(10) UNSIGNED NOT NULL,
  `date_submitted` datetime DEFAULT NULL,
  `Fire_Department` int(10) UNSIGNED DEFAULT NULL,
  `Address` int(10) UNSIGNED DEFAULT NULL,
  `County` int(10) UNSIGNED DEFAULT NULL,
  `Region` int(10) UNSIGNED DEFAULT NULL,
  `IAFF_Local` int(10) UNSIGNED DEFAULT NULL,
  `Total_operational_Workforce` int(5) DEFAULT NULL,
  `workforce_out` int(5) DEFAULT NULL,
  `Percentage_Out` decimal(3,0) DEFAULT NULL,
  `Total_Positive` varchar(40) DEFAULT NULL,
  `Email` varchar(80) DEFAULT NULL

Thanks again

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-21 20:17
by pbottcher
Hi,
can you try

SELECT * FROM Workforce_Impact_Survey WHERE workforce_impact_survey.Fire_Department = (SELECT workforce_impact_report.Fire_Department FROM workforce_impact_report WHERE workforce_impact_report.Fire_Department = workforce_impact_survey.Fire_Department ORDER BY workforce_impact_report.`date_submitted` DESC LIMIT 1)

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-22 01:26
by jangle
I am stumped....

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0016 seconds.)

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-22 10:38
by pbottcher
Hi,

sorry, I put a wrong information in.

Can you try

SELECT t1.* FROM workforce_impact_survey t1 WHERE t1.ID = (SELECT t2.ID FROM workforce_impact_survey t2 WHERE t2.Fire_Department = t1.Fire_Department ORDER BY t2.date_submitted DESC LIMIT 1)

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-22 13:12
by jangle
First: don't be sorry.... I appreciate you help very much.

Second: it seems to work. I can put the query into the "report" table and it seems to work. How do I get that saved for that report and the call it in my AP program?

Thanks

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-22 14:20
by jangle
I have the report saved in my DB. When I click on the view in phpadmin it works great..

Last piece how to I get to it from my AG program menu?

Thanks

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-22 17:35
by jangle
I have tried to add to hooks: Workforce_Impact_Report_init

SELECT * FROM `report_view`
Or
$QueryFrom = `report_view`
Or
$sql = "SELECT * FROM `report_view`";

While it works in sql (phpadmin) I cannot get to work in AppGini

Thanks

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-22 18:24
by pbottcher
Hi,

you have the table workforce_impact_report create in AppGini.

Now you use phpmyadmin and

1, drop table workforce_impact_report
2, create view workforce_impact_report as SELECT t1.* FROM workforce_impact_survey t1 WHERE t1.ID = (SELECT t2.ID FROM workforce_impact_survey t2 WHERE t2.Fire_Department = t1.Fire_Department ORDER BY t2.date_submitted DESC LIMIT 1)

Now your "Tableview" in your app shall show the requested data

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-22 20:28
by jangle
WOW, I think it might be working...…..

Thank you so much, I learned a great deal! and solved a problem.

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-22 21:02
by jangle
So if I make changes and recompile will that have to be done again?

Hopefully the final question.
Thanks

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-23 12:50
by jangle
Disregard I think I figured that one out!

Thanks again!

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-04-29 02:56
by jangle
Hello Forum,

I now need a daily report from the view that is created with the query - SELECT t1.* FROM workforce_impact_survey t1 WHERE t1.ID = (SELECT t2.ID FROM workforce_impact_survey t2 WHERE t2.Fire_Department = t1.Fire_Department ORDER BY t2.date_submitted DESC LIMIT 1) -

I need to pull a count or records, sum of workforce, sum of members out and sum of tests. I would pull this from the current data each morning and save by date. I would also like to break down further by the region field.

I have tried by create a table in appgini but can’t really get it to do what I need.

If someone could point me in the right direction.

Thanks

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-10-05 20:43
by jangle
Hello pböttcher and forum,

I think you might remember the help you gave me on this. It has been working great.

Code: Select all

select `t1`.`ID` AS `ID`,`t1`.`date_submitted` AS `date_submitted`,`t1`.`Fire_Department` AS `Fire_Department`,`t1`.`Total_operational_Workforce` AS `Total_operational_Workforce`,`t1`.`workforce_out` AS `workforce_out`,`t1`.`Percentage_Out` AS `Percentage_Out`,`t1`.`County` AS `County`,`t1`.`Region` AS `Region`,`t1`.`Total_Positive` AS `Total_Positive`,`t1`.`IAFF_Local` AS `IAFF_Local`,`t1`.`Address` AS `Address`,`t1`.`Email` AS `Email` from `serp`.`workforce_impact_survey` `t1` WHERE `t1`.`ID` = (select `t2`.`ID` from `serp`.`workforce_impact_survey` `t2` where `t2`.`Fire_Department` = `t1`.`Fire_Department` order by `t2`.`date_submitted` desc limit 1)
Now I need this page to only pull from the last 30 day submissions. I got the following code to work fine on another table, but was hopoing you could tell me where to put it in the above statement.

Code: Select all

WHERE `date_submitted` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
I am hoping that you have a minute to tell me.

Thanks

Jim

Re: REmove Duplicates - Keeping most recent

Posted: 2020-10-06 20:17
by jangle
Please disregard..... I got it.....

Thanks

Code: Select all

select `t1`.`ID` AS `ID`,`t1`.`date_submitted` AS `date_submitted`,`t1`.`Fire_Department` AS `Fire_Department`,`t1`.`Total_operational_Workforce` AS `Total_operational_Workforce`,`t1`.`workforce_out` AS `workforce_out`,`t1`.`Percentage_Out` AS `Percentage_Out`,`t1`.`County` AS `County`,`t1`.`Region` AS `Region`,`t1`.`Total_Positive` AS `Total_Positive`,`t1`.`IAFF_Local` AS `IAFF_Local`,`t1`.`Address` AS `Address`,`t1`.`Email` AS `Email`,`t1`.`Operational_Impacts` AS `Operational_Impacts`,`t1`.`Strategies_Used_to_Overcome` AS `Strategies_Used_to_Overcome` from `serp`.`workforce_impact_survey` `t1` where `t1`.`ID` = (select `t2`.`ID` from `serp`.`workforce_impact_survey` `t2` where `t2`.`Fire_Department` = `t1`.`Fire_Department` and `t2`.`date_submitted` between current_timestamp() - interval 30 day and current_timestamp() order by `t2`.`date_submitted` desc limit 1)
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/