Cumulative addition among views.
-
The program I created aims to display values on the seen textboxes. Values to be displayed here are computed via queries which utilize database views. (vw0112, vw0212, etc.) Currently, the program can perform displaying of monthly reports. The queries I’ve used to generate the values to be displayed are cut and stored on string variables because I need some parts of it to be dynamic (i.e. name of the view). For example, if I choose March on the first dropdown box, the value 03 will be generated and stored in a variable. If you choose 2012 in the year dropdown box, the value 12 will be stored in a variable. A formula is created for the program to be able to generate the view name, in our case, vw0312. My problem is that, I want for the program to perform cumulative addition on the values to be displayed on the textboxes based from the months to be chosen by the user. For example, if the user chooses March and June on the two drop boxes respectively, then what the report should display on each of the textboxes should be the sum of the values of the queries from the month of March to the month of June.static string notdoh = " AND (hospital Not In ('SOUTHERN ISABELA GENERAL HOSPITAL','CAGAYAN VALLEY MEDICAL CENTER','VETERANS REGIONAL HOSPITAL'))";
static string where_pg1 = "WHERE hosp_categ Not In ('S','T') And hosp_class='G' " + notdoh;
static string where_pg2 = " Or hosp_categ=' ' And hosp_class='G' " + notdoh;static string where_prigov = where_pg1 + where_pg2;
//Claim_Amount
string sel_amt = "SELECT SUM(totalamount)";
string fr_amt_orig = "FROM vw";
string fr_amt = "FROM vw";
string SQLquery_amt = "";//Claim_Count
string sel_count = "SELECT COUNT(*)";
string fr_count_orig = "FROM vw";
string fr_count = "FROM vw";
string SQLquery_count = "";string qm = " ";
string var;SqlCommand cmd = new SqlCommand();
string SQL_cmd = "";public void amount(int slctdind1_p, int ind1_p, string sub_p)
{
if ((slctdind1_p + 1) < 10)
var = "0";
else
var = "";
fr_amt += (var + ind1_p + sub_p);
SQLquery_amt = sel_amt + qm + fr_amt;
fr_amt = fr_amt_orig;
}public void count(int slctdind1_p, int ind1_p, string sub_p)
{
if ((slctdind1_p + 1) < 10)
var = "0";
else
var = "";
fr_count += (var + ind1_p + sub_p);
SQLquery_count = sel_count + qm + -
The program I created aims to display values on the seen textboxes. Values to be displayed here are computed via queries which utilize database views. (vw0112, vw0212, etc.) Currently, the program can perform displaying of monthly reports. The queries I’ve used to generate the values to be displayed are cut and stored on string variables because I need some parts of it to be dynamic (i.e. name of the view). For example, if I choose March on the first dropdown box, the value 03 will be generated and stored in a variable. If you choose 2012 in the year dropdown box, the value 12 will be stored in a variable. A formula is created for the program to be able to generate the view name, in our case, vw0312. My problem is that, I want for the program to perform cumulative addition on the values to be displayed on the textboxes based from the months to be chosen by the user. For example, if the user chooses March and June on the two drop boxes respectively, then what the report should display on each of the textboxes should be the sum of the values of the queries from the month of March to the month of June.static string notdoh = " AND (hospital Not In ('SOUTHERN ISABELA GENERAL HOSPITAL','CAGAYAN VALLEY MEDICAL CENTER','VETERANS REGIONAL HOSPITAL'))";
static string where_pg1 = "WHERE hosp_categ Not In ('S','T') And hosp_class='G' " + notdoh;
static string where_pg2 = " Or hosp_categ=' ' And hosp_class='G' " + notdoh;static string where_prigov = where_pg1 + where_pg2;
//Claim_Amount
string sel_amt = "SELECT SUM(totalamount)";
string fr_amt_orig = "FROM vw";
string fr_amt = "FROM vw";
string SQLquery_amt = "";//Claim_Count
string sel_count = "SELECT COUNT(*)";
string fr_count_orig = "FROM vw";
string fr_count = "FROM vw";
string SQLquery_count = "";string qm = " ";
string var;SqlCommand cmd = new SqlCommand();
string SQL_cmd = "";public void amount(int slctdind1_p, int ind1_p, string sub_p)
{
if ((slctdind1_p + 1) < 10)
var = "0";
else
var = "";
fr_amt += (var + ind1_p + sub_p);
SQLquery_amt = sel_amt + qm + fr_amt;
fr_amt = fr_amt_orig;
}public void count(int slctdind1_p, int ind1_p, string sub_p)
{
if ((slctdind1_p + 1) < 10)
var = "0";
else
var = "";
fr_count += (var + ind1_p + sub_p);
SQLquery_count = sel_count + qm +I think you are using the wrong tool for the job. You have a bunch of views (1 for each month) that you are trying to force into a larger structure (aggregated monthly values) why not ignore the monthly views and go directly to the underlying table/view to service your requirement.
SELECT MthField, SUM(field1), COUNT(Field2)
FROM TransactionView
WHERE filter = ....
GROUP BY MthFieldOR If you are using SSRS (you mention report) then use SSRS aggregating functions to get you totals.
Never underestimate the power of human stupidity RAH
