0

I have two tables: Incomings and Expenditures. Incomings has IncDate and IncCost and Expenditures has ExpDate and ExpCost. IncCost and ExpCost are stored as a double and IncDate and ExpDate are stored as a string dd/MM/yyyy (bad practice I know). I am trying to merge IncDate and ExpDate and sum up the costs for each day. I am trying to add this data to a datatable so that I can create a chart with it. The error I am getting says "Incorrect syntax near the keyword 'AS'" although that's all it says so it's not much use. Here is the code I am using to try and create this datatable

DataTable dt = new DataTable();
    public DataTable DatatableNew()
    {

        SqlDataAdapter Adp = new SqlDataAdapter("select CONVERT(DATETIME, IncDate, 103) AS IncDate, SUM(IncCost) AS IncCost, CONVERT(DATETIME, ExpDate, 103) AS ExpDate, SUM(ExpCost) AS ExpCost from Incomings i FULL OUTER JOIN Expenditures e ON i.IncDate = e.ExpDate AS FinalDate GROUP BY CONVERT(DATETIME, FinalDate, 103) ORDER BY CONVERT(DATETIME, FinalDate, 103)", con);
        dt.AcceptChanges();
        Adp.Fill(dt);
        return dt;

    }

Any help is greatly appreciated. Cheers in advance

Stuart
  • 143
  • 1
  • 3
  • 18

1 Answers1

1

Error is with your "AS FinalDate" statement (shown in bold in the query below) which you have used in ON clause. Not sure how come that As clause has suddenly erupted in an ON clause. Aliasing of columns (using the AS keyword) is not allowed with ON clause in SQL. Try fixing that to sort out your issue. If FinalDate is computed field then that should come in SELECT clause.

SELECT CONVERT(DATETIME, IncDate, 103) AS IncDate,
SUM(IncCost) AS IncCost, 
CONVERT(DATETIME, ExpDate, 103) AS ExpDate, 
SUM(ExpCost) AS ExpCost 
FROM Incomings i 
FULL OUTER JOIN Expenditures e 
ON i.IncDate = e.ExpDate 
AS FinalDate
GROUP BY CONVERT(DATETIME, FinalDate, 103) 
ORDER BY CONVERT(DATETIME, FinalDate, 103)

Hope this helps!

RBT
  • 24,161
  • 21
  • 159
  • 240
  • Thank you for your answer. FinalDate isn't a field within my table I was hoping to create a new column within my datatable which will be called FinalDate and will contain my IncDate and ExpDates combined. Not sure if it's possible! – Stuart Mar 07 '16 at 00:01
  • What do you mean by IncDate and ExpDates combined? Do you want to keep them as appended strings in a new column called FinalDate in your data table for every record? I'm really wondering what purpose would that solve even if you end up doing that. – RBT Mar 07 '16 at 00:10
  • I'm creating a dotnethighchart with it: http://stackoverflow.com/questions/35802637/dotnet-highchart-cost-not-being-plotted-against-the-correct-date-asp-net-c-sharp (this is my issue). And when I concat IncDate and ExpDate in that question, the dates are just being added together randomly, and not in the order I'm hoping for. Im not sure of the best way to go about solving this at all. Im at a crossroads – Stuart Mar 07 '16 at 00:16