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
