retrieve data from SQL tables

[Violet_82]

Hi guys, I'm in a bit of a situation here and I was wondering if anybody has any suggestion. Basically, I need to retrieve some data from 2 or perhaps 1 SQL table (that's what I'd like you to help me finding out).
OK, here are my two tables (sure you've seen them already):
expenses table (which is updated by the application):

expensesCode (whose data never change):

Right, so I have to get the data out of the tables. I'd do what I normally do, but here there is a problem: this is how my application for presenting the data looks like:

So I have to determine which expense is what when I retrieve the data because the first table (expenses) contains the relevant numerical data I'm interested in but it has ints in place of the type of expense. So, how do I proceed? I'd use a normal SELECT statement to retrieve the data but it's not clear to me how, when and if I should determine what's what (is it a food, car, bill or rent expense?) Do I have to access both tables or is it just a matter of filtering the data in the expenses table, something like "is expensesID value 4? If so it's food so get the cost and display it in the food field"?

[djjeavons]

Hi

You could use the PIVOT function to create a CrossTab table so that your Rent, Car, Food and Bills becomes columns. Then you can simply read the data into your program as standard.

    SELECT Rent, Car, Bills, Food
    FROM (
    SELECT        dbo.expenseCode.ExpenseType, dbo.expenses.Cost, dbo.expenses.Date
    FROM            dbo.expenses INNER JOIN
                             dbo.expenseCode ON dbo.expenses.ExpenseId = dbo.expenseCode.Id
    WHERE        (DATEPART(month, dbo.expenses.Date) = 1)
    ) sq
    PIVOT(
    SUM(Cost) For ExpenseType IN (Rent, Car, Bills, Food)
    ) As pt

HTH

[Traevel]

Regardless of Entity Framework or your data model, the select query could be something like:

SELECT expenseCode.ExpenseId,expenseCode.Cost,expenses.ExpenseType FROM expenseCode INNER JOIN expenses ON expenseCode.ExpenseId=expenses.Id;

Which you can put in a view if you don't want to have this query in your code.

Though I believe Entity Framework automatically links tables (with foreign key relations) for you. You can access one through the other and vice versa through the database context.

"is expensesID value 4? If so it's food so get the cost and display it in the food field"

What if you added an extra expense type? You'd add a new input manually? Your table structure suggests you intend a dynamic expense type, but the code would prevent such behaviour again if you used manually defined inputs.

[Violet_82]

Hi thanks, that's great. I'm not terribly familiar with SQL, so I'm looking into this PIVOT and finding it slightly difficult to visualize this new table, not to mention the SQL query which is a tad unfamiliar to me. OK, let's look at them. Does the new table (just so I can visualize it, that's all) look like this:

About the query, in the first bit you're selecting Rent, Car, Food and Bills but from which table? If I read it correctly with all the parentesis, it looks like you're selecting from "sq"? Then PIVOT statement which we said creates the table and then I seem to understand, you sum up all the Food, Car, Bills and Rent values, correct?
I looked this up WHERE(DATEPART(month, dbo.expenses.Date) = 1) so you are essentially getting the month part of the date, but what does '=1' mean?
Finally, that "AS pt", does it mean that you're creating an extra column called pt?

Also, one more question: would it be easier to do what I thought I could do above, adding a filter to the SQL retrieval statement to say if the ExpenseId = 1 or 2 or 3 or 4 and take it from there, or is this a silly approach? I ask just so I understand really, if you excuse my ignorance in SQL :- )!

[djjeavons]

Hi

Does the new table (just so I can visualize it, that's all) look like this:
It will look like:

About the query, in the first bit you're selecting Rent, Car, Food and Bills but from which table?

The first part of the query hard codes the expense types then within the parenthesis we do the actual SELECT statement to get your Expenses with an Inner Join to the ExpenseCode table so that we can associate 1 with rent, 2 with Car etc. This is then called sq as a table name.

I looked this up WHERE(DATEPART(month, dbo.expenses.Date) = 1) so you are essentially getting the month part of the date, but what does '=1' mean?

The 1 is for January as I assumed from your UI that you want to get the expenses for a month.

Finally, that "AS pt", does it mean that you're creating an extra column called pt?

No, we are simply giving the statement within the parenthesis another table name. It is not used anywhere but if you were to build on this query then you may refer to it.

Also, one more question: would it be easier to do what I thought I could do above, adding a filter to the SQL retrieval statement to say if the ExpenseId = 1 or 2 or 3 or 4 and take it from there, or is this a silly approach? I ask just so I understand really, if you excuse my ignorance in SQL :- )!

You could do this but it would be a bit cumbersome in SQL. If however, you managed the data from within your program then yes this is a feasible approach. However, I would go with this method as you are getting the database to do this work for you (and this is the correct place to do this) and you simply read the values of each column back in your code.

I would recommend looking further into the PIVOT function as I am by no means an expert in SQL and my comments above may be better explained by other sources.

[Violet_82]

Thanks guys.

What if you added an extra expense type? You'd add a new input manually?

Good point.
@djjeavons. Thanks for the explanation. I took a bit of time to read a bit more about SQL (the w3c school SQL tutorial helped a bit ) because I realized I really don't know much about it....I feel a bit better now :-)! Anyway, I'm just thinking: so we create this new table where I can get the data from, but what I don't understand is, when is this table created? Everytime I run the application? Where does it reside, together with the other two tables? Is the data retrieval as it would be for any other table or is it different since it is a table that we create dynamically?
So in other words, I use your code to create the merged table and then I can get read out values straightaway in a normal fashion

[djjeavons]

but what I don't understand is, when is this table created?

It isn't really created in the true sense of the word, it is the same as a SELECT statement but it does use inner selects which are like temporary tables but in memory, not stored in your database.

So in other words, I use your code to create the merged table and then I can get read out values straightaway in a normal fashion

Yes, you can treat it as a normal SQL statement that will return four columns (one for each expense type). Then in your code, you simply read each of the column values and assign it to your text fields. You can then do your sum afterwards. Something like the following (not tested):

    SqlConnection connection = new SqlConnection ("your connection string");

    string pivotStatement = @"SELECT Rent, Car, Bills, Food FROM (SELECT dbo.expenseCode.ExpenseType,
    dbo.expenses.Cost, dbo.expenses.Date FROM dbo.expenses INNER JOIN;
    dbo.expenseCode ON dbo.expenses.ExpenseId = dbo.expenseCode.Id WHERE 
    (DATEPART(month, dbo.expenses.Date) = 1)) sq PIVOT( SUM(Cost) For ExpenseType 
    IN (Rent, Car, Bills, Food)) As pt";

    SqlCommand command = new SqlCommand(pivotStatement, connection);

    connection.Open();
    SqlDataReader reader = command.ExecuteReader();

    //Should only be one row so no need to loop around the DataReader
    if (reader.HasRows)
    {
        reader.Read();
        YourRentTextField.Text = reader["Rent"].ToString();
        YourCarTextField.Text = reader["Car"].ToString();
        YourFoodTextField.Text = reader["Food"].ToString();
        YourBillsTextField.Text = reader["Bills"].ToString();
    }
    connection.Close();

    //Now do your total
    Decimal total = (decimal)YourRentTextField.Text + (decimal)YourCarTextField.Text + (decimal)YourFoodTextBox.Text + (decimal)YourBillsTextField.Text;

    TotalsTextBox.Text = total.ToString();

HTH

[Violet_82]

Yes I was thinking about something similar, except for the sum...so are you effectively doing it twice (once in the SQL statement and once outside?) or is the first one part of the PIVOT statement? Finally, 2 more questions if I may.
1)I had a look at subqueries as well, since you're using one, and they are largely clear but here you're using a FROM subquery, and I only saw a few examples. How should I read that subquery? My interpretation, probably wrong, of the whole SQL statement is (in pseudocode):

Select the Rent, Car, Bills and Food FROM [start of subquery]the new table containing ExpenseType, Cost and Date fields obtained by merging expenseCode and expenses WHERE the month is january[end of subquery] and create a new sq table, then SUM all the costs and 'rotate' the table so that Rent, Car, Bills and Food are column headings and name the new table pt

2)You used WHERE(DATEPART(month, dbo.expenses.Date) = 1) as filter, which is great, but what if I want to display, say February? I mean, what I have in mind - and please forgive me for not making it clear at the beginning - would be to have the current month's worth of expenditure always at the top, and then perhaps past months at the bottom...so perhaps for the other months I will have to use a separate SQL statement rather than trying to incorporate everything in one?

[djjeavons]

Yes I was thinking about something similar, except for the sum...so are you effectively doing it twice (once in the SQL statement and once outside?) or is the first one part of the PIVOT statement?

It's not so much doing it twice, rather the Sum in the SQL statement is summing all instances of each expense type, whereas the sum in the code is summing the entire expenses retrieved. I imagine it could be cominged into one SQL statement but haven't got time to investigate unfortunately.

How should I read that subquery? My interpretation, probably wrong, of the whole SQL statement is (in pseudocode):

Yes, that is pretty much it.

2)You used WHERE(DATEPART(month, dbo.expenses.Date) = 1) as filter, which is great, but what if I want to display, say February? I mean, what I have in mind - and please forgive me for not making it clear at the beginning - would be to have the current month's worth of expenditure always at the top, and then perhaps past months at the bottom...so perhaps for the other months I will have to use a separate SQL statement rather than trying to incorporate everything in one?

Yes, I figured you would want to change this as the month changes. So when you are building the SQL statement in your code you can make use of DateTime.Now.Month; to get the current month number. If you want to retrieve all expenses by month for the current month and previous you could do something like:

    SELECT Rent, Car, Bills, Food, DATENAME(month, date) As MonthDate
    FROM (
    SELECT        dbo.expenseCode.ExpenseType, dbo.expenses.Cost, dbo.expenses.Date
    FROM            dbo.expenses INNER JOIN
                             dbo.expenseCode ON dbo.expenses.ExpenseId = dbo.expenseCode.Id
    WHERE        (DATEPART(month, dbo.expenses.Date) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
    ) sq
    PIVOT(
    SUM(Cost) For ExpenseType IN (Rent, Car, Bills, Food)
    ) As pt

But you will need to modify the IN statement to only include month numbers less than the current month. That is, when in February, only supply 1, when in march supply 1, 2 etc. There may be better ways to do this but this is what springs to mind at the moment.

[Violet_82]

Hi, thanks for the suggestions. I'm halfway developing this, and something occurred to me about displaying the current month. Would it be easier if I did something like this (which isn't that different from what you suggested anyway):
-get the numerical representation of month:

DateTime  now = DateTime.Now;//get current date
 testingDate.Value = now.ToString("MM");

And then somehow using that now.ToString("Month"); in the SQL string, something along the lines of:

"SELECT Rent, Car, Bills, Food FROM( SELECT expenseCode.ExpenseType, expenses.Cost, expenses.Date FROM expenses INNER JOIN expenseCode ON expenses.ExpenseId = expenseCode.Id WHERE (DATEPART(month, expenses.Date) = " + now.ToString('MM') + ")) sq PIVOT( SUM(Cost) FOR ExpenseType IN (Rent, Car, Bills, Food)) AS pt";

And that should just sort the data out each month, correct?

[Violet_82]

Ah, wait apparently that's bad practice, I just came across this interesting resource http://www.csharp-station.com/Tutorial/AdoDotNet/lesson06.
Ok, so forget the above. I have done it again with parameters:

 //get the data
        hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Applications;" + "Integrated Security=True");
        strInsert = "SELECT Rent, Car, Bills, Food FROM( SELECT expenseCode.ExpenseType, expenses.Cost, expenses.Date FROM expenses INNER JOIN expenseCode ON expenses.ExpenseId = expenseCode.Id WHERE (DATEPART(month, expenses.Date) = @theMonth)) sq PIVOT( SUM(Cost) FOR ExpenseType IN (Rent, Car, Bills, Food)) AS pt";
        //1. defining @theMonth parameter as in http://www.csharp-station.com/Tutorial/AdoDotNet/lesson06
        SqlParameter param = new SqlParameter();
        param.ParameterName = "@theMonth";
        param.Value = now.ToString("MM");

        sqlCmd = new SqlCommand(strInsert, hookUp);

        // 2. add new parameter to command object
        sqlCmd.Parameters.Add(param);
        hookUp.Open();
        reader = sqlCmd.ExecuteReader();

I tested it (well only for January as I wouldn't know how to test it for February) and it works, so now I have the current month results always displayed at the top of the page. There is only one thing that puzzles me.
As you can see I used param.Value = now.ToString("MM"); but originally I used param.Value = now.ToString("M"); because I was after an int with no 0's: but if I use only one "M" it doesn't work, no errors or anything, it just doesn't retrieve the data. If I go for 2 "MM" it works. Not convinced, I run a test, which, alas, confused me even more.
I added a test input field <input type="text" id="testingDate" runat="server" readonly> in my aspx file and in the code behind I added this:
testingDate.Value = now.ToString("M"); This populate the input field with "23 January". Maybe I'm being thick here, but shouldn't I get "1" instead??
I should, according to this https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx#M_Specifier

[pritaeas]

From (found in your linked URL): https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx#UsingSingleSpecifiers

Using Single Custom Format Specifiers

"To use any of the custom date and time format specifiers as the only specifier in a format string (that is, to use the "d", "f", "F", "g", "h", "H", "K", "m", "M", "s", "t", "y", "z", ":", or "/" custom format specifier by itself), include a space before or after the specifier, or include a percent ("%") format specifier before the single custom date and time specifier."

[djjeavons]

Hi

The reason "M" does not return any data is that the output of that will be "23 January". Instead, if you want to get the Month without leading zero's then continue using "MM" but convert it to an Int so that the leading zero is removed.

param.Value = now.ToString(Convert.ToInt32(now.ToString("M")));

HTH

[Violet_82]

Hi thanks, Ok didn't occur to me, but since it is working as it is with 01, do I need to convert that to an int anyway? Is it just for good practice?
Also, is there any simple way to test another month to make sure that it work, or should I wait for February and see then :-)?
thanks

[djjeavons]

If you change your system clock you can test different months.

[Violet_82]

Um, unfortunately I've just realized that the application doesn't work, for whatever reasons. I can insert data in the database - I can check that from visual studio and the new data is there - but I don't seem to be able to retrieve the data from the database and populate the input fields with anything except for 2 old values that are there, car and food (respectively 44.00 and 56.00, you can see them in the first post, where I have both tables and here is a screenshot of the Overview page as it is now)

but if I insert more values for anything, including food and car, the app doesn't add them up or display them at all (but note that it is adding up the old values for car). Weird. I'm looking through my code over and over, but I don't seem to be able to find the error.

Here are the files, just in case anybody fancies having a look through it, because I can't spot anything wrong, but then again I have been looking at this app for a while now so perhaps some fresh eyes will help:

Overview.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="Overview.aspx.cs" Inherits="Overview" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="HeadingPlaceholder" Runat="Server">
    <h2>Data extract for the month of <%--<span runat="server" id="currentMonth"></span>--%><asp:Label ID="currentMonth" runat="server" Text="Label"></asp:Label> </h2>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="ContentPlaceholder" Runat="Server">
    <div class="results">
        <div class="control-group">
            <label class="control-label" for="rent">Rent</label>
            <div class="controls">
                <input type="text" id="rent" runat="server" readonly>
            </div>
        </div>
        <div class="control-group">
            <label class="control-label" for="car">Car</label>
            <div class="controls">
                <input type="text" id="car" runat="server" readonly>
            </div>
        </div>
        <div class="control-group">
            <label class="control-label" for="bills">Bills</label>
            <div class="controls">
                <input type="text" id="bills" runat="server" readonly>
            </div>
        </div>
        <div class="control-group">
            <label class="control-label" for="food">Food</label>
            <div class="controls">
                <input type="text" id="food" runat="server" readonly>
            </div>
        </div>    
        <%--<div class="control-group">
            <label class="control-label" for="comment">Comment</label>
            <div class="controls">
                 <textarea id="expenseComment" runat="server" readonly></textarea>
            </div>
        </div>--%>
        <div class="control-group">
            <label class="control-label" for="total">Total</label>
            <div class="controls">
                <input type="text" id="total" runat="server" readonly>
                <input type="text" id="testingDate" runat="server" readonly>
            </div>
        </div>
    </div>

</asp:Content>

ANd this is Overview.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class Overview : System.Web.UI.Page
{
    DateTime now;//date variable
    //sql variables
    private SqlCommand sqlCmd;
    private SqlConnection hookUp;
    private SqlDataReader reader;
    private string strInsert;

    protected void Page_Load(object sender, EventArgs e)
    {
        //extract data from SQL table
        now = DateTime.Now;//get current date
        currentMonth.Text = now.ToString("MMMM");//get and display current month in full
        //to remove, month number
        testingDate.Value = now.ToString("MM");

        //get the data
        hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Applications;" + "Integrated Security=True");
        strInsert = "SELECT Rent, Car, Bills, Food FROM( SELECT expenseCode.ExpenseType, expenses.Cost, expenses.Date FROM expenses INNER JOIN expenseCode ON expenses.ExpenseId = expenseCode.Id WHERE (DATEPART(month, expenses.Date) = @theMonth)) sq PIVOT( SUM(Cost) FOR ExpenseType IN (Rent, Car, Bills, Food)) AS pt";
        //1. defining @theMonth parameter as in http://www.csharp-station.com/Tutorial/AdoDotNet/lesson06
        SqlParameter param = new SqlParameter();
        param.ParameterName = "@theMonth";
        param.Value = now.ToString("MM");

        sqlCmd = new SqlCommand(strInsert, hookUp);

        // 2. add new parameter to command object
        sqlCmd.Parameters.Add(param);
        hookUp.Open();
        reader = sqlCmd.ExecuteReader();
        if (reader.HasRows) { //because there should be only 1 row to read, otherwise use while
            reader.Read();
            rent.Value = Convert.ToString(reader["Rent"]);
            car.Value = Convert.ToString(reader["Car"]);
            bills.Value = Convert.ToString(reader["Bills"]);
            food.Value = Convert.ToString(reader["Food"]); 


        }
        reader.Close();
        hookUp.Close();
    }
}

I check the code that reads and copy the values from the database into the input fields and they seem OK (I check whether they start with capital or small letter, and I think they are correctly starting with an upper case letter); also I'm not prefixing the database table names with "dbo.", but to be honest, I can't be that, can it, otherwise I wouldn't see those two values?!

[Violet_82]

Has anybody got an idea? I'm really struggling to understand why only part of the data in the table is returned.

[djjeavons]

Hi

Have you tried putting a breakpoint in your page load routine and checking each of the values?

For example, you are using the "MM" format for your parameter value which returns "01", could this be a problem. Also, you could write out the SQL statement to the immediate window and execute it as a new query to see what results are returned. This would identify or eliminate any problems with the actual SQL statement.

[James E]

Here is a statement that will return the correct results for you:
SELECT et.ExpenseType, ISNULL(SUM(er.Cost),0.00) as Cost
FROM ExpenseType et WITH(NOLOCK)
LEFT OUTER JOIN ExpenseRecord er WITH(NOLOCK) ON er.ExpenseTypeId = et.ExpenseTypeId
GROUP BY et.ExpenseType

The query will return all data from the ExpenseType table (where you have identified the types of expenses) and only those records from the ExpenseRecord table where the ExpenseTypeId fields match. The records where the tables do not match will only return nulls, so we use ISNULL to return 0.00 instead of NULL values.

The WITH(NOLOCK) statements will reduce blocking in the database if others are running similar queries at the same time.

[Violet_82]

@james E: thanks for that but I get an error when I use your string strInsert = "SELECT et.ExpenseType, ISNULL(SUM(er.Cost),0.00) as Cost FROM ExpenseType et WITH(NOLOCK) LEFT OUTER JOIN ExpenseRecord er WITH(NOLOCK) ON er.ExpenseTypeId = et.ExpenseTypeId GROUP BY et.ExpenseType"; saying that :

Invalid object name 'ExpenseType'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'ExpenseType'.

Source Error: 


Line 42:         sqlCmd.Parameters.Add(param);
Line 43:         hookUp.Open();
Line 44:         reader = sqlCmd.ExecuteReader();
Line 45:         if (reader.HasRows) { //because there should be only 1 row to read, otherwise use while
Line 46:             reader.Read();

Source File: c:\Users\antonio.borrillo\Documents\Visual Studio 2012\WebSites\Expenses\Overview.aspx.cs    Line: 44 

Stack Trace: 


[SqlException (0x80131904): Invalid object name 'ExpenseType'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1754082
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5295874
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +59
   System.Data.SqlClient.SqlDataReader.get_MetaData() +90
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
   System.Data.SqlClient.SqlCommand.ExecuteReader() +99
   Overview.Page_Load(Object sender, EventArgs e) in c:\Users\antonio.borrillo\Documents\Visual Studio 2012\WebSites\Expenses\Overview.aspx.cs:44
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
   System.Web.UI.Control.OnLoad(EventArgs e) +92
   System.Web.UI.Control.LoadRecursive() +54
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772

@djjavons yes that has occurred to me to, but I haven't done it, and the reason is that since it seems that the problem is in the SQL string because the table populates OK, I wasn't sure that I would be able to see what exactly happens in the string using debug mode. I will try now and see if I can get anything out of it
thanks

[Violet_82]

@djjavons, I run teh debug and it looks like the values just don't get across as you can see from the debugging screenshot http://s22.postimg.org/5i02eo1xd/debugging.jpg (you'll see that all teh values are incorrect, rent is empty, car is 44.0 0 but it should be 54.00, bills should be 30,00 but it is empty, and food is 56.00 but should be 66.00).

Also, you could write out the SQL statement to the immediate window and execute it as a new query to see what results are returned

How is that done? is it just right click on the table and select new query, then run it fromt here? The thins is I don't see any results except for Command completed successfully, as in the screenshot here http://s2.postimg.org/ms61wn7yh/sql_query_result.jpg

[djjeavons]

Hi

To write out your SQL statement, in the immediate window which is next to your call stack in the screen shot you provided, enter ?strInsert and press enter. This will then output the value of strInsert which you can then paste into your SQL Server query window and execute. You will need to change the value of @Month but before doing so, can you also check (expand) the value of param in your Locals window to see what the value of @Month is in your code.

[Violet_82]

OK, so apparently the right query is

strInsert = "SELECT Rent, Car, Bills, Food FROM( SELECT expenseCode.ExpenseType, expenses.Cost FROM  expenses INNER JOIN  expenseCode ON expenses.ExpenseId = expenseCode.Id  WHERE (Datepart(month, expenses.Date) = @theMonth)) sq PIVOT( SUM(Cost) FOR ExpenseType IN (Rent, Car, Bills, Food)) pt;";

Got to admit that I didn't come to the solution myself, but got some help as I'm not too strong in SQL. So, the difference is that expenses.Date from the inner SELECT is gone, the old one was

strInsert = "SELECT Rent, Car, Bills, Food FROM( SELECT expenseCode.ExpenseType, expenses.Cost, expenses.Date FROM expenses INNER JOIN expenseCode ON expenses.ExpenseId = expenseCode.Id WHERE (DATEPART(month, expenses.Date) = @theMonth)) sq PIVOT( SUM(Cost) FOR ExpenseType IN (Rent, Car, Bills, Food)) AS pt";

Not quite sure what the difference translates into what though. OK we got rid of expenses.Date: what does that do?