May 28, 2020 at 3:22 pm
select
count (H.Customs_Entry_Num)
,count(L.Customs_Entry_Num)
FROM ADHOC.ATS_ESH H -- this table has only unique Entry Numbers
INNER JOIN adhoc.ATS_ESL L -- this table is the child table (line level)
-- ESH has a 1:many relationship with ESL
ON h.TRANS_SK = l.TRANS_SK
WHERE
H.Importer = 'XXXX'
and H.Entry_Summary_Date > '4/1/2020'
I want the total records from ESH and the total number of records from ESL.
There are generally 5 rows in ESL per unique entry number in ESH.
thanks
May 28, 2020 at 3:33 pm
Your query will never get total record counts for the tables because you are filtering out rows in the WHERE clause before the COUNT is applied..
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 28, 2020 at 3:36 pm
the tables contain more than 1 Importer and records go back years. I only want the row counts for the filtered results.
May 28, 2020 at 3:38 pm
Have you tried using COUNT(DISTINCT col)?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 29, 2020 at 3:40 pm
I'm not 100% sure of what you need, but I think the total from the first table is being overstated. If so, then maybe this:
select
count (H.Customs_Entry_Num)
,max(L.[Child_Customs_Entry_Num_Count]) as [Child_Customs_Entry_Num_Count]
FROM ADHOC.ATS_ESH H
CROSS APPLY (
SELECT COUNT(Customs_Entry_Num) AS [Child_Customs_Entry_Num_Count]
FROM adhoc.ATS_ESL L
WHERE L.TRANS_SK = H.TRANS_SK
) AS L
WHERE
H.Importer = 'XXXX'
and H.Entry_Summary_Date > '20200401'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 29, 2020 at 6:01 pm
edit: never mind, don't think it would work after all.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply