Issue on SQL - User Defined Function

SQL 2008.

I have posted on ID: 26826918, But i am opening the issue again.

I am trying to rewrite the below script in either User Defined Function or View.

I received the response from Post Id :26826918,
Based on the UserDefined Function or View provided from that Post Id,I checked my output with database and realized there is a mismatch in the records.

If you happened to see the Excel Sheet in the Output - Under the NDC Column, i am getting Drug Name and under DrugName i am getting NDC. Both are swapped.
declare @TempTable table  
(  
[Generic Code] nvarchar(1000),
 NDC nvarchar(1000),   
 [Drug Name]	nvarchar(1000),   
 Qty nvarchar(1000)   
)  
  
Insert Into @TempTable ([Generic Code],NDC,[Drug Name],Qty)  
select    
   TR.[Generic Code],  
   TR.NDC,     
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By     TR.[Generic Code],TR.NDC,TR.[Drug Name]
  
Insert Into @TempTable ([Generic Code],NDC,[Drug Name],Qty)   
select Dr.GenericCode as [Generic Code],Dr.Name as [Drug Name],Br.NDC as [NDC],Br.Qty as [Qty] from BalanceReport Br
Inner Join Drug Dr
on 
stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6    when Dr.DrugNDCType = 52 then 10     end, 0, '0') = Br.NDC

  
  

  select [Generic Code],NDC,[Drug Name],Qty from @TempTable

Open in new window

SampleOutput-.xls
UserDefinedFunction.txt
chokkaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LowfatspreadConnect With a Mentor Commented:
so in that previous post you need to correct line 19

select Dr.GenericCode as [Generic Code],Dr.Name as [Drug Name],Br.NDC as [NDC],Br.Qty as [Qty] from BalanceReport Br

and change the order of the select to match the Insert (line 18)

e.g.
select Dr.GenericCode as [Generic Code],Br.NDC as [NDC],Dr.Name as [Drug Name],Br.Qty as [Qty] from BalanceReport Br


I still do not see what/why have it as a view/function not clear why that would help either?
0
 
LowfatspreadCommented:
not sure what you expect...

the problem appears to be in your initial data... sort it out there...
0
 
chokkaAuthor Commented:
I posted this

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26826918.html

Based on this post, i received two different SQL Queries. When i verified the output, i realized that some values are swapped their column.

I have attached the output as Excel Sheet in the above posting.

Expected :- We need to redefined the script in a View or UserDefined Function format - which helps me to execute as SELECT Statement


0
 
chokkaAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.