I need to return 6 records even if there are less

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

I need to return 6 records even if there are less

Author

Topic

Jbalbo
Starting Member

10 Posts

Posted - 2012-06-04 : 14:11:00

HI,
I have the following sql, which someone else did, but what it does is return the top 6 records. Some clients only have 1, 2 3 up to 10 records I want to fill in 6 fields evenif they only have say.. 4
example:
1st record is med1
2nd record is med2
3rd record is med3
4th record is med4
5th record is null
6th record is null

Right now it just returns 4 records...
Thanks IN advance

iNSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TOP 6 TAG_NAME = 'CUST_EXTMEDS', TAG_DATA = EM.OID + '~' + ISNULL(SI.SERVICE_ITEM_DESCRIPTION,EM.FullName) + '~' + '' + '~' + EM.EXT_MED_DOSE + '; ' + EM.EXT_MED_ROUTE + '; ' + EM.EXT_MED_FREQUENCY + '~' + '' + '~' + dbo.usi_fnGetUDF_usi('MSDP-013',EM.OID) + '~' + dbo.usi_fnGetUDF_usi('MSDP-014',EM.OID)
FROM CLIENTAGENCY_TO_CLIENT_EXTERNAL_MEDICATION_COLLECTION C (NOLOCK)
INNER JOIN CLIENT_EXTERNAL_MEDICATION EM (NOLOCK) ON C.OID_LINK = EM.OID
INNER JOIN SERVICE_ITEM SI (NOLOCK) ON EM.SERVICE_ITEM_MONIKER = SI.OID
WHERE C.OID = @CA_OID
AND EM.Expdate IS NULL
ORDER BY EM.EffDate DESC ;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 14:52:38

Create a master table for tages and left join your table to it. then take top 6 out of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources