1

Topic: How to add a line

Children, prompt please a command which at me would add under the project.
My request produces the table as on a picture, and it is necessary that it still produced a total sum (for example also 187+155,5 and 129+36,5 etc.)

select case row_number () over (partition by dd.projectcode order by dd.holeID) when 1 then dd. PROJECTCODE end as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) s from drillingdetail dd where dd.holeid in (select holeid from DRILLINGDETAIL where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
) and cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE, HOLEID

It seems at me curve hands...

2

Re: How to add a line

katish444;
Problem in that is values of different slits of one project and them it is necessary to add. But that the nobility what to add it is necessary to learn what slits  Holeid, therefore gets rid I can not

3

Re: How to add a line

And why you deduce dd.projectcode only for the first hole?
Deduce for all and add then on it

4

Re: How to add a line

wrote:

and why you deduce dd.projectcode only for the first hole?
Deduce for all and add then on it

That it was not doubled. Values are deduced for all
Tried just in case - all the same does not add

5

Re: How to add a line

katish444;
Here so it is necessary to you?
[CSV=t] x_projecttstss
KEt187t342.5
KEt155.5t342.5
MAt129t165.5
MAt36.5t165.5

6

Re: How to add a line

katish444;
You  like all under SSRS saw, he is able to add to group and , what for this account programming on TSQL

7

Re: How to add a line

katish444;
Can so:

group by PROJECTCODE, HOLEID>>> With Rollup <<<

8

Re: How to add a line

katish444;
Well and for appearance:

case When row_number () over (partition by dd.projectcode order by dd.holeID) = 1 then dd. PROJECTCODE
When Hole_ID is null Then ' Total ' + isnull (' on ' +cast (dd. PROJECTCODE as Varchar (3)), ") + ': ' end as x_project;

9

Re: How to add a line

katish444;
- dd. PROJECTCODE a line:

case When row_number () over (partition by dd.projectcode order by dd.holeID) = 1 then dd. PROJECTCODE
When Hole_ID is null Then ' Total ' + isnull (' on ' +dd. PROJECTCODE, ") + ': ' end as x_project;

10

Re: How to add a line

katish444;
Like not at a forum, and it is clear to ask the first day and did not learn.... Why for the answer to your question we should set to you from tens specifying questions? Can already time came to read point 6 and all questions connected to the help in compilation of requests, to set according to requirements?

11

Re: How to add a line

  - Eh wrote:

katish444;
Like not at a forum, and it is clear to ask the first day and did not learn.... Why for the answer to your question we should set to you from tens specifying questions? Can already time came to read point 6 and all questions connected to the help in compilation of requests, to set according to requirements?

So this time all like is clear, unless is not present?

12

Re: How to add a line

Kopelly wrote:

katish444;
- dd. PROJECTCODE a line:

case When row_number () over (partition by dd.projectcode order by dd.holeID) = 1 then dd. PROJECTCODE
When Hole_ID is null Then ' Total ' + isnull (' on ' +dd. PROJECTCODE, ") + ': ' end as x_project;

I thank. Now I will try

13

Re: How to add a line

wrote:

katish444;
Here so it is necessary to you?
[CSV=t] x_projecttstss
KEt187t342.5
KEt155.5t342.5
MAt129t165.5
MAt36.5t165.5

Not, so at me also it turns out. And it is necessary to me that KE it was not doubled, and it was added

14

Re: How to add a line

Kopelly wrote:

katish444;
- dd. PROJECTCODE a line:

case When row_number () over (partition by dd.projectcode order by dd.holeID) = 1 then dd. PROJECTCODE
When Hole_ID is null Then ' Total ' + isnull (' on ' +dd. PROJECTCODE, ") + ': ' end as x_project;

It is necessary to me that it added all meters under the project, and it divides them into slits

15

Re: How to add a line

wrote:

katish444;
Here so it is necessary to you?
[CSV=t] x_projecttstss
KEt187t342.5
KEt155.5t342.5
MAt129t165.5
MAt36.5t165.5

Though wait a moment if to hide column S column SS is that that is necessary
Prompt, please, what method you of it reached

16

Re: How to add a line

TaPaK wrote:

katish444;
You  like all under SSRS saw, he is able to add to group and , what for this account programming on TSQL

You so much not clear words told....

17

Re: How to add a line

katish444;
Draw (though in Excel) that it is necessary for you on an output.

18

Re: How to add a line

Kopelly wrote:

katish444;
Draw (though in Excel) that it is necessary for you on an output.

19

Re: How to add a line

Kopelly;
That it added depths on one 5 i.e. while project , it adds, then adds project  etc.

20

Re: How to add a line

katish444;
Eliminate HOLE_ID from Group by!!!

21

Re: How to add a line

katish444;

select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as s
from drillingdetail dd
where dd.holeid in (select holeid from DRILLINGDETAIL
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017 ')
and cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE

22

Re: How to add a line

Even is easier:

select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as s
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE

23

Re: How to add a line

katish444 wrote:

so this time all like is clear, unless is not present?

if all was clear - that the first answer would contain the decision correct and necessary to you in this topic.... But judging by that the topic "was expanded" - not clearly not only only to me...

24

Re: How to add a line

Kopelly wrote:

Even is easier:

select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as s
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE

Quite the cheese!
I thank