I finished the campaign in Halo 2 for the first time playing coop with my friend, and we couldn't believe it ended that way after we were so hyped for it.
I still remember the day we went to pick up Halo 3. My same friend and I had both preordered it but in stores 45 minutes apart. We decided to drive to one midnight launch and then race over to the other, but that night it rained harder than I'd ever seem it rain before.
If that wasn't bad enough, his fuse for his windscreen wipers had blown the day before and we had to drive with our heads out the window. We could see maybe one car space ahead of us and we were driving at 10 miles an hour. Even then we still almost died a few times.
We both got home safe with out legendary editions though and quickly started the game. Finished it in a single sitting and we both agreed it sucked. Not only was the single player a let down with maybe the worst ratio of good to bad levels in a Halo campaign, but it also looked really low res compared to what we were expecting from the 360's biggest title.
I grew to appreciate the well balanced multiplayer though, so it wasn't a total failure, but it was a while before I allowed myself to get hyped for a game again. That game next game was MGS4 (it was out ages before I got around to playing it).
...I was not impressed.
And after that I really haven't been hyped for a game since. I'll look forward to certain titles, but I know not to get my hopes up now.
Oh, and off topic but it goes to show you how bad I am at picking what to hype up, but the most hyped I've ever been for something was The Matrix Reloaded. I was so hyped for that I started to worry I would die before ever seeing it, and was so concerned that I caught a bus into the cinemas to see it figuring I'd be more likely to survive a bus crash than a car crash.
I watched that abomination at the cinemas around a dozen times in the hopes that it would grow on me. It never did.
GTAIV was tough. I was going to midnight launch and knew so two weeks out and man the days, hours, minutes were painful. It turned out to be a blast because the other people in line were cool and the cops showed up just to provide security.
The Queen of Light took her bow, and then she turned to go. The Prince of Peace embraced the gloom, and walked the night alone.-Battle of Evermore/Zeppelin
I think for me the worst wait for something gaming-related was the GameCube. I was 9 years old (holy cow) and begged my parents to get me one for Christmas. They didn't.....but Santa came through for me :)
That was the worst x-mas wait of all time.
As far as games. The worst wait was probably Halo 3, followed by Pokemon Crystal and Smash Bros Brawl.