Database

tblSpend

COLUMN NAME			DATATYPE	
-----------			-------
SPENDACCOUNT			int	
FROMCODE			int	
KEYWORD				varchar(50)	
KWTYPE				int	
CLICKS				int	
SPEND				int	

Values in tblSpend are

101	1	KeyWord1	1	1	10
102	2	KeyWord2	1	10	11
103	3	KeyWord3	2	21	15
104	4	KeyWord4	3	22	16
105	5	KeyWord5	5	11	11
106	6	KeyWord6	2	33	12
107	7	KeyWord7	1	111	11
108	8	KeyWord8	2	22	2
109	9	KeyWord9	1	1	1
110	10	KeyWord10	1	2	3

tblRev

COLUMN NAME			DATATYPE	
-----------			-------
NEWACCOUNTS			int
GROSS				int
REFUNDS				int
NET				int
TRANS				int
FROMCODE			int

Values in tblRev are

1	10	20	10	10	1
2	20	30	10	20	2
3	30	40	15	25	3
4	40	50	45	5	4
5	50	100	50	50	5
6	60	400	200	200	6
7	70	100	100	0	7
8	70	100	50	50	7
6	111	300	120	180	6

SELECT 

		S.SPENDACCOUNT, S.KEYWORD, 
		S.KWTYPE,S.CLICKS,S.SPEND,

		D.FROMCODE,D.TRANS,D.NEWACCOUNTS,
		D.GROSS,D.REFUNDS,D.NET

	FROM (

				SELECT 
						T.FROMCODE,
						SUM (R.TRANS) TRANS,
						SUM (R.NEWACCOUNTS) NEWACCOUNTS,	
						SUM (R.GROSS) GROSS,	
						SUM (R.REFUNDS) REFUNDS,	
						SUM (R.NET) NET

				FROM TBLSPEND T, TBLREV R 
				WHERE 
						T.FROMCODE= R.FROMCODE

				GROUP BY T.FROMCODE 

			) D, TBLSPEND S

WHERE  S.FROMCODE   = D.FROMCODE 

101	KeyWord1	1	1	10	1	1	10	20	10	10
102	KeyWord2	1	10	11	2	2	20	30	10	20
103	KeyWord3	2	21	15	3	3	30	40	15	25
104	KeyWord4	3	22	16	4	4	40	50	45	5
105	KeyWord5	5	11	11	5	5	50	100	50	50
106	KeyWord6	2	33	12	6	12	171	700	320	380
107	KeyWord7	1	111	11	7	15	140	200	150	50

select 
spend.account,
spend.fromcode,
spend.keyword,
spend.kwtype, 
(select sum(rev.trans) from rev where rev.fromcode=spend.fromcode) trans, 
(select sum(rev.newaccounts) from rev where rev.fromcode=spend.fromcode) newaccounts, 
(select sum(rev.gross) from rev where rev.fromcode=spend.fromcode) gross,
(select sum(rev.refunds) from rev where rev.fromcode=spend.fromcode) refunds,
(select sum(rev.net) from rev where rev.fromcode=spend.fromcode) net, 
spend.clicks, 
spend.spend
from spend 
order by spend.fromcode

DROP FUNCTION IF EXISTS sortString;
DELIMITER |
CREATE FUNCTION sortString(inString TEXT) RETURNS TEXT
BEGIN
  DECLARE delim CHAR(1) DEFAULT ','; -- delimiter 
  DECLARE strings INT DEFAULT 0;     -- number of substrings
  DECLARE forward INT DEFAULT 1;     -- index for traverse forward thru substrings
  DECLARE backward INT;   -- index for traverse backward thru substrings, position in calc. substrings
  DECLARE remain TEXT;               -- work area for calc. no of substrings
-- swap areas TEXT for string compare, INT for numeric compare
  DECLARE swap1 TEXT;                 -- left substring to swap
  DECLARE swap2 TEXT;                 -- right substring to swap
  SET remain = inString;
  SET backward = LOCATE(delim, remain);
  WHILE backward != 0 DO
    SET strings = strings + 1;
    SET backward = LOCATE(delim, remain);
    SET remain = SUBSTRING(remain, backward+1);
  END WHILE;
  IF strings < 2 THEN RETURN inString; END IF;
  REPEAT
    SET backward = strings;
    REPEAT
      SET swap1 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward-1),delim,-1);
      SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward),delim,-1);
      IF  swap1 > swap2 THEN
        SET inString = TRIM(BOTH delim FROM CONCAT_WS(delim
        ,SUBSTRING_INDEX(inString,delim,backward-2)
        ,swap2,swap1
        ,SUBSTRING_INDEX(inString,delim,(backward-strings))));
      END IF;
      SET backward = backward - 1;
    UNTIL backward < 2 END REPEAT;
    SET forward = forward +1;
  UNTIL forward + 1 > strings
  END REPEAT;
RETURN inString;
END |
DELIMITER ;
 
-- example call:
SET @Xstr  = "The,quick,brown,fox,jumped,over,the,lazy,dog";
SET @Ystr  = "9,8,7,6,5,4,3,2,1";  
SET @Zstr  = "7,8,9,6,5,2,1,4,3";  
SET @str  = "3,2,3,4,2,3";
 
SELECT sortString(@str) AS s1,sortString(@Xstr) AS s2,sortString(@Ystr) AS s3,sortString(@Zstr) AS s4;

>

create FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item int)

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

go

create  FUNCTION GetSortedString
(
 @inputString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
 
declare @outputString varchar(max)
set @outputString = ''
select @outputString = ltrim(rtrim(str(item))) + ','  + @outputString  from dbo.fnSplit(@inputString,',') order by item desc
if  SUBSTRING(@outputString,len(@outputString),1) = ','
 begin
  set @outputString = SUBSTRING(@outputString,1,len(@outputString)-1) 
 end
return @outputString

END
GO

select dbo.GetSortedString('2,1,4,5,3,6')

 -- 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.SortedList
	-- Add the parameters for the stored procedure here
	(@DelimitedValues AS VARCHAR(50))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @OUTPUT VARCHAR(30)
	DECLARE @TEMPOUTPUT VARCHAR(30)
    
	SET @OUTPUT = ''
	SET @TEMPOUTPUT = ''

	DECLARE @MYCURSOR CURSOR
	SET @MYCURSOR = CURSOR FOR

SELECT STRINGVAL FROM dbo.fnSplit(@DelimitedValues,',')
	ORDER BY RIGHT(REPLICATE('0', 11) + stringval, 10)

OPEN @MYCURSOR
	FETCH NEXT
	FROM @MYCURSOR INTO @TEMPOUTPUT
	WHILE @@FETCH_STATUS = 0

	BEGIN
	SET @OUTPUT =  @OUTPUT + @TEMPOUTPUT + ','

	FETCH NEXT
	FROM @MYCURSOR INTO @TEMPOUTPUT
	END
	CLOSE @MYCURSOR
	DEALLOCATE @MYCURSOR

SET @OUTPUT	 = SUBSTRING(@OUTPUT,0,LEN(@OUTPUT))

	PRINT @OUTPUT	
END