listening
Main Topics
Browse All TopicsI am trying to generate a report that accepts two text entries (start_date and end_date) via a php page and uses these values to query a postgresql database. My primary objective of this question is to find the most appropriate/accurate way to handle the date range identification and processing so providing thoughts/examples of the necessary loops and arrays is desired. Use any combination of PHP and SQL.
Database table/fields necessary are:
table: tbleventlog
field: id
field: dtdatetime
field: iusername
The input/output (note: users can input any valid start and end dates - no pulldowns, calendars, etc):
Each column of output must represent one week (sunday through saturday mandatory).The trick is to
capture the end_date the user submitted and if it isn't a saturday, find the next calendar saturday and then begin working backward "week-by-week" until we get to the start_date*. However, the start_date must be a sunday, so if the start_date the user input isn't a sunday, then we need to find the sunday immediately preceeding the start_date supplied by the user. This functionality must be capable of spanning months and years.
For example: a user submits start-date of 02/18/03 and end_date: 03/07/03.
** 02/18/03 is a tuesday and 03/07/03 is a friday **. Therefore, the end_date must be redefined as
03/08/03 because that's the next saturday. Likewise, the start_date of 02/18/03 will have to be
redefined as 02/16/02 because that's the preceeding saturday.
The report output should resmble the following:
Weekly Logins
|username | 02/16/ - 02/22 | 02/23 - 03/01 | 03/02 - 03/08 | total
|-------------------------
|johndoe@123.com | 12 | 0 | 3 | 15 |
|janewho@987.com | 3 | 4 | 13 | 20 |
|danwhat@345.com | 1 | 12 | 9 | 22 |
|jimwhen@abc.com | 0 | 7 | 15 | 22 |
|-------------------------
Thanks and good luck. (Many points for the headache). Ask for additional details if I haven't been thorough enough.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
bljak, no tables are created in this example. The table (tbleventlog) is queried for the information shown in the sample output. Date validation is not critical but for the sake of this example, let's ensure the user inputs the dates in m/d/Y (03/31/2002) format. Good point about the login numbers. Here the revised tbleventlog:
table: tbleventlog
field: id
field: eventid
field: dtdatetime
field: iusername
ieventid: 4 (Login)
Thanks,
Thomas
No code, but hopefully a method to acheive what is required.
Essentially, sub-selects are required.
This is via Access (it is on the pc!).
SELECT DISTINCT
Actions.name,
[2003-03-01].CountOfid,
[2003-03-02].CountOfid,
[2003-03-03].CountOfid
FROM
((Actions
LEFT JOIN [2003-03-01] ON Actions.name = [2003-03-01].name)
LEFT JOIN [2003-03-02] ON Actions.name = [2003-03-02].name)
LEFT JOIN [2003-03-03] ON Actions.name = [2003-03-03].name
ORDER BY Actions.name;
This is the main query.
The queries [2003-03-01], etc are ...
SELECT DISTINCT
Actions.name,
Count(Actions.id) AS CountOfid
FROM
Actions
GROUP BY
Actions.name,
Actions.date
HAVING
Actions.date=#3/1/2003#
ORDER BY
Actions.name;
And the HAVING clause being different for each day.
So. This is not yet ideal as this requires the queries to already exist and then you pull all of it together in 1 hit.
So, adding them all together is a little messy but ...
SELECT DISTINCT Actions.name, [2003-03-01].CountOfid, [2003-03-02].CountOfid, [2003-03-03].CountOfid
FROM ((Actions
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/1/2003# ORDER BY Actions.name) AS [2003-03-01] ON Actions.name = [2003-03-01].name)
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/2/2003# ORDER BY Actions.name) AS [2003-03-02] ON Actions.name = [2003-03-02].name)
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/3/2003# ORDER BY Actions.name) AS [2003-03-03] ON Actions.name = [2003-03-03].name
ORDER BY Actions.name;
So. with my data looking like ...
id name state date
1 name1 email1 01/02/2003
2 name2 inactive 02/02/2002
3 name3 new 23/06/2003
4 name4 active 12/08/2002
5 name2 new 15/03/2003
6 name1 new 16/03/2003
7 name1 inactive 11/10/2002
10 Richard new 01/03/2003
11 Richard active 02/03/2003
12 Alan new 02/03/2003
13 Mike new 03/03/2003
14 John new 04/03/2003
15 John active 06/03/2003
16 Mike active 07/03/2003
17 Richard expired 08/03/2003
18 Alan active 09/03/2003
I get results of ...
name 2003-03-01.CountOfid 2003-03-02.CountOfid 2003-03-03.CountOfid
Alan 1
John
Mike 1
name1
name2
name3
name4
Richard 1 1
You basically get ALL the users you have in the table and how many entries for the chosen dates.
Ok. This is the query, adding the rest of the code to do all of this is pretty basic, but is this a good enough start for you?
Just to be a little more helpful query, this is the same with the names of the sub-queries being daynames.
SELECT DISTINCT Actions.name, Sun.CountOfid, Mon.CountOfid, Tue.CountOfid, Wed.CountOfid, Thu.CountOfid, Fri.CountOfid, Sat.CountOfid
FROM ((((((Actions
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/2/2003# ORDER BY Actions.name) AS Sun ON Actions.name = Sun.name)
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/3/2003# ORDER BY Actions.name) AS Mon ON Actions.name = Mon.name)
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/4/2003# ORDER BY Actions.name) AS Tue ON Actions.name = Tue.name)
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/5/2003# ORDER BY Actions.name) AS Wed ON Actions.name = Wed.name)
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/6/2003# ORDER BY Actions.name) AS Thu ON Actions.name = Thu.name)
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/7/2003# ORDER BY Actions.name) AS Fri ON Actions.name = Fri.name)
LEFT JOIN (SELECT DISTINCT Actions.name, Count(Actions.id) AS CountOfid FROM Actions GROUP BY Actions.name, Actions.date HAVING Actions.date=#3/8/2003# ORDER BY Actions.name) AS Sat ON Actions.name = Sat.name)
ORDER BY Actions.name;
You can add ...
, SUM(Sun.CountOfid, Mon.CountOfid, Tue.CountOfid, Wed.CountOfid, Thu.CountOfid, Fri.CountOfid, Sat.CountOfid) as WeeklyTotal
to the list of fields, but this won't work in access.
Regards,
Richard Quadling.
Slightly better laid out.
SELECT DISTINCT
Actions.name,
Sun.CountOfid,
Mon.CountOfid,
Tue.CountOfid,
Wed.CountOfid,
Thu.CountOfid,
Fri.CountOfid,
Sat.CountOfid,
SUM(
Sun.CountOfid,
Mon.CountOfid,
Tue.CountOfid,
Wed.CountOfid,
Thu.CountOfid,
Fri.CountOfid,
Sat.CountOfid) as WeeklyTotal
FROM ((((((
Actions
LEFT JOIN (
SELECT DISTINCT
Actions.name,
Count(Actions.id) AS CountOfid
FROM
Actions
GROUP BY
Actions.name,
Actions.date
HAVING
Actions.date=#3/2/2003#
ORDER BY
Actions.name) AS Sun
ON
Actions.name = Sun.name)
LEFT JOIN (
SELECT DISTINCT
Actions.name,
Count(Actions.id) AS CountOfid
FROM
Actions
GROUP BY
Actions.name,
Actions.date
HAVING
Actions.date=#3/3/2003#
ORDER BY
Actions.name) AS Mon
ON
Actions.name = Mon.name)
LEFT JOIN (
SELECT DISTINCT
Actions.name,
Count(Actions.id) AS CountOfid
FROM
Actions
GROUP BY
Actions.name,
Actions.date
HAVING
Actions.date=#3/4/2003#
ORDER BY
Actions.name) AS Tue
ON
Actions.name = Tue.name)
LEFT JOIN (
SELECT DISTINCT
Actions.name,
Count(Actions.id) AS CountOfid
FROM
Actions
GROUP BY
Actions.name,
Actions.date
HAVING
Actions.date=#3/5/2003#
ORDER BY
Actions.name) AS Wed
ON
Actions.name = Wed.name)
LEFT JOIN (
SELECT DISTINCT
Actions.name,
Count(Actions.id) AS CountOfid
FROM
Actions
GROUP BY
Actions.name,
Actions.date
HAVING
Actions.date=#3/6/2003#
ORDER BY
Actions.name) AS Thu
ON
Actions.name = Thu.name)
LEFT JOIN (
SELECT DISTINCT
Actions.name,
Count(Actions.id) AS CountOfid
FROM
Actions
GROUP BY
Actions.name,
Actions.date
HAVING
Actions.date=#3/7/2003#
ORDER BY
Actions.name) AS Fri
ON
Actions.name = Fri.name)
LEFT JOIN (
SELECT DISTINCT
Actions.name,
Count(Actions.id) AS CountOfid
FROM
Actions
GROUP BY
Actions.name,
Actions.date
HAVING
Actions.date=#3/8/2003#
ORDER BY
Actions.name) AS Sat
ON
Actions.name = Sat.name)
ORDER BY
Actions.name;
This query can be constructed with different dates for different weeks.
If your database is PostgreSQL (good choice), and you have installed pl/pgsql on your database, then run the code below in your psql client. Then :
select my_report('view_name','200
will generate the view 'view_name' and :
select * from view_name;
will display the results.
I've tested it on PostgreSQL 7.3.2 (latest) but it should work with earlier versions.
the php code needed to display this is easy, but if you have any difficulties let me know.
CREATE OR REPLACE FUNCTION my_report(TEXT, DATE, DATE) RETURNS TEXT AS '
-- create a view with one week per column based on table :
-- CREATE TABLE tbleventlog (
-- id SERIAL;
-- dtdatetime TIMESTAMP;
-- iusername TEXT;
-- )
--
-- Copyleft 2003, GNU GPL V2, by polo (alias cryptic SQL generator :)
--
DECLARE
_view ALIAS FOR $1; -- name of the view to create
_date1 ALIAS FOR $2; -- start date of the report
_date2 ALIAS FOR $3; -- end date of the report
datefdow DATE; -- date of the First Day Of Week
dateldow DATE; -- date Of the Last Day Of Week
sql TEXT; -- sql code to be executed
BEGIN
-- first drop the view if it exists
EXECUTE ''SELECT viewname FROM pg_views WHERE viewname = '' || quote_literal(_view) || '';'';
IF FOUND THEN
EXECUTE ''DROP VIEW '' || quote_ident(_view) || '';'';
END IF;
-- initiate the sql string for view creation
sql:= ''CREATE VIEW '' || quote_ident(_view) || '' AS SELECT iusername,'';
-- datefdow gets the first day of week
datefdow := _date1 - CAST(EXTRACT(dow FROM _date1) || '' days'' AS INTERVAL);
-- iterate until _date2
WHILE datefdow <= _date2 LOOP
-- setting the last day for week
dateldow = datefdow + INTERVAL ''6 days'';
sql := sql || '' SUM(CASE WHEN CAST(dtdatetime AS DATE) BETWEEN '' || quote_literal(datefdow) || '' AND '' || quote_literal(dateldow) || '' THEN 1 ELSE 0 END) AS "'';
sql := sql || TO_CHAR(datefdow,''MM/DD - '') || TO_CHAR(dateldow, ''MM/DD'') || ''",'';
-- next week
datefdow := datefdow + INTERVAL ''7 days'';
END LOOP;
-- add the trailing statements
sql := sql || '' COUNT(*) AS TOTAL FROM tbleventlog GROUP BY iusername ORDER BY iusername;'';
-- create the view
EXECUTE sql;
-- return the sql generated (for debugging purposes)
RETURN sql;
END;
' LANGUAGE 'plpgsql';
Business Accounts
Answer for Membership
by: drnadeemPosted on 2003-03-29 at 21:19:42ID: 8232930
listening