July 29, 2016 at 2:39 pm
Firstly I must admit I learned t-sql on my own and my syntax I know is not the best as I use temp tables, but other than that, I am stumped. Every time I make a small change to fix one error, I get 3 more. It's been 4 hours and I just cannot get it to work.
Here is the temp table I've created in my sp where it references other temp tables in my sp (I know it's not the best way to do this but I digress):
Create Table #SG_MonthlyTotals(
GLAcct varchar(12),
GLDescription varchar(50),
AcctType varchar(5),
MainAccount varchar(5),
Location varchar(2),
Division varchar(3),
ContractType varchar(10),
Category varchar(50),
SubCategory varchar(50),
Mth date,
MonthlyBalance money,
MonthlyBudget money,
MonthlyBalancePY money
)
insert into #SG_MonthlyTotals
select a.*,m.Mth,m.MonthlyBalance,b.MonthlyBudget,p.MonthlyBalancePY
from #SG_GLAccounts a left outer join #SG_MonthlyBalances m
on a.GLAcct=m.GLAcct left outer join #SG_MonthlyBudgets b
on m.GLAcct=b.GLAcct and m.Mth=b.Mth left outer join #SG_MonthlyBalancesPY p
on a.GLAcct=p.GLAcct
I have 1 date parameters for the sp: @StartDate DateTime
I want to pivot 12 months of data and aggregate the MonthlyBalance amount. I need all the other columns then have the MonthlyBalance pivot for each month. The months are Dynamics, so I've used DateAdd to start at the @StartDate then add 11 months. I've done it the long way but it just makes more sense to me.
I tried it without the Convert and received an error but now still getting these 2 errors. I've tried everything. After some changes I get rid of the convert error but then get an error about the Mth being referenced twice. The Mth is the field I want to Pivot. The values in the db are DateTime and it has 2015-01-01 (the year and month change but the day is always 1 for every record so that's why I was adding a month).
Please help if you can. I had to put the [] around the dates at one point due to some other error.
Msg 8114, Level 16, State 1, Procedure SG_FinancialReports_Budgets_YTD, Line 107
Error converting data type nvarchar to date.
Msg 473, Level 16, State 1, Procedure SG_FinancialReports_Budgets_YTD, Line 107
The incorrect value "Convert(date,@StartDate)" is supplied in the PIVOT operator.
My code:
select *,Convert(date,@StartDate),DateAdd(month,1,Convert(date,@StartDate)),
DateAdd(month, 2, Convert(date,@StartDate)),DateAdd(month, 3, Convert(date,@StartDate)),
DateAdd(month, 4, Convert(date,@StartDate)),DateAdd(month, 5, Convert(date,@StartDate)),
DateAdd(month, 6, Convert(date,@StartDate)),DateAdd(month, 7, Convert(date,@StartDate)),
DateAdd(month, 8, Convert(date,@StartDate)),DateAdd(month, 9, Convert(date,@StartDate)),
DateAdd(month, 10, Convert(date,@StartDate)),DateAdd(month, 11, Convert(date,@StartDate))
from
(select Mth, MonthlyBalance from #SG_MonthlyTotals) as src
PIVOT
(
sum(MonthlyBalance)
For Mth
IN
(
[Convert(date,@StartDate)],[DateAdd(month, 1, Convert(date,@StartDate))],
[DateAdd(month, 2, Convert(date,@StartDate))],[DateAdd(month, 3, Convert(date,@StartDate))],
[DateAdd(month, 4, Convert(date,@StartDate))],[DateAdd(month, 5, Convert(date,@StartDate))],
[DateAdd(month, 6, Convert(date,@StartDate))],[DateAdd(month, 7, Convert(date,@StartDate))],
[DateAdd(month, 8, Convert(date,@StartDate))],[DateAdd(month, 9, Convert(date,@StartDate))],
[DateAdd(month, 10, Convert(date,@StartDate))],[DateAdd(month, 11, Convert(date,@StartDate))]
)
)
as PVT
August 2, 2016 at 12:43 pm
Two comments so that you can help us help you.
First, thank you for the CREATE table statement, but we have no way to populate it with the sample data you expect because your insert query references other tables for which we have no data.
Second, while your code samples show that you've tackled this problem on your own, we really need to see what results you expect. When you provide sample data and expected results, then you can get answers that have already been tested.
So please just insert values into #SG_Monthly_Totals and tell us what results you expect. Don't describe the results in words, draw us a picture. Show us exact columns and amounts and you will get good responses much quicker.
HERE[/url] is a good article about submitting questions properly. Please take a few minutes to read it and you'll see what I'm talking about.
Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 2, 2016 at 12:59 pm
Use Cross tabs instead. I'm not sure if there's only one date per month so I included a range, but you can change the condition as needed.
SELECT SUM( CASE WHEN Mth >= DATEADD(month, 0, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 1, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 1, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 2, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 2, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 3, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 3, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 4, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 4, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 5, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 5, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 6, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 6, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 7, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 7, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 8, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 8, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 9, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 9, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 10, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 10, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 11, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
, SUM( CASE WHEN Mth >= DATEADD(month, 11, CONVERT( DATE, @StartDate)) AND Mth < DATEADD(month, 12, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END)
FROM #SG_MonthlyTotals;
August 2, 2016 at 1:36 pm
Part of the problem that you are having is that the IN clause of a PIVOT requires a set of quoted identifiers, but you are supplying expressions. When determining matches it interprets the identifiers as NVARCHAR strings and tries to CAST them to the appropriate data type. Since the string "Convert(date,@StartDate)" is not a valid date, it fails.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 2, 2016 at 1:58 pm
I tried this code but the only results I get is a single line of 12 columns with $0 and (no column name) as the column name for each. Should the < be the @EndDate?
August 2, 2016 at 2:05 pm
I'm using the results of this sp on a Crystal Report. I'd like to simply drag in these 12 values onto the details of a report. I have managed to get the results I need, however, since the Column Names are Dynamic, my report won't work as the Field Names is now hard coded to the Month Name. For example, if I ran the sp @StartDate='2015-11-01', @EndDate='2016-03-01' and created the report, the Field Names would show '2015-11-01' all the way to '2016-03-01' If I then re-run the sp for different dates, the field names (column name in SQL) change thus would get errors that '2015-11-01' does not exist etc.
Here's the code I was able to get working with Dynamic columns, which I might have have to scrap.
The report will always use @StartDate being Nov 1 and the year changes (it's a financial report so they enter their beginning fiscal year as teh Start Date). So bascially If I created a field called "Period 1" or "Nov" it would be consistent so I can use in Crystal and the values will be monthlybalance when the Mth=@StartDate .There should always be 12 months and the next should be "Dec" which is @StartDate + 1 month.....@StartDate+11.
if object_id('tempdb..##SGtemp') is not null
begin
drop table ##SGtemp
end
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Mth)
FROM #SG_MonthlyBalances
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT GLAcct,
GLDescription,
AcctType,
MainAccount,
Location,
Division,
ContractType,
Category,
SubCategory,' + @cols + ' into ##SGtemp from
(
select a.GLAcct,
a.GLDescription,
a.AcctType,
a.MainAccount,
a.Location,
a.Division,
a.ContractType,
a.Category,
a.SubCategory,
m.Mth,
m.MonthlyBalance
from #SG_GLAccounts a left outer join #SG_MonthlyBalances m
on a.GLAcct=m.GLAcct
) x
pivot
(
sum(MonthlyBalance)
for Mth in (' + @cols + ')
) p '
execute(@query)
August 2, 2016 at 2:15 pm
I think I have it with the Cross tab. I added the additional fields, then a Group By as well as adding "as 'Nov' to the first Sum then 'Dec' etc. Will test the amounts. I think this is it!!
August 3, 2016 at 12:33 pm
Everything worked great for this cross tab. I now have to modify it slightly, but it's now giving me multiple values per month because of my additional syntax.
Example @StartDate='2015-11-01',@EndDate='2016-05-01'
What I'd like in each month is the MonthlyBalance for any dates <=@EndDate. If it's greater than the @EndDate, I want to use another value MonthlyBudget
Code before:
SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 7, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 8, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Jun'
Code after:
SUM( CASE
WHEN Convert(date,Mth) >= DATEADD(month,7, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 8, CONVERT( DATE, @StartDate))
AND Convert(date,Mth) < DATEADD(month,8, CONVERT( DATE, @EndDate))
THEN MonthlyBalance ELSE MonthlyBudget END) as 'Jun'
What's happening is that it's putting a value for all 6 rows (6 additional months).
I've tried this but it doesn't work either.
sum( CASE
WHEN Convert(date,Mth) >= DATEADD(month, 7, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 8, CONVERT( DATE, @StartDate))
THEN MonthlyBalance
when Convert(date,Mth)<Convert(date,@EndDate) then MonthlyBudget
END)
The temp table is as such:
Create Table #SG_MonthlyTotals(
GLAcct varchar(12),
GLDescription varchar(50),
AcctType varchar(5),
MainAccount varchar(5),
Location varchar(2),
Division varchar(3),
ContractType varchar(10),
Category varchar(50),
SubCategory varchar(50),
Mth date,
MonthlyBalance money,
MonthlyBudget money
)
insert into #SG_MonthlyTotals
select a.*,m.Mth,m.MonthlyBalance,b.MonthlyBudget
from #SG_GLAccounts a left outer join #SG_MonthlyBalances m
on a.GLAcct=m.GLAcct left outer join #SG_MonthlyBudgets b
on m.GLAcct=b.GLAcct and m.Mth=b.Mth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply