March 21, 2016 at 2:08 pm
I have this script below:
How can I take out the sub-query in this?
select
datepart(yyyy, D.Date)as "Year",
count(*)as "A",
sum(DV.PageCount)as "B"
from
[DB].[dbo].[Test] D with (nolock)
inner join [DB].[dbo].[TestVersion] DV with (nolock)
on D.TestumentID = DV.TestumentID
inner join [DB].[dbo].[TestFragment] DF with (nolock)
on DV.TestumentVersionID = DF.TestumentVersionID
where
D. Date is null and
DF. TypeKy in ('A') and
DV.TestumentVersionID = (select max([TestumentVersionID]) from [DB].[dbo].[TestVersion] DV1 with (nolock)
where DV1.TestumentID = D.TestumentID) and
exists (select 1 from [DB].[dbo].[Link] PL with (nolock)
where PL.TestumentID = D.TestumentID and PL. TypeID = 1)
group by
datepart(yyyy, D.Date)
order by
"Year"
March 21, 2016 at 4:02 pm
select
datepart(yyyy, D.Date)as "Year",
count(*)as "A",
sum(DV.PageCount)as "B"
FROM [DB].[dbo].[Test] D with (nolock)
INNER join (
SELECT TestumentID, TestumentVersionID, PageCount, ROW_NUMBER() OVER(PARTITION BY TestumentID ORDER BY [TestumentVersionID] DESC ) RN
FROM [DB].[dbo].[TestVersion]
) DV with (nolock) on D.TestumentID = DV.TestumentID AND DV.RB = 1
INNER join [DB].[dbo].[TestFragment] DF with (nolock) on DV.TestumentVersionID = DF.TestumentVersionID
WHERE D. Date is null and
DF. TypeKy in ('A') and
exists (select 1 from [DB].[dbo].[Link] PL with (nolock) where PL.TestumentID = D.TestumentID and PL. TypeID = 1)
group by datepart(yyyy, D.Date)
ORDER by "Year"
_____________
Code for TallyGenerator
March 21, 2016 at 8:56 pm
Also maybe this:
SELECT Datepart(yyyy, d.date) AS "Year",
Count(*) AS "A",
Sum(dv.pagecount) AS "B"
from [DB].[dbo].[Test] d WITH (nolock)
INNER JOIN [DB].[dbo].[TestVersion] dv WITH (nolock)
ON d.testumentid = dv.testumentid
INNER JOIN [DB].[dbo].[TestFragment] df WITH (nolock)
ON dv.testumentversionid = df.testumentversionid
CROSS APPLY
(
SELECT TOP 1
testumentversionid
FROM [DB].[dbo].[TestVersion] dv1 WITH (nolock)
WHERE dv1.testumentid = d.testumentid
AND dv1.testumentversionid=dv.testumentversionid
ORDER BY dv1.testumentversionid DESC )t
WHERE d. date IS NULL
AND df. typeky IN ('A')
AND EXISTS
(
SELECT 1
FROM [DB].[dbo].[Link] pl WITH (nolock)
WHERE pl.testumentid = d.testumentid
AND pl. typeid = 1)
GROUP BY datepart(yyyy, d.date)
ORDER BY "Year"
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply