I have a fairly complicated question. Is it possible to have a datagrid where each column represents a different day of the week with different data. Meaning each column dependent on the day of the week would be produced using a formula.
My problem is I have a database with 100's of customers. And i need to display a datagrid with each column being a day of the month. The rows would be customers. So in each box would be that customers revenue for that specific day in the month. Can anyone give me any advice on how to do this?
ThanksSeems like this would be fairly straightforward, but we don't know the structure of your data. But why not do a select customer, (subquery for week) as week 1 for your query. Then when you bind to the datagrid, your columns would be fixed.
something along those lines. If you need more help, give us the tables and fields you are working with, and any queries you have attempted to write so far.|||Here is what is going on.
The SQL name is pb_report_shippers.
The columns that are most important to me are customer_id and total_ext_price and shipper_date_time.
So i need to be able to query the table and grab total_ext_price for a specific customer_id and add up all there total_ext_price and sort them into columns by date or (shipper_date_time)
Hope this clarifies. Any examples in vb would be great.
Thanks|||If you are not able to modify your query, then I would suggest this thread be continued on the datagrids forum, since that's more what you need. If you can modify your SP, or create a new one, I'm suggesting that you create columns in your SP for the weekly totals. To give a little more meat to the sample query I posted:
Create Procedure blah
as
select customer,
(select sum(total_ext_price)
from table t2
where shipper_date_time between cast('10/01/03' as datetime) and cast('10/01/03' as datetime)
AND t2.customerid = t1.customerid ) as week1
... continue on as needed
The code I've given you there is close to what you might need but you would have to put in the right dates etc. Once that SP is created, you merely call that. And you bind your datagrid to the columns using the week1 etc alias. I'm not sure if I need to go into more detail on this method, or if that conveys the idea to you. Hopefully that clarifies what I was suggesting.|||I am not using stored procedures for this, nor do I at this point. I get the idea kinda. I guess I need a little more specific example. Also I need to pull the entire month at one time.
For example I need to pull all September revenue for every customer in the database. I need to add up each customers revenue arrange them in a table column for each day.
How would I be able to figure out how many days are in a specific month and know how to query for that specific range of dates?
Thanks
