dataset and non existing rows

Question

Please help out newbie. I am reading mothly sales statistics for last two years from stored procedure, display it on asp.net site and it works just fine.
Problem is with products that are not sold often I need to figure out which months do not have any sales. In that case I need to put zero in table cell and move to next row in dataset.
For...Each does not do the trick in case where there isn't data for every month.
Question is, how to move to next sqlrow and how to test when all rows heve been read?

sqlSelect = "EXECUTE dealer_sales_statistics @productID = '" & strProdID.Value & "'"
Dim sqlConn As New SqlConnection(sqlConnStr)
Dim sqlRow As DataRow
sqlConn.Open()
Dim sqlAdapt As New SqlDataAdapter(sqlSelect,sqlConn)
Dim sqlDataSet As New DataSet()
sqlAdapt.Fill(sqlDataSet, "sales_statistics")  
Do Until sqlRow.EOF  
 If intCounter < 12 Then
 ' arrMonth contains last 24 months, e.g. "12_2009" to "1_2008"'
 ' stored procedure can return values for any month between that range'
 ' amount of returned values (DataSet sqlRows) can vary from 0 to 24'
  If arrMonth(intCounter) = sqlRow("month") & "_" & sqlRow("year") Then
   strLine_1 &= "<td>" & CInt(sqlRow("qty")) & "</td>"
   arrSumma_1 = arrSumma_1 + CInt(sqlRow("qty"))
   sqlRow.MoveNext
  Else
   strLine_1 &= "<td class='cell'>0</td>"
  End If
 Else
 'using intCouter and same code to separate sales in 12 month periods'
  If arrMonth(intCounter) = sqlRow("month") & "_" & sqlRow("year") Then
   strLine_2 &= "<td>" & CInt(sqlRow("qty")) & "</td>"
   arrSumma_2 = arrSumma_2 + CInt(sqlRow("qty"))
   sqlRow.MoveNext
  Else
   strLine_2 &= "<td>0</td>"
  End If
 End If
 intCounter = intCounter + 1
Loop

Answer 1

I think that you are focusing on the wrong area by trying to do this in your code. I can think of a likely solution there but it is really messy. Instead, focus on making sure that the sets returned by the stored proc are complete so you can iterate them without worry about missing months. That is, the stored procedure is probably returning sets made up only of months where there were sales (e.g. due to an inner join) - and you need to change this so it returns all months.

So, instead of posting the VB code, I'd suggest that you post the stored proc to get help in resolving the issue.

As a general guideline, I'd approach this by creating a dummy table with the months of the year listed (along with their month numbers to perform the join). Then, fold that table in with the query using a left outer join to ensure that all months are represented. Also, when selecting the final sales figures, make sure that there are no null values (for months where there were no sales) by using an "IsNull(Val, 0) as Val" to substitute a zero.

Again, this is just general guidance, we'd need to see the actual sproc to really help.

Answer 2

Here is how I did solve this with SQL. I create dynamically temp table that holds last 24 months and another temp table with sales data 0 to 24 months. Maybe this will help somebody with similar problem. (code below is in sql server as stored procedure). Thank you for help Mark!

DECLARE @strTemp_months TABLE 
( 
    sorting INT,
    months INT,
    years INT
)

DECLARE @mnth INT
SET @mnth = 0

WHILE (@mnth < 24)
    BEGIN
        INSERT @strTemp_months
        SELECT CASE WHEN YEAR(GETDATE()) = YEAR(DATEADD( m , -@mnth , GETDATE())) THEN 1 ELSE 2 END AS sorting, 
            MONTH(DATEADD( m , -@mnth , GETDATE())), YEAR(DATEADD( m , -@mnth , GETDATE()))
        SET @mnth = @mnth + 1
END

DECLARE @productID VARCHAR(12) 
SET @productID = '1234567890'

DECLARE @strTemp_statistics TABLE 
( 
    sorting INT, 
    months INT, 
    years INT, 
    productno VARCHAR(35),
    salesqty DECIMAL(9,2)
)

INSERT @strTemp_statistics 
SELECT CASE WHEN YEAR(transaction_date) = YEAR(GETDATE()) THEN 1 ELSE 2 END AS sorting, 
        MONTH(transaction_date) AS months, YEAR(transaction_date) AS years, product_number AS productno, 
        SUM(qty) AS salesqty
FROM sales_events

WHERE product_number = @productID
    -- including all transactions from last 24 full months until today
    AND transaction_date >= CAST(YEAR(DATEADD( m , -23 , GETDATE())) AS CHAR(4)) +  '-' + CAST(MONTH(DATEADD( m , -23 , GETDATE())) AS VARCHAR(2)) +  '-01' 
GROUP BY MONTH(transaction_date), YEAR(transaction_date), product_number

SELECT m.sorting, m.months, m.years, COALESCE(productno, 'No Sales') AS productno, COALESCE(kpl, 0) AS salesqty
FROM @strTemp_months m LEFT OUTER JOIN @strTemp_statistics s
    ON m.months = s.months AND m.years = s.years
ORDER BY 1, 2 DESC