REmove Duplicates - Keeping most recent
REmove Duplicates - Keeping most recent
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
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
Hi,
can you explain how your table looks like? What does "most recent" mean? Based on which information?
can you explain how your table looks like? What does "most recent" mean? Based on which information?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: REmove Duplicates - Keeping most recent
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
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
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.
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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: REmove Duplicates - Keeping most recent
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?????
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
Disregard last question. I see now to create the view in the database.
Hope it works!!
Thanks
Hope it works!!
Thanks
Re: REmove Duplicates - Keeping most recent
Does this look right, it does not produce a result???
Thanks!!
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)
Re: REmove Duplicates - Keeping most recent
Hi,
can you post the definition of your tables. Which is the common key between both?
can you post the definition of your tables. Which is the common key between both?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: REmove Duplicates - Keeping most recent
This is the original table
This is the copy I made that I want to make the view.
Thanks again
Jim
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;
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
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)
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)
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: REmove Duplicates - Keeping most recent
I am stumped....
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0016 seconds.)
Jim
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0016 seconds.)
Jim
Re: REmove Duplicates - Keeping most recent
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)
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)
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: REmove Duplicates - Keeping most recent
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
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
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
Last piece how to I get to it from my AG program menu?
Thanks
Re: REmove Duplicates - Keeping most recent
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
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
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
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
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: REmove Duplicates - Keeping most recent
WOW, I think it might be working...…..
Thank you so much, I learned a great deal! and solved a problem.
Jim
Thank you so much, I learned a great deal! and solved a problem.
Jim
Re: REmove Duplicates - Keeping most recent
So if I make changes and recompile will that have to be done again?
Hopefully the final question.
Thanks
Jim
Hopefully the final question.
Thanks
Jim
Re: REmove Duplicates - Keeping most recent
Disregard I think I figured that one out!
Thanks again!
Jim
Thanks again!
Jim
Re: REmove Duplicates - Keeping most recent
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
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
Hello pböttcher and forum,
I think you might remember the help you gave me on this. It has been working great.
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.
I am hoping that you have a minute to tell me.
Thanks
Jim
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)
Code: Select all
WHERE `date_submitted` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
Thanks
Jim
Re: REmove Duplicates - Keeping most recent
Please disregard..... I got it.....
Thanks
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)