ssms - SQL - Cross tab. Sum of a field instead of count rows in columns -


currently trying create query give me sales made agent day whole year. i've managed total column work, daily sales don't, looking @ raw data seems rather summing total sales made day particular agent instead counting rows agent listed , outputting instead. code follows:

declare @cols nvarchar(max),         @query  nvarchar(max)  select @cols = stuff((select  ',' + quotename(convert(varchar(20), [entry_date], 106))                    [sales_data] (nolock)                  where([entry_date] between '01-jan-2017' , '01-jul-2017'                   group [entry_date]                 order [entry_date]         xml path(''), type         ).value('.', 'nvarchar(max)')      ,1,1,'')  set @query =   ' select    [advisor_name]                                                                   [advisor_name]  ,[line_managers_name]                                                             [line_managers_name]  ,[sale_type]  ,[total]  ,'+ @cols +'       (             select                      sum([sales])                                  over(partition [advisor_name], [line_managers_name], [sale_type])                                        [total]                                 ,    [entry_date]                                 ,    [advisor_name]                                 ,     [line_managers_name]                                 ,    [sale_type]               [sales_data] (nolock)                    )     x      pivot       (                                       count([entry_date])                                       [entry_date] in (' + @cols + ')     ) p    order    [advisor_name],     [line_managers_name],     [channel]            '  execute(@query)

if couldn't change query had planned on instead altering source data give me line line sale info rather aggregating sum, unfortunately of rows might have customer -1 sale indicate refund , don't know how accommodate these, have tried select distinct, select (select distinct.....) having count(*) = 1, nothing seems want work.

i've amended our dynamically generated sql generate new output. 

 declare @cols nvarchar(max) declare @colsforpivot nvarchar(max) declare @query  nvarchar(max)  select @cols = stuff((select  ',isnull(' + quotename(convert(varchar(20), [entry_date], 106)) + ',0)'                   [sales_data] (nolock)                  where([entry_date] between '01-jan-2017' , '01-jul-2017')                 group [entry_date]                 order [entry_date]         xml path(''), type         ).value('.', 'nvarchar(max)')      ,1,1,'')  select @colsforpivot = stuff((select  ',' + quotename(convert(varchar(20), [entry_date], 106))               [sales_data] (nolock)              where([entry_date] between '01-jan-2017' , '01-jul-2017')             group [entry_date]             order [entry_date]     xml path(''), type     ).value('.', 'nvarchar(max)')  ,1,1,'')  set @query =   'select   [advisor_name], [line_managers_name], [sale_type], isnull(totalsales,0), ' + @cols + '        (             select distinct                     sum([sales])                                  over(partition [advisor_name], [line_managers_name], [sale_type],    [entry_date])                                        [total]                                 ,    [entry_date]                                 ,    [advisor_name]                                 ,     [line_managers_name]                                 ,    [sale_type], sum([sales])  over(partition [advisor_name], [line_managers_name], [sale_type]) totalsales               [sales_data] (nolock)                    )     x      pivot       (                                       sum([total])                                        [entry_date] in (' + @colsforpivot + ')     ) p    order    [advisor_name],     [line_managers_name]        '  print(@query)

Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more