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

Posted on 2009-02-13
Last Modified: 2012-05-06
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.
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.


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

Question by:Terrace
    LVL 17

    Accepted Solution

    Give a try to some outer joins on the table with the missing rows like follows...
    I don't have your table, but it should go something like the following.

    SELECT transID, DateFund, MaxVol, SUM(ln_amount) As SchVol,
     SUM(a_wire_amt) As WireVol  
    FROM tblDTA D
    LEFT OUTER JOIN lock L ON  D.Datefund= L.date_fund
    and D.datefund=L.a_wire_date
    and D.Datefund between '01/01/2009'  and '02/12/2009'  
    group by D.datefund, maxvol, transid

    Author Comment

    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.

    LVL 17

    Expert Comment

    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
    LVL 17

    Expert Comment

    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)

    Author Comment

    Thank you very much.

    Author Comment

    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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video discusses moving either the default database or any database to a new volume.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now