How to limit event type to one per day
I have a query like so.. it's not working, though, I'm getting a syntax error on the WHERE NOT EXISTS bit. However I'm not sure it would work the way I'd want it to anyway.. What I want to do is make it so there can only be one 'B' in any day, and it will be the first instance.. but I'm not sure if the below code worked, that anything would be 'B' until the very end anyway.
UPDATE FoodIntake
SET MealTypeCode = CASE
WHEN substr(DateTime, -8)
BETWEEN (SELECT BreakfastStart FROM Patient WHERE PatientId = :PatientId)
AND (SELECT LunchStart FROM Patient WHERE PatientId = :PatientId)
AND CarbAmount >= 25
AND MealTypeCode IS NULL
WHERE NOT EXISTS (
SELECT * FROM FoodIntake as Old
WHERE MealTypeCode = 'B'
AND substr(Old.DateTime, 10) = substr(FoodIntake.DateTime, 10)
)
THEN 'B'
END
The other option, that I'm not sure how to approach would be to tag everything that applies as B, then do a second query to find any time there are 2 Bs in one day and rename the second one as something else.
I have a query I tried for that but I know it's got some major problems.. there's some syntax errors I can't quite figure out, but I开发者_如何学编程 think it at least gets across what I want to do.
UPDATE FoodIntake
SET MealTypeCode = 'U'
WHERE MealTypeCode = 'B'
AND count(SELECT 1 FROM FoodIntake as Old WHERE MealTypeCode ='B' AND substr(Old.DateTime, 10) = substr(FoodIntake.DateTime, 10) ) > 1
AND FoodIntake.DateTime > (SELECT DateTime FROM FoodIntake as Old WHERE MealTypeCode ='B' AND substr(Old.DateTime, 10) = substr(FoodIntake.DateTime, 10) ORDER By DateTime ASC LIMIT = count-1)
I think you'd be better off using an IF ... ELSE
statement for this, which while perhaps more verbose will more explicitly state the logic occurring here.
IF (SELECT COUNT(1) FROM FoodIntake
WHERE (date('now') BETWEEN date('now','start of day')
AND date('now','start of day','+1 day') )
AND MealTypeCode = 'B') > 0
-- Make update based on 'B' code already taken
ELSE
-- Make update based on 'B' code not yet taken
I am not a SQLite guy, but I am good with mySQL and T-SQL, so if the date functions are inaccurate, I was referencing this article: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
精彩评论