Re: A query for Report

On Sat, 9 Sep 2000, Manuel wrote: > Hope someone can help me with this query. > > I want to create a query with this result, salesman sales count for the > past xx months. > > Jan Feb Mar etc Total > salesman 1 XX XX XX XXX > salesman 2 XX XX XX XXX > etc > Total XXX XXX XXX > > XX - count > > sales Table > *********** > salesmancode > salesdate > > I use this to get the individual total but I do not know how to group them > into last xx months and the totals. > > select salemancode,count(*) from sales group by salesmancode > > Total > salesman 1 xx > salesman 1 xx > > My problem: > 1. How to split them into months? > 2. How to total them up by Months(Jan, Feb, etc) and by Salesman (Salesman > 1, Salesman 2, etc)? > > Any idea/suggestion will be greatly appreciated. Standard disclaimer - I don't guarentee the advice below at all. I have done some small testing, but I don't have your data. And I sometimes make mistakes either conceptual or in typing. But I hope it is of some help. Well, first, I don't know of any single SQL query which will do what you want. You will need to combine an SQL query with some PHP programming. Also, I think you're going to need to post more information. Such as what database are you using (MySQL, PostgreSQL, Oracle)? The exact query syntax may vary depending on which database you use. My queries are based on PostgreSQL because that's what I use. As a brief overview of what I'd try. In PHP, get today's date. From that get the first day of the current month. Now backup the month by the number of months you want in your result. If the month is now <= 0, add 12 to it and subtract 1 from the year. The code would look something like: $today=getdate(time()); $u_year=$today['year']; $u_mon=$today['mon']; $upperdate=$u_year.'-'.$u_mon.'-01'; $l_mon=$today['mon']-num_of_months_to_go_back; $l_year=$today['year']; if ($l_mon < 1) { $l_mon+=12; $l_year--; } $lowerdate=$l_year.'-'.sprintf('%02d',$l_mon).'-01'; Note that I use sprintf() in order to make sure that $l_mon has leading zeros! Now form a query similiar to (PostgreSQL specific!) select salesmancode, count(salesmancode) as salescount, date_trunc('month',salesdate)::date as sdate from sales where salesdate<$upperdate and salesdate>=$lowerdate group by salesmancode, date_trunc('month',salesdate); The output from this command will be a number of records. Each record will contain the salesmancode, the number of sales, and a date which is the first of the month for the date in "salesdate". Something like: salesmancode count date 1 10 2000-01-01 1 5 2000-02-01 1 7 2000-03-01 2 8 2000-02-01 2 19 2000-03-01 Now is the very hard part. I would make a multi-dimensional array. One array index would be "salesmancode", the other would be "date". Each entry would be the count returned above. So in the example, you would have: $SalesCount['1','2000-01-01']==10 $SalesCount['1','2000-02-01']==5 $SalesCount['1','2000-03-01']==7 $SalesCount['2','2000-02-01']==8 $SalesCount['2','2000-03-01']==19 In a loop, we would fetch each result row and put it into that array. Most likely will code similar to: $Row_Data=pg_fetch_array(...); salesmancode=$Row_Data['salesmancode']; salesdate=$Row_Data['sdate']; $SalesCount[salesmancode][salesdate]=$Row_Data['salescount']; $TotalSales[salesmancode]+=$Row_Data['salescount']; One thing nice is that if a variable doesn't have a value (is not set) and you do arithmetic on it, PHP assumes the value is 0. That's why that last line works without needing any initialization (neat!). Also, rememeber that we have the upper and lower bounds on the date in $u_year/$u_mon and $l_year/$l_year, respectively. You can now output the data by doing something similar to: foreach($TotalSales as salesmancode=>salescount) { $c_mon=$l_mon; $c_year=$l_year; echo salesmancode; while (($c_mon<>$u_mon) & ($c_year<>$u_year)){ echo '\t'; sdate=sprintf('%04d',c_year); sdate.='-'.sprintf('%02d',c_mon).'-01'; DateSales[sdate]+=$SalesCount[salesmancode][sdate]; echo $SalesCount[salesmancode][sdate]; c_mon++; if (c_mon>12) { c_mon=1; c_year++; } } echo '<br>'; } Note that the above has two failures. First, it does not output any sort of headers. But a similar loop over only the dates can be used to put out the headers. Second, I don't output the totals. I calculate them in $DateSales, but you'll need another loop over the dates after the above loop to output them. I don't understand what that last line in your example is supposed to be, so I didn't try anything. If it is a "grand total" of sales, then just put a like such as: $GrandTotal=+SalesCount[salesmancode][sdate]; inside the inner loop. Well, this is a very long post and I hope that I have been of some help to you while not getting others upset. John