Reputation: 4 Apprentice
- Active Members
- Active Posts:
- 88 (0.05 per day)
- 16-October 09
- Profile Views:
- Last Active:
- May 20 2014 07:22 AM
- OS Preference:
- Favorite Browser:
- Favorite Processor:
- Favorite Gaming Platform:
- Your Car:
- Dream Kudos:
26 Aug 2011 - 06:51
Posts I've Made
Posted 20 May 2014I thought of another way around this, but that didn't work either
The report database is refreshed overnight, and as long as there are no transactions over the weekend, I thought I could cast OrderDate as an integer, then return rows where there was a max value:
max(cast('OrderDate' as integer))
This didn't work though, as it complained about an aggregate not being permitted in a WHERE clause unless it is a subquery contained in a HAVING clause. Playing around with HAVING didn't get me anywhere either
Posted 13 Jan 2014Did you mean header status or item status? As, header status is either 'Active' or 'Inactive', and item status is 'Open' or 'Closed'?
Off home now, will pick this up tomorrow. Thanks again for your help!
Posted 13 Jan 2014Whoa, hold the phone!!
Flipping the 0 & 1 in the CASE WHEN seems to have done the trick! So:
select HeadGUID from Item group by HeadGUID having sum(case when Status='Closed' then 0 else 1 end)=0
Gonna check through the data, but we could be onto a winner!!
Thanks very much!!
Posted 13 Jan 2014Ok, if I lose the "dbo." at the start of the "join name", and also on the right side of the join, it returns data! But....it still looks like the wrong data (Foo, in this case) />
Posted 13 Jan 2014Doh, I was sure that would have cracked it, but now it throws a syntax error
QuoteLine 4: Incorrect syntax near '.'
Line 4 is the sub-select line....I'm going to play a bit more to see if it occurs as a select on its own
- Member Title:
- D.I.C Head
- 31 years old
- August 31, 1983
- Portsmouth, UK
- Programming Languages:
- Website URL:
chemicalfan hasn't added any friends yet.