开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜