Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. sewilj

    Query problem with multiple rows

    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"...
  2. sewilj

    Query problem with multiple rows

    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...
  3. sewilj

    Pre-validation of date before conversion within a script

    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...
  4. sewilj

    Pre-validation of date before conversion within a script

    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...
  5. sewilj

    Pre-validation of date before conversion within a script

    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...
  6. sewilj

    Pre-validation of date before conversion within a script

    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...
  7. sewilj

    Pre-validation of date before conversion within a script

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

    Pre-validation of date before conversion within a script

    ...Full SP below CREATE PROCEDURE [DBO].LRD_Date @LRD_mindate datetime, @LRD_maxdate datetime as declare @BeginDate DATETIME, @EndDate DATETIME /* "Chop off" the times passed from Crystal */ SELECT @BeginDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_mindate, 101)) SELECT @EndDate =...
  9. sewilj

    Pre-validation of date before conversion within a script

    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...
  10. sewilj

    Pre-validation of date before conversion within a script

    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...
  11. sewilj

    Problem with trigger not updating properly

    ...having difficulties resolving this issue I have tried to create a trigger that would correct the sequence number if incorect. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRG_CPNAME_SEQUENCE]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) drop trigger...
  12. sewilj

    Date field appears as string...!!!

    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...
  13. sewilj

    Date field appears as string...!!!

    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...
  14. sewilj

    How do I pass a date range to the server?

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

    How do I pass a date range to the server?

    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
  16. sewilj

    How do I pass a date range to the server?

    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}...
  17. sewilj

    Crystal Reports Basic Training

    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...
  18. sewilj

    % Change Formula

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

    I have a problem like this: The da

    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...
  20. sewilj

    Can you search for an apostrophe in a text string

    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

Part and Inventory Search

Back
Top