Question Sequence : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26826746.html
I need to SUM of Qty Column which is Group by GenericCode Column. Value of NDC & DrugName has to pick from Default Largest Qty Column. To do this, i have attached SQL Query which thought to do this feature. But it failed.
I have attached an Excel Sheet - SampleDate.xls
In which Tab : CurrentDatabase - which is directly extracted from database by the query
select * from vw_Test3
From the Tab : DerivedFromQuery - is the output which i got from the attached Query.
Tab - SampleExpectedData :- Explains what we expect from the Query.
with p as (
Sum(cast(TR.[Qty] as int)) as Qty
from vw_Test3 TR
Group By TR.[Generic Code], TR.NDC,TR.[Drug Name]
select x.[Generic Code], x.NDC, x.[Drug Name], y.Qty from
select p.*, row_number() over (partition by [Generic Code] order by Qty desc) rn from p
select [Generic Code], sum(cast(Qty as int)) Qty from p group by [Generic Code]
on x.[Generic Code]=y.[Generic Code]