SQL: Getting count from Case when and partition by

Question

Hi this is my code, and I'm having trouble getting the exact pax count for the pax column. I tried using the "date difference" on the partition by but it's causing me error.

SELECT DISTINCT
CONVERT(VARCHAR(6),TF.Issuedate,112) AS "Issue Month",
CASE WHEN (DATEDIFF(day,TF.Issuedate, CPN.FlightDate) + 1) = 0 THEN '<24H'
WHEN ((DATEDIFF(day,TF.Issuedate, CPN.FlightDate) + 1) = 1) THEN '24H - <48H'
WHEN ((DATEDIFF(day,TF.Issuedate, CPN.FlightDate) + 1) = 2) THEN '48H - <72H'
ELSE '72H' END AS "Date_Difference",
CPN.BCC,
COUNT(TF.PrimaryDocNbr) OVER (PARTITION BY FORMAT(TF.Issuedate, 'YYYYMM'), CPN.BCC) AS "Pax Count"

FROM PRODVIEW.V_TKT_TAXESFEES TF  
  
LEFT OUTER JOIN PRODVIEW.V_TKT_COUPON CPN  
	ON TF.PrimaryDocNbr = CPN.PrimaryDocNbr  
	AND TF.Issuedate = CPN.Issuedate  
  
LEFT OUTER JOIN PRODVIEW.V_TKT_TICKET TKT  
	ON TF.PrimaryDocNbr = TKT.PrimaryDocNbr  
	AND TF.Issuedate = TKT.Issuedate  
  
WHERE TF.TaxCode LIKE 'CP%'  
AND TF.IssueDate >= '2022-01-01'  
AND TKT.OrigIssueDocumentNbr IS NOT NULL  
AND TKT.DocumentType = 'TKT'  
  
ORDER BY "Issue Month", "Date_Difference", CPN.BCC  

This is the result and it's repeating the count

Answer 1

Hi @Sherlan Emmanuel Burgonia
Check this untested query:

;WITH CTE AS  
(  
 SELECT CONVERT(VARCHAR(6),TF.Issuedate,112) AS "Issue Month",  
        CASE WHEN (DATEDIFF(day,TF.Issuedate, CPN.FlightDate) + 1) = 0 THEN '<24H'  
             WHEN ((DATEDIFF(day,TF.Issuedate, CPN.FlightDate) + 1) = 1) THEN '24H - <48H'  
             WHEN ((DATEDIFF(day,TF.Issuedate, CPN.FlightDate) + 1) = 2) THEN '48H - <72H'  
             ELSE '72H' END AS "Date_Difference",  
        CPN.BCC AS BCC,  
        TF.PrimaryDocNbr AS PrimaryDocNbr  
 FROM PRODVIEW.V_TKT_TAXESFEES TF  
   LEFT OUTER JOIN PRODVIEW.V_TKT_COUPON CPN ON TF.PrimaryDocNbr = CPN.PrimaryDocNbr AND TF.Issuedate = CPN.Issuedate  
   LEFT OUTER JOIN PRODVIEW.V_TKT_TICKET TKT ON TF.PrimaryDocNbr = TKT.PrimaryDocNbr AND TF.Issuedate = TKT.Issuedate  
 WHERE TF.TaxCode LIKE 'CP%'  
   AND TF.IssueDate >= '2022-01-01'  
   AND TKT.OrigIssueDocumentNbr IS NOT NULL  
   AND TKT.DocumentType = 'TKT'  
)  
SELECT "Issue Month","Date_Difference",BCC,COUNT(PrimaryDocNbr) AS "Pax Count"  
FROM CTE  
GROUP BY "Issue Month",BCC,"Date_Difference"  
ORDER BY "Issue Month", "Date_Difference",BCC  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @LiHongMSFT-4306 , thanks I tried using this script and it works well.