Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
 Posted - 2014-02-10 : 17:31:20
|
| IN this sql we are looking for subst_instructions from the formulary table. we are getting only 1 or 2 of these, even though there should be others with same med in the first table:select * from (SELECT ID_KEY, [BATCH] AS column1, [IMPORTDATE], [DATEBILLED], [RX], [DATEDISPENSED], [DAYSUPPLY], [PAYTYPE], [NPI], [PHYSICIAN], [COST], [QUANTITY], [MEDICATION], A.[NDC], [PATIENTNAME], [ROUTEOFADMIN], [INVOICECAT], [COPAY], [BRAND], [TIER], [SKILLLEVEL], [STAT] STATUS, [LASTTASKDATE],SEQNO,B.[SUBST_INSTRUCTIONS], row_number() over(partition by ID_KEY order by ID_KEY) rn FROM [PBM].[T_CHARGES] A LEFT OUTER JOIN [OGEN].[NDC_M_FORMULARY] B ON A.[NDC] = B.[NDC] Where [STAT] not in (3, 4) AND [TIER] <> 'T1' )a where rn = 1 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-10 : 17:41:27
|
| You need to query the first table and see where it's "failing" for your where clause.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2014-02-10 : 17:51:24
|
yes but failure is to the second table, we are not getting all the substitute_instructions/quote:
Originally posted by tkizer
You need to query the first table and see where it's "failing" for your where clause.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-10 : 19:11:40
|
| You'll need to provide some sample data for us to help.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-11 : 01:19:13
|
quote:
Originally posted by AdamWest
IN this sql we are looking for subst_instructions from the formulary table. we are getting only 1 or 2 of these, even though there should be others with same med in the first table:select * from (SELECT ID_KEY, [BATCH] AS column1, [IMPORTDATE], [DATEBILLED], [RX], [DATEDISPENSED], [DAYSUPPLY], [PAYTYPE], [NPI], [PHYSICIAN], [COST], [QUANTITY], [MEDICATION], A.[NDC], [PATIENTNAME], [ROUTEOFADMIN], [INVOICECAT], [COPAY], [BRAND], [TIER], [SKILLLEVEL], [STAT] STATUS, [LASTTASKDATE],SEQNO,B.[SUBST_INSTRUCTIONS], row_number() over(partition by ID_KEY order by ID_KEY) rn FROM [PBM].[T_CHARGES] A LEFT OUTER JOIN [OGEN].[NDC_M_FORMULARY] B ON A.[NDC] = B.[NDC] Where AND [STAT] not in (3, 4) AND [TIER] <> 'T1' )a where rn = 1
try this tweak and see if it helpsif not, then post some sample data as suggested by Tara------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
the sql only returns some from the join table