Multi-part Select with qualifiers
I am a newbie to SQL and I am trying to perform a select with the following code: I don't know what the Improper Syntax error I am getting is. SELECT * From ProductionBatch , ProductionBatch AS PB Where PB.StartDateTime BETWEEN '2006-08-01' and '2006-08-30' Group BY PB.FactoryID, PB.ItemID, PB.QuantityProduced, PB.QualityRating, PB.EstEndTime, PB.ActualEndTime, PB.ProductionCost, PB.CostCurrency, PB.CostExchangeDate, PB.OrderID, PB.StartDateTime Having (PB.StartDateTime >= '2006-08-01') PB.StartDateTime = DATEPART(dd, PB.StartDateTime) PB.EstEndTime = DATEPART(dd, PB.EstEndTime) PB.ActualEndTime = DATEPART(dd, PB.ActualEndTime) PB.CostExchangeDate = DATEPART(dd, PB.CostExchangeDate) CAST '$' + (Convert(int,ProductionCost,0) AS VARCHAR)AS ProductionCost ORDER BY ProductionCost DESC Micah
November 10th, 2010 10:16pm

Hello Micah, I don't know where to start ... SELECT * and a GROUP BY, without any aggregation; that's not good. HAVING is used with aggregated values and it's not meant for filtering on field values. The rest after HAVING is incoherent. Comparing a date with a part of a date wouldn't return any result. I suggest you describe what you want to select and we give you some hints how to solve it. SELECT * From ProductionBatch , ProductionBatch AS PB Where PB.StartDateTime BETWEEN '2006-08-01' and '2006-08-30' Group BY PB.FactoryID, PB.ItemID, PB.QuantityProduced, PB.QualityRating, PB.EstEndTime, PB.ActualEndTime, PB.ProductionCost, PB.CostCurrency, PB.CostExchangeDate, PB.OrderID, PB.StartDateTime Having (PB.StartDateTime >= '2006-08-01') PB.StartDateTime = DATEPART(dd, PB.StartDateTime) PB.EstEndTime = DATEPART(dd, PB.EstEndTime) PB.ActualEndTime = DATEPART(dd, PB.ActualEndTime) PB.CostExchangeDate = DATEPART(dd, PB.CostExchangeDate) CAST '$' + (Convert(int,ProductionCost,0) AS VARCHAR)AS ProductionCost ORDER BY ProductionCost DESC Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich tglich http://olafhelper.over-blog.de
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 1:21am

What I was trying to accomplish was to select a specific month from the Production Batch table to build the report around and then change all of the dates from yy/mm/dd/hh/mm/ss/ms to just days. I only wanted to display the rows with the Start Date of August 2006. and Group them by row. However when I tried to do this without specifically naming the rows I got an error stating that FactoryID was not a valid column to group by. The Having statement I am trying to extract the day from the DATETIME using the DATEPART function. Then I was trying to display the Production Cost with a '$' and no decima points.Micah
November 12th, 2010 8:35am

Ok, that' a bit more clearer. Let's solve it step by step, here the first statement with day and cost as char without decimal + $. But if you "group by row" you get rows, no groups. Do you want to aggregate some values, e.g. values per day => SUM(costs) or something else? SELECT PB.FactoryID, PB.ItemID, PB.QuantityProduced, PB.QualityRating, PB.EstEndTime, PB.ActualEndTime, PB.CostCurrency, PB.CostExchangeDate, PB.OrderID, DATEPART(dd, PB.StartDateTime) AS StartDay, '$' + CONVERT(varchar(30), CONVERT(int, PB.ProductionCost)) AS ProdCost From ProductionBatch AS PB Where PB.StartDateTime BETWEEN {d '2006-08-01'} and {d '2006-08-30'} Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 9:03am

Yes, I left out that the rows are supposed to be sorted by the Day and by the Production Cost, before they are grouped. I realize that I have left this completely out of my query statement.Micah
November 12th, 2010 9:25am

Sorry, I don't get what you mean / want to get? Only the total cost per day? SELECT DATEPART(dd, PB.StartDateTime) AS StartDay, '$' + CONVERT(varchar(30) ,CONVERT(int, SUM(PB.ProductionCost))) AS ProdCost From ProductionBatch AS PB Where PB.StartDateTime BETWEEN {d '2006-08-01'} and {d '2006-08-30'} GROUP BY DATEPART(dd, PB.StartDateTime)Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich tglich http://olafhelper.over-blog.de
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 12:21pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics