1

Topic: two requests

Again I... Ridiculous situation Any at me. There are two requests which separately work perfectly but when I try to merge them in one table produce incorrect numbers.
Request 1

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

Request 2

select s. PROJECTCODE as x_project;
count (sd.sampleid) as sss
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

From below the information which they produce separately and that which should be is presented, being in one request. Tried different methods and subqueries and two  to unite - produces any hogwash, digits do not coincide, as I only did not unite them... Digits produces generally the space

2

Re: two requests

katish444;
If I write here so some projects disappear, that connection can not?

select s. PROJECTCODE as x_project, sam.s;
count (sd.sampleid) as sss
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO left join sample s on s. SAMPLEID=sd. SAMPLEID left join (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) sam on sam.x_project=s. PROJECTCODE
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE, sam.s

3

Re: two requests

katish444;
Select connections (... Join) depending on completeness of the data.
In this case the data in the second request more - it is necessary to collect to it.
In your code the second request "on the right" accordingly use Right Join.

4

Re: two requests

katish444;

select s. PROJECTCODE as x_project, sam.s;
count (sd.sampleid) as sss
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO>>> right <<<join sample s on s. SAMPLEID=sd. SAMPLEID left join (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) sam on sam.x_project=s. PROJECTCODE
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE, sam.s

5

Re: two requests

Kopelly wrote:

katish444;

select s. PROJECTCODE as x_project, sam.s;
count (sd.sampleid) as sss
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO>>> right <<<join sample s on s. SAMPLEID=sd. SAMPLEID left join (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) sam on sam.x_project=s. PROJECTCODE
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE, sam.s

It for a specific case? And if at me next time will be on the contrary - in other table many projects, and in this it is not enough?
When I use  or   changes nothing

6

Re: two requests

Kopelly, again you hasten
select s. PROJECTCODE as x_project, sam.s;
count (sd.sampleid) as sss
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO left join sample s on s. SAMPLEID=sd. SAMPLEID>>> right <<<join (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) sam on sam.x_project=s. PROJECTCODE
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE, sam.s

7

Re: two requests

Kopelly;
It for a specific case? And if at me next time will be on the contrary - in other table many projects, and in this it is not enough?
When I use  or   changes nothing

8

Re: two requests

katish444 wrote:

It for a specific case? And if at me next time will be on the contrary - in other table many projects, and in this it is not enough?
When I use  or   changes nothing

If can be more than the data in different places - Full Join:

Select isnull (a.x_project, b.x_project);
a.s;
b.sss
From
(
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
) a
full join (
select s. PROJECTCODE as x_project;
count (sd.sampleid) as sss
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

9

Re: two requests

And  union not?

10

Re: two requests

Kopelly;
Thanks. That is necessary, and I not correctly used

11

Re: two requests

waszkiewicz wrote:

and  union not?

It is possible and through Union, but Full join, in my opinion, is more correct:

Select x_project
sum (s) as s;
sum (sss) as sss
From
(
select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as s;
cast (null as int) as sss
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
union all
select s. PROJECTCODE as x_project;
null;
count (sd.sampleid) as sss
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
) a

12

Re: two requests

Forgot

Group by x_project

13

Re: two requests

Kopelly wrote:

Forgot

Group by x_project

I try to use   to connect with  request, but now at me the project which in first two is not present disappears, and in  it is and the table from below turns out. Can use here another ?

Select isnull (a.x_project, b.x_project) p;
a. GeolLenght;
b. SendSample, c.assay
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, 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=c. PROJECTCODE

14

Re: two requests

katish444, I think in the previous case it would be necessary to understand why the request begins with

Select isnull (a.x_project, b.x_project) p;

Here what for there isnull it is written?

15

Re: two requests

Mr. Fontaine that they in one column registered and were not doubled

16

Re: two requests

Mr. Fontaine wrote:

katish444, I think in the previous case it would be necessary to understand why the request begins with

Select isnull (a.x_project, b.x_project) p;

Here what for there isnull it is written?

Without isnull it turns out so

17

Re: two requests

katish444;
Replace Isnull () on COALESCE (a.x_project, b.x_project, c.x_project)

18

Re: two requests

Kopelly wrote:

katish444;
Replace Isnull () on COALESCE (a.x_project, b.x_project, c.x_project)

Thanks, I already found a way out through . Connected all three by the additional table)))

19

Re: two requests

, the request with UNPIVOT turns out more visually
[spoiler it is careful! Inside a rigid hardcore 27 +]

WITH
s AS (
SELECT
[project] = ' KE ';
[value] = 342.5
UNION ALL
SELECT
[project] = ' MA ';
[value] = 165.5
UNION ALL
SELECT
[project] = ' VA ';
[value] = 330.0
);
sss AS (
SELECT
[project] = ' KE ';
[value] = 237.0
UNION ALL
SELECT
[project] = ' MA ';
[value] = 268.0
);
xxx AS (
SELECT
[project] = ' KE ';
[value] = 369.0
UNION ALL
SELECT
[project] = ' VA ';
[value] = 473.0
);
tbl AS (
SELECT
[project];
[type] = ' s';
[value]
FROM
s
UNION ALL
SELECT
[project];
[type] = ' sss';
[value]
FROM
sss
UNION ALL
SELECT
[project];
[type] = ' xxx ';
[value]
FROM
xxx
)
SELECT
*
FROM
tbl
PIVOT (
MAX ([value]) FOR [type] IN ([sss], [xxx])
) pvt

[/spoiler]