Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

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

Open in new window

SQLOutput.xls
0
chokka
Asked:
chokka
1 Solution
 
Anthony PerkinsCommented:
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 

Open in new window

0
 
chokkaAuthor Commented:
Perfect, Excellent - Timely Help !! Thank you
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now