asked on

VB.NET and SQL 2000 - how to fill datagrid by results of 3 SQL queries

Trying to combine 3 querie results into 1 datagrid.
This is VB.NET Web app and SQL 2000 combined question I guess.

Basically Ideal data grid would look like this:

DateFund MaxVol SchVol WireVol
01/01/09 20000 15000 0
01/02/09 0 10000 5000
01/03/09 12000 0 0
01/04/09 12000 9000 6000
etc (listing ALL days in a year)

MaxVol, SchedVol, WireAmt are actually sums that come from 2 different tables.
(tblDTA and Lock).

I tried to run 1 query, where appropriate dates from both tables would be equal to each other, but I get a very limited result that actually does not show any rows if any of the 3 sums does not exists for a certain date.
Example.
Instead of getting all 4 rows like above, I get only last row (01/04/09) where ALL 3 sums exist for that certain date. I understand that is the way SQL works, but I am trying to find a workaround.
I think I could run a separate, individual query for each table to get 3 results, 3 individual sums - but HOW to merge them into one, same datagrid and if a sum does not exist for a certain date, how to show 0 there????

All details in the code below.

Thanks

The combined query used to fill mu datagrid currently:
 
SELECT transID, DateFund, MaxVol, SUM(ln_amount) As SchVol, 
 SUM(a_wire_amt) As WireVol  
FROM tblDTA D, lock L where D.Datefund= L.date_fund 
and D.datefund=L.a_wire_date 
and Datefund between '01/01/2009'  and '02/12/2009'  
group by datefund, maxvol, transid
 
The query above produces ONLY 3 rows for Datefund between '01/01/2009'  and '02/12/2009'. It should show 43 rows with results for each of 43 dates
 
If I separate this query into 3 basic ones:
Query 1:
select datefund, maxvol from tblDTA where datefund between '01/01/2009'  and '02/12/2009'  
(this query produces 43 rows showing 43 days since Jan 1 and MaxVol = 0 - by default)
 
Query 2:
select a_wire_dat, sum(a_wire_amt) as WireVol from lock where a_wire_dat between '01/01/2009' and '02/12/2009'
group by a_wire_dat
(this query produces 29 rows showing 29 days where a_wire_dat  actually exists)
In datagrid I must somehow show the other 14 days where a_wire_dat does not exist as WireVol  = 0
 
Query 3:
select date_fund, sum(ln_amount)SchVol from lock where date_fund between '01/01/2009' and '02/12/2009'
group by date_fund
(this query produces 35 rows showing 35 days where date_fund actually exists)
In datagrid I must somehow show the other 8 days where date_fund does not exist as WireVol  = 0

Open in new window

ASKER CERTIFIED SOLUTION

Daniel Reynolds

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Terrace

ASKER

Thank you for the fast reponse.

I tried Outer Joins, and seems to work, so SQL side of my problem seems to be resolved.
I can fill datagrid with just 1 query, as I initially set it up.

The values for SchVol and WireVol that don't exist for certain dates are shown as NULL.
Now I need to see how to show Null as 0 in datagrid.

Thanks

Daniel Reynolds

In your select, try using isnull() or coalesce() to return a value.
like so
SELECT transID, DateFund, IsNull(MaxVol,0), IsNull(Sum(ln_amount),0) as SchoVol, IsNull(Sum(a_wire_amt),0) AS WireVol

Daniel Reynolds

The isnull() function returns the value of the first item in parethesis unless it is null, in the case it is null, it returns the second value

coalesce goes one further, it will check each value in order for null and return the next value...
Coalesce(myfield, yourfield, 0)

Terrace

ASKER

Thank you very much.

Terrace

ASKER

Actually, I was wrong.
On the first glance, everything seems OK, but those SUMS are not working properly. It seems like instead of SUM of few recoreds, I get only 1 record per date.
I'll play with it more now, but if you know what the reason is, let me know
Thanks