Modify Dynamic SQL to return sum of second column

Hi,

I have the dymaic SQL below to sum up the datachange column. I need to modify this to also sum the 'baseloaddatachange' column. I'm sure I'm missing something simple but I can't figure this out.

Thanks

insert #tmp_reportdata
exec ('select timestamp,datestamp,calendardate,convert(decimal(12,4),0'+@parse+')  as datachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),datachange) as datachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),manualdatachange) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),baseloadmanualdatachange) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(datachange) for tlinstance in ('+@columns+')) pvt')

Open in new window

LVL 1
wint100Asked:
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.

PortletPaulfreelancerCommented:
please amend you code to produce the whole query string into a variable, then print that variable (or just select it) instead of executing it.
--insert #tmp_reportdata
@SQL = ('select timestamp,datestamp,calendardate,convert(decimal(12,4),0'+@parse+')  as datachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),datachange) as datachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),manualdatachange) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),baseloadmanualdatachange) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(datachange) for tlinstance in ('+@columns+')) pvt')

select @sql

Open in new window

Study that generated string. Is it a viable sql query?
Paste it into this and try to format it: http://www.sql-format.com/

if having trouble with please paste the UNAMENDED query string into a comment here.
0
wint100Author Commented:
This is what is slelected suing the code you gave:

select timestamp,datestamp,calendardate,convert(decimal(12,4),0 1 ( case when '1' in ('*','/') and isnull([12],0) = 0 then 1 else isnull([12],0) end * 1 ))  as datachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,baseloadmanualdatachange,interval from  (select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),datachange) as datachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),manualdatachange) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),baseloadmanualdatachange) as baseloadmanualdatachange,interval from #reportdata) src  PIVOT  (sum(datachange) for tlinstance in ([12])) pvt
0
wint100Author Commented:
Sorry, here is the correct string formatted:

SELECT
  timestamp,
  datestamp,
  calendardate,
  CONVERT(decimal(12, 4), 0 + (CASE
    WHEN '+' IN ('*', '/') AND
      ISNULL([12], 0) = 0 THEN 1
    ELSE ISNULL([12], 0)
  END * 1)) AS datachange,
  occupancy,
  oat,
  spacetemp,
  widgets,
  ManualRead,
  manualdatachange,
  baseloadManualRead,
  baseloadmanualdatachange,
  interval
FROM (SELECT
  timestamp,
  datestamp,
  calendardate,
  tlinstance,
  CONVERT(decimal(12, 4), datachange) AS datachange,
  occupancy,
  oat,
  spacetemp,
  widgets,
  ManualRead,
  CONVERT(decimal(12, 4), manualdatachange) AS manualdatachange,
  BaseloadManualRead,
  CONVERT(decimal(12, 4), baseloadmanualdatachange) AS baseloadmanualdatachange,
  interval
FROM #reportdata) src PIVOT (SUM(datachange) FOR tlinstance IN ([12])) pvt

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

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 now.

PortletPaulfreelancerCommented:
This isn't right

  CONVERT(decimal(12, 4), 0

+ (CASE  WHEN '+' IN ('*', '/') AND  ISNULL([12], 0) = 0 THEN 1 ELSE ISNULL([12], 0)  END * 1)

) AS datachange,


what is it supposed to do?

It comes from @parse

convert(decimal(12,4),0'+@parse+')  as datachange
0
wint100Author Commented:
That part is OK, it has worked like that for years. It takes a csv of functions (+ or -), unpacks it and works out the calculation to get the datachange value.

All I'm looking to do is return the same calculation for the baseloaddatachange but it always returns the same as datachange even though I know the data is different.
0
PortletPaulfreelancerCommented:
Then the ONLY suggestion open to me is: put together some sample data and an expected result.
(The sample does not have to be large but it should demonstrate the problem.)

Otherwise I can see no chance of tracing an issue that only you can see (remember all I can see is on this page).
0
Mark WillsTopic AdvisorCommented:
Well, I think it is a lot more involved than you think...

The way I read the question is that you want the PIVOT results to show the sum(datachange) and also the sum(baseloadmanualdatachange).

but also see in your comment above that you want to apply @parse the same way as you do for datachange (is that correct) ?

Might end up easier to first load into your temp table, and then pivot (for two columns and that can be involved because PIVOT works for a single aggregation )

Then there is the number of columns in your selection - might want to consider your implied group by (ie a new line for each combo of items being selected - if that makes sense).
0
PortletPaulfreelancerCommented:
Fully agree with Mark here.
The "simple request" for a new column is going to be much harder that you probably expected.
0
Mark WillsTopic AdvisorCommented:
I understand that your final select is trying to use @parse to do arithmetic on various columns resulting from the pivot that generates those columns used in @parse (ie the columns from @columns).

Those columns are different tlinstances and in the example above there is one tlinstance being the (new) column [12]

so, there might be at least three problems.

1) using pivot to generate more than 1 aggregated result column
2) applying the same @parse rules to the baseload column
3) the columns from the inner select and the outer select must represent the desired (unique) columns that you actually want the aggregations to apply to (so if raw baseloadmanualdatachange from #reportdata changes from one tlinstance to another it will generate additional lines in your pivot results)

Think it can be done, just need to properly understand the desired results.
0
wint100Author Commented:
Hi Mark,

You are correct, I need an additional result of baseloaddatachange with the @parse applied so it is basically the same calc as for datachange that needs to be returned but with different data.

I have experimented with using the pivot as is, then running an update to set the baseloaddatachange using a similar pivot but I'm getting arithmetic errors. I can't post that code just now as I'm on my mobile.

Sounds like you've got the requirement spot on though.

Things are always harder than initially hoped, that's where you guys come in. :-)
0
wint100Author Commented:
This is what I tried.

insert #tmp_reportdata
exec ('select timestamp,datestamp,calendardate,convert(decimal(12,4),0'+@parse+')  as datachange,0 as baseloaddatachange ,  occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),datachange) as datachange,0 baseloaddatachange ,  occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),manualdatachange) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),baseloadmanualdatachange) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(datachange) for tlinstance in ('+@columns+')) pvt')
update #tmp_reportdata 
set baseloaddatachange=('select top 1 convert(decimal(12,4),0'+@parse+')  as baseloaddatachange from
(select convert(decimal(12,4),baseloaddatachange) as baseloaddatachange from #reportdata )src where src.timestamp=timestamp
PIVOT
(sum(baseloaddatachange) for tlinstance in ('+@columns+')) pvt')
select * from #tmp_reportdata order by timestamp asc

Open in new window

0
wint100Author Commented:
Any idea where I'm going wrong with the above code, or what I can do to improve it?
0
Mark WillsTopic AdvisorCommented:
G'day,

Yeah, a couple of things can be improved upon...

Gimme a few hours to unpack and settle down (had a few days away) and I will throw a few ideas around with you.

Cheers,
Mark
0
Mark WillsTopic AdvisorCommented:
OK, so the @parse is very simply doing arithmetic to consolidate @columns back into a single column.

the reason for pivot is so @parse can do the arithmetic on those newly created @columns.

the correlated subquery is questionable because it relies on timestamp to uniquely identify all the applicable candidate rows for @columns. Consideration is that any of the other columns (in the select part) which the pivot uses to help create the effective control breaks (for the aggregation) may not generate the same results as a correlated timestamp subquery.

For example, timestamp + datestamp + calendardate might lead one to think that timestamp would repeat over different days let alone tlinstance (and same applies to any other columns in the select that maybe tlinstance dependant in the first place)

So, I am looking at doing a union query - the union providing the resolution of @columns needed for @parse, first for datachange (with zeros for baseloaddatachange) and the second for baseloaddatachange (with zeros for datachange) and then a group by to bring back to single rows when we no longer need to be concerned about @parse and @columns.

something like (and need to resolve field names properly - cannot see a baseloaddatachange in your question header qry, so used baseloadmanualdatachange instead) :

--insert #tmp_reportdata
exec ('select timestamp,datestamp,calendardate,convert(decimal(12,4),0'+@parse+')  as datachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),datachange) as datachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),0) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),0.0) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(datachange) for tlinstance in ('+@columns+')) pvt
UNION ALL
select timestamp,datestamp,calendardate,datachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,convert(decimal(12,4),0'+@parse+')  as baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),0) as datachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),manualdatachange) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),baseloadmanualdatachange) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(baseloadmanualdatachange) for tlinstance in ('+@columns+')) pvt'
-- and then group by to merge the two)

Open in new window

the group by doesn't have to be in the dynamic SQL because we no longer need @parse and @columns, by that time we would simply be doing a select from @tmp_reportdata using sum(datachange) as datachange and a sum(baseload....) as baseload...
0
wint100Author Commented:
This looks good. I've added in baseloaddatachange to give the following code:

--insert #tmp_reportdata
exec ('select timestamp,datestamp,calendardate,convert(decimal(12,4),0'+@parse+')  as baseloaddatachange,convert(decimal(12,4),0'+@parse+')  as datachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),datachange) as datachange,convert(decimal(12,4),baseloaddatachange) as baseloaddatachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),0) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),0.0) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(datachange) for tlinstance in ('+@columns+')) pvt
UNION ALL
select timestamp,datestamp,calendardate,datachange,baseloaddatachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,convert(decimal(12,4),0'+@parse+')  as baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),0) as datachange,convert(decimal(12,4),0) as baseloaddatachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),manualdatachange) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),baseloadmanualdatachange) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(baseloaddatachange) for tlinstance in ('+@columns+')) pvt'
-- and then group by to merge the two)

Open in new window

0
wint100Author Commented:
Modified slightly, but I get an error

Msg 8115, Level 16, State 8, Procedure GetCostPerPeriod_Virtual, Line 223
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.

exec ('select timestamp,datestamp,calendardate,convert(decimal(12,4),0'+@parse+')  as baseloaddatachange,convert(decimal(12,4),0'+@parse+')  as datachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),datachange) as datachange,convert(decimal(12,4),0.0) as baseloaddatachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),0) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),0.0) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(datachange) for tlinstance in ('+@columns+')) pvt
UNION ALL
select timestamp,datestamp,calendardate,datachange,convert(decimal(12,4),0'+@parse+')  as baseloaddatachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead, baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),0) as datachange,convert(decimal(12,4),baseloaddatachange) as baseloaddatachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),manualdatachange) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),baseloadmanualdatachange) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(baseloaddatachange) for tlinstance in ('+@columns+')) pvt')
-- and then group by to merge the two)

Open in new window

0
Mark WillsTopic AdvisorCommented:
hmmmm....

Need to make sure all columns are in alignment. There is an error above with column sequence.

Also, only convert the columns that absolutely need it. e.g. do you need to convert manualdatachange and baseloadmanualdatachange

Finally, you may need to check your data (and data types) - which columns think they are INT ?  Can they be converted without overflow ? Do you need to makes the convert a bigger size ?

exec ('select timestamp,datestamp,calendardate,convert(decimal(12,4),0'+@parse+')  as datachange, baseloaddatachange,occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead,baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),datachange) as datachange,convert(decimal(12,4),0.0) as baseloaddatachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),0.0) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),0.0) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(datachange) for tlinstance in ('+@columns+')) pvt
UNION ALL
select timestamp,datestamp,calendardate,datachange,convert(decimal(12,4),0'+@parse+')  as baseloaddatachange, occupancy, oat, spacetemp, widgets, ManualRead,manualdatachange, baseloadManualRead, baseloadmanualdatachange,interval from
(select timestamp,datestamp,calendardate,tlinstance, convert(decimal(12,4),0.0) as datachange,convert(decimal(12,4),baseloaddatachange) as baseloaddatachange, occupancy, oat, spacetemp, widgets,ManualRead,convert(decimal(12,4),manualdatachange) as manualdatachange,BaseloadManualRead,convert(decimal(12,4),baseloadmanualdatachange) as baseloadmanualdatachange,interval from #reportdata) src
PIVOT
(sum(baseloaddatachange) for tlinstance in ('+@columns+')) pvt')
-- and then group by to merge the two)

Open in new window


to help track it down, just run the inner (ie highlight the inner 'src' select and run those individually)

and of course, double check that line number - it seems to be inside a larger procedure and might not be the current code, or, might be using generated columns in a different way in subsequent code.
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
wint100Author Commented:
It does seem to occur even with my original code, so may not be related to this new code.

I don't really need to convert manualdatachange and baseloadmanualdatachange really, at least not at this stage.

I'll have a play//
0
wint100Author Commented:
This ended up being another part of the code where an isnull was screwing things up, cheers.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.