Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

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

0
Terrace
Asked:
Terrace
  • 3
  • 3
1 Solution
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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
0
 
TerraceAuthor Commented:
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
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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)
0
 
TerraceAuthor Commented:
Thank you very much.
0
 
TerraceAuthor Commented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now