1. 

   bushraj, Member

   Hi ,

   I have 200 clients and i want to show their details with products and count like this :

   Client 1

   Week Open Close
   Product 1
   Product 2
   Product 3

   Client 2

   Week Open Close
   Product 1
   Product 2
   Product 3

   Through filters i can do this with cross tabulation but i want to show all clients in their separate tables. I can't hard code the client name in query for 200 clients.. Plus it should be dynamic, if the entries of client is not coming in date range, it should not show that client.

   With filters my query is :

   select 'dbr.crosstab', 'ClientOrganization';

   select 'dbr.hsum', 'Open','Close';
   select 'dbr.sum', 'Open','Close';

   SELECT Product,ClientOrganization , Month,
   week,
   sum(open) AS Open,
   sum(close)as Close
   FROM (

   SELECT ClientOrganization, Product, CONCAT(SubmissionMonth,'-', SubmissionYear) as Month,
   WEEK(Submission,5) -
   WEEK(DATE_SUB(Submission, INTERVAL DAYOFMONTH(Submission)-1 DAY),5) as week,
   count(*) AS open,
   0 as close,SubmissionYear as Year,month(Submission) as mon
   FROM wf_frmwrk.tps_tbl_master_all_coumns

   -- where SubmissionYear=year(curdate())

   where (ClientOrganization = Org or Org='All')

   and (Product = Pro or Pro = 'All')
   AND Submission between StartDate and EndDate
   GROUP BY ClientOrganization, Product, Month, week

   UNION ALL

   SELECT ClientOrganization, Product,CONCAT(LatestClosedMonth,'-',LatestClosedYear) AS Month,
   WEEK(LatestClosed,5) -
   WEEK(DATE_SUB(LatestClosed, INTERVAL DAYOFMONTH(LatestClosed)-1 DAY),5) as week,
   0 as open,
   count(*) close,LatestClosedYear as Year,month(LatestClosed) as mon
   FROM wf_frmwrk.tps_tbl_master_all_coumns

   -- where LatestClosedYear=year(curdate())

   where (ClientOrganization = Org or Org='All')

   and (Product = Pro or Pro = 'All')
   AND LatestClosed between StartDate and EndDate
   GROUP BY ClientOrganization, Product, Month, week

   ) alias
   GROUP BY ClientOrganization, Product, Month, week
   ORDER BY ClientOrganization, Product, Month, week;

   I want to show it like the format shown above ..

   Please help.

   Regards.
2. 

   myDBR Team, Key Master

   You can use database cursors to loop through the clients and then do a separate report object for each client.

   Beware that looping in can affect your reports performance as one query becomes 200 queries. If this becomes a factor, you can cache the results first to temp table.

   --
   myDBR Team
3. 

   bushraj, Member

   Can you provide any example to use my scenario in temp table? I can see the example in documentation,but how to use it for my scenario.
4. 

   myDBR Team, Key Master

   Please see cursor examples at MySQL documentation http://dev.mysql.com/doc/refman/5.5/en/cursors.html.

   Temporary tables behave exactly like normal tables when working with cursors.

   --
   myDBR Team
5. 

   bushraj, Member

   I have write the following stored procedure with temporary table and cursor, but it keep on executing and did not return the result set :

   DROP PROCEDURE IF EXISTS sp_DBR_Client_ProductWiseT
   $$
   CREATE PROCEDURE sp_DBR_Client_ProductWiseT(Org varchar(100),Pro varchar(50),StartDate date,EndDate date,format varchar(50))
   BEGIN
   DECLARE no_more_rows BOOLEAN;

   DECLARE productss VARCHAR(50);
   DECLARE orgs VARCHAR(100);
   DECLARE org1 VARCHAR(100);
   DECLARE temp VARCHAR(100);
   DECLARE mons VARCHAR(32);

   DECLARE weeks VARCHAR(32);

   DECLARE opens VARCHAR(32);
   DECLARE closes VARCHAR(32);
   DECLARE new_count VARCHAR(32);

   select 'dbr.text', concat('Date range: ', StartDate, ' - ', EndDate );

   select 'dbr.text', concat('Organization: ', Org);

   select 'dbr.text', concat('Product: ', Pro);

   select 'dbr.text', concat('Report Format: ', format);

   DROP TEMPORARY TABLE IF EXISTS fruits_tmp;
   CREATE TEMPORARY TABLE fruits_tmp ( products varchar(50),org varchar(100),mon varchar(20),week varchar(5), open int(4),close int(5) );

   INSERT INTO fruits_tmp (products,org,mon,week,open,close)

   SELECT Product,ClientOrganization , Month,
   week,
   sum(open) AS Open,
   sum(close)as Close
   FROM (

   SELECT ClientOrganization, Product, CONCAT(SubmissionMonth,'-', SubmissionYear) as Month,
   WEEK(Submission,5) -
   WEEK(DATE_SUB(Submission, INTERVAL DAYOFMONTH(Submission)-1 DAY),5) as week,
   count(*) AS open,
   0 as close,SubmissionYear as Year,month(Submission) as mon
   FROM wf_frmwrk.tps_tbl_master_all_coumns

   -- where SubmissionYear=year(curdate())

   where (ClientOrganization = Org or Org='All')

   and (Product = Pro or Pro = 'All')
   AND Submission between StartDate and EndDate
   GROUP BY ClientOrganization, Product, Month, week

   UNION ALL

   SELECT ClientOrganization, Product,CONCAT(LatestClosedMonth,'-',LatestClosedYear) AS Month,
   WEEK(LatestClosed,5) -
   WEEK(DATE_SUB(LatestClosed, INTERVAL DAYOFMONTH(LatestClosed)-1 DAY),5) as week,
   0 as open,
   count(*) close,LatestClosedYear as Year,month(LatestClosed) as mon
   FROM wf_frmwrk.tps_tbl_master_all_coumns

   -- where LatestClosedYear=year(curdate())

   where (ClientOrganization = Org or Org='All')

   and (Product = Pro or Pro = 'All')
   AND LatestClosed between StartDate and EndDate
   GROUP BY ClientOrganization, Product, Month, week

   ) alias
   GROUP BY ClientOrganization, Product, Month, week
   ORDER BY ClientOrganization, Product, Month, week;

   SELECT products,org,mon,week,open,close FROM fruits_tmp;

   increment: LOOP
   block_cursor: BEGIN
   DECLARE cur1 CURSOR FOR SELECT products, org, mon, week, open, close FROM fruits_tmp order by org;

   OPEN cur1;

   FETCH cur1 INTO productss, orgs, mons, weeks, opens, closes;

   IF no_more_rows THEN
   CLOSE cur1;
   LEAVE increment;
   END IF;

   set org1= temp;

   IF (org1=temp) then
   select productss, orgs, mons, weeks, opens, closes;

   END IF;
   select org1;
   select temp;
   select productss, orgs, mons, weeks, opens, closes;
   CLOSE cur1;
   END block_cursor;
   END LOOP increment;

   END
   $$

   What I m doing wrong here? Do help

   Thanks.
6. 

   myDBR Team, Key Master

   There are two reasons why you might not get results:
   1) Temporary table does contain any rows
   2) You cursor loop does not work

   To check the 1), select count(*) form the table. To check the 2), see your code and compare it to MySQL examples about loops.

   --
   myDBR Team
7. 

   bushraj, Member

   Hi,

   Both query and cursor is running i checked them separately but how can I use cursor and temporary table in one stored procedure?

   My code is :

   DROP PROCEDURE IF EXISTS sp_DBR_Client_ProductWiseT
   $$
   CREATE PROCEDURE sp_DBR_Client_ProductWiseT(Org varchar(100),Pro varchar(50),StartDate date,EndDate date,format varchar(50))
   BEGIN

   select 'dbr.text', concat('Date range: ', StartDate, ' - ', EndDate );

   select 'dbr.text', concat('Organization: ', Org);

   select 'dbr.text', concat('Product: ', Pro);

   select 'dbr.text', concat('Report Format: ', format);

   DROP TEMPORARY TABLE IF EXISTS fruits_tmp;
   CREATE TEMPORARY TABLE fruits_tmp ( products varchar(50),orgs varchar(200),mon varchar(20),week varchar(5), open int(4),close int(5) );

   INSERT INTO fruits_tmp (products,orgs,mon,week,open,close)

   SELECT Product,ClientOrganization , Month,
   week,
   sum(open) AS Open,
   sum(close)as Close
   FROM (

   SELECT ClientOrganization, Product, CONCAT(SubmissionMonth,'-', SubmissionYear) as Month,
   WEEK(Submission,5) -
   WEEK(DATE_SUB(Submission, INTERVAL DAYOFMONTH(Submission)-1 DAY),5) as week,
   count(*) AS open,
   0 as close,SubmissionYear as Year,month(Submission) as mon
   FROM wf_frmwrk.tps_tbl_master_all_coumns

   -- where SubmissionYear=year(curdate())

   where (ClientOrganization = Org or Org='All')

   and (Product = Pro or Pro = 'All')
   AND Submission between StartDate and EndDate
   GROUP BY ClientOrganization, Product, Month, week

   UNION ALL

   SELECT ClientOrganization, Product,CONCAT(LatestClosedMonth,'-',LatestClosedYear) AS Month,
   WEEK(LatestClosed,5) -
   WEEK(DATE_SUB(LatestClosed, INTERVAL DAYOFMONTH(LatestClosed)-1 DAY),5) as week,
   0 as open,
   count(*) close,LatestClosedYear as Year,month(LatestClosed) as mon
   FROM wf_frmwrk.tps_tbl_master_all_coumns

   -- where LatestClosedYear=year(curdate())

   where (ClientOrganization = Org or Org='All')

   and (Product = Pro or Pro = 'All')
   AND LatestClosed between StartDate and EndDate
   GROUP BY ClientOrganization, Product, Month, week

   ) alias
   GROUP BY ClientOrganization, Product, Month, week
   ORDER BY ClientOrganization, Product, Month, week;

   SELECT products,orgs,mon,week,open,close FROM fruits_tmp;
   CALL wf_frmwrk.cur();
   END
   $$

   DROP PROCEDURE IF EXISTS wf_frmwrk.cur $$
   CREATE PROCEDURE wf_frmwrk.cur ()
   BEGIN
   DECLARE done INT DEFAULT FALSE;

   DECLARE productss VARCHAR(50);
   DECLARE orgss VARCHAR(100);
   DECLARE org1 VARCHAR(100);
   DECLARE temp VARCHAR(100);
   DECLARE mons VARCHAR(32);

   DECLARE weeks VARCHAR(32);

   DECLARE opens VARCHAR(32);
   DECLARE closes VARCHAR(32);

   DECLARE cur1 CURSOR FOR SELECT products,orgs,mon,week,open,close FROM fruits_tmp;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   OPEN cur1;

   read_loop: LOOP
   FETCH cur1 INTO productss,orgss,mons,weeks,opens,closes;

   IF done THEN
   LEAVE read_loop;
   END IF;

   set org1= temp;

   select productss,orgss,mons,weeks,opens,closes;

   END LOOP;

   CLOSE cur1;
   END $$

   The second procedure of cursor is not executing..
8. 

   myDBR Team, Key Master

   Hi,
   unfortunately we cannot write your report for you as we do not have access to your database or data to have all necessary data nor do we have resources to do so. As this is not really a myDBR support question, but a generic SQL question, a better place this would be a MySQL forum / Stack Overflow. We do offer consultancy via shop.mydbr.com.

   Your intention was to create separate table element for each customer in the report. This means that you need to create cursor which goes through all possible customers in your data. For each customer found you need to perform a query you want. To have a temporary table prebuilt where you do the query from, will lower the performance impact of the loop as the data is ready for you.

   In your cursor example you just loop through the rows of your temp table without doing the search per customer. In addition to this, you've got extra code (set org1= temp;) that really does not do anything.

   --
   myDBR Team

Reply

You must log in to post.