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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by codrutza

  1. codrutza

    Concatenate the Results

    The table is Containers. Few records are: JobNumber ContainerNumber 1120339 1120339 TTNU2499140 1 1120423 1120423 ICUU9601717 1120423 ICUU9606365 1120423 CAIU4202803 3 1121260 1121260 MRKU5429434 1121260 MSKU8439227 2 1121739 1121739 HASU5138730 1121739 SUDU6551036 1121739 TCKU6954748 3...
  2. codrutza

    Concatenate the Results

    SAP CR2003 Hello eg I have 4 records in the result: 1.Cont.NumberX 2.Cont.UnitA 3.Cont.UnitB 4.Cont.UnitC and I need the result in a record: 1.Cont.NumberX 2. Cont.UnitA +' '+Cont.UnitB +' '+Cont.UnitC where 2 = a field Please help.
  3. codrutza

    Formula Extracting Numeric from String, with £

    Thank you, LB. It works for £10,000, but stops at some record saying: the string is not numeric and highlights Tonumber(Mid(x,2)). Could you explain the formula, please?
  4. codrutza

    Formula Extracting Numeric from String, with £

    Hello I have a formula: @extract if isnumeric ({FreightJobs.SpecialCharacter06}) then tonumber({FreightJobs.SpecialCharacter06}) else 0; and when it has to extract from £10,000, the 10,000, it gives me 0, so it says that are not numeric in £10,000. The formula works fine for €500, $30,000, ...
  5. codrutza

    Combine Several Select Statements in a SP

    I use SAP Crystal Reports and Microsoft SQL Server Management Studio. I have a select1 which retrieve all the jobs, sales I have another select2 which retrieve only the jobs which have invoices to customs; for taking the invoices to customs, I have to link other tables to the original link; I...
  6. codrutza

    Running Totals On Group Headers

    Hello CR2013 I have running total sales in the Group Footer Year and the running totals for each client. I can't use sum. I know that I can't have the running totals in Group Header, but I have to display the Total sales per year first. Is it a way around this? Could I have within the group...
  7. codrutza

    CTE Missing

    Thank you Boris. Sorted :)
  8. codrutza

    CTE Missing

    I wrote a basic stored procedure from TableJ, that selects all jobs and I developed a report with Crystal Reports which shows the jobs with missing vessels, missing dates,….. Each job has one vessel, one date, … 1. Some jobs with the field value TableJ.car='F' have one or more containers, 2...
  9. codrutza

    2 CTE in one select

    Please help on writing this. I have to combine in one select 2 CTEs: one which count the containers for a job, and one with sum sales for a job. 1. ;WIH Cont_CTE AS ( SELECT CJNo , COUNT(1) AS ContCount FROM Cons GROUP BY CJNo ) 2. ;WIH Sales_CTE AS ( SELECT J.JNo, sum(H.Amount*H. Sign) as...
  10. codrutza

    Select Result One Record Per Job

    Maybe you help me with something else. I have to combine in one select 2 CTEs: one which count the containers for a job, and one with sum sales for a job. How do I write this? 1. ;WIH Cont_CTE AS ( SELECT CJNo , COUNT(1) AS ContCount FROM Cons GROUP BY CJNo ) 2. ;WIH Sales_CTE AS ( SELECT...
  11. codrutza

    Select Result One Record Per Job

    ;WITH ConsSum AS ( SELECT CJNo , COUNT(1) AS ConsCount FROM Cons GROUP BY CJNo ) SELECT j.JNo , j.JDate , j.Sales , c.ConsCount AS TotalCNoperJob FROM Jobs j JOIN ConsSum c ON j.JNo = c.CJNo ORDER BY j.JNo I have the select above for j.type=’A’, where I have to...
  12. codrutza

    Multiple Rows Same Data

    Hi I have to put in a report details on 11 lines the very same data. Does anybody has an idea? I have to copy/paste in sections 1 to 11 the same fields or is another way? JNo JDate Sales 10 21/01/2015 10 line 1 10 21/01/2015 10 line 2 10 21/01/2015 10 line 3 10 21/01/2015 10 line 4 … 10...
  13. codrutza

    Select Result One Record Per Job

    It works, thank you, kjv1611. Am I right when I say could be more then one CTE? From what I read. I have in the report some other subreports, each with their selection for which I'd like CTEs.
  14. codrutza

    Select Result One Record Per Job

    Thank you. Can you repeat ;WITH ConsSum AS ( SELECT CJNo ,COUNT(1) AS Cons FROM Cons GROUP BY CJNo ) SELECT j.JNo, j.JDate, j.Sales, c.Cons AS TotalCNoperJob FROM Jobs j JOIN Cons c ON j.JNo = CJNo ORDER BY j.JNo with the correction ConsSum? I am confused.
  15. codrutza

    Select Result One Record Per Job

    Thanks Andy. kjv1611 I read now about CTE, I didn't know about them, thank you. If I use a CTE: - it is not clear for me the join: ON j.JNo = CJNo? - where is the table ConsSum in the second select? - can you explain me Count(1)?
See more

Part and Inventory Search

Back
Top