Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, youâ€™ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.
(SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally
FROM (SELECT '14) Total WIA positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '14) Total WIA positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
SELECT '15) WIA Positions remaining to be filled' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
;with grandtotal as (
(SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally
FROM (SELECT '14) Total WIA positions created' AS measure,
Sum(positionsavailable) AS tally1,
Sum(positionsavailable) AS temp_tally
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '14) Total WIA positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
SELECT '15) WIA Positions remaining to be filled' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally,
-Sum(txtworksitejobtitlepositionsnumber) AS temp_tally
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
)
select measure, tally from grandtotal
union
select '16) Positions filled' as measure, sum(temp_tally) as tally from grandtotal
;with grandtotal as (
(SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally
x1.tally1 + x2.tally2 AS temp_tally
FROM (SELECT '14) Total WIA positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '14) Total WIA positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
SELECT '15) WIA Positions remaining to be filled' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally,
-Sum(txtworksitejobtitlepositionsnumber) AS temp_tally
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
)
select measure, tally from grandtotal
union
select '16) Positions filled' as measure, sum(temp_tally) from grandtotal
;with grandtotal as (
(SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally
x1.tally1 + x2.tally2 AS temp_tally
FROM (SELECT '14) Total WIA positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND positionsavailable IS NOT NULL) x1,
(SELECT '14) Total WIA positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
SELECT '15) WIA Positions remaining to be filled' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally,
-Sum(txtworksitejobtitlepositionsnumber) AS temp_tally
FROM jobtitles
WHERE (keyemployerid > 200910201)
)
select measure, tally from grandtotal
union
select '16) Positions filled' as measure, sum(temp_tally) from grandtotal
...the first set of queries...
UNION
...more queries...
UNION
;with grandtotal as (
(SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally
FROM (SELECT '05) Total CSC positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200900000)
AND (keyemployerid < 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '05) Total CSC positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200900000)
AND (keyemployerid < 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
(SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally
FROM (SELECT '14) Total WIA positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '14) Total WIA positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
SELECT '15) WIA Positions remaining to be filled' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally,
-Sum(txtworksitejobtitlepositionsnumber) AS temp_tally
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
)
select measure, tally from grandtotal
union
select '16) Positions filled' as measure, sum(temp_tally) from grandtotal
(SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally
FROM (SELECT '05) Total CSC positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200900000)
AND (keyemployerid < 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '05) Total CSC positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200900000)
AND (keyemployerid < 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
-- From ralmada at Experts-Exchange
;WITH grandtotal
AS ((SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally,
x1.tally1 + x2.tally2 AS temp_tally
FROM (SELECT '14) Total WIA positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '14) Total WIA positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
SELECT '15) WIA Positions remaining to be filled' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally,
-Sum(txtworksitejobtitlepositionsnumber) AS temp_tally
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
)
SELECT measure,
tally
FROM grandtotal
UNION
SELECT '16) Positions filled' AS measure,
Sum(temp_tally)
FROM grandtotal
;WITH grandtotal
AS ((SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally,
x1.tally1 + x2.tally2 AS temp_tally
FROM (SELECT '14) Total WIA positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '14) Total WIA positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
UNION
SELECT '15) WIA Positions remaining to be filled' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally,
-Sum(txtworksitejobtitlepositionsnumber) AS temp_tally
FROM jobtitles
WHERE (keyemployerid > 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
)
(SELECT x1.measure,
x1.tally1 + x2.tally2 AS tally
FROM (SELECT '05) Total CSC positions created' AS measure,
Sum(positionsavailable) AS tally1
FROM jobtitles
WHERE (keyemployerid > 200900000)
AND (keyemployerid < 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NOT NULL) x1,
(SELECT '05) Total CSC positions created' AS measure,
Sum(txtworksitejobtitlepositionsnumber) AS tally2
FROM jobtitles
WHERE (keyemployerid > 200900000)
AND (keyemployerid < 200910201)
AND employername NOT LIKE '%xxx%'
AND (employername NOT LIKE '%xx NONE xx%')
AND (employername NOT LIKE '%zz NONE zz%')
AND positionsavailable IS NULL) x2
WHERE x1.measure = x2.measure)
union
SELECT measure,
tally
FROM grandtotal
UNION
SELECT '16) Positions filled' AS measure,
Sum(temp_tally)
FROM grandtotal
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Problem with duplicate records in Oracle query | 16 | 39 | |
performance query | 4 | 32 | |
Trouble with <> | 2 | 20 | |
RAISERROR WITH NOWAIT | 2 | 14 |
Join the community of 500,000 technology professionals and ask your questions.