Database

prubyholl wrote:

Plz i want the accounts and customers details to appear even if no transactions exists.

SELECT * 
FROM dbo.tblRecords
WHERE [Key] = CAST(RAND()*10 AS INT)

Category

ID  Code
--  ----
1   1.1.1
2   2.2.2
etc

BudgetLine

CategoryCode  SubCode
------------  -------
1             1        (gives 1.1.1.1)
1             2        (gives 1.1.1.2)
2             1        (gives 2.2.2.1)
2             2        (gives 2.2.2.2)

ID  Code   ParentID
--  ----   --------
1   1      NULL
2   1      1        (gives 1.1)
3   1      2        (gives 1.1.1)
3   2      2        (gives 1.1.2)

Table: ACC_GROUP
Id  Parent  Desc
1   NULL    Assets
2   1       Current Assets
3   2       Cash on Hand


Table: ACC
Id Group Number Name
1  3     1-1110 Checking Account
2  3     1-1120 Payroll Account
3  2     1-1300 Deposits Paid

However, the solution is

1) Use a Cursor or While loop and use a split function to split the strings based on '\' character

2) Extract the last string and store it in some temporary table or table variables or you can make your can write a table valued split function.

3) Then you apply the Order by operation.

--Created by Niladri Biswas
ALTER FUNCTION [dbo].[fnSplit] 
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100)) 
AS
Begin 
		
		Declare @newstring as varchar(100)
		Declare @pos as int
		Declare @i as int
		Declare @c as int	

		set @newstring = '';		
		set @i = 1
		set @c = 0

		set @pos = CHARINDEX(@delimeter, @oldstring) 

		WHILE (@i != 0)

			Begin

				set @c = @c +1
				insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))

				
				set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))

				set @pos = CHARINDEX(@delimeter, @oldstring)

				set @i = @pos;
				if (@i = 0)
                Begin
                    set @i = 0;
						set @c = @c +1
                
					insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
					
                End

			End

			return 

End

--Created by Niladri Biswas
ALTER PROCEDURE Dbo.SortTxtFile
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- VARIABLE DECLARATION
	DECLARE @FILEPATH VARCHAR(50)

	DECLARE @FINALSTORAGE TABLE 
	(
		FILENAMES VARCHAR(100)
	)

    -- STEP 1: DECLARE A CURSOR
	DECLARE MYRANDOMCURSOR CURSOR FOR

		SELECT    FILEPATH
		FROM      tblFileNameSort

	-- STEP 2: OPEN THE CURSOR
	OPEN MYRANDOMCURSOR 

	FETCH MYRANDOMCURSOR INTO @FILEPATH

	-- STEP 3: START THE LOGIC

	WHILE @@Fetch_Status = 0

	BEGIN

		-- STEP 4: INSERT THE COMBINED RECORDS INTO TABLE      
                --          @FINALSTORAGE

			INSERT INTO @FINALSTORAGE

			SELECT stringval FROM dbo.fnSplit(@FILEPATH,'\')

			WHERE stringval LIKE '%.txt'
			
		
	-- STEP 5: GET THE NEXT RECORD
	FETCH MYRANDOMCURSOR INTO @FILEPATH           

   END

	--STEP 6: CLOSE THE CURSOR
	CLOSE MYRANDOMCURSOR

	--STEP 6: DEALLOCATE THE CURSOR
	DEALLOCATE MYRANDOMCURSOR

	SELECT * FROM @FINALSTORAGE ORDER BY FILENAMES
END
GO

Select * 
from Tablename
order by REVERSE(LEFT(REVERSE(path),(CHARINDEX('\',REVERSE(path))-1)))

/*
Extracts the file name from the file path. If file path is D:\myFolder\new1.txt, then it returns
new1.txt. not in original SQLite.
*/
const char* extractFileName(const char* s){
  while( *s != '\0' ) ++s;
  while( *s != '\\') --s;
  ++s;
  return s;
}

/*
Extracts the file name from the file path. If file path is D:\myFolder\new1.txt, then it returns
new1.txt. not in original SQLite.
*/
static void extractFileNameFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  const char *z;

  assert( argc==1);

  if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
    sqlite3_result_null(context);
    return;
  }
  z = sqlite3_value_text(argv[0]);
  sqlite3_result_text(context, extractFileName(z), -1, SQLITE_TRANSIENT); 
}