Bill,

You didn't tell what is wrong so I'll have to guess. I can see two potential problems in you query:
- Condition

date between '03/01/2003' and '03/19/2003 11:59:59 PM'

may miss some records because SQL Server stores datetime with 3 digits after decimal point (milliseconds). Try to change all date conditions to

date > = '03/01/2003' and date < '03/19/2003'

.
- You calculate store id but group by location_id. Try to change to

GROUP BY CASE WHEN LEFT(location_id, 1) = '0' THEN '5' + RIGHT(location_id, 2) ELSE location_id END

>I have a query that returns the data I need for a management report but it is in the wrong format. For example I am pulling for several accounts the quantity and amount for each store the way they want to see this data is with the individual stores on the x-axis and the accounts and their quantity and amounts across the y-axis. Obviously the quantity and amount are already columns so that is not an issue where the problem comes in is the account. Those are obviously separated in the row data but for a given store they want only one row with the accounts for that store going across the columns. To accomplish this task I came up with a query that uses case statements to determine which column a given data element should be accumulated in but it isn't getting the results I expected to see. Does anyone have any ideas on how I should be going about this? I will include a piece of the query as an example of what I did that isn't working. Any and all help will be greatly appreciated.

>SELECT store, day1_01030, day2_01030, day3_01030, day4_01030, day5_01030, day6_01030, day7_01030,
>	(day1_01030 + day2_01030 + day3_01030 + day4_01030 + day5_01030 + day6_01030 + day7_01030) / 6 avg01030,
>	day1_01046, day2_01046, day3_01046, day4_01046, day5_01046, day6_01046, day7_01046,
>	(day1_01046 + day2_01046 + day3_01046 + day4_01046 + day5_01046 + day6_01046 + day7_01046) / 6 avg01046,
>	day1_01045, day2_01045, day3_01045, day4_01045, day5_01045, day6_01045, day7_01045,
>	(day1_01045 + day2_01045 + day3_01045 + day4_01045 + day5_01045 + day6_01045 + day7_01045) / 6 avg01045
> FROM 	(
>	SELECT 	CASE WHEN LEFT(location_id, 1) = '0' THEN '5' + RIGHT(location_id, 2) ELSE location_id END AS store,
>		SUM(CASE WHEN account = '01030' AND date between '03/01/2003' and '03/19/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day1_01030,
>		SUM(CASE WHEN account = '01030' AND date between '03/01/2003' and '03/18/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day2_01030,
>		SUM(CASE WHEN account = '01030' AND date between '03/01/2003' and '03/17/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day3_01030,
>		SUM(CASE WHEN account = '01030' AND date between '03/01/2003' and '03/16/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day4_01030,
>		SUM(CASE WHEN account = '01030' AND date between '03/01/2003' and '03/15/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day5_01030,
>		SUM(CASE WHEN account = '01030' AND date between '03/01/2003' and '03/14/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day6_01030,
>		SUM(CASE WHEN account = '01030' AND date between '03/01/2003' and '03/13/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day7_01030,
>		SUM(CASE WHEN account = '01046' AND date between '03/01/2003' and '03/19/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day1_01046,
>		SUM(CASE WHEN account = '01046' AND date between '03/01/2003' and '03/18/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day2_01046,
>		SUM(CASE WHEN account = '01046' AND date between '03/01/2003' and '03/17/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day3_01046,
>		SUM(CASE WHEN account = '01046' AND date between '03/01/2003' and '03/16/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day4_01046,
>		SUM(CASE WHEN account = '01046' AND date between '03/01/2003' and '03/15/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day5_01046,
>		SUM(CASE WHEN account = '01046' AND date between '03/01/2003' and '03/14/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day6_01046,
>		SUM(CASE WHEN account = '01046' AND date between '03/01/2003' and '03/13/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day7_01046,
>		SUM(CASE WHEN account = '01045' AND date between '03/01/2003' and '03/19/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day1_01045,
>		SUM(CASE WHEN account = '01045' AND date between '03/01/2003' and '03/18/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day2_01045,
>		SUM(CASE WHEN account = '01045' AND date between '03/01/2003' and '03/17/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day3_01045,
>		SUM(CASE WHEN account = '01045' AND date between '03/01/2003' and '03/16/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day4_01045,
>		SUM(CASE WHEN account = '01045' AND date between '03/01/2003' and '03/15/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day5_01045,
>		SUM(CASE WHEN account = '01045' AND date between '03/01/2003' and '03/14/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day6_01045,
>		SUM(CASE WHEN account = '01045' AND date between '03/01/2003' and '03/13/2003 11:59:59 PM' THEN debit-credit ELSE 0 END) AS day7_01045
>	 FROM 	glhistory
>	 WHERE 	date between '03/01/2003' and '03/19/2003 11:59:59 PM'
>	  AND 	account in('01030','01046','01045','01040','05045','05046','05050')
>	 GROUP BY location_id
>	) xy
> ORDER BY store