SQL Query using Coalesce and filtration !!
I have attached a spreadsheet which explains about output of this query and what we expect as output. We need unique NDC records order by Date Fill Desc and SUM(DaysSupply) based on NDC. I assume, it will be easy if an expert can view the output which i attached on the spreadsheet. Simple logic, i am missing the syntax part !!
select FirstName,
LastName,
convert(varchar(10),DateOfBirth,101) as DateOfBirth,
Dayssupply,
NDC,
convert(varchar(10),DateFilled,101) As DateFilled,
1.0 * Coalesce(SUM(dayssupply), 0) AS SumDaysSupply
from voeorderhistorywide
WHERE FirstName = 'ABC'
AND LastName = 'ABC'
AND DateOfBirth = '02/09/2035'
AND Datefilled >= DATEADD(DAY, -90, DATEDIFF(DAY, 0, GETDATE()))
Group by FirstName,LastName,DateOfBirth,
dayssupply,ndc,datefilled
ORDER BY Datefilled DESC
Is this what you mean:
SELECT FirstName,
LastName,
CONVERT(varchar(10), DateOfBirth, 101) AS DateOfBirth,
Dayssupply,
NDC,
CONVERT(varchar(10), MAX(DateFilled), 101) AS DateFilled,
SUM(ISNULL(dayssupply, 0)) AS SumDaysSupply
FROM voeorderhistorywide
WHERE FirstName = 'ABC'
AND LastName = 'ABC'
AND DateOfBirth = '20350209'
AND Datefilled >= DATEADD(DAY, -90, DATEDIFF(DAY, 0, GETDATE()))
GROUP BY FirstName,
LastName,
DateOfBirth,
dayssupply,
ndc
ORDER BY Datefilled DESC
Premium Content
You need an Expert Office subscription to comment.Start Free Trial