|
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;
string sel_amt = "SELECT SUM(totalamount)";
string fr_amt_orig = "FROM vw";
string fr_amt = "FROM vw";
string SQLquery_amt = "";
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 + fr_count;
fr_count = fr_count_orig;
}
public void btnGenerateGov_Click(object sender, EventArgs e)
{
cnn.ConnectionString = Properties.Settings.Default.DBInfo;
string year = cbYearGov.Text;
string sub = year.Substring(2, 2);
int slctdind1 = cbMonthGov.SelectedIndex;
int slctdind2 = cbMonth2Gov.SelectedIndex;
int ind1 = slctdind1 + 1;
int ind2 = slctdind2 + 1;
amount(slctdind1, ind1, sub);
count(slctdind1, ind1, sub);
try
{
cnn.Open();
#region government
SQL_cmd = SQLquery_count + " " + where_prigov;
txtPGClaims.Text = claims(cmd, SQL_cmd);
SQL_cmd = SQLquery_amt + " " + where_prigov;
txtPGAmt.Text = claims(cmd, SQL_cmd);
}
|
|
|
|
|
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 MthField
OR
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
|
|
|
|
|
hi Guys , i am a bit stuck here
i have this value "0.833333333333333"
and i want the asnwer to be 83 , i tried this
SELECT ROUND(0.833333333333333,2)
but it does not work
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
SELECT TRUNC(0.833333333333333 * 100)
Add .5 prior to multiplying if you actually need to "round" the number.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks
DECLARE @Value FLOAT = 0.833333333333333;
SELECT CAST(@Value * 100 AS INT);
Worked for me
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Declare @X float = 0.833333333333333
SELECT ROUND(@X*100,0)
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
|
How to convert Physical XML files into Sqlserver Tables?
* Physical XML need to be converted into XML table with respective columns
* XML file may contains multiple tables, in such case multiple tables has to be created in db
Regards,
Murali Kumar Pitchika
http://muralip.f2g.net
|
|
|
|
|
I wasn't aware that XML files were physical.
What you are trying to do depends on the schema. Can you show an example?
|
|
|
|
|
Member 4006546 wrote: How to convert Physical XML files into Sqlserver Tables?
..documentation[^].
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
How do you use the AVG api in sql to manage grouped output? For example:
Given a SQL Table “Employee”
Name Salary City
John Doe 15000 Seattle
Jane Doe 30000 Redmond
Tim Wayne 25000 Seattle
Write a SQL query that would return the average salary per city in the format shown below:
City Salary
Seattle 20000
Redmond 30000
|
|
|
|
|
Xarzu wrote: Write a SQL query that would return the average salary per city in the format shown below:
For some reason this sounds like homework. What have you done so far for a query?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Select City, AVG(Salary) from Employee where ... I don't know the rest
Where city == city or something like that?
|
|
|
|
|
You could try:
select city, avg(salary) from Employee group by city
This will give you the average grouped by cities. How to sort the cities in the order you want to order by in your expected result, I'm leaving that as an exercise
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
modified 11-Aug-12 20:19pm.
|
|
|
|
|
Xarzu wrote: How do you use the AVG api function in sql
It's not an "API".
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
All Version of Sql server
Select City, AVG(Salary) AS Salary
FROM @t
Group By City
Order By City DESC
This can also be done by treating AVG function as Analytical function which is available since SQL Server 2005
Select City,Salary From
(
Select
Rn = Row_Number() Over(PARTITION BY City Order By (Select 1))
,City
,AVG(Salary) OVER(PARTITION BY City) AS Salary
From @t) X
Where X.Rn = 1
Order By City DESC
The answer for both the cases
City Salary
Seattle 20000
Redmond 30000
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
|
I have a question that's more of the hypothetical kind.
I have two tables:
Usersuserid | username |
---|
1 | John Doe | 2 | Jane Doe |
emailuserid | email |
---|
1 | john.doe@myworkmail.com | 1 | john.doe@myprivatemail.net | 1 | john.doe@someothermail.org | 2 | jane.doe@myworkmail.com | 2 | jane.doe@myprivatemail.net | 2 | jane.doe@someothermail.org |
Now I'd like to make one (or zero) email address per user the default address.
On an ANSI compliant system I could simply add a nullable column to the email table and add a check so that the value can only be 'DEFAULT' or null and add a unique composite key on this column and the userid column.
This would of course not work on certain databases that don't allow more than one null value when having a unique key.
So what other solutions are there? Preferrably not allowing null values.
|
|
|
|
|
How about a bit field (0/1;true/false)? Set the default to 0, not null.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
How would that allow only one emailaddress to be set to default?
|
|
|
|
|
The default is set to true, the rest to false.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
An extra table to store only a reference to the user, and a reference to his default email;
..which would work best if email would get an identity-like field.
--edit;
Add a UNIQUE constraint on the userId-column
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Where's the 'slapping my forehead' icon when I need it? I guess will have to do.
|
|
|
|
|
Hi all,I want to write an application(web or desktop) which will monitor different database status(status:conncetion error,running or down e.t.c) and report back to say:database1 is not down, oracle database2 is down or connection error etc. So I dont really have an idea where to start.Coding using C#.thanx.
|
|
|
|
|
You could begin by something as simple as:
1) Make a connection to the database New SqlConnection(GetConnString())
2) Open the connection SQLconn.Open()
3) Issue some sort of simple command SQLcmd = New SqlCommand("select count(*) from customers", SQLconn)
4) Get data from the DB r = SQLcmd.ExecuteScalar
5) Wrap all of this code in a Try-Catch block and if successful, then you can safely assume that your connection to the database is up and the database is running.
Good luck
|
|
|
|
|
Thanx a lot.I even add more functionalities/Methods
|
|
|
|
|
leketekoa wrote: oracle database2 is
Did you start by looking for existing solutions?
As an example you might want to start with the following in google: nagios oracle
leketekoa wrote: nd report back
What that means can add significantly to scope of the project.
|
|
|
|