>I need to create a query that will provide 12 months of data that can be dropped into a spreadsheet via _vfp.dataclip(,,3) function. The results should look something like:
>
> JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
>ITEM1 12 13 9 12 6 21 13 15 12 16 9 12
>ITEM2 9 10 10 14 10 17 15 16 17 17 12 18
>ITEM3 11 17 16 16 16 23 12 14 16 18 20 17
>ETC ..
>
>The only way I know to acquire this data is to run a SQL query with a monthly date range 12 times. I know there must be a way to write a single SQL statement that would pull all of this data at once but I'm not sure how to construct it.
>
>Anyone available to give me an example of how to do this?
>
>Thanks,
>Robert Wright
This can be done in a single command, more or less like this:
select ItemName,;
sum(iif(month(DateField) = 1, ValueField, $0)) as Jan,;
sum(iif(month(DateField) = 2, ValueField, $0)) as Feb,;
...
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)