searched CASE in SELECT - using MS Query on MSSQL2000 1

[Ach005ki]

Hello all
I have a problem that I can't get around.

I have limited access to our database. I can only use the query tool MS Query to interrogate our data tables, as the results are usually returned to Excel spreadsheets.
I've asked for other query and reporting tools but in the meantime I'm stuck with this.

I don't know if my problem is borne purely of my limitations, those of MS Query or a combination.

I'm trying to return summary sales demand for given periods by items and returning the results grouped by item and period.

I thought a piece of code along the lines of the below would help but MS Query doesn't seem to like a searched CASE statement (I've used the simple CASE statements before without problem).

Any assistance would be very much appreciated.

[red] Didn't expect 'SorMaster' after the SELECT column list[/red]

SELECT 
  SorDetail.MStockCode, 
  Sum(SorDetail.MOrderQty) AS 'Sum of MOrderQty',
  
[highlight]  Mnth = 
  CASE
	 WHEN SorMaster.OrderDate BETWEEN {ts '2007-03-01 00:00:00'} And {ts '2007-03-31 00:00:00'}
		THEN 'Mar'
	 WHEN SorMaster.OrderDate BETWEEN {ts '2007-04-01 00:00:00'} And {ts '2007-04-30 00:00:00'}
		THEN 'Apr'
	 WHEN SorMaster.OrderDate BETWEEN {ts '2007-05-01 00:00:00'} And {ts '2007-05-31 00:00:00'}
		THEN 'May'
	 WHEN SorMaster.OrderDate BETWEEN {ts '2007-06-01 00:00:00'} And {ts '2007-06-30 00:00:00'}
		THEN 'Jun'
	 ELSE 'XXX'
  END[/highlight]
  
FROM 
  SysproCompanyH.dbo.SorDetail SorDetail
  INNER JOIN SysproCompanyH.dbo.SorMaster SorMaster
  ON SorDetail.SalesOrder = SorMaster.SalesOrder

WHERE  
  (SorDetail.MWarehouse='BM') 
  AND (SorMaster.OrderStatus Not In ('*','\') 
  AND (SorMaster.OrderDate Between {ts '2007-03-01 00:00:00'} And {ts '2007-06-10 00:00:00'}))

GROUP BY 
  SorDetail.MStockCode, Mnth

ORDER BY 
  SorDetail.MStockCode

Mark, HH Associates

 

[r937]

is SorMaster.OrderDate populated with date and time values? if so, any reason why you want to assign rows for the last day of each month to 'XXX'?

otherwise, have you thought about

SELECT ...
, month(SorMaster.OrderDate) as Mnth
...


r937.com | rudy.ca

 

[Ach005ki]

OMG - Mental block!

Sorry - wife is in Gibraltar as part of her elective placement midwifery final, I have our youngest child and it was a baking Sunday afternoon... poor child, poor me, lol!

[blue]Rudy, thank you very much - can't think why I missed this but I'm glad you were here to put me straight.[/blue]

SELECT 
  SorDetail.MStockCode, 
  Sum(SorDetail.MOrderQty) AS 'Sum of MOrderQty',
  DATENAME(Month, SorMaster.OrderDate) AS 'Mon'
  
FROM 
  SysproCompanyH.dbo.SorDetail SorDetail
  INNER JOIN SysproCompanyH.dbo.SorMaster SorMaster
  ON SorDetail.SalesOrder = SorMaster.SalesOrder

WHERE  
  (SorDetail.MWarehouse='BM') 
  AND (SorMaster.OrderStatus Not In ('*','\') 
  AND (SorMaster.OrderDate Between {ts '2007-03-01 00:00:00'} And {ts '2007-06-10 00:00:00'}))

GROUP BY 
  SorDetail.MStockCode, DATENAME(Month, SorMaster.OrderDate)

ORDER BY 
  SorDetail.MStockCode

Just have one more question - sometimes MS Query will accept my column name aliases and sometimes it won't and I have noooo idea why - its not like I use different ODBCs.

Anyone else ever experienced this / similar problems? In order to get MSQ to return column labels, I had to place the query in a syntax which allowed it a graphical perspective but (for example) negates the use of "JOIN" in the SQL?

SELECT 
  SorDetail.MStockCode, 
  Sum(SorDetail.MOrderQty) AS 'Sum of MOrderQty', 
  DATENAME(Month,SorMaster.OrderDate) AS 'Mon'
FROM 
  SysproCompanyH.dbo.SorDetail SorDetail,
  SysproCompanyH.dbo.SorMaster SorMaster
WHERE 
  SorMaster.SalesOrder = SorDetail.SalesOrder AND
  (
    (SorDetail.MWarehouse='BM') AND (DATEPART(m,SorMaster.OrderDate) Between 3 And 6)
  )
GROUP BY 
  SorDetail.MStockCode, 
  DATENAME(Month,SorMaster.OrderDate)
ORDER BY 
  SorDetail.MStockCode

Mark, HH Associates