Summarizing data by month/year

I'm using VS2008 using vb.
I need to summarize data.  Currently the data may not contain every month.  I need to show every month and if the current month is not in the data, it needs to get the last existing months value.
Also, the Stock_Number needs  a value even if not in the table.
There are 9 Stock_Numbers:

091
092
093
094
095
096
097
098
099

These need to have a record for each month, even if they are not in the table.

Using Table: "FuelCost"
Columns:  Stock_Number, Quantity, Cost, Extended_Cost, Month(Integer), Year, Fuel_Type

Stock_Number   Quantity    Cost   Extended_Cost   Month   Year   Fuel_Type
 091                      3000       2.13        6390                   1       2008      Diesel
 091                      4000       2.15        8600                   1       2008      Diesel
 091                      2000       1.99        3980                   1       2008      Diesel
 091                      500         2.17        1085                   3       2008      Diesel
 092                      4500       2.10        9450                   6       2008      Unleaded
 093                      1500       2.11        3165                   2       2008      30WT

Outcome needed:
I currently have a table with columns named below:  "FuelCostWeightedAVGByMonth"

Jan_Cost      Jan_QTY   Jan_WeightAVG    Feb_Cost   Feb_QTY   Feb_WeightAVG              
18970           9000          2.1077                    0                0                0
0                   0                0                             0                0                2.1077          
0                   0                0                             0                0                0
0                   0                0                             0                0                0
0                   0                0                             0                0                0
0                   0                0                             0                0                0        
Can't fit it all across...continued

Mar_Cost      Mar_QTY  Mar_WeightAVG    Apr_Cost   Apr_QTY  Apr_WeightAVG
0                   0                0                             0                0                0
0                   0                0                             0                0                0
1085             500            2.17                        0                0                0
0                   0                0                             0                0                2.17
0                   0                0                             0                0                0
0                   0                0                             0                0                0

Cont...

May_Cost     May_QTY  May_WeightAVG   Jun_Cost   Jun_QTY  Jun_WeightAVG
0                   0                0                             0                0                0
0                   0                0                             0                0                0
0                   0                0                             0                0                0
0                   0                0                             0                0                0
0                   0                2.17                        0                0                0
0                   0                0                             9450          4500          2.10


Etc...through December
Cont...columns across
Stock_Number  Month   Year   Fuel_Type
091                    1          2008   Diesel
091                    2          2008   Diesel
091                    3          2008   Diesel
091                    4          2008   Diesel
091                    5          2008   Diesel
092                    6          2008   Unleaded

Need summary for each month and year, by stock_number.

This is very complex to me and I have been trying to figure this out for several days and can't grasp it.

This table is needed to be used in a Grid eventually.  So, I need it broke down close to the above layout.

I don't want to rebuild it everytime the user views it.  I would like for it to refresh the last two months of data only.

Thanks in advance!

Wish I could give 1,000,000 points for this...


The code below is what I currently have, but it only pulls in existing data.  It empties the table and then rebuilds it.  I don't want to do that...would like to update the last 2 months.
QueryString = "DELETE FROM FuelCostWeightedAVGByMonth;"
Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)


QueryString = " INSERT INTO FuelCostWeightedAVGByMonth ( Stock_Number, Year, Month, Type, January_Weighted_AVG, January_Cost, January_Quantity, February_Weighted_AVG, February_Cost, February_Quantity,  March_Weighted_AVG, March_Cost, March_Quantity, April_Weighted_AVG, April_Cost, April_Quantity, May_Weighted_AVG, May_Cost, May_Quantity, June_Weighted_AVG, June_Cost, June_Quantity, July_Weighted_AVG, July_Cost, July_Quantity, August_Weighted_AVG, August_Cost, August_Quantity, September_Weighted_AVG, September_Cost, September_Quantity, October_Weighted_AVG, October_Cost, October_Quantity, November_Weighted_AVG, November_Cost, November_Quantity, December_Weighted_AVG, December_Cost, December_Quantity ) SELECT Stock_Number, YEAR(Date), Month(Date),  Fuel_Type, CASE WHEN MONTH(date) = 1 THEN Round(SUM(extended_cost) / SUM(Quantity),4) ELSE 0 END AS January_Weighted_AVG, CASE WHEN MONTH(date) = 1 THEN SUM(Extended_cost) ELSE 0 END AS January_Cost, CASE WHEN MONTH(date) = 1 THEN SUM(quantity) ELSE 0 END AS January_Quantity, CASE WHEN MONTH(date) = 2  THEN Round(SUM(extended_cost) / SUM(Quantity),4)  ELSE 0 END AS February_Weighted_AVG, CASE WHEN MONTH(date) = 2 THEN SUM(Extended_cost) ELSE 0 END AS February_Cost, CASE WHEN MONTH(date) = 2 THEN SUM(quantity) ELSE 0 END AS February_Quantity, CASE WHEN MONTH(date) = 3  THEN Round(SUM(extended_cost) / SUM(Quantity),4)  ELSE 0 END AS March_Weighted_AVG, CASE WHEN MONTH(date) = 3 THEN SUM(Extended_cost) ELSE 0 END AS March_Cost, CASE WHEN MONTH(date) = 3 THEN SUM(quantity) ELSE 0 END AS March_Quantity, CASE WHEN MONTH(date) = 4 THEN Round(SUM(extended_cost) / SUM(Quantity),4)  ELSE 0 END AS April_Weighted_AVG, CASE WHEN MONTH(date) = 4 THEN SUM(Extended_cost) ELSE 0 END AS April_Cost, CASE WHEN MONTH(date) = 4 THEN SUM(quantity) ELSE 0 END AS April_Quantity, CASE WHEN MONTH(date) = 5  THEN Round(SUM(extended_cost) / SUM(Quantity),4) ELSE 0 END AS May_Weighted_AVG, CASE WHEN MONTH(date) = 5 THEN SUM(Extended_cost) ELSE 0 END AS May_Cost, CASE WHEN MONTH(date) = 5 THEN SUM(quantity) ELSE 0 END AS May_Quantity, CASE WHEN MONTH(date) = 6  THEN Round(SUM(extended_cost) / SUM(Quantity),4) ELSE 0 END AS June_Weighted_AVG, CASE WHEN MONTH(date) = 6 THEN SUM(Extended_cost) ELSE 0 END AS June_Cost, CASE WHEN MONTH(date) = 6 THEN SUM(quantity) ELSE 0 END AS June_Quantity, CASE WHEN MONTH(date) = 7  THEN Round(SUM(extended_cost) / SUM(Quantity),4)  ELSE 0 END AS July_Weighted_AVG, CASE WHEN MONTH(date) = 7 THEN SUM(Extended_cost) ELSE 0 END AS July_Cost, CASE WHEN MONTH(date) = 7 THEN SUM(quantity) ELSE 0 END AS July_Quantity, CASE WHEN MONTH(date) = 8  THEN Round(SUM(extended_cost) / SUM(Quantity),4) ELSE 0 END AS August_Weighted_AVG, CASE WHEN MONTH(date) = 8 THEN SUM(Extended_cost) ELSE 0 END AS August_Cost, CASE WHEN MONTH(date) = 8 THEN SUM(quantity) ELSE 0 END AS August_Quantity, CASE WHEN MONTH(date) = 9  THEN Round(SUM(extended_cost) / SUM(Quantity),4)  ELSE 0 END AS September_Weighted_AVG, CASE WHEN MONTH(date) = 9 THEN SUM(Extended_cost) ELSE 0 END AS September_Cost, CASE WHEN MONTH(date) = 9 THEN SUM(quantity) ELSE 0 END AS September_Quantity, CASE WHEN MONTH(date) = 10  THEN Round(SUM(extended_cost) / SUM(Quantity),4)  ELSE 0 END AS October_Weighted_AVG, CASE WHEN MONTH(date) = 10 THEN SUM(Extended_cost) ELSE 0 END AS October_Cost, CASE WHEN MONTH(date) = 10 THEN SUM(quantity) ELSE 0 END AS October_Quantity, CASE WHEN MONTH(date) = 11  THEN Round(SUM(extended_cost) / SUM(Quantity),4)  ELSE 0 END AS November_Weighted_AVG, CASE WHEN MONTH(date) = 11 THEN SUM(Extended_cost) ELSE 0 END AS November_Cost, CASE WHEN MONTH(date) = 11 THEN SUM(quantity) ELSE 0 END AS November_Quantity, CASE WHEN MONTH(date) = 12  THEN Round(SUM(extended_cost) / SUM(Quantity),4)  ELSE 0 END AS December_Weighted_AVG, CASE WHEN MONTH(date) = 12 THEN SUM(Extended_cost) ELSE 0 END AS December_Cost, CASE WHEN MONTH(date) = 12 THEN SUM(quantity) ELSE 0 END AS December_Quantity FROM FuelCost GROUP BY YEAR(Date), Fuel_Type, MONTH(Date), Stock_Number;"
Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)

Open in new window

TanyaDHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
What about like this?
 

select	Stock_number,
	max([1_2008_Cost]) as [Jan_2008_cost],
	max([2_2008_Cost]) as [Feb_2008_cost],
	max([3_2008_Cost]) as [Mar_2008_cost],
	max([4_2008_Cost]) as [Apr_2008_cost],
	...
	max([1_2008_Qty]) as [Jan_2008_Qty],
	max([2_2008_Qty]) as [Feb_2008_Qty],
	max([3_2008_Qty]) as [Mar_2008_Qty],
	max([4_2008_Qty]) as [Apr_2008_Qty],
	...
	max([1_2008_wavg]) as [Jan_2008_wavg],
	max([2_2008_wavg]) as [Feb_2008_wavg],
	max([3_2008_wavg]) as [Mar_2008_wavg],
	max([4_2008_wavg]) as [Apr_2008_wavg],
	... and so on...

from (
	select 	a.Stock_number, 
		isnull(b.Quantity,0) as Quantity, 
		isnull(b.Cost,0) as Cost, 
		isnull(b.Extended_Cost,0) as Extended_Cost, 
		avg(b.Cost) over (partition by a.Stock_number) as wavg,
		cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Cost' as monthyear_cost,
		cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Qty' as monthyear_qty,
		cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Wavg' as monthyear_wavg
	from stocktable a
	left join fuelcost b on a.Stock_number = b.Stock_number
	) o

pivot(sum(Extended_cost) for monthyear_cost in ([1_2008_Cost], [2_2008_Cost], [3_2008_Cost], .... and so on till ... [12_2008_Cost])) p1
pivot(sum(Quantity) for monthyear_qty in ([1_2008_Qty], [2_2008_Qty], [3_2008_Qty], .... and so on till ... [12_2008_Qty])) p2
pivot(max(wavg) for monthyear_wavg in ([1_2008_Wavg], [2_2008_Wavg], [3_2008_Wavg], .... and so on till ... [12_2008_Wavg])) p3
group by Stock_number

Open in new window

0
TanyaDHAuthor Commented:
Thank you for the quick response.
I'm not sure I'm following the first section...

Select Stock_Number,
 max([1_2008_Cost]) as [Jan_2008_cost],
        max([2_2008_Cost]) as [Feb_2008_cost],
        max([3_2008_Cost]) as [Mar_2008_cost],
        max([4_2008_Cost]) as [Apr_2008_cost],
        ...
        max([1_2008_Qty]) as [Jan_2008_Qty],
        max([2_2008_Qty]) as [Feb_2008_Qty],
        max([3_2008_Qty]) as [Mar_2008_Qty],
        max([4_2008_Qty]) as [Apr_2008_Qty],
        ...
        max([1_2008_wavg]) as [Jan_2008_wavg],
        max([2_2008_wavg]) as [Feb_2008_wavg],
        max([3_2008_wavg]) as [Mar_2008_wavg],
        max([4_2008_wavg]) as [Apr_2008_wavg],
        ... and so on...

The table that I am getting the data from, FuelCost, has several years of records.  2008 - 2010...so far and will continue to grow.  I can't see creating a column for each year, then each month for each cost, qty and avg.  
Do you have something a little less complex?
0
ralmadaCommented:
>>2008 - 2010...so far and will continue to grow<<
Do you intend to prepare a report with 2+ years of data? That doesn't sound very easy to read. You should consider limiting it to 1 year or so.
>>I can't see creating a column for each year, then each month for each cost, qty and avg.  <<
You will have to do so, because you're trying to do this with your query and not with a reporting tool.
There's nothing less complex, actually you can make it more user friendly so you don't have to create a column for each month by using dynamic SQL there, but it won't get any simple.

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TanyaDHAuthor Commented:
What I need is a history table with the summaries as above.  When the user keys in a date range (by month range) it will look the data within the date range, refresh  the data for the current and previous month and open a report (monthly for the current month usually).  If they wanted to look at data from 4 months back, they should be able to that also.
This table will be previewed in a grid to show the user history for 2008, 2009 and 2010 and any other years as time goes on.  They can compare fuel cost from month to month and year to year.

Could it be possible to use the code you provided, but instead of locking it into YEAR naming, like 1_2008_cost, maybe do 1_Year_Cost (letting the word Year be a variable that is assigned when the user enters a date)?  Guess that would be dynamic?
0
ralmadaCommented:
yep, you will have to go with dynamic SQL. So you will need to pass a @startdate and @enddate variable and use something like this:
declare @strSQL varchar(max)
declare @cols_cost varchar(max)
declare @cols_qty varchar(max)
declare @cols_wavg varchar(max)
declare @mcols_cost varchar(max)
declare @mcols_qty varchar(max)
declare @mcols_wavg varchar(max)

declare @startdate datetime
declare @enddate datetime

;with CTE as (
	select @startdate as sdate
	union all
	select dateadd(m, 1, sdate) from CTE
	where sdate <= @enddate
)
select 	@cols_cost = suff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost'
			from CTE for xml path('')),1, 2, '') + ']',

	@cols_qty = suff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty'
			from CTE for xml path('')), 1, 2, '') + ']',

	@cols_wavg = suff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg'
			from CTE for xml path('')), 1, 2, '') + ']',

	@mcols_cost = suff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost'
			from CTE for xml path('')), 1, 3,'') + '])',

	@mcols_qty = suff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty'
			from CTE for xml path('')), 1, 3,'') + '])',

	@mcols_wavg = suff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg'
			from CTE for xml path('')), 1, 3,'') + '])'



set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg
+ ' from ( 
        select  a.Stock_number,  
                isnull(b.Quantity,0) as Quantity,  
                isnull(b.Cost,0) as Cost,  
                isnull(b.Extended_Cost,0) as Extended_Cost,  
                avg(b.Cost) over (partition by a.Stock_number) as wavg, 
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost, 
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty, 
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg 
        from stocktable a 
        left join fuelcost b on a.Stock_number = b.Stock_number 
	' where cast((''01/''+cast(b.[month] as varchar) +''/'' + cast(b.[year] as varchar)) as datetime) between ' + @startdate + ' and ' + @enddate +
        ) o 

pivot(sum(Extended_cost) for monthyear_cost in (' + @col_cost + ')) p1 
pivot(sum(Quantity) for monthyear_qty in (' + @col_qty + ')) p2
pivot(max(wavg) for monthyear_wavg in (' + @col_wavg + ')) p3 
group by Stock_number'

exec(@strSQL)

Open in new window

0
TanyaDHAuthor Commented:
Is this in VB?  That is what I am using and this code looks way different than what I've been using.  Sorry!
0
ralmadaCommented:
Nope. This is SQL Server.
0
ralmadaCommented:
but you can still put this code in a stored procedure and call it from your VB code.
0
TanyaDHAuthor Commented:
Okay...I will be able to try it out tomorrow and will let you know.  I do appreciate all of you help!
0
TanyaDHAuthor Commented:
Alright...I've never done a Stored Procedure before.  I found the place to add a new stored procedure, copied and pasted the code, ran it, and it comes up with errors:

Msg 195, Level 15, State 10, Line 18
'suff' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'for'.
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'for'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'for'.
Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'for'.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'for'.
Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'where'.

Will this code actually build a table, put information in the FuelCostWeightedAVGByMonth table or what?
I think this may be way over my head.  I've heard about arrays and was wondering if this may be an option...not sure.

I would like to do an initial build of the data and then add records as time goes on.

Sorry, I don't mean to waste your time...but, at this point I am clueless as to how to get what I want from this data.  I know what I want as an end result...just not "how" to do it.
0
ralmadaCommented:
yep, some typos there.... Check this one now.
This will only produce a select, try it first and see if that's the desired output, then to insert the result to a new table FuelCostWeightedAVGByMonth we need to add the insert part, which won't be complicated. But, could you please provide the structure of this table so we can adjust the query and add the insert part accordingly?

declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from stocktable a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
         where cast((''01/''+cast(b.[month] as varchar) +''/'' + cast(b.[year] as varchar)) as datetime) between ' + @startdate + ' and ' + @enddate + 
        ) o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @col_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @col_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @col_wavg + ')) p3  
group by Stock_number' 
 
exec(@strSQL)

Open in new window

0
TanyaDHAuthor Commented:
Following errors:

Msg 102, Level 15, State 1, Line 51
Incorrect syntax near ')'.
Msg 105, Level 15, State 1, Line 56
Unclosed quotation mark after the character string '
 
exec(@strSQL)
'.

The table FuelCostWeightedAVGByMonth:
ID                     -int/unique id
Stock_Number - varchar
Type                - varchar
Month              - int
Year                -varchar
Cost_1            - numberic....all the rest
Quantity_1
Weighted_AVG_1
Cost_2
Quantity_2
Weighted_AVG_2
Cost_3
Quantity_3
Weighted_3..............all the way through 12 representing Month Integer.

I had to change the field names from example: January_Cost  to Cost_1
                                                                           January_Quantity to Quantity_1.........and so on.

I use table "TankMaster" to get the Stock_Number, table "FuelCost" to get the cost, quantity and weighted_avg.



0
ralmadaCommented:
Ok, let's try to fix all the typos first, try this now and then we can focus on the insert part.
declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
         where cast((''01/''+cast(b.[month] as varchar) +''/'' + cast(b.[year] as varchar)) as datetime) between ' + @startdate + ' and ' + @enddate + 
        ') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @col_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @col_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @col_wavg + ')) p3  
group by Stock_number' 
 
exec(@strSQL)

Open in new window

0
TanyaDHAuthor Commented:
Got the following error:

Msg 241, Level 16, State 1, Line 38
Conversion failed when converting datetime from character string.
0
ralmadaCommented:
instead of exec(@strSQL) in line 58, change it to
print @strSQL
and post the result here
0
TanyaDHAuthor Commented:
Same error:

Msg 241, Level 16, State 1, Line 38
Conversion failed when converting datetime from character string.
0
ralmadaCommented:
I see, change line 50 for this
 

where cast((''01/''+right('00' + cast(b.[month] as varchar),2) +''/'' + cast(b.[year] as varchar)) as datetime) between ' + @startdate + ' and ' + @enddate +

Open in new window

0
ralmadaCommented:
or like this.
Btw did you make sure to pass the date range to the @startdate and @enddate parameters, right?
 

where cast((cast(b.[year] as varchar) + ''-'' + right('00' + cast(b.[month] as varchar),2) +''-01'' + ) as datetime) between ' + @startdate + ' and ' + @enddate +

Open in new window

0
TanyaDHAuthor Commented:
New error:

where cast((cast(b.[year] as varchar) + ''-'' + right('00' + cast(b.[month] as varchar),2) +''-01'' + ) as datetime) between ' + @startdate + ' and ' + @enddate +

>>Btw did you make sure to pass the date range to the @startdate and @enddate parameters, right?<<

At the risk of showing ALL of my stupidity regarding Stored Procedures...How do I do that?
0
TanyaDHAuthor Commented:
Sorry...wrong paste above regarding error:

Msg 102, Level 15, State 1, Line 50
Incorrect syntax near '00'.
0
ralmadaCommented:
oops, missed quotes there.
where cast((cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' + ) as datetime) between ' + @startdate + ' and ' + @enddate +
so I guess you haven't created the stored procedure yet, have you? No worries, lets test this code and get it to work before we put it into a stored procedure and do the insert part.
In this example I'm selecting the date range from 01/01/2008 to 31/12/2008. you can adjust it if you want, just change lines 12 and 13

declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 

set @startdate = '01/01/2008'
set @enddate = '31/12/2008'
 
;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
	where cast((cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' + ) as datetime) between ' + @startdate + ' and ' + @enddate +
        ') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @col_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @col_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @col_wavg + ')) p3  
group by Stock_number' 
 
print @strSQL

Open in new window

0
TanyaDHAuthor Commented:
Error:

Msg 241, Level 16, State 1, Line 42
Conversion failed when converting datetime from character string.

Below is the code that I run.

I've attached a pic of where I am running this.  I selected new Stored Procedure and pasted your code into it and then executed it.  I'm using Microsoft Sql Server 2005.
declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
	where cast((cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' + ) as datetime) between ' + @startdate + ' and ' + @enddate +
        ') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number' 
 
print @strSQL

Open in new window

StoredProcedure.jpg
0
ralmadaCommented:
this is a stubborn one :) , try the below
 

declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
	where case when b.year is not null then cast(cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' as datetime) else + ''' + @startdate + ' between ''' + @startdate + ''' and ''' + @enddate +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number' 
 
print @strSQL

Open in new window

0
TanyaDHAuthor Commented:
Sorry...same error:

Msg 241, Level 16, State 1, Line 42
Conversion failed when converting datetime from character string.
0
ralmadaCommented:
line 54 again
where case when b.year is not null then cast(cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' as datetime) else + ''' + @startdate + ''' between ''' + @startdate + ''' and ''' + @enddate +

Open in new window

0
TanyaDHAuthor Commented:
Still the same error:

Msg 241, Level 16, State 1, Line 42
Conversion failed when converting datetime from character string.

Below is my last run.  A portion of the code.

I'm sorry this is being so stubborn...

set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
        where case when b.year is not null then cast(cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' as datetime) else + ''' + @startdate + ''' between ''' + @startdate + ''' and ''' + @enddate +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number' 
 
print @strSQL

Open in new window

0
ralmadaCommented:
ohh, I think I know what's wrong there

set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
        where case when b.year is not null then cast(cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' as datetime) else + ''' + cast(@startdate as varchar) + ''' between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number' 
 
print @strSQL

Open in new window

0
TanyaDHAuthor Commented:
Arighty then...got the following:

select  Stock_number,  max([1_2008_Cost]), max([2_2008_Cost]), max([3_2008_Cost]), max([4_2008_Cost]), max([5_2008_Cost]), max([6_2008_Cost]), max([7_2008_Cost]), max([8_2008_Cost]), max([9_2008_Cost]), max([10_2008_Cost]), max([11_2008_Cost]), max([12_2008_Cost]), max([1_2009_Cost]),  max([1_2008_qty]), max([2_2008_qty]), max([3_2008_qty]), max([4_2008_qty]), max([5_2008_qty]), max([6_2008_qty]), max([7_2008_qty]), max([8_2008_qty]), max([9_2008_qty]), max([10_2008_qty]), max([11_2008_qty]), max([12_2008_qty]), max([1_2009_qty]),  max([1_2008_wavg]), max([2_2008_wavg]), max([3_2008_wavg]), max([4_2008_wavg]), max([5_2008_wavg]), max([6_2008_wavg]), max([7_2008_wavg]), max([8_2008_wavg]), max([9_2008_wavg]), max([10_2008_wavg]), max([11_2008_wavg]), max([12_2008_wavg]), max([1_2009_wavg]) from (  
        select  a.Stock_number,  
                isnull(b.Quantity,0) as Quantity,  
                isnull(b.Cost,0) as Cost,  
                isnull(b.Extended_Cost,0) as Extended_Cost,  
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Cost' as monthyear_cost,  
                cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Qty' as monthyear_qty,  
                cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Wavg' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
        where case when b.year is not null then cast(cast(b.[year] as varchar) + '-' + right('00' + cast(b.[month] as varchar),2) +'-01' as datetime) else + 'Jan  1 2008 12:00AM' between 'Jan  1 2008 12:00AM' and 'Dec 31 2008 12:00AM') o  
 
pivot(sum(Extended_cost) for monthyear_cost in ( [1_2008_Cost], [2_2008_Cost], [3_2008_Cost], [4_2008_Cost], [5_2008_Cost], [6_2008_Cost], [7_2008_Cost], [8_2008_Cost], [9_2008_Cost], [10_2008_Cost], [11_2008_Cost], [12_2008_Cost], [1_2009_Cost])) p1  
pivot(sum(Quantity) for monthyear_qty in ( [1_2008_qty], [2_2008_qty], [3_2008_qty], [4_2008_qty], [5_2008_qty], [6_2008_qty], [7_2008_qty], [8_2008_qty], [9_2008_qty], [10_2008_qty], [11_2008_qty], [12_2008_qty], [1_2009_qty])) p2
pivot(max(wavg) for monthyear_wavg in ( [1_2008_wavg], [2_2008_wavg], [3_2008_wavg], [4_2008_wavg], [5_2008_wavg], [6_2008_wavg], [7_2008_wavg], [8_2008_wavg], [9_2008_wavg], [10_2008_wavg], [11_2008_wavg], [12_2008_wavg], [1_2009_wavg])) p3  
group by Stock_number
0
ralmadaCommented:
ok great! now just switch the last line (print @strSQL) to exec(@strSQL) and see if the result you get is correct then we will work on the insert part
0
TanyaDHAuthor Commented:
Did that and got the following:  

Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'between'.
0
ralmadaCommented:
yep, typo again sorry about that. See below:
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
        where case when b.year is not null then cast(cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' as datetime) else ''' + cast(@startdate as varchar) + ''' end between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

Open in new window

0
TanyaDHAuthor Commented:
Error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

Below is full code that I have.
declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
        where case when b.year is not null then cast(cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'' as datetime) else ''' + cast(@startdate as varchar) + ''' end between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

 
exec(@strSQL) 

Open in new window

0
ralmadaCommented:
try now
declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
        where case when b.year is not null then cast('''' + cast(b.[year] as varchar) + ''-'' + right(''00'' + cast(b.[month] as varchar),2) +''-01'''''' as datetime) else ''' + cast(@startdate as varchar) + ''' end between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

 
exec(@strSQL)

Open in new window

0
TanyaDHAuthor Commented:
Same error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
0
ralmadaCommented:
can you do a print again? (change exec(@strSQL) for print @strSQL
0
TanyaDHAuthor Commented:
Got this:


select  Stock_number,  max([1_2008_Cost]), max([2_2008_Cost]), max([3_2008_Cost]), max([4_2008_Cost]), max([5_2008_Cost]), max([6_2008_Cost]), max([7_2008_Cost]), max([8_2008_Cost]), max([9_2008_Cost]), max([10_2008_Cost]), max([11_2008_Cost]), max([12_2008_Cost]), max([1_2009_Cost]),  max([1_2008_qty]), max([2_2008_qty]), max([3_2008_qty]), max([4_2008_qty]), max([5_2008_qty]), max([6_2008_qty]), max([7_2008_qty]), max([8_2008_qty]), max([9_2008_qty]), max([10_2008_qty]), max([11_2008_qty]), max([12_2008_qty]), max([1_2009_qty]),  max([1_2008_wavg]), max([2_2008_wavg]), max([3_2008_wavg]), max([4_2008_wavg]), max([5_2008_wavg]), max([6_2008_wavg]), max([7_2008_wavg]), max([8_2008_wavg]), max([9_2008_wavg]), max([10_2008_wavg]), max([11_2008_wavg]), max([12_2008_wavg]), max([1_2009_wavg]) from (  
        select  a.Stock_number,  
                isnull(b.Quantity,0) as Quantity,  
                isnull(b.Cost,0) as Cost,  
                isnull(b.Extended_Cost,0) as Extended_Cost,  
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Cost' as monthyear_cost,  
                cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Qty' as monthyear_qty,  
                cast(b.[Month] as varchar) + '_' + cast(b.[Year] as varchar) + '_Wavg' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
        where case when b.year is not null then cast('' + cast(b.[year] as varchar) + '-' + right('00' + cast(b.[month] as varchar),2) +'-01''' as datetime) else 'Jan  1 2008 12:00AM' end between 'Jan  1 2008 12:00AM' and 'Dec 31 2008 12:00AM') o  
 
pivot(sum(Extended_cost) for monthyear_cost in ( [1_2008_Cost], [2_2008_Cost], [3_2008_Cost], [4_2008_Cost], [5_2008_Cost], [6_2008_Cost], [7_2008_Cost], [8_2008_Cost], [9_2008_Cost], [10_2008_Cost], [11_2008_Cost], [12_2008_Cost], [1_2009_Cost])) p1  
pivot(sum(Quantity) for monthyear_qty in ( [1_2008_qty], [2_2008_qty], [3_2008_qty], [4_2008_qty], [5_2008_qty], [6_2008_qty], [7_2008_qty], [8_2008_qty], [9_2008_qty], [10_2008_qty], [11_2008_qty], [12_2008_qty], [1_2009_qty])) p2
pivot(max(wavg) for monthyear_wavg in ( [1_2008_wavg], [2_2008_wavg], [3_2008_wavg], [4_2008_wavg], [5_2008_wavg], [6_2008_wavg], [7_2008_wavg], [8_2008_wavg], [9_2008_wavg], [10_2008_wavg], [11_2008_wavg], [12_2008_wavg], [1_2009_wavg])) p3  
group by Stock_number
0
ralmadaCommented:
let's change the approach. see the point is that you've stored your year and month in different columns, that's creating a problem. Usually in SQL you should store dates in datetime format. Let's try now
declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
	where dateadd(m, b.month-1, dateadd(y, (b.year - 1900), 0)) between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

 
exec(@strSQL)

Open in new window

0
TanyaDHAuthor Commented:
I ran the last code, got an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'February' to data type int.

I understand about the datetime format.  But, I need to break out the month and year though.
0
ralmadaCommented:
basically what I was saying is that you need to store everything in one column with a datetime datatype for easy handling of the dates. so instead of having two columns, one for the month and one for the year, have one column with a datetime datatype.
well, here we have another problem so your month column is actually string with the month names not the numbers. can you change that and have the month number instead? and make that column an int column? that would definitively make this easier.
0
TanyaDHAuthor Commented:
Yes, the month number will be fine.
0
ralmadaCommented:
Ok, so let me know when you're done with that conversion so we can continue with this query.
0
ralmadaCommented:
see this is how much extra we need to do if you don't change the month column to have the number instead of the month name.
declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


create table #month_names (
	mid int,
	month_name varchar(20)
)

insert #month_names
select 1, 'January'
union 
select 2, 'February'
union 
select 3, 'March'
union 
select 4, 'April'
union 
select 5, 'May'
union 
select 6, 'June'
union 
select 7, 'July'
union 
select 8, 'August'
union 
select 9, 'September'
union 
select 10, 'October'
union 
select 11, 'November'
union 
select 12, 'December'





;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + datename(m, sdate) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + datename(m, sdate) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + datename(m, sdate) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + datename(m, sdate) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + datename(m, sdate) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + datename(m, sdate) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                b.[Month] + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                b.[Month] + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                b.[Month] + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join (select * from fuelcost t1 inner join #month_names t2 on t1.month = t2.month_name) b on a.Stock_number = b.Stock_number  
	where dateadd(m, b.mid-1, dateadd(y, (b.year - 1900), 0)) between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

 
exec(@strSQL)

drop table #month_names

Open in new window

0
TanyaDHAuthor Commented:
Okay, I have a column called "Month" that is an Int.
0
TanyaDHAuthor Commented:
Change...

I forgot that I have a lot of code that refers to the current "Month" column.  I added a column "Month_Integer" to use for what you need here.

Sorry...
0
ralmadaCommented:
have you populated your month_integer column too? If so, here's the query with the updated column name
declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
	where dateadd(m, b.month_integer-1, dateadd(y, (b.year - 1900), 0)) between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

 
exec(@strSQL)

Open in new window

0
TanyaDHAuthor Commented:
Okay...there is not data, but it seems to have created a table.
There are columns:
Stock_Number and then 39 columns (No Column Name)...I attached a pic of it.

When I run it as Print (@strSQL) I get the following:

select  Stock_number,  max([1_2008_Cost]), max([2_2008_Cost]), max([3_2008_Cost]), max([4_2008_Cost]), max([5_2008_Cost]), max([6_2008_Cost]), max([7_2008_Cost]), max([8_2008_Cost]), max([9_2008_Cost]), max([10_2008_Cost]), max([11_2008_Cost]), max([12_2008_Cost]), max([1_2009_Cost]),  max([1_2008_qty]), max([2_2008_qty]), max([3_2008_qty]), max([4_2008_qty]), max([5_2008_qty]), max([6_2008_qty]), max([7_2008_qty]), max([8_2008_qty]), max([9_2008_qty]), max([10_2008_qty]), max([11_2008_qty]), max([12_2008_qty]), max([1_2009_qty]),  max([1_2008_wavg]), max([2_2008_wavg]), max([3_2008_wavg]), max([4_2008_wavg]), max([5_2008_wavg]), max([6_2008_wavg]), max([7_2008_wavg]), max([8_2008_wavg]), max([9_2008_wavg]), max([10_2008_wavg]), max([11_2008_wavg]), max([12_2008_wavg]), max([1_2009_wavg]) from (  
        select  a.Stock_number,  
                isnull(b.Quantity,0) as Quantity,  
                isnull(b.Cost,0) as Cost,  
                isnull(b.Extended_Cost,0) as Extended_Cost,  
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month_integer] as varchar) + '_' + cast(b.[Year] as varchar) + '_Cost' as monthyear_cost,  
                cast(b.[Month_integer] as varchar) + '_' + cast(b.[Year] as varchar) + '_Qty' as monthyear_qty,  
                cast(b.[Month_integer] as varchar) + '_' + cast(b.[Year] as varchar) + '_Wavg' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
      where dateadd(m, b.month_integer-1, dateadd(y, (b.year - 1900), 0)) between 'Jan  1 2008 12:00AM' and 'Dec 31 2008 12:00AM') o  
 
pivot(sum(Extended_cost) for monthyear_cost in ( [1_2008_Cost], [2_2008_Cost], [3_2008_Cost], [4_2008_Cost], [5_2008_Cost], [6_2008_Cost], [7_2008_Cost], [8_2008_Cost], [9_2008_Cost], [10_2008_Cost], [11_2008_Cost], [12_2008_Cost], [1_2009_Cost])) p1  
pivot(sum(Quantity) for monthyear_qty in ( [1_2008_qty], [2_2008_qty], [3_2008_qty], [4_2008_qty], [5_2008_qty], [6_2008_qty], [7_2008_qty], [8_2008_qty], [9_2008_qty], [10_2008_qty], [11_2008_qty], [12_2008_qty], [1_2009_qty])) p2
pivot(max(wavg) for monthyear_wavg in ( [1_2008_wavg], [2_2008_wavg], [3_2008_wavg], [4_2008_wavg], [5_2008_wavg], [6_2008_wavg], [7_2008_wavg], [8_2008_wavg], [9_2008_wavg], [10_2008_wavg], [11_2008_wavg], [12_2008_wavg], [1_2009_wavg])) p3  
group by Stock_number



Columns.jpg
0
ralmadaCommented:
yes, that's because you need to populate your month_integer column with your data. Here's a quick way
create table #month_names (
	mid int,
	month_name varchar(20)
)

insert #month_names
select 1, 'January'
union 
select 2, 'February'
union 
select 3, 'March'
union 
select 4, 'April'
union 
select 5, 'May'
union 
select 6, 'June'
union 
select 7, 'July'
union 
select 8, 'August'
union 
select 9, 'September'
union 
select 10, 'October'
union 
select 11, 'November'
union 
select 12, 'December'



update a
set a.month_integer = b.mid
from fuelcost a
inner join #month_names b on a.month = b.month_name

Open in new window

0
TanyaDHAuthor Commented:
Okay...I will have to try this tomorrow...it is going on 7pm here/my time EST.  I will get that done and will let you know ASAP.
Thank you so much for all you are doing!
0
TanyaDHAuthor Commented:
I wasn't able to get to this yesterday.  I populated the Month_Integer column with your code above.  Ran the code below and gave me the same:

No data and there are 39 columns named "(No Column Name)" and a column named "Stock_Number".


declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @mcols_cost = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_qty = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 3,'') + '])', 
 
        @mcols_wavg = stuff((select ']), max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 3,'') + '])' 
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
	where dateadd(m, b.month_integer-1, dateadd(y, (b.year - 1900), 0)) between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

 
exec(@strSQL)

Open in new window

0
ralmadaCommented:
The unnamed columns is not a problem, here's the query that will put names to them. What I'm concerned about is that you don't have data. Do you have data for 2008? Can you run this simple query and post the result here?
select top 50 * from tankmaster a left join fuelcost b on a.stock_number = b.stock_number

declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
declare @startdate datetime 
declare @enddate datetime 
 
set @startdate = '01/01/2008'
set @enddate = '12/31/2008'


;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 

        @mcols_cost = stuff((select ', max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost]) as [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost]'
                        from CTE for xml path('')), 1, 2,''), 
 
        @mcols_qty = stuff((select ', max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty]) as [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty]'
                        from CTE for xml path('')), 1, 2,''), 
 
        @mcols_wavg = stuff((select ', max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg]) as [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg]'
                        from CTE for xml path('')), 1, 2,'')
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
	where dateadd(m, b.month_integer-1, dateadd(y, (b.year - 1900), 0)) between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

 
exec(@strSQL)

Open in new window

0
TanyaDHAuthor Commented:
Yes, there is data with 2008 in it.  I ran the query you gave above and data was there.  I ran the latest code you posted and the columns now have names, but still no data.

1_2008_Cost, 2_2008_Cost...through 12
1_2008_qty, 2_2008_qty...through 12
1_2008_wavg, 2_2008_wavg...through 12

These are the column names now.
0
TanyaDHAuthor Commented:
Forgot to post results...alignment isn't the greatest...


ID          Tank_Number     Tank_Description                                   Tank_Diameter Tank_Diameter_Inches Tank_Length Tank_Length_Inches Capacity    Type            Tank_Listing Gains_Loss_Grouping Gains_Loss_Grouping_Order End_of_Month_Grouping End_of_Month_Order Stock_Number    Issue_UM ID          Date                    Vendor_ID       Vendor                                                            Stock_Number                    Unit_of_Measure Quantity                                Cost                                    Extended_Cost                           Location    Month                                              Month_Integer Year                                               Fuel_Type       Weighted_Avg                            Description
----------- --------------- -------------------------------------------------- ------------- -------------------- ----------- ------------------ ----------- --------------- ------------ ------------------- ------------------------- --------------------- ------------------ --------------- -------- ----------- ----------------------- --------------- ----------------------------------------------------------------- ------------------------------- --------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- -------------------------------------------------- ------------- -------------------------------------------------- --------------- --------------------------------------- --------------------------------------------------
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267900      2008-10-21 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8370.00000                              2.56387                                 21459.59000                             AW          October                                            10            2008                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267901      2008-11-07 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8411.00000                              2.19887                                 18494.70000                             AW          November                                           11            2008                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267902      2008-11-21 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8433.00000                              1.86887                                 15760.18000                             AW          November                                           11            2008                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267903      2008-12-10 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8421.00000                              1.73987                                 14651.45000                             AW          December                                           12            2008                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267904      2008-12-26 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8390.00000                              1.65387                                 13875.97000                             AW          December                                           12            2008                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267905      2009-01-07 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8475.00000                              2.04187                                 17304.85000                             AW          January                                            1             2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267906      2009-01-09 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8424.00000                              1.89707                                 15980.92000                             AW          January                                            1             2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267907      2009-01-28 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8456.00000                              1.92317                                 16262.33000                             AW          January                                            1             2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267908      2009-04-02 00:00:00.000 006963          PORT CONSOLIDATED                                                 09302500                        GL              8895.00000                              2.00537                                 17837.77000                             AW          April                                              4             2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267909      2009-05-27 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8372.00000                              2.46697                                 20653.47000                             AW          May                                                5             2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267910      2009-07-30 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8335.00000                              2.45997                                 20503.85000                             AW          July                                               7             2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267911      2009-08-31 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8317.00000                              2.57747                                 21436.82000                             AW          August                                             8             2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267912      2009-10-07 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8313.00000                              2.40337                                 19979.21000                             AW          October                                            10            2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267913      2009-10-20 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8362.00000                              2.65927                                 22236.82000                             AW          October                                            10            2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267914      2009-10-28 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8369.00000                              2.74487                                 22971.82000                             AW          October                                            10            2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267915      2009-11-17 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8409.00000                              2.68287                                 22560.25000                             AW          November                                           11            2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267916      2009-12-01 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8395.00000                              2.65387                                 22279.24000                             AW          December                                           12            2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267917      2009-12-14 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8449.00000                              2.59337                                 21911.38000                             AW          December                                           12            2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267918      2009-12-30 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8453.00000                              2.73617                                 23128.85000                             AW          December                                           12            2009                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267919      2010-01-13 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8548.00000                              2.76677                                 23650.35000                             AW          January                                            1             2010                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       267920      2010-01-30 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8444.00000                              2.49687                                 21083.57000                             AW          January                                            1             2010                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       268645      2010-02-13 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8485.00000                              2.54587                                 21601.71000                             AW          February                                           2             2010                                               Gasoline        NULL                                    NULL
611         Tank #1         (30U)-Unleaded Gasoline                            10.6          126                  46'         560                30228       Gasoline        1            1                   1                         Gasoline              NULL               09302500        GL       268646      2010-02-26 00:00:00.000 008095          SSI PETROLEUM                                                     09302500                        GL              8475.00000                              2.65337                                 22487.31000                             AW          February                                           2             2010                                               Gasoline        NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267403      2008-10-01 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7423.00000                              3.17622                                 23577.08000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267404      2008-10-01 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7421.00000                              3.17622                                 23570.73000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267405      2008-10-21 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7416.00000                              2.57662                                 19108.21000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267406      2008-10-21 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7414.00000                              2.57662                                 19103.06000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267407      2008-10-28 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7431.00000                              2.24262                                 16664.91000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267408      2008-10-28 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7433.00000                              2.24262                                 16669.39000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267409      2008-10-29 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7452.00000                              2.23652                                 16666.55000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267410      2008-10-29 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7448.00000                              2.23652                                 16657.60000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267411      2008-10-30 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7432.00000                              2.37262                                 17633.31000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267412      2008-10-30 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7432.00000                              2.37262                                 17633.31000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267413      2008-10-31 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7457.00000                              2.34512                                 17487.56000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267414      2008-10-31 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7443.00000                              2.34512                                 17454.73000                             AW          October                                            10            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267415      2008-11-01 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7442.00000                              2.42452                                 18043.28000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267416      2008-11-01 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7447.00000                              2.42452                                 18055.40000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267417      2008-11-03 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7445.00000                              2.42452                                 18050.55000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267418      2008-11-03 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7439.00000                              2.42452                                 18036.00000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267419      2008-11-03 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7436.00000                              2.42452                                 18028.73000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267420      2008-11-04 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7455.00000                              2.32052                                 17299.48000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267421      2008-11-04 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7453.00000                              2.32052                                 17294.84000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267422      2008-11-04 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7450.00000                              2.32052                                 17287.87000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267423      2008-11-05 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7451.00000                              2.51062                                 18706.63000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267424      2008-11-05 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7450.00000                              2.51062                                 18704.12000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267425      2008-11-06 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7447.00000                              3.59202                                 26749.77000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267426      2008-11-07 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7436.00000                              3.59202                                 26710.26000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267427      2008-11-07 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7441.00000                              3.59202                                 26728.22000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267428      2008-11-08 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7430.00000                              3.59202                                 26688.71000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL
612         Tank #3         (30D)-Diesel Fuel                                  10.6          126                  0           557                30066       Diesel Fuel     3            2                   1                         Diesel Fuel           NULL               09301500        GL       267429      2008-11-08 00:00:00.000 008095          SSI PETROLEUM                                                     09301500                        GL              7430.00000                              3.59202                                 26688.71000                             AW          November                                           11            2008                                               Diesel Fuel     NULL                                    NULL

(50 row(s) affected)

0
ralmadaCommented:
Ok, got it, there's a typo in line 54. it shoud be like this:
where dateadd(m, b.month_integer-1, dateadd(yy, (b.year - 1900), 0)) between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +

Open in new window

0
TanyaDHAuthor Commented:
Great!  I got data!
Stock_number    1_2008_Cost                             2_2008_Cost                             3_2008_Cost                             4_2008_Cost                             5_2008_Cost                             6_2008_Cost                             7_2008_Cost                             8_2008_Cost                             9_2008_Cost                             10_2008_Cost                            11_2008_Cost                            12_2008_Cost                            1_2009_Cost                             1_2008_qty                              2_2008_qty                              3_2008_qty                              4_2008_qty                              5_2008_qty                              6_2008_qty                              7_2008_qty                              8_2008_qty                              9_2008_qty                              10_2008_qty                             11_2008_qty                             12_2008_qty                             1_2009_qty                              1_2008_wavg                             2_2008_wavg                             3_2008_wavg                             4_2008_wavg                             5_2008_wavg                             6_2008_wavg                             7_2008_wavg                             8_2008_wavg                             9_2008_wavg                             10_2008_wavg                            11_2008_wavg                            12_2008_wavg                            1_2009_wavg
--------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
09102680        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    270900.00000                            NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    5000.00000                              NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    9.030000                                NULL
09102690        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    11680.00000                             NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    250.00000                               NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    11.680000                               NULL
09102890        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    1291.95000                              NULL                                    1374.45000                              NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    55.00000                                NULL                                    55.00000                                NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    8.080000                                NULL                                    8.080000                                NULL
09106880        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    96915.00000                             NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    1500.00000                              NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    9.230000                                NULL
09106885        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    15940.80000                             NULL                                    8154.80000                              NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    405.00000                               NULL                                    190.00000                               NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    10.285000                               NULL                                    10.285000                               NULL
09204000        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    45938.64000                             NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    2400.00000                              NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    1.740100                                NULL                                    NULL
09301500        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    423199.44000                            1920810.96000                           1605848.40000                           NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    7457.00000                              7463.00000                              7476.00000                              NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    2.396974                                2.396974                                2.396974                                NULL
09302500        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    64378.77000                             55484.10000                             43954.35000                             NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    8370.00000                              8433.00000                              8421.00000                              NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    2.005070                                2.005070                                2.005070                                NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

(8 row(s) affected)

Open in new window

0
ralmadaCommented:
ok, good. now you just need to pass @startdate and @enddate as parameters
so you just call your procedure like this:
exec yourprocedure '01/01/2008', '31/12/2008'
regarding the possibility of inserting this to your FuelCostWeightedAVGByMonth. yes it can be done, but I would suggest you reconsider this approach, because you will have to add a column for each new month, that could be really difficult to manage at a certain point. So I would suggest week this as a select as we currently have.
 

create procedure yourprocedure 
@startdate datetime,
@enddate datetime
as
declare @strSQL varchar(max) 
declare @cols_cost varchar(max) 
declare @cols_qty varchar(max) 
declare @cols_wavg varchar(max) 
declare @mcols_cost varchar(max) 
declare @mcols_qty varchar(max) 
declare @mcols_wavg varchar(max) 
 
;with CTE as ( 
        select @startdate as sdate 
        union all 
        select dateadd(m, 1, sdate) from CTE 
        where sdate <= @enddate 
) 
select  @cols_cost = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost' 
                        from CTE for xml path('')),1, 2, '') + ']', 
 
        @cols_qty = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty' 
                        from CTE for xml path('')), 1, 2, '') + ']', 
 
        @cols_wavg = stuff((select '], [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg' 
                        from CTE for xml path('')), 1, 2, '') + ']', 

        @mcols_cost = stuff((select ', max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost]) as [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_Cost]'
                        from CTE for xml path('')), 1, 2,''), 
 
        @mcols_qty = stuff((select ', max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty]) as [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_qty]'
                        from CTE for xml path('')), 1, 2,''), 
 
        @mcols_wavg = stuff((select ', max([' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg]) as [' + cast(month(sdate) as varchar) + '_' + cast(year(sdate) as varchar) + '_wavg]'
                        from CTE for xml path('')), 1, 2,'')
 
 
 
set @strSQL = 'select  Stock_number, ' + @mcols_cost + ', ' + @mcols_qty + ', ' + @mcols_wavg 
+ ' from (  
        select  a.Stock_number,   
                isnull(b.Quantity,0) as Quantity,   
                isnull(b.Cost,0) as Cost,   
                isnull(b.Extended_Cost,0) as Extended_Cost,   
                avg(b.Cost) over (partition by a.Stock_number) as wavg,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Cost'' as monthyear_cost,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Qty'' as monthyear_qty,  
                cast(b.[Month_integer] as varchar) + ''_'' + cast(b.[Year] as varchar) + ''_Wavg'' as monthyear_wavg  
        from Tankmaster a  
        left join fuelcost b on a.Stock_number = b.Stock_number  
	where dateadd(m, b.month_integer-1, dateadd(y, (b.year - 1900), 0)) between ''' + cast(@startdate as varchar) + ''' and ''' + cast(@enddate as varchar) +
        ''') o  
 
pivot(sum(Extended_cost) for monthyear_cost in (' + @cols_cost + ')) p1  
pivot(sum(Quantity) for monthyear_qty in (' + @cols_qty + ')) p2 
pivot(max(wavg) for monthyear_wavg in (' + @cols_wavg + ')) p3  
group by Stock_number'

 
exec(@strSQL)

Open in new window

0
TanyaDHAuthor Commented:
Okay, please keep in mind, this is the first time I've done anything with a Stored Procedure.  Where do I call this procedure from?
I did run the above code and it created the Stored Procedure.  I then went to Visual Studio 2008 and through the Database Wizard, selected the Stored Procedure, which I named WeightedAVG.  It then created a table adapter with the query/stored procedure in it.

In VS 2008, I have a form that the user selects the date range.  Do I put it in the code of this form?  And then, how do I show the user the data?

Also, I still need to get the previous months Weighted AVG if the Weighted AVG is zero/null.  If January/2009 is zero I would need to get Dec/2008's Weighted AVG.  Or if January/2009 and February/2009 is zero I would need to get Dec/2008's Weighted AVG and assign it to both January and February.

With all of this...I will need to be able to run Crystal Reports.  In them...they will use the weighted average times the Equipment Numbers total fuel quantity for the month.  Which then will be broken down by Department.

My biggest thing to do is get the data in a table that will retain the records for future use.  I will use the data in many areas of the program.



0
ralmadaCommented:
>>Where do I call this procedure from?

Here's an example of how to call a stored procedure with parameters
http://www.macronimous.com/resources/calling_stored_procedures_from_ASP.NET_and_VB.NET.asp
>>In VS 2008, I have a form that the user selects the date range
I guess you are using ASP, aren't you? To obtain the date range from the form and pass it to the stored procedure you might consider taking a look at the following video tutorials:
http://www.asp.net/LEARN/videos/ 
>>My biggest thing to do is get the data in a table that will retain the records for future use.  I will use the data in many areas of the program.<<
You don't need to store it in a table, actually that will cause all the problems I've mentioned above. You simply call the stored procedure when you need to populate any datasource.
 
>> will need to be able to run Crystal Reports
I can't help you with that part. You might consider asking a question in the Crystal Report zone.
>>Also, I still need to get the previous months Weighted AVG if the Weighted AVG is zero/null.  If January/2009 is zero I would need to get Dec/2008's Weighted AVG.  Or if January/2009 and February/2009 is zero I would need to get Dec/2008's Weighted AVG and assign it to both January and February.

I will have to think about this one.  You might want to consider opening another question for that :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TanyaDHAuthor Commented:
ralmada...thank you...your very patient and I appreciate your helping me through what we did get through.  I know I exhausted you and again...thank you for your patience!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.