Counting Logins per month over 2 years

We are using Livelink 9.0.0 and SQL 7.I have already created a report that shows the monthly user logins for a specified year by means of a line chart. However we need a report that counts the logins per month for 1 year and also counts the logins per month for a previous year, so as they can be compared, i.e. 2 lines in the line graph - one for each year - super-imposed on each other.What I need to know is if you can use two different count functions, each with different criteria, in the same select statement. At the moment my statement uses one count function to count all the 'LOGIN' values in DAudit.Event where DAudit.auditdate is 2001 or 2002, etc. I can't get it working when I add a second count function with the criteria DAudit.auditdate is 2002, etc.Am I going about this problem in the wrong way or am I on the right track. Thanks in advance.

Find more posts tagged with

Comments

eLink User

Message from Mohsin Jessa via eLinkI have provided the solution for an oracle environment. You'll have tofigure out the solution for SQL server.I created a sample table with just one colcreate table dd ( c1 date);and then populated it with some dates. insert into dd values (to_date('01-JAN-01','dd-MON-YY'))/ insert into dd values (to_date('01-FEB-01','dd-MON-YY'))/ insert into dd values (to_date('01-MAR-01','dd-MON-YY'))/ insert into dd values (to_date('01-APR-01','dd-MON-YY'))/ insert into dd values (to_date('01-MAY-01','dd-MON-YY'))/ insert into dd values (to_date('01-JUN-01','dd-MON-YY'))/ insert into dd values (to_date('01-JUL-01','dd-MON-YY'))/ insert into dd values (to_date('01-AUG-01','dd-MON-YY'))/ insert into dd values (to_date('01-SEP-01','dd-MON-YY'))/ insert into dd values (to_date('01-OCT-01','dd-MON-YY'))/ insert into dd values (to_date('01-NOV-01','dd-MON-YY'))/ insert into dd values (to_date('01-DEC-01','dd-MON-YY'))/ insert into dd values (to_date('01-JAN-02','dd-MON-YY'))/ insert into dd values (to_date('01-FEB-02','dd-MON-YY'))/ insert into dd values (to_date('01-MAR-02','dd-MON-YY'))/ insert into dd values (to_date('01-APR-02','dd-MON-YY'))/ insert into dd values (to_date('01-MAY-02','dd-MON-YY'))/ insert into dd values (to_date('01-JUN-02','dd-MON-YY'))/ insert into dd values (to_date('01-JUL-02','dd-MON-YY'))/ insert into dd values (to_date('01-AUG-02','dd-MON-YY'))/ insert into dd values (to_date('01-SEP-02','dd-MON-YY'))/ insert into dd values (to_date('01-OCT-02','dd-MON-YY'))/ insert into dd values (to_date('01-NOV-02','dd-MON-YY'))/ insert into dd values (to_date('01-DEC-02','dd-MON-YY'))/ insert into dd values (to_date('15-JAN-02','dd-MON-YY'))/ insert into dd values (to_date('15-FEB-02','dd-MON-YY'))/ insert into dd values (to_date('15-MAR-02','dd-MON-YY'))/ insert into dd values (to_date('15-APR-02','dd-MON-YY'))/ insert into dd values (to_date('15-MAY-02','dd-MON-YY'))/ insert into dd values (to_date('15-JUN-02','dd-MON-YY'))/ insert into dd values (to_date('15-JUL-02','dd-MON-YY'))/ insert into dd values (to_date('15-AUG-02','dd-MON-YY'))/ insert into dd values (to_date('15-SEP-02','dd-MON-YY'))/ insert into dd values (to_date('15-OCT-02','dd-MON-YY'))/ insert into dd values (to_date('15-NOV-02','dd-MON-YY'))/ insert into dd values (to_date('15-DEC-02','dd-MON-YY'))/ insert into dd values (to_date('16-JAN-02','dd-MON-YY'))/ insert into dd values (to_date('17-JAN-02','dd-MON-YY'))/ insert into dd values (to_date('18-JAN-02','dd-MON-YY'))/ insert into dd values (to_date('16-JUL-02','dd-MON-YY'))/ insert into dd values (to_date('17-AUG-01','dd-MON-YY'))/ insert into dd values (to_date('18-DEC-01','dd-MON-YY'))/ insert into dd values (to_date('16-JAN-99','dd-MON-YY'))/ insert into dd values (to_date('17-JAN-99','dd-MON-YY'))/ insert into dd values (to_date('18-JAN-99','dd-MON-YY'))/ insert into dd values (to_date('16-JUL-99','dd-MON-YY'))/ insert into dd values (to_date('17-AUG-04','dd-MON-YY'))/ insert into dd values (to_date('18-DEC-04','dd-MON-YY'))/commit the inserts.Notice that I have entered some values for the 2004 and 2099 years too andwill exclude them from the final results.In what follows, ddr2 is my sql script the contents of which are echoed:SQL> @ddr2SQL> select count(*) "FY 2099" from dd where to_char(c1, 'YYYY')='2099' FY 2099---------- 4SQL> select count(*) "FY 2001" from dd where to_char(c1, 'YYYY')='2001' FY 2001---------- 14SQL> select count(*) "FY 2002" from dd where to_char(c1, 'YYYY')='2002' FY 2002---------- 28SQL> select count(*) "FY 2003" from dd where to_char(c1, 'YYYY')='2004' FY 2003---------- 2SQL> select count(*) from dd COUNT(*)---------- 48SQL> select to_char(c1,'YYYY') " Year ",count(*) from dd group byto_char(c1,'YYYY') Yea COUNT(*)---- ----------2001 14 Yea COUNT(*)---- ----------2002 28 Yea COUNT(*)---- ----------2004 2 Yea COUNT(*)---- ----------2099 4SQL> select count(*) from dd where to_char(c1,'YYYY')='2001' orto_char(c1,'YYYY')='2002' COUNT(*)---------- 42SQL> select to_char(c1,'YYYY') " Year ", count(*) from dd whereto_char(c1,'YYYY')='2001' or to_char(c1,'YYYY')='2002' group by(to_char(c1,'YYYY')) Yea COUNT(*)---- ----------2001 14 Yea COUNT(*)---- ----------2002 28SQL> set pagesize 16SQL> break on " Year " skip pageSQL> col " Year " format 99999SQL> select to_char(c1,'YYYY') " Year ", 2 decode(to_char(c1,'MM'),'01','JAN', 3 '02','FEB', 4 '03','MAR', 5 '04','APR', 6 '05','MAY', 7 '06','JUN', 8 '07','JUL', 9 '08','AUG', 10 '09','SEP', 11 '10','OCT', 12 '11','NOV', 13 '12','DEC') " Month ", 14 count(*) " Logins " 15 from dd 16 group by (to_char(c1,'YYYY')), 17 (to_char(c1,'MM')) 18 having to_char(c1,'YYYY') in ( '2001','2002') 19 / Yea Mo Logins---- --- ----------2001 JAN 1 FEB 1 MAR 1 APR 1 MAY 1 JUN 1 JUL 1 AUG 2 SEP 1 OCT 1 NOV 1 DEC 2 Yea Mo Logins---- --- ----------2002 JAN 5 FEB 2 MAR 2 APR 2 MAY 2 JUN 2 JUL 3 AUG 2 SEP 2 OCT 2 NOV 2 DEC 224 rows selected.SQL> spool off;In the above output I have suppressed duplicate values of the " Year "column. The decode statement within the sql was used to enable me to sortthe values by month but to display the month by name. In the actual sql the"having" selects the years that I am interested in. You would use the userinput parameters here for livereports for the current_year and prio_year.Hope this helps.MohsinOracle Technical Specialist.> -----Original Message-----> From: eLink Discussion: Livelink LiveReports Discussion> [mailto:livereportsdiscussion@elinkkc.opentext.com]> Sent: Thursday, June 27, 2002 12:25 PM> To: eLink Recipient> Subject: Counting Logins per month over 2 years>>> Counting Logins per month over 2 years> Posted by CRHAdmin on 06/27/2002 12:20 PM>> We are using Livelink 9.0.0 and SQL 7.>> I have already created a report that shows the monthly user> logins for a specified year by means of a line chart. However we> need a report that counts the logins per month for 1 year and> also counts the logins per month for a previous year, so as they> can be compared, i.e. 2 lines in the line graph - one for each> year - super-imposed on each other.>> What I need to know is if you can use two different count> functions, each with different criteria, in the same select> statement. At the moment my statement uses one count function to> count all the 'LOGIN' values in DAudit.Event where> DAudit.auditdate is 2001 or 2002, etc. I can't get it working> when I add a second count function with the criteria> DAudit.auditdate is 2002, etc.>> Am I going about this problem in the wrong way or am I on the> right track.>> Thanks in advance.>> [To reply to this thread, use your normal e-mail reply function.]>> ============================================================>> Discussion: Livelink LiveReports Discussion> https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe