?
Solved

Modify Dynamic SQL to return sum of second column

Posted on 2014-08-29
19
Medium Priority
?
187 Views
Last Modified: 2014-09-26
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

0
Comment
Question by:wint100
  • 10
  • 5
  • 4
19 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40292456
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
 
LVL 1

Author Comment

by:wint100
ID: 40292462
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
 
LVL 1

Author Comment

by:wint100
ID: 40292482
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40292623
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
 
LVL 1

Author Comment

by:wint100
ID: 40292640
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40292697
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40293772
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40293786
Fully agree with Mark here.
The "simple request" for a new column is going to be much harder that you probably expected.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40293903
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
 
LVL 1

Author Comment

by:wint100
ID: 40294134
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
 
LVL 1

Author Comment

by:wint100
ID: 40296210
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
 
LVL 1

Author Comment

by:wint100
ID: 40302830
Any idea where I'm going wrong with the above code, or what I can do to improve it?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40303242
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40305016
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
 
LVL 1

Author Comment

by:wint100
ID: 40305297
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
 
LVL 1

Author Comment

by:wint100
ID: 40305300
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 40306617
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
 
LVL 1

Author Comment

by:wint100
ID: 40306625
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
 
LVL 1

Author Comment

by:wint100
ID: 40345718
This ended up being another part of the code where an isnull was screwing things up, cheers.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question