I am in a very similar situation and I know that feel bro. We will go through times were we have sex once a month, maybe. Then other times, usually because of some incident, I will get laid a couple days in a row, but like you said, it putters out real fast. It's super annoying, especially as you said she promised you and has you anticipating it only to be disappointed and you can't sleep because your mind just races as it feels like you are unworthy and you just have a never ending boner. You are mad and feel so insignificant at the same time. Trying to have sex with a sleeping person makes you feel like a rapist, and the pity fucks you mentioned is not even worth it. I for one am turned on when a woman is into it not just "hurry up". I remember once she said that after I woke her up and I lost my erection and just stopped. I usually get it on my Bday but I probably have not got laid more on Valentine's days then I have, which is so cruel considering the things we do to show our affection. It seems we are left to instigate all sexual encounters, but if we actually tried to fuck every time we were horny we would never leave them alone.
There's really no easy answer. The one thing you have to do though is tell your wife how you feel. I know you have, but woman must here it over and over agin I think or they forget and become complacent or w/e. She needs to know that sex is probably the most important thing when it comes to a man's view on a relationship and that you want her to want you ya know. I told my girl, look it's unfair to expect me to stay faithful to you, when you don't want to have sex with me. If you're not interested in sex then let me know and we can go our separate ways as friends. That's not what I want, but I have desires...etc. I understand if your in pain and the last thing I want to do is hurt you (my wife has chronic pain too), in fact it's a major turn off for me, but there's more we can do then just intercourse. Give me a blowjob or a HJ or let me jerk off to you playing with yourself, I don't care. As long as your not getting sex from me, I have to wonder if you're getting it somewhere else. That will kind of talk will usually get ya a couple really good sessions but you just kinda got to stick to your guns about it sometimes.
Other things will get you in the panties too, like household chores, romantic gestures, any hotel stay is a given for me(If I'm paying for a hotel/vacation I will wake her ass up I don't care and neither does she), compliments, and just showing interest. But again, sometimes these fixes are short-lived and not reciprocated. The other thing, which is kind of fucked up, is that I can usually get laid before I leave if I'm going to be spending time away from my wife where I might come into contact with another woman (everytime you are not in your wife's view am I right?) it's like they didn't want anything to do with you before but now they will give you something to remember and mark their territory. I sometimes use this to my advantage. The good thing is my wife has always been fine with me watching porn because she knows that all men do it, and she would rather have me spanking it than roaming the city looking for the real thing. It's still a secretive act I do when the opportunity arises (LOL) but, if I ever got caught it would be okay, hell it might get me laid even. I actually remember my wife asking me about my porn one day, maybe I left the DVD in the player or something, and she said something to the effect of why were you watching porn. I said why do you want to have sex? She said No. And I said well then, there ya go.
Anyways, good luck my friend. Hopefully you can work it out because sometimes when you least expect it, you can change your sex life and have some incredible sex, which in turn gets you even more of it. The fire is rekindled and it all started with you standing up for yourself and expressing your needs/desires.