Search - Joel Lipman .Com

21. Split a row into multiple rows based on a column value: / Transact-SQL /; ... EmployeeNo , DaysOffSick , DateOfSickness ,'1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS Items FROM Employees_Attendance_Table JOIN master..spt_values ...; Created on 23 August 2013
22. T-SQL concatenate an incremental row number: / Transact-SQL /; ... 3) AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + '01' AS QualificationRef FROM Qualifications q INNER JOIN Employee e ON RIGHT('000' + CAST(e.ID AS VARCHAR), ...; Created on 24 May 2013
23. T-SQL Conversion failed when converting the varchar to data type int: / Transact-SQL /; What? A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read ...; Created on 14 May 2013
24. Convert Decimal (Person Days) to Time in Excel: / Excel /; ... tasks down the first column, and time spent in the form of person days in the cross-join. Why? Currently the smallest bookable time by low-level tape monkeys and techies is 30 minutes (Managers ...; Created on 13 February 2013
25. JDatabase: using the Joomla database with examples: / Joomla /; ... p')); $query->join('INNER', $db->quoteName('#__users', 'u') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('p.user_id') . ')') $query->where($db->quoteName('u.real_name') . ' LIKE '. $db->quote('\'%smith%\'')); $query->order('u.real_name ...; Created on 27 January 2013
26. Delete related records from multiple tables: / MySQL /; What? So this is a quick article on how to delete from multiple tables in a mySQL database where we use some JOIN statements. Why? It is never recommended to delete from multiple tables and instead ...; Created on 11 January 2013
27. Search a database for a string (MySQL, T-SQL): / Databases /; ... ( SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) FROM SYS.columns SC JOIN SYS.types STy ON ...; Created on 08 October 2012
28. Inline Labels in Form Fields using Javascript: / JavaScript /; ... this as "no one entered anything", treat it as empty. this.value.split(' ').join('') -- split this value into an array separated by the delimiter character "" -- join this array back replacing any ...; Created on 21 August 2012
29. MySQL: Display Users and Duration in Matrix Timesheet: / MySQL /; ... of the previous version of this query was pretty abysmal and worse if you want to list users who have no associated activities (outer join but all-in-one query). The DBA responsible for the system asked ...; Created on 27 April 2012
30. Returning Oracle Stored Procedure Resultset in SSRS: / SQL Server Reporting Services /; ... NVL(a.student_id, b.person_id) "p_STUDENT_REF" FROM student_accounts a INNER JOIN student_details b ON a.student_id = b.person_id WHERE a.student_id = p_STUDENT_REF; ...; Created on 19 January 2012
31. Basic Oracle Stored Procedure Structure: / Oracle PL/SQL /; ... second query which we're hoping will be quicker than a straightforward table join. Syntax CREATE [OR REPLACE] PROCEDURE stored_procedure_name [ (parameter [,parameter]) ] IS | AS ...; Created on 16 January 2012
32. Performance Report - Background colors based on dataset values: / SQL Server Reporting Services /; ... e.TimeStart, e.TimeEnd) [Total Time] ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] FROM [ReportServer].[dbo].[ExecutionLogStorage] e INNER JOIN [ReportServer].[dbo].[Catalog] ...; Created on 02 December 2011
33. How to Display Report Execution Time in SSRS (milliseconds): / SQL Server Reporting Services /; ... e.TimeDataRetrieval + e.TimeProcessing + e.TimeRendering AS TotalTime FROM [ExecutionLog] e INNER JOIN [Catalog] c ON c.ItemID=e.ReportID WHERE c.Name='My Amazing Report' -- just change the value ...; Created on 17 November 2011
34. T-SQL Record Separator: / Transact-SQL /; ... THEN NULL ELSE 'Separator Marker' END as 'Marker' FROM tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1 ORDER BY t2.RowNumber -- ------------------------------------------------------------------------------------------- -- ...; Created on 11 October 2011
35. List MediaWiki Articles: / MediaWiki /; ... WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory FROM wikimedia_page p LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id WHERE p.page_namespace=0 AND ...; Created on 22 September 2011
36. MySQL parameters in Excel 2007 PivotTables: / MySQL /; ... FROM 5), '/', SUBSTRING(YEARWEEK(s.DateTimeCreated,1), 1, 4)) AS ActivityWeek, CASE COUNT(*) WHEN 1 THEN '(1 Event)' ELSE '(Multiple Events)' END AS DataAccuracy FROM StaffActivities s INNER JOIN ...; Created on 24 August 2011
37. The ReportServer Database: / Databases /; ... ,a.[Status] ,a.[ByteCount] ,a.[RowCount] FROM [ReportServer].[dbo].[ExecutionLog] a INNER JOIN [ReportServer].[dbo].[Catalog] b ON a.ReportID = b.ItemID The Top 5 Most ...; Created on 08 August 2011
38. Before and After, Highs and Lows: / SQL Server Reporting Services /; ... AS YearRun, COUNT(Catalog.Name) AS Counter FROM ExecutionLogStorage INNER JOIN Catalog ON ExecutionLogStorage.ReportID = Catalog.ItemID WHERE (Catalog.Type = 2) AND ...; Created on 03 August 2011
39. SSRS Querying on either of 2 Parameters: / SQL Server Reporting Services /; ... the second solution will mean each dataset must at least join both tables that you are using the OR clause against. There must be a solution that executes a query at report execution time and populates/assigns ...; Created on 19 July 2011
40. SSRS Hide results table if empty: / SQL Server Reporting Services /; ... JOIN [EQUIPMENT] eq ON eq.EquipId=re.EquipId WHERE re.RoomId=@GivenRoom AND re.SetId=@setId This seems to have problems because when there are no matching rows (so zero results), BIDS ...; Created on 29 March 2011