In Atlantis, Steven Caldwell would only have reported to Elizabeth Weir and would have been the highest ranking military officer. He would have been solely responsible for the military forces in the Pegasus galaxy. If ever called back to Earth, that kind of remote command experience would almost certainly have resulted in a promotion (to brigadier general, like O'Neill).
Meanwhile, he's captain of the Daedalus. That had him on Earth every two weeks or so. Therefore, he was continuously receiving updated orders. Further, most of the work of the Daedalus was boring. He flew from the Earth to Atlantis and back. There wasn't sufficient autonomy or action to lead to promotion. And since Earth had so few starships, it was unlikely that he was going to be put in command of a group.
If he was angling for promotion, he needed to be in one of two places. Either on Atlantis and in charge, or he needed to be back on Earth where there were higher command slots. He may also have been angling for an Earth spot. Of course we wouldn't see that from Atlantis.
His role was probably rather frustrating. When he was at Atlantis, he was the highest ranking officer there. He was a full-bird colonel while John Sheppard was a major and then a lieutenant colonel. If something went wrong in Atlantis while Caldwell was there, he would catch some of the blame. But he had very little ability to make things go right in Atlantis. Sheppard was in charge, and Weir was actively blocking Caldwell from making changes.
Another route to promotion would be for the Atlantis Expedition to grow. That didn't happen in the show, but it could have. Particularly if Caldwell was the one choosing whether or not to ask for reinforcements. He could have made the military presence large enough to require a brigadier general and acted in that slot. A high chance then of being promoted to fill the slot that he was holding. Note how Sheppard was promoted to lieutenant colonel for much the same reason.
