1

Topic: SQL request

Guys, greetings;
Help with one request,  brain does not turn on...
There are 2 tables from which it is necessary to generate request by a following principle:
In the first table there are 2 fields:
Inventory:
Inventory. ID_Branch - int
Inventory. InvDate - Datetime
It is a lot of subdividings and at various times on them inventory was led...
And table Storage:
id_branch - int
Data - datetime
Qnty - numeric (12,2)
As to me to transfer in the second subquery lastInvent thus
That all transactions got to a subquery since the maximum date from the first table...
select inv. *, s.* FROM
(
select a.id_branch, max (a. InvDate) as lastInvent from Inventory a Group by a.id_branch
) inv
Left join (select id_branch, sum (qnty) as totQnty from storage where data> = LastDate ) s
ON inv.id_branch = s.id_branch
All thanks in advance...

2

Re: SQL request

Valerii wrote:

Guys, greetings;
Help with one request,  brain does not turn on...
There are 2 tables from which it is necessary to generate request by a following principle:
In the first table there are 2 fields:
Inventory:
Inventory. ID_Branch - int
Inventory. InvDate - Datetime
It is a lot of subdividings and at various times on them inventory was led...
And table Storage:
id_branch - int
Data - datetime
Qnty - numeric (12,2)
As to me to transfer in the second subquery lastInvent thus
That all transactions got to a subquery since the maximum date from the first table...
select inv. *, s.* FROM
(
select a.id_branch, max (a. InvDate) as lastInvent from Inventory a Group by a.id_branch
) inv
Left join (select id_branch, sum (qnty) as totQnty from storage where data> = LastDate ) s
ON inv.id_branch = s.id_branch
All thanks in advance...

Forgot.... SQL 2000!!!!!

3

Re: SQL request

SELECT *
FROM (SELECT ID_Branch, MAX (InvDate) MaxDate
FROM Inventory
) subquery1;
Storage
WHERE subquery1.ID_Branch = Storage. ID_Branch
AND Storage. Data> = subquery1.MaxDate

?

4

Re: SQL request

Akina;
We try....

5

Re: SQL request

Akina;
Most likely does not transit as if there were no movements in storage there are only those on which were and it is incorrect.
It is necessary LEFT JOIN

6

Re: SQL request

Akina;
Without GROUP BY ID_Branch works?

7

Re: SQL request

Valerii , well add, ...

8

Re: SQL request

iap , no, certainly. But so it only idea, accordingly  on a knee.

9

Re: SQL request

Akina wrote:

SELECT *
FROM (SELECT ID_Branch, MAX (InvDate) MaxDate
FROM Inventory
) subquery1;
Storage
WHERE subquery1.ID_Branch = Storage. ID_Branch
AND Storage. Data> = subquery1.MaxDate

?

SELECT *
FROM (SELECT ID_Branch, MAX (InvDate) MaxDate
FROM Inventory
) subquery1;
LEFT JOIN (SELECT id_branch, sum (qnty) AS totQnty FROM Storage where data> = inventory. MaxDate Group by id_branch) Store
ON inventory. ID_Branch = Store. ID_Branch
Something of type of it....

10

Re: SQL request

iap;
No, does not work

11

Re: SQL request

SELECT *
FROM (SELECT ID_Branch, MAX (InvDate) MaxDate
FROM Inventory
GROUP BY ID_Branch/* it has been missed, thanks iap */
) subquery1/*, */
LEFT JOIN
Storage
/* WHERE */ON subquery1.ID_Branch = Storage. ID_Branch
AND Storage. Data> = subquery1.MaxDate

12

Re: SQL request

Valerii wrote:

iap;
No, does not work

Once again I will try to explain....
I need to calculate from 2 tables a data set thus that from the first table there were all subdividings with the last date inventories - that is
id_branch And invDate
I do it simply.
select id_branch, max (invDate) as maxDate from inventory group by id_branch
Id_branch InvDate
3 2017-09-11 07:00:00.000
4 2017-09-11 07:00:00.000
4 2017-09-11 07:00:00.000
4 2017-09-11 07:00:00.000
4 2017-09-11 07:00:00.000
4 2017-09-11 07:00:00.000
4 2017-09-11 07:00:00.000
4 2017-09-11 07:00:00.000
Now I need to make sampling of other table storage  only those amounts which date more or it is equal
invDate from the first sampling:
That is these 2 subqueries need to be spliced somehow that additions got to the second subquery only on a condition data> =inventory.maxDate
I do not know how to transfer MaxDate in  a subquery that was approximately here so:
select a. *, b.* from
(select id_branch, max (invDate) as maxDate from inventory group by id_branch) a
Left Join (select id_branch, sum (qnty) from storage Where data> = a.maxDate )
ON a.id_branch = b.id_branch
WHERE does not approach as at  to transaction on the given subdividing, this subdividing falls out of 1st subquery.
The logic can is initially incorrect... Can eat other methods how to solve such tasks. Once again MS SQL 2000!!!!

13

Re: SQL request

select
a.id_branch. a.maxDate, sum (b.qty)
from
(select id_branch, max (invDate) as maxDate from inventory group by id_branch) a
Left Join storage b on b.id_branch = a.id_branch and b.data> = a.maxDate
group by
a.id_branch. a.maxDate;

14

Re: SQL request

invm wrote:

select
a.id_branch. a.maxDate, sum (b.qty)
from
(select id_branch, max (invDate) as maxDate from inventory group by id_branch) a
Left Join storage b on b.id_branch = a.id_branch and b.data> = a.maxDate
group by
a.id_branch. a.maxDate;

Similar on truth...
. ... Now we look. Truth I already made in another way.... Through -. Heavy according to plan but clear for me.

15

Re: SQL request

Valerii wrote:

it is passed...
Similar on truth...
. ... Now we look. Truth I already made in another way.... Through -. Heavy according to plan but clear for me.

Yes - it!! Thanks!!!
The code less same result...