[sharpy]

Hi all

What i am trying to do is total my sales,no sales and cancellations for an individual month and year (i.e.Jan 2008) and output the value as a percentage sales for that month which i am linking to a graph.

The SQL queries i am using for this are
Sales

SELECT COUNT(ClientID) AS Expr1
FROM Clientdata
WHERE (Month = 1) AND ([ResultofIV/APP] = 1) AND (Year = 1)

No sales

SELECT COUNT(ClientID) AS Expr1
FROM Clientdata
WHERE (Month = 1) AND ([ResultofIV/APP] = 2) AND (Year = 1)

Cancellations

SELECT COUNT(ClientID) AS Expr1
FROM Clientdata
WHERE (Month = 1) AND ([ResultofIV/APP] = 4) AND (Year = 1)

I have created these queries using the query builder within the table adaptor of my table CLIENTDATA. I am using VB Express 2008 and have created the database within this.

The VB code i am using to make the calculation is below

 'Jan Sales
		Try
			Dim salej8 As Integer = ClientdataTableAdapter.JanS08
			Dim Nsalej8 As Integer = ClientdataTableAdapter.JanNS08
			Dim Cancelj8 As Integer = ClientdataTableAdapter.JanC08
			If salej8 > 0 Then
				Jan.Text = CInt(Val(salej8) / Val((salej8 + Nsalej8 + Cancelj8) / 100))
			Else
				Jan.Text = CStr(0)
			End If
		Catch ex As Exception

		End Try

Alll this code does what its supposed to do and works well. However its very long winded and I
in the end I will have 36 queries (3 for each month) to do what I want. Also if I want to graph the percentages for say 2009 then it would mean another 36 queries. I am sure there is a better way to do this but i am not sure how.

Can anybody suggest a better way. I know you can use stored procedures but i am not sure how.

Hope this all makes sense

[baavgai]

I'm betting you're looking for something like this:

SELECT 
		sum(case when [ResultofIV/APP] = 1 then 1 else 0 end)  AS Sales,
		sum(case when [ResultofIV/APP] = 2 then 1 else 0 end)  AS NoSales,
		sum(case when [ResultofIV/APP] = 4 then 1 else 0 end)  AS Cancellations
	FROM Clientdata
	WHERE (Month = 1) AND (Year = 1)

For added goodness, throw it in a view with key fields:

SELECT Month, Year,
		sum(case when [ResultofIV/APP] = 1 then 1 else 0 end)  AS Sales,
		sum(case when [ResultofIV/APP] = 2 then 1 else 0 end)  AS NoSales,
		sum(case when [ResultofIV/APP] = 4 then 1 else 0 end)  AS Cancellations
	FROM Clientdata
	GROUP BY Month, Year

Hope this helps.

This post has been edited by baavgai: 15 January 2008 - 10:25 AM

[sharpy]

Thanks baavgai

I will give it a try. I have been looking for examples for a while now as I always ask as a last resort. Are there any sites out there which give clear examples of SQL Queries for MSSQL

[sharpy]

Hi baavgai

This is the the Query that I used and it works well I can now populate the textboxes with one Query.

SELECT
sum(case when [ResultofIV/APP] = 1 then 1 else 0 end) AS Sales,
sum(case when [ResultofIV/APP] = 2 then 1 else 0 end) AS NoSales,
sum(case when [ResultofIV/APP] = 4 then 1 else 0 end) AS Cancellations
FROM Clientdata
WHERE (Month = 1) AND (Year = 1)

Is there a way that I can make the year a variable. Say if I had a combo box containing multiple years. When a year is selected lets say 2009 the query will update the fields from Jan08 to Jan09 automatically.

Sorry to be a pain but I am really out of the realms of my Knowledge

Thanks again for your help.

[baavgai]

sharpy, on 15 Jan, 2008 - 05:53 PM, said:

Is there a way that I can make the year a variable.

That depends quite strongly on the tools you're using. It's also the reason for the second query. If you run number two, you'll get an record for every month, year. If you make a view to that, then you need only call the view with a where for year and month.

What are you using? For database and client? ( Please, dont' say Access. )

[sharpy]

Pissed off lost my connection to the internet.

Anyway. I created the database within VB2008 Express. IE Add item, Database. The file extention is .mdf.

So its definately not access

[baavgai]

Why is it always VB? Worst damn syntax, ever... Ok, I'm better. At least it's not Access.

Try this, don't know what the 2008 Express has, but it might do it for you.

Add a new item to the project, a DataSet. If you have a server window that shows your little project database, drag all the database object into the dataset. Now you have a DataSet with mappings to your data that should allow you to do all manner of happy manipulations. If you're using any kind of .NET, DataSets are the way to go. Do a little reading on them.

For what you're looking for, you can create a table addapter in the DataSet designer based on SQL. If you put the elements you wat variable in the where class with either "?" or "@varName" where the values will go, it can write the code for you.

I shan't be able to get back here for a bit. See how far you get and I'll check in tommorrow. Good luck.

[sharpy]

Thanks for that baavgai

The last query you gave me I used in a stored procedure which return a new table to my dataset with all the information within it. From that it was just a question of dragging it onto my form. I have created all of my application like this. Producing a graph is the final thing I have to do. As I am a hobbiest I used VB express because its free but I hope to learn other languages in the future, any suggestions.

Thanks again I will post if I have any problems or even to let you know I have sorted it.

[baavgai]

sharpy, on 16 Jan, 2008 - 07:14 AM, said:

I hope to learn other languages in the future, any suggestions.

From the same place as VB Express, there's C# Express. It's a well thought out language, building on years of expirience from other languages, mostly Java. There are a number of little things that are just easier to write in C# than VB. It's more preference, really. Under covers, they both preform pretty much the same.

For machines other than Windows, Java is a good choice. If you learn Java, you actually get a lot of C# expirience for free. Python is also fun, though a little more quirky; I have a fundamental issue with loosely typed or typed at runtime languages.

It really doesn't matter the language. The fundamentals are the same. However, for easy to user IDEs, the Microsoft stuff is hard to beat. The Express series is just scaled down Visual Studio, which I use for most of my development projects.

[sharpy]

I think C# is the next thing I wil try.

Anyway took your advise and created a seperate dataset for my SQL queries which has made it alot more tidy. I am now going to work on how I can join my queries to give me one table ie

SELECT
sum(case when [ResultofIV/APP] = 1 then 1 else 0 end) AS SalesJ,
sum(case when [ResultofIV/APP] = 2 then 1 else 0 end) AS NoSalesJ,
sum(case when [ResultofIV/APP] = 4 then 1 else 0 end) AS CancellationsJ
FROM Clientdata
WHERE (Month = 1) AND (Year = 1)

SELECT
sum(case when [ResultofIV/APP] = 1 then 1 else 0 end) AS SalesF,
sum(case when [ResultofIV/APP] = 2 then 1 else 0 end) AS NoSalesF,
sum(case when [ResultofIV/APP] = 4 then 1 else 0 end) AS CancellationsF
FROM Clientdata
WHERE (Month = 2) AND (Year = 1)

I need to join these 2 queries into into 1 query I can then expand it to cover all the months in a year and then add the variable for the year.

lots to do, any suggestions

Cheers for the advise.

[baavgai]

In that dataset designer, use this as a base query.

SELECT 
		sum(case when [ResultofIV/APP] = 1 then 1 else 0 end)  AS Sales,
		sum(case when [ResultofIV/APP] = 2 then 1 else 0 end)  AS NoSales,
		sum(case when [ResultofIV/APP] = 4 then 1 else 0 end)  AS Cancellations
	FROM Clientdata
	WHERE (Month = @Month) AND (Year = @Year)

Notice the names with "@" signs? With luck, it will generate a data adapter with @Month and @Year as parameters. With a little playing you may be able to get fills with other options in the same adapter.

Now, when you call it, you pass the values and get the data you want.

If you truly want to join those two queries, try either

SELECT 
		sum(case when [ResultofIV/APP] = 1 then 1 else 0 end)  AS Sales,
		sum(case when [ResultofIV/APP] = 2 then 1 else 0 end)  AS NoSales,
		sum(case when [ResultofIV/APP] = 4 then 1 else 0 end)  AS Cancellations
	FROM Clientdata
	WHERE (Month in (1,2)) AND (Year = 1)

Or, probably more what you're looking for

SELECT Month,
		sum(case when [ResultofIV/APP] = 1 then 1 else 0 end)  AS Sales,
		sum(case when [ResultofIV/APP] = 2 then 1 else 0 end)  AS NoSales,
		sum(case when [ResultofIV/APP] = 4 then 1 else 0 end)  AS Cancellations
	FROM Clientdata
	WHERE (Year = 1)
   group by Month

Hope this helps.

[sharpy]

Thanks baavgai

Will have a further play. I have been using @month and @Year but it keeps throwing errors up. Although I use this with @lastname which works fine. The only difference is lastname is a string and month and year are values as I am using combo boxes to select month and year. Jan = 1 and 2008 = 1, so I may have to change this for the queries to be able to work, although if I use year = 1 instead of [email protected] then its fine.

I will be trying different things later on if I cannot get over the error I will post the error message.

Thanks again for giving me the Ideas.

[sharpy]

Hi baavgai

Just to let you Know I have managed to sort out what I was trying to do.

I can now fill a graph from Jan to Dec with any year entered into a textbox.
The Query I used was as you suggested

SELECT 
		sum(case when [ResultofIV/APP] = 1 then 1 else 0 end)  AS Sales,
		sum(case when [ResultofIV/APP] = 2 then 1 else 0 end)  AS NoSales,
		sum(case when [ResultofIV/APP] = 4 then 1 else 0 end)  AS Cancellations
	FROM Clientdata
	WHERE (Month = 1) AND (Year = @Year)

Although I had to generate 12 queries one for every month.
Ie Month 1 =Jan to Month 12 = Dec.

This is the generated VB code although this is only for Jan, Feb and march

 Private Sub FillToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillToolStripButton.Click
		Try
			Me.JananyyearTableAdapter.Fill(Me.Sqldataset.Jananyyear, _dateToolStripTextBox.Text)
			Me.FebanyyearTableAdapter.Fill(Me.Sqldataset.Febanyyear, _dateToolStripTextBox.Text)
			Me.MaranyyearTableAdapter.Fill(Me.Sqldataset.Maranyyear, _dateToolStripTextBox.Text)
		Catch ex As System.Exception
			System.Windows.Forms.MessageBox.Show(ex.Message)
		End Try
		Try
			Jan.Text = 0
			Dim salesJan As Integer = Val(SalesTextBox.Text)
			Dim NsalesJan As Integer = Val(NoSalesTextBox.Text)
			Dim CancelJan As Integer = Val(CancellationsTextBox.Text)
			Jan.Text = CInt(Val(salesJan) / (Val(salesJan + NsalesJan + CancelJan) / 100))


		Catch ex As Exception

		End Try
		Try
			Feb.Text = 0
			Dim salesfeb As Integer = Val(SalesTextBox1.Text)
			Dim Nsalesfeb As Integer = Val(NoSalesTextBox1.Text)
			Dim Cancelfeb As Integer = Val(CancellationsTextBox1.Text)
			Feb.Text = CInt(Val(salesfeb) / (Val(salesfeb + Nsalesfeb + Cancelfeb) / 100))
		Catch ex As Exception

		End Try

		Try
			Mar.Text = 0
			Dim salesMar As Integer = Val(SalesTextBox2.Text)
			Dim NsalesMar As Integer = Val(NoSalesTextBox2.Text)
			Dim CancelMAR As Integer = Val(CancellationsTextBox2.Text)
			Mar.Text = CInt(Val(salesMar) / (Val(salesMar + NsalesMar + CancelMAR) / 100))
		Catch ex As Exception

		End Try

		'  Generate the Data
		GetData()
		'  Then Draw the Chart
		DrawChart()

	End Sub

I had to make slight alterations to the lines that fill the textboxes so that they all use one textbox which contains the year variable. Once I had the data I could calculate the percentage sales for each month which is then loaded to the graph. It all needs a little tiding up with error messages etc, but at least it works now.

Again thanks for all your help.

PS please dont come up with one line of code which will do all of this.

[baavgai]

sharpy, on 18 Jan, 2008 - 06:10 PM, said:

PS please dont come up with one line of code which will do all of this.

Not at all. Glad you got something working. I'm still convinced WHERE (Month = @Month) AND (Year = @Year) should do it for you, it pains me to see multi datatables like that.

That said, I usually do this stuff manually. You get a lot more control and for simple stuff it can be easier.

' We'll assume you have connectionString defined somewhere.  You can find in in your server explorer.
Private Function GetConnection() As SqlConnection
	Return New SqlConnection(connectionString)
End Function

' Now, a test call
Private Sub DumpData(ByVal month As Integer, ByVal year As Integer)
	Dim cmd As SqlCommand = Me.GetConnection().CreateCommand()
	cmd.CommandText = "SELECT" _
		& " sum(case when [ResultofIV/APP] = 1 then 1 else 0 end)  AS Sales," _
		& " sum(case when [ResultofIV/APP] = 2 then 1 else 0 end)  AS NoSales," _
		& " sum(case when [ResultofIV/APP] = 4 then 1 else 0 end)  AS Cancellations" _
		& " FROM Clientdata" _
		& " WHERE (Month = @Month) AND (Year = @Year)"
	' yes, you could just include the proper values in the SQL statement string
	' However, binding values is prefered.  It's also proof against nasty SQL injection
	' and frees us from worring about quote or no quote.  Also, much easier for dates
	cmd.Parameters.AddWithValue("@Month", month)
	cmd.Parameters.AddWithValue("@Year", year)
	
	
	' using the command, we make a generic DataAdapter
	Dim da As New SqlDataAdapter(cmd)

	' our DataAdapter needs to fill something
	' this is an typed dataset
	' parent class of what you created in designer.
	Dim ds As New DataSet

	' Get the data
	da.Fill(ds)
	
	'See what we got
	Debug.Print(ds.GetXml())
End Sub

Also, here's and alternate manual method, with zero dataset goodness. While not good for many rows, it's often prefered for a single value or row... like yours.

Private Sub DumpData(ByVal month As Integer, ByVal year As Integer)
	Dim cmd As SqlCommand = Me.GetConnection().CreateCommand()
	cmd.CommandText = "SELECT" _
		& " sum(case when [ResultofIV/APP] = 1 then 1 else 0 end)  AS Sales," _
		& " sum(case when [ResultofIV/APP] = 2 then 1 else 0 end)  AS NoSales," _
		& " sum(case when [ResultofIV/APP] = 4 then 1 else 0 end)  AS Cancellations" _
		& " FROM Clientdata" _
		& " WHERE (Month = @Month) AND (Year = @Year)"
	cmd.Parameters.AddWithValue("@Month", month)
	cmd.Parameters.AddWithValue("@Year", year)
	
	' Here, we have to manually open and close connections ourselves
	' Note the finally, we'll let the exceptions fly, but still want to 
	' close the db connect
	Try
		cmd.Connection.Open()
		Dim reader As SqlDataReader = cmd.ExecuteReader()
		' often see a while here, but we only want one row
		If (reader.Read()) Then
			Debug.Print(reader.GetValue(0))
			Debug.Print(reader.GetValue(1))
			Debug.Print(reader.GetValue(2))
		End If
	Finally
		cmd.Connection.Close()
	End Try
End Sub

Just wanted you to know, there's more than one way to do it.

[sharpy]

Hi baavgai

Thanks for that it has always been my intention to manually write the code for my application in the first instance thats what I started to do. But as I had to build my database tables within VB express it just became a logical conclusion to carry on using the tools provided. I originally downloaded MYSQl to use in conjuction with VB but because I could build my tables within the IDE it became irrelavant. I tried to manually code totalling a column which worked well, but when I built my application the connection string I used which pathed to the database I created would only total what I had originally entered into my data table. Which led me to believe when you build the app it creates a whole new database to use.(confused)

Anyway the connection string is place within the app.config file see below

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<configSections>
	</configSections>
	<connectionStrings>
		<add name="Client_data.My.MySettings.ClientDataConnectionString"
			connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ClientData.mdf;Integrated Security=True;User Instance=True"
			providerName="System.Data.SqlClient" />
	</connectionStrings>
	<system.diagnostics>
		<sources>
			<!-- This section defines the logging configuration for My.Application.Log -->
			<source name="DefaultSource" switchName="DefaultSwitch">
				<listeners>
					<add name="FileLog"/>
					<!-- Uncomment the below section to write to the Application Event Log -->
					<!--<add name="EventLog"/>-->
				</listeners>
			</source>
		</sources>
		<switches>
			<add name="DefaultSwitch" value="Information" />
		</switches>
		<sharedListeners>
			<add name="FileLog"
				 type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL"
				 initializeData="FileLogWriter"/>
			<!-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->
			<!--<add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="APPLICATION_NAME"/> -->
		</sharedListeners>
	</system.diagnostics>
</configuration>

You would think if I used that connection string where I do my coding it would work.
Or I probably I just got it wrong.

Any way I have copied the two latest solutions to try at a later date so thanks for that.

Sharpy