The more I dig into SQL Server 2005, the more I like it. There is so much good stuff there from the developer point of view. I’m not a DBA, so I can’t talk about the administrative things that they have done, but the abilities of T-SQL and the things that you can do with it are just amazing.

This (64 lines) statements create a summary of how much I spent in each month in the year (including past months), and it actually made me stare in awe at the screen, proud of my accomplishment (which replace a three hundred lines of cursor based script that did some things that were Not Nice^TM to the database.

 

Am I made to thing that this is pretty?

 

INSERT INTO PaymentsByMonth Aggregated

(

            [YEAR],

            [January],

            [Febuary],

            [March],

            [April],

            [May],

            [June],

            [July],

            [August],

            [September],

            [October],

            [November],

            [December]

)

SELECT

 

            Year,

            Jan,

            Jan + Feb,

            Jan + Feb + Mar,

            Jan + Feb + Mar + Apr,

            Jan + Feb + Mar + Apr + May,

            Jan + Feb + Mar + Apr + May + Jun,

            Jan + Feb + Mar + Apr + May + Jun + Jul,

            Jan + Feb + Mar + Apr + May + Jun + Jul + Aug,

            Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep,

            Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct,

            Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov,

            Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec

FROM

(

            SELECT

                        [Year],

                        [1] as Jan,

                        [2] as Feb,

                        [3] as Mar,

                        [4] as Apr,

                        [5] as May,

                        [6] as Jun,

                        [7] as Jul,

                        [8] as Aug,

                        [9] as Sep,

                        [10] as Oct,

                        [11] as Nov,

                        [12] as Dec

            FROM

            (

                        SELECT

                                    DATEPART(year, date) as  [Year],

                                    DATEPART(month, date) as [Month],

                                    Amount

                        FROM Payments

            ) Source

            PIVOT

            (

                        sum( Amount )

                        FOR [Month] IN

                        (

                                    [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

                        )

            ) as Pvt

) Pivoted