Have SQL code refer to cells in worksheet

All2Cheesy

Board Regular
Joined
Mar 4, 2015
Messages
127
Hello all,

I'm looking to enter the below query from a sql server database. However, in the Enter Connotes section, rather than entering in a list of connotes into the code, I would like for it to refer to a list of connotes in the excel workbook.

I would appreciate it if someone could advise me on the best way to go about this. Thanks in advance.

SQL:
SELECT

REPLACE(REPLACE(IV.fldFreightMode,2,'BEB'),4,'BEP') AS ServiceType 
,IV.fldInvoiceNumber AS ConNoteNumber
,'' AS ReceiverCode
,ISNULL(IV.fldSenderReference,'') AS SenderReference
,ISNULL(IV.fldReceiverReference,'') AS ReceiverReference

,ISNULL(ISNULL(AG.fldTradingName, AG.fldLegalName), RC.[Sending Centre]) AS fldOnfwdRcvBranch
,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(IV.fldReceiverName ,''),CHAR(13)+CHAR(10),''),CHAR(9),''),CHAR(10),''),CHAR(13),''),'"','') AS Receiver
,RS.Suburb AS ReceiverSuburb
,IV.fldReceiverState AS ReceiverState
,IV.fldReceiverPostcode AS ReceiverPostcode

,CONVERT(DATE,IV.fldInvoiceDate,103) AS DespatchDate
,CONVERT(DATE,IV.fldETADate,103) AS ETADate
,'' AS ReleaseDate -- @ReleaseDate AS ReleaseDate
,ISNULL((SELECT CONVERT(NVARCHAR(10),MAX(POD.fldDateSigned),103) FROM TS2000.vwPOD POD WITH(NOLOCK) WHERE POD.fldInvoiceNumber = IV.fldInvoiceNumber),'') AS DeliveryDate

,ISNULL((SELECT TOP 1 fldNameSigned FROM TS2000.fncPODValidRank(IV.fldInvoiceNumber) WHERE fldNameSigned IS NOT NULL),'') AS ReceivedBy  
,(SELECT (Case When fldBEXOnlineText = 'Delivered' Then 'Complete' Else '' End) FROM TS2000.fncGetConsignmentStatus(IV.pkInvoice)) AS PODStatus
,(SELECT fldBEXOnlineText FROM TS2000.fncGetConsignmentStatus(IV.pkInvoice)) AS DeliveryDesc

FROM TS2000.vwInvoiceValid IV WITH(NOLOCK)
LEFT JOIN TS2000.syn_Suburbs RS WITH(NOLOCK) ON IV.fkReceiverSuburb = RS.ID
LEFT JOIN TS2000.vwAgentDetails AS AG WITH (NOLOCK) ON RS.fkAgent = AG.pkAgentId
LEFT JOIN TS2000.tblSuburbDepot RD WITH (NOLOCK) ON IV.fkReceiverSuburb = RD.fkSuburbID
LEFT JOIN TS2000.[Sending Centres] RC WITH (NOLOCK) ON RD.fkDepotID = RC.fldDepotID

WHERE RD.fldType = 'R' AND RD.fldOrder = 1
AND IV.fldInvoiceNumber IN (

-- Enter Connotes

*Refer to list of connotes in excel*

)

ORDER BY DespatchDate, ConNoteNumber
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,130,245
Messages
5,641,064
Members
417,190
Latest member
Sdwd76

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top