Search results for query: *

I have tried the query in this format but have found the performance shocking SELECT C.SERNO, C.DOFF, C.REGNO, C.STATE, C.STATE_DATE, P.DESC1, SUM(CASE WHEN BOX = 'H' THEN TOTAL ELSE 0 END) AS "TOTAL CHARGE", SUM(CASE WHEN BOX = 'P' THEN TOTAL ELSE 0 END) AS "TOTAL PAID"...

Dear All (any help will be greatly appreciated) Database version: SQL 2000 I have created the following query which combines 2 tables containing multiple rows to the table CPCASE which holds unique records. SELECT C.SERNO, C.DOFF, C.REGNO, C.STATE, C.STATE_DATE, P.DESC1, SUM(CASE WHEN...

George Now it makes sense. I think the problem lied in that I though by the case clause was reducing the @begindate by 1. Now I understand that if the date was invalid the case logic set it to B rather than A before then comparing it with the date range. Again many thanks for the help Lewis...

George The only bit I am still unsure about is the @begindate - 1 I'll try to put into words why it still confuses me As the @begindate and @enddate variables are input externally from a crystal report, and are set for example as @begindate = 01/01/2006 @enddate = 31/01/2006 why then, when...

George Any chance you could just explain the case logic a little bit. I understand about using case logic, the bit I am unsure about is setting @begindate -1. I would think that this would just reduce the @begindate by one day and therefore increase the query range by one day. Obviously it...

George This was very useful. With a little tinkering with the 2 lines below - changing the 101 to 103 to works. SELECT @BeginDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_mindate, 101)) SELECT @EndDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_maxdate, 101)) Many many thanks for your help...

George thanks for the response! i'll give it a try when i'm in the office tomorrow morning. thanks Lewis United Kingdom

Just to let you know that I am currently trying to sort it using the set dateformat and isdate functions. For info the query that I posted is part of a stored procedure which is used by used by a crystal report to extract management info on performance. Full SP below CREATE PROCEDURE...

Thank for you comments so fast. To explain why we have LRD and the date added to the CHAR field. We need to record the date which is stamped onto an envelope when correspondance is sent into our office. We then scan the item to our database. We do not have the ability to enter the date...

I have the following query that I have written for a database running on SQL 7. The query relies on the users inserting a specific string into a memo field within the application which is stored in a CHAR field in the database. What they enter is LRD dd/mm/yyyy unfortunately this...

The situation I have is that our end user system does not always correctly update the sequence number within a names table. We often experience the following; A user will add name details against the main case details screen within our system. This means that the name details are added to a...

Synapsevampire Thanks for the suggestion. Normally I do not use dictionaries. I have created this one because someone in the ultimate wisdom has deemed that I should train several supervisors in my department to use Crystal Reports. Given that there abilities of writting reports is somewhat...

Kutoose This might not be an issue any more but I have just been looking at dictionaries and found that you can pull dates into Crystal Reports as dates. To do this open the dictionary and goto FILE/OPTIONS. In options select the 'SQL OPTIONS'. At the bottom you should see 'Convert...

Synapsevampire Thanks for the above. I have taken a look at the FAQ and this will be very helpful. Thanks Lewis United Kingdom

rhinok Thanks for the advice. This has now quickened my report a great deal. Now i'll just go away and study how IIF is different to IF statements. Thanks again Lewis United Kingdom

I am trying to pass the following to the SQL v7 server IF CurrentDate > date(year(currentdate),04,01) then {CPCASE.DOFF} in date(year(currentdate),04,01) to date(year(currentdate)+1,03,31) else {CPCASE.DOFF} in date(year(currentdate)-1,04,01) to date(year(currentdate),03,31) {CPCASE.DOFF}...

Not sure if this is the right Forum for this but I'll put it out into the playing field anyway. I have been tasked with providing basic Crystal Reports training to about 5 supervisors within my department (it’s been highlighted by a consultant that no-body else really understands what I do with...

Jim Great tip for the @README formula!!!!! I think I shall be using that in the future.... :-) Lewis United Kingdom

mondi From what you have said you have your tables linked so that <TRANSPORTS> links on <TRANSPORTS.TRANSPORT_CODE> to <TRANSPORT_SENDER> You have then said that <TRANSPORTS> I assume this should be <TRANSPORT_SENDER> is linked to <SENDERS>. As long as this is true then there will...

SQLBill I have changed the query to DECLARE @mystring VARCHAR(5) SET @mystring = 'ab''cd' SELECT @mystring SELECT PATINDEX('%''%', @mystring) This works as you described. This gives me what i need. Thanks for your help. Lewis United Kingdom