Help with SQL and datagrids

Hello-

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

Help with query

Suppose that I have a table that represents the chain-of-command hierarchy
of a company. Lets say that the first column of the table represents the
employee name and the other represents the employee's boss. Lets also assume
that one employee can have several bosses.
What I would like to do is to create some kind of query that will return the
company's Boss hierarchy. For example, lets pretend that my table has the
following information.
Worker Boss
-- --
JoeCacas Dumb
JoeCacas Dumber
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
BigDaddy Lordy
In this case, if I asked the query to tell me the Boss hierarchy of JoeCacas
I would expect the query to first search for all the bosses of JoeCacas
which will return the records:
-- 1 Level--
JoeCacas Dumb
JoeCacas Dumber
Then the query will need to search for the bosses of Dumb and Dumber which
will return the following:
-- 2 Level--
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
Finally, the query will return the bosses of MadDog, BidDaddy and BigCheese.
Since the only person that has a boss is BigDaddy then the result will be:
-- 3 Level--
BigDaddy Lordy
The result that I would expect would be:
JoeCacas Dumb
JoeCacas Dummber
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
BigDaddy Lordy
I think this can be done with some kind of recursive query. Is this
possible? Thank you.Hi Rene,
You can create a UDF to achieve the task.
Basically, iterate through the levels going upwards:
USE tempdb;
SET NOCOUNT ON;
GO
CREATE TABLE EmpsHier
(
worker VARCHAR(20) NOT NULL,
boss VARCHAR(20) NOT NULL,
PRIMARY KEY(worker, boss)
);
INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
GO
CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
@.MgmtChain TABLE
(
worker VARCHAR(20),
boss VARCHAR(20),
lvl INT
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.MgmtChain
SELECT worker, boss, @.lvl FROM EmpsHier
WHERE worker = @.emp;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.MgmtChain
SELECT P.worker, P.boss, @.lvl
FROM EmpsHier AS P
JOIN @.MgmtChain AS C
ON C.lvl = @.lvl - 1
AND C.boss = P.worker;
END
RETURN;
END
GO
SELECT * FROM fn_mgmtchain('JoeCacas');
worker boss lvl
-- -- --
JoeCacas Dumb 0
JoeCacas Dumber 0
Dumb MadDog 1
Dumber BigCheese 1
Dumber BigDaddy 1
BigDaddy Lordy 2
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rene" <nospam@.nospam.com> wrote in message
news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
> Suppose that I have a table that represents the chain-of-command hierarchy
> of a company. Lets say that the first column of the table represents the
> employee name and the other represents the employee's boss. Lets also
> assume that one employee can have several bosses.
> What I would like to do is to create some kind of query that will return
> the company's Boss hierarchy. For example, lets pretend that my table has
> the following information.
> Worker Boss
> -- --
> JoeCacas Dumb
> JoeCacas Dumber
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> BigDaddy Lordy
>
> In this case, if I asked the query to tell me the Boss hierarchy of
> JoeCacas
> I would expect the query to first search for all the bosses of JoeCacas
> which will return the records:
> -- 1 Level--
> JoeCacas Dumb
> JoeCacas Dumber
> Then the query will need to search for the bosses of Dumb and Dumber which
> will return the following:
> -- 2 Level--
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> Finally, the query will return the bosses of MadDog, BidDaddy and
> BigCheese.
> Since the only person that has a boss is BigDaddy then the result will be:
> -- 3 Level--
> BigDaddy Lordy
>
> The result that I would expect would be:
> JoeCacas Dumb
> JoeCacas Dummber
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> BigDaddy Lordy
> I think this can be done with some kind of recursive query. Is this
> possible? Thank you.
>|||Hi
you can also check for this:
http://support.microsoft.com/defaul...kb;en-us;248915
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Itzik Ben-Gan" wrote:

> Hi Rene,
> You can create a UDF to achieve the task.
> Basically, iterate through the levels going upwards:
> USE tempdb;
> SET NOCOUNT ON;
> GO
> CREATE TABLE EmpsHier
> (
> worker VARCHAR(20) NOT NULL,
> boss VARCHAR(20) NOT NULL,
> PRIMARY KEY(worker, boss)
> );
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
> INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
> INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
> GO
> CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
> @.MgmtChain TABLE
> (
> worker VARCHAR(20),
> boss VARCHAR(20),
> lvl INT
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.MgmtChain
> SELECT worker, boss, @.lvl FROM EmpsHier
> WHERE worker = @.emp;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.MgmtChain
> SELECT P.worker, P.boss, @.lvl
> FROM EmpsHier AS P
> JOIN @.MgmtChain AS C
> ON C.lvl = @.lvl - 1
> AND C.boss = P.worker;
> END
> RETURN;
> END
> GO
> SELECT * FROM fn_mgmtchain('JoeCacas');
> worker boss lvl
> -- -- --
> JoeCacas Dumb 0
> JoeCacas Dumber 0
> Dumb MadDog 1
> Dumber BigCheese 1
> Dumber BigDaddy 1
> BigDaddy Lordy 2
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>|||Chandra,
The example you provided expands/explodes a node (returning descendants down
the digraph). Rene asked for ancestors (up the digraph).
BG, SQL Server MVP
www.SolidQualityLearning.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:7E732409-B2C7-4B00-B986-3A85B5098E65@.microsoft.com...
> Hi
> you can also check for this:
> http://support.microsoft.com/defaul...kb;en-us;248915
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Itzik Ben-Gan" wrote:
>|||Thanks Itsik, that was awesome! I pretty much copied and pasted your code
and everything worked great.
It took me a little bit to figure out what you did but now I understand it.
Thanks again.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uiI%23HwNYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Chandra,
> The example you provided expands/explodes a node (returning descendants
> down the digraph). Rene asked for ancestors (up the digraph).
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:7E732409-B2C7-4B00-B986-3A85B5098E65@.microsoft.com...
>|||I was just thinking. What if multiple users call the function at the same
time? Will they all be modifying the same table at the same time screwing up
the result? If this is the case, is there a way to create a unique table
every time the function is called?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hi Rene,
> You can create a UDF to achieve the task.
> Basically, iterate through the levels going upwards:
> USE tempdb;
> SET NOCOUNT ON;
> GO
> CREATE TABLE EmpsHier
> (
> worker VARCHAR(20) NOT NULL,
> boss VARCHAR(20) NOT NULL,
> PRIMARY KEY(worker, boss)
> );
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
> INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
> INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
> GO
> CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
> @.MgmtChain TABLE
> (
> worker VARCHAR(20),
> boss VARCHAR(20),
> lvl INT
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.MgmtChain
> SELECT worker, boss, @.lvl FROM EmpsHier
> WHERE worker = @.emp;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.MgmtChain
> SELECT P.worker, P.boss, @.lvl
> FROM EmpsHier AS P
> JOIN @.MgmtChain AS C
> ON C.lvl = @.lvl - 1
> AND C.boss = P.worker;
> END
> RETURN;
> END
> GO
> SELECT * FROM fn_mgmtchain('JoeCacas');
> worker boss lvl
> -- -- --
> JoeCacas Dumb 0
> JoeCacas Dumber 0
> Dumb MadDog 1
> Dumber BigCheese 1
> Dumber BigDaddy 1
> BigDaddy Lordy 2
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||I am assuming that the @.MgmtChain TABLE is created on the tempDB database,
although I have a feeling tempDB is not used at all here. Just checking.
"Rene" <nospam@.nospam.com> wrote in message
news:OGy$fTXYFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I was just thinking. What if multiple users call the function at the same
>time? Will they all be modifying the same table at the same time screwing
>up the result? If this is the case, is there a way to create a unique table
>every time the function is called?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
>|||>> the chain-of-command hierarchy of a company. Lets say that the first colu
mn of the table represents the employee name and the other represents the em
ployee's boss. Lets also assume that one employee can have several bosses. <
<
Several bosses? Isn' t that like having a circle has corners? Look
up the definition of a hierarchy. If you really wanted a hierarchy,
then get a copy of TREES & HIERARCHIES IN SQL for several
non-procedural, non-recursive ways to model this which will run orders
of magnitude faster than a recursive proc or a loop.|||> Several bosses? Isn' t that like having a circle has corners?
I don't have such table. It was just an example (analogy) that I came up
with to try to make it easier to get my point across. Hierarchy may not be
the most proper word to use but it I figured it would help explain what I
needed.|||The table is logically a variable local to the batch. No conflicts with
other invocations.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rene" <nospam@.nospam.com> wrote in message
news:OGy$fTXYFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I was just thinking. What if multiple users call the function at the same
>time? Will they all be modifying the same table at the same time screwing
>up the result? If this is the case, is there a way to create a unique table
>every time the function is called?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
>