Big Challange

hi all

i need to write a function to replace one field from another field in strode procedure .

as a example

SELECT
profc.SURNAME + ', ' + profc.FORENAME AS ProfCarer,
SPECS.DESCRIPTION AS Speciality,
steams.STEAM_REFNO_NAME AS StaffTeam,
REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,
COUNT(REFS.REFRL_REFNO) AS NoOfOpenReferrals
FROM REFS
LEFT OUTER JOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO
LEFT OUTER JOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO
LEFT OUTER JOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO
INNER JOIN PrimaryCareTrust pct ON REFS.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL

i need to replace REFS.PCT_CODE FROM GEOGAREA.PCT_CODE THIS FUNCTION NEED ONE INPUT PARAMETER CALLED REFS.REFTOPROCA_REFNO

Any Idea
Thank's

I'm a little confused.

You want to translate the REFS.PCT_CODE in order to use it in the joing on PrimaryCareTrust?

|||ya i need to replace refs.pct_code from another cord in all the strode procs|||

OK, what is the the code you want as the replacement?
Where does it come from?

Where does it go within the query?

Please provide more info.

|||the code is GEOGAREA.PCT_CODE its from GeographicArea table
it just replase that REFS.PCT_CODE
|||

This doesn't make sense, but anyway:

Code Snippet

SELECT

profc.SURNAME +', '+ profc.FORENAME AS ProfCarer,

SPECS.DESCRIPTION AS Speciality,

steams.STEAM_REFNO_NAME AS StaffTeam,

REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,

COUNT(REFS.REFRL_REFNO)AS NoOfOpenReferrals

FROM REFS

INNERJOIN GEOGAREA

ON REFS.PCT_CODE = GEOGAREA.PCT_CODE

LEFTOUTERJOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO

LEFTOUTERJOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO

LEFTOUTERJOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO

INNERJOIN PrimaryCareTrust pct ON GEOGAREA.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL

|||ya i know this mate. i have lots of procedures so what i want to do is i need create a function that i execute that it'll go and search that REFS.PCT_CODE in each and every procedures and replace it by GEOGAREA.PCT_CODE

can we do this?|||

You bet.

REFS.PCT_CODE is the input

GEOGAREA.PCT_CODE is the output

Now, how are REFS and GEOGAREA related?
How does REFS.PCT_CODE find the correct entry in GEOGAREA?

|||

Spend a little bit time to change your quires(even it is on multiple stored procedures).

If you use function it may decrease the performance.

|||PCT_CODE STANDS geographic area code this are same but refs.pct_code is going to be change thats why.|||

Niranga,

Please provide some details and specifics.

What is the current DDL/schema?

How is it changing?

Provide some sample table data and your expected results.

Between Dates Query - HELP!

Hi,

I have an SQl query that will list the results if it lies between two dates, for example;

SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2

This returns all results where Date1 and Date2 fall between the Current Date

What i am looking for is a way to replace the { fn Now() } with a date of my choice.

For example;

SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2

However this does not work.

Has anyone any ideas why this may be ??

Thanks in advance

Andrew Vint

try to use an sql server variable and assign it with the date u want and then put it in ur select statement

|||

Hi,

I am afraid that does not work either.

Thanks

|||

You could use a variable in the SQL query. If it didnt work for you please post the code you have.

|||Are you sure you want to put a constant on the left while 2 fields on the right? Then you have to make sure Date2 is greater than Date1, otherwise the expression will return false no matter what's the constant. Another thing you need to pay attention to is the DATEFORMAT option of current user (which can be checked using DBCC USEROPTIONS), you can useSET DATEFORMAT command to change the current date format, or always use the ODBC date format yyyy-mm-dd.|||

Hi,

Thanks for the response.

I have tried using the yyyy-mm-dd format without sucess i am afraid.

Any more ideas anyone ?

Andrew

|||

I was able to run your query without a problem on SQL 2005. The query below also runs.

SELECT * FROM myTable
WHERE cast('9/1/2006' as datetime) BETWEEN Date1 AND Date2

What are the data types for your Date1 and Date2 fields? I assume they are either datetime or smalldatetime, right? Could the problem be that they are bigint fields which store ticks instead of the actual datetime? Or are they char/varchar fields that are storing the date as a string?

Can you describe the problem you have? Do you get an error message? Do you get zero results or results that seemingly should not be returned?

|||That's really strange. It would help if we can repro your issue. Can you post some sample data from the table you're trying to query, as well as the result you expect to get, and the actual result the query returns?|||

Hi All,

Thank you for your assitance in this but after hours of staring at the same lines of query strings it has become clear that it was staring me in the face all along.

I had a date convert on the date2, because of this it was not working. As soon as i removed the convert it worked as i had expected it too.

Now before you all deservedly laugh at me please look at what i had to work with below;

strSQLHoliday = "SELECT TBL_Holidays.AgentRef, TBL_Holidays.EmpVacatID, " _
& "TBL_Agents.LastName + ', ' + TBL_Agents.FirstName AS FullName, " _
& "TBL_Teams.TeamName, CONVERT(varchar(12), TBL_Holidays.ActivityDate, 106) " _
& "AS ActDate, CONVERT(varchar(12), (DATEADD(d, CASE DATEPART(dw, ActivityDate) " _
& "WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate) + " _
& "(DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN " _
& "2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) - 2 + " _
& "CAST(TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs / 5) AS Int)) % 5 " _
& "+ (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN " _
& "2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) - 2 + " _
& "CAST(TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs / 5) AS Int)) " _
& "/ 5 * 7) - (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) " _
& "WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) " _
& "- 2), 106) AS ReturnDate, TBL_Agents.ContractHrs / 5 AS DailyHrs, " _
& "TBL_Holidays.TimeTaken, TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs " _
& "/ 5) AS DaysTaken, TBL_Holidays.Status, TBL_Holidays.TheNote FROM " _
& "TBL_Holidays INNER JOIN TBL_Agents ON TBL_Holidays.AgentRef = " _
& "TBL_Agents.AgentRef INNER JOIN TBL_TeamsTracker ON TBL_Agents.AgentRef = " _
& "TBL_TeamsTracker.AgentRef INNER JOIN TBL_Teams ON TBL_TeamsTracker.TeamRef " _
& "= TBL_Teams.TeamRef WHERE (TBL_TeamsTracker.EndDate IS NULL) AND " _
& "('" & ChosenDate & "' BETWEEN TBL_Holidays.ActivityDate AND (DATEADD(d, CASE " _
& "DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate) + (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, " _
& "ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate)) - 2 + CAST(TBL_Holidays.TimeTaken / " _
& "(TBL_Agents.ContractHrs / 5) AS Int)) % 5 + (DATEPART(dw, DATEADD(d, " _
& "CASE DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate)) - 2 + CAST(TBL_Holidays.TimeTaken / " _
& "(TBL_Agents.ContractHrs / 5) AS Int)) / 5 * 7) - (DATEPART(dw, " _
& "DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 " _
& "ELSE 0 END, TBL_Holidays.ActivityDate)) - 2)) AND " _
& "(TBL_Agents.Location = N'" & lblLocation.Text & "') " _
& "ORDER BY TBL_Holidays.ActivityDate DESC, TBL_Agents.LastName, " _
& "TBL_Holidays.Status"

I thank you all for taking the time to assist me with this matter, your comments did make me look more closely and as a result stumble across the solution.

I have now come to the conlcusion that 10 cups of coffee are no substitute for a good nights sleep when trying to code stuff :)

Thanks Again

Andrew