TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
ahmed elbarbary
642
1.6k
282.2k
How to rewrite query below with best practise way ?
Mar 16 2020 1:50 AM
I work on SQL server 2012 I need to rewrite query with way more best practice and good
for performance
because query below take two much time
What I have tried:
SELECT
Companies.CompanyName [CompanyName],
ISNULL
(Total.FamilyTotal,0) [Total Family
Count
],
ISNULL
(Total.Partstotal, 0) [Total Part
Count
],
ISNULL
(Done.DoneFamily, 0) [Done Family
Count
],
ISNULL
(Done.DoneParts, 0) [Done Part
Count
],
ISNULL
(NotDone.NotDoneFamily, 0) [NotDone Family
Count
],
ISNULL
(NotDone.NotDoneParts, 0) [NotDone Part
Count
]
FROM
(
SELECT
CompanyID,
COUNT
(
DISTINCT
PartsFamilyID) FamilyTotal,
COUNT
(PartID) Partstotal
FROM
Parts.Nop_Part
GROUP
BY
CompanyID
)
AS
Total
LEFT
JOIN
(
SELECT
p.CompanyID,
COUNT
(
DISTINCT
p.PartsFamilyID) DoneFamily,
COUNT
(p.PartID)
as
DoneParts
from
Parts.FamilyIntroductionDate f
inner
JOIN
Parts.Nop_Part p
ON
p.PartsFamilyID = f.FamilyID
GROUP
BY
p.CompanyID
)
AS
Done
ON
Done.CompanyID = Total.CompanyID
LEFT
JOIN
(
SELECT
p.CompanyID,
COUNT
(
DISTINCT
p.PartsFamilyID) NotDoneFamily,
COUNT
(p.PartID)
as
NotDoneParts
from
Parts.Nop_Part p
LEFT
JOIN
Parts.FamilyIntroductionDate f
ON
p.PartsFamilyID = f.FamilyID
WHERE
f.FamilyID
IS
NULL
GROUP
BY
p.CompanyID
)
AS
NotDone
ON
NotDone.CompanyID = Total.CompanyID
INNER
JOIN
(
SELECT
c.CompanyID, C.CompanyName
FROM
Z2DataCompanyManagement.CompanyManagers.Company c
)
AS
Companies
ON
Companies.CompanyID = Total.CompanyID
ORDER
BY
Companies.CompanyName
END
;
Reply
Answers (
1
)
CrystalReport to DataSource using SQL Client Native
How to select data from RequestesEmail table based on two co