SQL: select published/working/active/inactive ID's
Hello experts,
I have the following query:
Vesion = 1 means working status
Version = 0 means published status
Active=1 means active ID
Active =0 means inactive ID
I would like to perform 2 Select queries in order to identify
1-all the Active ID's which have a published status and it working status is inactive.
This means that the published is Active and the working status is Inactive.
2-All the ID which have published status without having a working status. The working status doesn't exist in the table.
If you have questions, don't hesitate to contact.
Thank you very much for your help.
I have the following query:
Select ID,
[Version],
[Active]
from mstt_schema.Project
Order by [ID]Vesion = 1 means working status
Version = 0 means published status
Active=1 means active ID
Active =0 means inactive ID
I would like to perform 2 Select queries in order to identify
1-all the Active ID's which have a published status and it working status is inactive.
This means that the published is Active and the working status is Inactive.
2-All the ID which have published status without having a working status. The working status doesn't exist in the table.
If you have questions, don't hesitate to contact.
Thank you very much for your help.
Pawan Kumar
Give me expected output for both the queries..?
ASKER
First query:
ID;Version;Active
toto;1;0
toto;0;1
toto: should be part of the first query as published status is active and working status is inactive.
Second query:
ID;Version;Active
tata;0;1
titi;0;1
titi;1;0
tata should be part of the second query as tata just exist with a published status and an active version (working status doesn't exist).
titi shouldn't be part of any of the expected queries.
ID;Version;Active
toto;1;0
toto;0;1
toto: should be part of the first query as published status is active and working status is inactive.
Second query:
ID;Version;Active
tata;0;1
titi;0;1
titi;1;0
tata should be part of the second query as tata just exist with a published status and an active version (working status doesn't exist).
titi shouldn't be part of any of the expected queries.
Reading via a phone so its awkward but you just need a where clause that chooses the wanted combination of values. Like these:
-- query1
Select ID,
[Version],
[Active]
from mstt_schema.Project
Where [version] = 0
And [active] = 1
Order by [ID]
;
-- query 2
Select ID,
[Version],
[Active]
from mstt_schema.Project
Where [version] = 1
And [active] = 0
Order by [ID]
;
-- query1
Select ID,
[Version],
[Active]
from mstt_schema.Project
Where [version] = 0
And [active] = 1
Order by [ID]
;
-- query 2
Select ID,
[Version],
[Active]
from mstt_schema.Project
Where [version] = 1
And [active] = 0
Order by [ID]
;
The working status doesn't exist in the table.So how do we know the working status?
ASKER
We know the working status as there is just one ID listed in the table which Version = 0 and not it equivalent with a Version = 1.
Better you provide sample with correct data and not tata, tete, titi, toto.
From the provided sample in the main question which rows should be returned by Query1 and which ones by Query2?
From the provided sample in the main question which rows should be returned by Query1 and which ones by Query2?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you provide some data and expected output. ?
I think you want to use MAX() OVER() to get the maximum of those Version and Active columns for each "id". With this technique you get all the relevant complete source rows
Additionally, provide the "expected result, which should relate directly to that sample data.
select
*
from (
select
*
, max(version) over(partition by id) max_version
, max(active) over(partition by id) max_active
from @project
) d
where max_version = 1
and max_active = 1
row id version active max_version max_active
--- -------- ------- ------ ----------- ----------
403 14DD8UM 0 1 1 1
404 14DD8UM 1 1 1 1
405 14DD8UNE 0 1 1 1
406 14DD8UNE 1 1 1 1
409 14DD8UNH 0 1 1 1
410 14DD8UNH 1 1 1 1
413 14E225S 0 1 1 1
414 14E225S 1 0 1 1 select
*
from (
select
*
, max(version) over(partition by id) max_version
, max(active) over(partition by id) max_active
from @project
) d
where max_version = 1
and max_active = 0
row id version active max_version max_active
--- -------- ------- ------ ----------- ----------
407 14DD8UNF 0 0 1 0
408 14DD8UNF 1 0 1 0
411 14DD8UP 0 0 1 0
412 14DD8UP 1 0 1 0 declare @Project table
([row] int, [id] varchar(8), [version] int, [active] int)
;
INSERT INTO @Project
([row], [id], [version], [active])
VALUES
(403, '14DD8UM', 0, 1),
(404, '14DD8UM', 1, 1),
(405, '14DD8UNE', 0, 1),
(406, '14DD8UNE', 1, 1),
(407, '14DD8UNF', 0, 0),
(408, '14DD8UNF', 1, 0),
(409, '14DD8UNH', 0, 1),
(410, '14DD8UNH', 1, 1),
(411, '14DD8UP', 0, 0),
(412, '14DD8UP', 1, 0),
(413, '14E225S', 0, 1),
(414, '14E225S', 1, 0)
;Additionally, provide the "expected result, which should relate directly to that sample data.
ASKER
Hello Experts,
I am really sorry for the delay.
I added a dummy file with required data.
I highlighted in orange the values which should be displayed through the first request and in red the one related to the second query.
28977649_Published_Working_Version.xlsx
I am really sorry for the delay.
I added a dummy file with required data.
I highlighted in orange the values which should be displayed through the first request and in red the one related to the second query.
28977649_Published_Working_Version.xlsx
ASKER
I tested the query reported at:
https://www.experts-exchange.com/questions/28977649/SQL-select-published-working-active-inactive-ID's.html?anchorAnswerId=41852509#a41852509 and it works!
@PortletPaul: your proposal don't give the expected result with the dummy file that I attached in my previous comment.
I will keep the question opened if necessary.
https://www.experts-exchange.com/questions/28977649/SQL-select-published-working-active-inactive-ID's.html?anchorAnswerId=41852509#a41852509 and it works!
@PortletPaul: your proposal don't give the expected result with the dummy file that I attached in my previous comment.
I will keep the question opened if necessary.
Can you explain the logic again for Query 2 ?
Solution for Query 2 ...pls verify
--
SELECT id FROM ids v1
CROSS APPLY
(
SELECT COUNT(*) cnt1
FROM Ids v2
WHERE v1.id = v2.id
AND v2.version = 0
)u
CROSS APPLY
(
SELECT COUNT(*) cnt2
FROM Ids v2
WHERE v1.id = v2.id
AND v2.version = 1
)u1
CROSS APPLY
(
SELECT COUNT(*) cnt3
FROM Ids v2
WHERE v1.id = v2.id
AND v2.Active = 0
)u2
CROSS APPLY
(
SELECT COUNT(*) cnt4
FROM Ids v2
WHERE v1.id = v2.id
AND v2.Active = 1
)u3
WHERE ( u.cnt1 = 1 AND u1.cnt2 = 0 ) AND (u2.cnt3 = 0 AND u3.cnt4 = 1)
--
Solution for Query 1, You may have to change code for 476H2F4, CiZV04
Hope it helps. !!
SELECT id FROM ids v1
CROSS APPLY
(
SELECT COUNT(*) cnt1
FROM Ids v2
WHERE v1.id = v2.id
AND v2.version = 0
)u
CROSS APPLY
(
SELECT COUNT(*) cnt2
FROM Ids v2
WHERE v1.id = v2.id
AND v2.version = 1
)u1
CROSS APPLY
(
SELECT COUNT(*) cnt3
FROM Ids v2
WHERE v1.id = v2.id
AND v2.Active = 0
)u2
CROSS APPLY
(
SELECT COUNT(*) cnt4
FROM Ids v2
WHERE v1.id = v2.id
AND v2.Active = 1
)u3
WHERE ( u.cnt1 = 1 AND u1.cnt2 = 1 ) AND ( u2.cnt3 = 1 AND u3.cnt4 = 1 )Hope it helps. !!
If Scott's solution produces the expected result then please award the points and close. I was guessing you wanted full original rows.
Pawan. You would need to convince me that 3 cross applys work better than Scott's approach.
Pawan. You would need to convince me that 3 cross applys work better than Scott's approach.
ASKER
Thank you all for your help!
@Paul Sir - No problem sir. Actually the author posted the data and the expected output yesterday so I started solving it. Later I got to know that It was already solved. :)
Bye !!
Bye !!