1

Topic: How to count total length?

Children, prompt please how to count the general  the drilled meters, it is unimportant on what slit.
There is such table

declare @assay table (sampleid varchar (255), projectcode varchar (255), loaddate varchar (255), sampfrom float, sampto float, [value3] float)
insert into @assay (sampleid, projectcode, loaddate, sampfrom, [sampto], [value3]) values (' UZ-001 ', ' uz ', ' 2017-02-10 17:57:55.707 ', 5, 10,22)
insert into @assay (sampleid, projectcode, loaddate, sampfrom, [sampto], [value3]) values (' UZ-001 ', ' uz ', ' 2017-02-17 17:57:55.707 ', 10, 22,32)
insert into @assay (sampleid, projectcode, loaddate, sampfrom, [sampto], [value3]) values (' UZ-001 ', ' uz ', ' 2017-02-17 17:57:55.707 ', 50, 52,32)
insert into @assay (sampleid, projectcode, loaddate, sampfrom, [sampto], [value3]) values (' uz-002 ', ' uz ', ' 2017-02-17 17:57:55.707 ', 0, 40,100)
insert into @assay (sampleid, projectcode, loaddate, sampfrom, [sampto], [value3]) values (' uz-002 ', ' uz ', ' 2017-02-17 17:57:55.707 ', 50, 80,100)
select * from @assay where cast ([LOADDATE] as date) between ' 13-feb-2017 ' and ' 20-oct-2017'

I need to receive
UZ and 122 (Total length)
That is if would be length on a slit all is simple. A minus , but here after all then these all the length long it is necessary to add that and I at all do not know as to be....
That is at first

(max (sampto)-min (SAMPFROM))

, and then for each slit it is all to add...
As it to implement I do not represent
It seems at me curve hands...

2

Re: How to count total length?

katish444;

select
Slit
,sum (length)
from
(select (max (sampto)-min (SAMPFROM)) as length
....)
as t
group by a slit

3

Re: How to count total length?

StarikNavy wrote:

katish444;

select
Slit
,sum (length)
from
(select (max (sampto)-min (SAMPFROM)) as length
....)
as t
group by a slit

So for each slit? And if I do not know how many them will be?

4

Re: How to count total length?

katish444 wrote:

it is passed...
So for each slit? And if I do not know how many them will be?

You of anything also do not need to know, for you the table of slits "knows"...

5

Re: How to count total length?

katish444 wrote:

Children, prompt please how to count the general  the drilled meters, it is unimportant on what slit.
(...)
It seems at me curve hands...

The girl, you write not about the hands, and about the photo in a profile. Estimating curvature of your hands and other parts of a body, visitors is mandatory help you and explain, and very quickly and willingly.

6

Re: How to count total length?

katish444 wrote:

That is if would be length on a slit all is simple. A minus , but here after all then these all the length long it is necessary to add that and I at all do not know as to be....
That is at first

(max (sampto)-min (SAMPFROM))

, and then for each slit it is all to add...

Already after all all painted:

With [At first] as
(Select projectcode, max (sampto)-min (SAMPFROM) as [DlinaUchastkaSkvazhiny] From @assay
where cast ([LOADDATE] as date) between ' 13-feb-2017 ' and ' 20-oct-2017'
Group by sampleid, projectcode)
Select projectcode, sum ([DlinaUchastkaSkvazhiny]) as [] From [At first]
Group by projectcode

7

Re: How to count total length?

Kopelly;
Thanks

8

Re: How to count total length?

Kopelly wrote:

With [At first] as
(Select projectcode, max (sampto)-min (SAMPFROM) as [DlinaUchastkaSkvazhiny] From @assay
where cast ([LOADDATE] as date) between ' 13-feb-2017 ' and ' 20-oct-2017'
Group by sampleid, projectcode)
Select projectcode, sum ([DlinaUchastkaSkvazhiny]) as [] From [At first]
Group by projectcode

Help me to interpose into a script correctly please. Where I only did not interpose and as soon as did not modify - does not work. One underlines that, another. Tried before group by to interpose With.... But did not work...

Select pp. PROJECTCODE, pp. DESCRIPTION;
a. GeolLenght;
b. SendSample, c.assay, TakenSample
From
(
select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as GeolLenght
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) a
full join (
select s. PROJECTCODE as x_project;
count (sd.sampleid) as SendSample
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO left join sample s on s. SAMPLEID=sd. SAMPLEID
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) b on a.x_project =b.x_project
full join
(Select projectcode as x_project, count (sss) assay from (select distinct s.sampleid as sss, PROJECTCODE
from cORPSAMPLEASSAY c left join sample s on s. SAMPLEID = c. SAMPLEID
where cast ([LOADDATE] as date) between ' 2017-02-13 ' and ' 2017-03-10 ') abc
group by abc. PROJECTCODE) c on a.x_project=COALESCE (b.x_project, c.x_project)
full join (
select ss. PROJECTCODE as x_project;
count (ts.sampleid) as TakenSample
from SAMPLEDETAILS ts left join sample ss on ss. SAMPLEID=ts. SAMPLEID
where cast ((case when name = ' SampDateTaken ' then value end) as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by ss. PROJECTCODE
) t on t.x_project=COALESCE (a.x_project, b.x_project, c.x_project)
left join PROJECT pp on pp. PROJECTCODE=c.x_project or pp. PROJECTCODE=b.x_project or pp. PROJECTCODE=a.x_project or pp. PROJECTCODE=t.x_project
group by pp.PROJECTCODE,a.GeolLenght,pp.DESCRIPTION;
b. SendSample, c.assay, TakenSample
order by pp. DESCRIPTION

9

Re: How to count total length?

katish444;
With the coming:

Select pp. PROJECTCODE, pp. DESCRIPTION;
a. GeolLenght;
b. SendSample, c.assay, TakenSample, []
From
PROJECT pp
Left join
(
select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as GeolLenght
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) a on pp. PROJECTCODE=a.x_project
left join (
select s. PROJECTCODE as x_project;
count (sd.sampleid) as SendSample
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO left join sample s on s. SAMPLEID=sd. SAMPLEID
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) b on pp. PROJECTCODE=b.x_project
left join
(Select projectcode as x_project, count (sss) assay from (select distinct s.sampleid as sss, PROJECTCODE
from cORPSAMPLEASSAY c left join sample s on s. SAMPLEID = c. SAMPLEID
where cast ([LOADDATE] as date) between ' 2017-02-13 ' and ' 2017-03-10 ') abc
group by abc. PROJECTCODE
) c on pp. PROJECTCODE=b.x_project
left join (
select ss. PROJECTCODE as x_project;
count (ts.sampleid) as TakenSample
from SAMPLEDETAILS ts left join sample ss on ss. SAMPLEID=ts. SAMPLEID
where cast ((case when name = ' SampDateTaken ' then value end) as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by ss. PROJECTCODE
) t on pp. PROJECTCODE=t.x_project
left join (
Select projectcode as x_project, sum ([DlinaUchastkaSkvazhiny]) as [] From
(Select projectcode, max (sampto)-min (SAMPFROM) as [DlinaUchastkaSkvazhiny] From @assay
where cast ([LOADDATE] as date) between ' 13-feb-2017 ' and ' 20-oct-2017'
Group by sampleid, projectcode) zz
Group by projectcode
) z on pp. PROJECTCODE=z.x_project
Where
a.x_project is not null or
b.x_project is not null or
c.x_project is not null or
x.x_project is not null or
t.x_project is not null or
z.x_project is not null
order by pp. DESCRIPTION

10

Re: How to count total length?

Kopelly, thanks huge
It seems to me it is time to share with you the salary