Datagrid and sql query isssue. Revenue reporting system

Hi-

I am trying to develop a page that pulls all customers from a database and display the revenue for each customer for each day I query in a months time.

I am able to pull out the revenue for one day in the month and display it in the column with the appropriate revenue next to the customer name.

My problem is two things.

1. It only displays customers that actually have revenue. So how would I get my datagrid to display all customers regardless if they have revenue in the database.

2. I need to be able to display more than 1 day in columns format. For example

Customer | Day1 | Day 2 |
----------
Acme Inc | $2200. |$1300.

Here is my sql code that pulls out the customers that have revenue and displays one day between a specific set of dates.

SQL = "SELECT pb_customers.customer_name AS 'customer', sum(pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost) as 'total' FROM pb_report_shippers INNER JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id INNER JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id WHERE pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND job_completed = '1' GROUP by pb_customers.customer_name"

Any help would be appreciated

ThanksTo get all customers, you'll want a left outer join for your related tables. Then try something like this

SELECT pb_customers.customer_name AS 'customer',
 sum(case (when day(pb_report_shippers.shipper_date_time) = 1
 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 else 0
 end) as day1,
 sum(case (when day(pb_report_shippers.shipper_date_time) = 2
 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 else 0
 end) as day2,
... and so on
FROM pb_report_shippers
 Left outer JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id
 left outer JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id WHERE pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND job_completed = '1' GROUP by pb_customers.customer_name

HTH|||Thanks for the reply.

I am confused about this line.

sum(case (when day(pb_report_shippers.shipper_date_time) = 1

then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost

else 0

end) as day1,

What does the "1" represent. Am I supposed to put the datevalue I am looking for right there? When I do i get an error with the "when" keyword.|||The DAY() function returns the DD portion of MM/DD/YYYY. So, for 10/23/2003 it would contain 23. I don't think this is exactly what you are looking for, but the methodology should work. Instead I would use the DATEDIFF() function.

SELECT
 pb_customers.customer_name AS 'customer',
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 1
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day1,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 2
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day2,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,cast('9/01/03' AS datetime)) = 3
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day3,
... and so on
FROM
 pb_report_shippers
LEFT OUTER JOIN
 pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id
LEFT OUTER JOIN
 pb_customers ON pb_jobs.customer_id = pb_customers.customer_id
WHERE
 pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND
 job_completed = '1'
GROUP BY
 pb_customers.customer_name

Terri|||thanks for the help. I am getting closer to what I need.

One thing that is still occuring however is it only displays the customers that have revenue. It wont display all customers regardless is no revenue is found in the database. Is this what the left outer join is suppoesed to handle. What does it doe exactly compated to a normal Inner join.|||The left outer join should accomplish this. experiment a bit and comment out the

LEFT OUTER JOIN
 pb_customers ON pb_jobs.customer_id = pb_customers.customer_id

and any related columns of the query to see if you get the same results. This should help you track down why not all customers are showing up.|||Then I would restruture slightly. I would make the primary table you are pulling from your customers table, and I would LEFT OUTER join your shippers table. Like this (untested but hopefully it's close):

SELECT
 pb_customers.customer_name AS 'customer',
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 1
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day1,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 2
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day2,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,cast('9/01/03' AS datetime)) = 3
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day3,
... and so on
FROM
 pb_customers
LEFT OUTER JOIN
 pb_jobs ON pb_jobs.customer_id = pb_customers.customer_id AND pb_report_shippers.shipper_date_time BETWEEN CAST('9/01/03' as datetime) AND CAST('9/02/03' as datetime) AND
 job_completed = '1'
LEFT OUTER JOIN
 pb_report_shippers ON pb_report_shippers.job_id = pb_jobs.job_id
GROUP BY
 pb_customers.customer_name