0

I have a query which is pretty simple in that it just gets records from a table, the output is like this:

ID      group   StatusType      Task                DateAdministered        ScoreA  ScoreB
5199    MSP     Completed   Follow Up Contact   2011-04-01 00:00:00.000         2       5
5199    MSP     Completed   Follow Up Contact   2011-06-02 00:00:00.000         2       7
5199    MSP     Completed   Follow Up Contact   2011-06-02 00:00:00.000         2       8
5199    MSP     Completed   Follow Up Contact   2011-06-02 00:00:00.000         2       7
5199    MSP     Completed   Follow Up Contact   2011-04-28 00:00:00.000         4       8
5199    MSP     Completed   Follow Up Contact   2011-06-02 00:00:00.000         2       7
5199    MSP     Completed   Follow Up Contact   2011-05-19 00:00:00.000         2       7
5199    MSP     Completed   Follow Up Contact   2011-05-26 00:00:00.000         3       7
5199    MSP     Completed   Follow Up Contact   2011-06-02 00:00:00.000         3       7
5199    MSP     Completed   Follow Up Contact   2011-06-09 00:00:00.000         1       6
5199    MSP     Completed   Follow Up Contact   2011-06-15 00:00:00.000         3       5
5199    MSP     Completed   Follow Up Contact   2011-06-23 00:00:00.000         2       6
5199    MSP     Completed   Follow Up Contact   2011-06-30 00:00:00.000         1       6
5199    MSP     Completed   Follow Up Contact   2011-07-07 00:00:00.000         4       6
5199    MSP     Completed   Follow Up Contact   2011-07-14 00:00:00.000         3       6
5199    MSP     Completed   Follow Up Contact   2011-07-21 00:00:00.000         2       7

What I need to do is re-organize the data into columns and not rows based on the date closest to the end of the month for a particular year of the DateAdministered for each ID, group, StatusType, and Task. In other words, if there are two rows for a task in April, I will need the max date of those two and the report on ScoreA and ScoreB. The do the same for May, June, etc...

The end result should look like this:

ID      group       StatusType      Task            April Assessment    ScoreA  ScoreB  May Assessment  ScoreA  ScoreB  June Assessment ScoreA  ScoreB  July Assessment ScoreA  ScoreB
5199    MSP         Completed   Follow Up Contact       4/1/2011            2       5   5/26/2011           3       7   6/30/2011           1       6   7/21/2011           2       7

I have tried several things and I just am not sure of the best way to do this. This almost seems like something for a pivot but don't need to SUM or ADD any columns, so I am not sure how that would work if I need a pivot.

Baub
  • 723
  • 4
  • 21
  • 36
  • It looks like you want the row closest to the beginning of the month, not the end. – acfrancis Nov 07 '13 at 00:17
  • Or did you just miss the row with ``2011-04-28``? – acfrancis Nov 07 '13 at 00:19
  • SQL, by itself, is not meant to allow the kind of dynamic column generation as you are looking for. You mentioned pivots, if you are using a spreadsheet to display you have may have to use them. If not it will depend on what type of GUI you are using to display this. – Jim Sosa Nov 07 '13 at 00:19
  • Unless you want to hardcode all the month column names, you will need a dynamic SQL statement. Look at this question for more information: http://stackoverflow.com/questions/11985796/sql-server-pivot-dynamic-columns-no-aggregation – acfrancis Nov 07 '13 at 12:24
  • Thanks for the replies, I feel better knowing that I didn't miss something and this can't easily be done. Ultimately this data will be sent in an excel spreadsheet but I was trying to get a query made and run it, then just save the results in excel. acfrancis - yes, I missed that month. I want the last one in each month. – Baub Nov 07 '13 at 15:32

0 Answers0