Recent content by codrutza

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

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.

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?

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

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

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

Thank you Boris. Sorted :)

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

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

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

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

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

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.

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.

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)?