gistfile1.txt

Reporting For MYSQL and SQL Server.
_____________________________________________________________________________
Most reporting are done before using report writer, odbc or else...
_____________________________________________________________________________
Single Query
	
	SELECT * FROM generalledger;

Dynamic Query

 SET @financialYear = 2013;
 SET @query = 'select * from generalledger where financeYearYear = ?';
 prepare statement from @query;
 execute statement using @financialYear;
 deallocate prepare statement;

 ** sql server using sp_execute;

 Store Procedure Query 
	
 CREATE PROCEDURE `demo`()
 LANGUAGE SQL
 NOT DETERMINISTIC
 CONTAINS SQL
 SQL SECURITY DEFINER
 COMMENT ''
 BEGIN
  SET @financialYear = 2013;
  SET @query = 'select * from generalledger where financeYearYear = ?';
  prepare statement from @query;
  execute statement using @financialYear;
  deallocate prepare statement;
END
call demo;
___________________________________________________________________________________
JOIN
    
     Select * from f,d where f.id = d.id
 
     Select * from f join d using (id) MySQL and oracle only

     Select * from f join d on f.id = d.id where 1
	
     Update f set d.v = 1 from family f join district d on f.id = d.id      
     ** SQL server only for updating join table... MySQL easier.

     If wanted to used LEFT JOIN and filter need to add before not after WHERE

	    WRONG

      Select * from f LEFT JOIN d on f.id = d.id AND d.k = “n” where 1

      RIGHT
	     
      Select * from f LEFT JOIN d on f.id = d.id where d.k = “n”
_____________________________________________________________________________________
SQL Server need to declare type for variable parameter;

DECLARE @financialYear INT;
DECLARE @financialPeriod INT;

SET @financialYear = 2013;
SET @financialPeriod = 7;
_______________________________________________________________________________________
Grouping

	Normal Grouping

select chartOfAccountCategoryDescription,localAmount  from
generalledger group by
chartOfAccountCategoryDescription
________________________________________________________________________________________
Group concat (MYSQL)

select group_concat(generalledgerDescription)
from generalledger;

Group Concat (SQL Server)

SELECT TOP 1 STUFF(
(
SELECT	',' + generalledgerDescription
FROM 		[generalledger]
FOR XML PATH ('')
),1,1,''
) AS  generalledgerDescription
FROM 	[generalledger]

Grouping will rollup 

select chartOfAccountCategoryDescription,localAmount  from
generalledger group by
chartOfAccountCategoryDescription with rollup

Grouping with rollup and SUM IF

select chartOfAccountCategoryDescription,
sum(IF(month(generalledgerdate)=1,abs(localAmount),0)) as January,
sum(IF(month(generalledgerdate)=2,abs(localAmount),0)) as February,
sum(IF(month(generalledgerdate)=3,abs(localAmount),0)) as March,
sum(IF(month(generalledgerdate)=4,abs(localAmount),0)) as April,
sum(IF(month(generalledgerdate)=5,abs(localAmount),0)) as May,
sum(IF(month(generalledgerdate)=6,abs(localAmount),0)) as Jun,
sum(IF(month(generalledgerdate)=7,abs(localAmount),0)) as July,
sum(IF(month(generalledgerdate)=8,abs(localAmount),0)) as August,
sum(IF(month(generalledgerdate)=9,abs(localAmount),0)) as September,
sum(IF(month(generalledgerdate)=10,abs(localAmount),0)) as October,
sum(IF(month(generalledgerdate)=11,abs(localAmount),0)) as November,
sum(IF(month(generalledgerdate)=12,abs(localAmount),0)) as December,
localAmount  
from	generalledger group by
chartOfAccountCategoryDescription with rollup

Grouping with roll up and sum if and union (Total Record)

select chartOfAccountCategoryDescription,
sum(IF(month(generalledgerdate)=1,abs(localAmount),0)) as January,
sum(IF(month(generalledgerdate)=2,abs(localAmount),0)) as February,
sum(IF(month(generalledgerdate)=3,abs(localAmount),0)) as March,
sum(IF(month(generalledgerdate)=4,abs(localAmount),0)) as April,
sum(IF(month(generalledgerdate)=5,abs(localAmount),0)) as May,
sum(IF(month(generalledgerdate)=6,abs(localAmount),0)) as Jun,
sum(IF(month(generalledgerdate)=7,abs(localAmount),0)) as July,
sum(IF(month(generalledgerdate)=8,abs(localAmount),0)) as August,
sum(IF(month(generalledgerdate)=9,abs(localAmount),0)) as September,
sum(IF(month(generalledgerdate)=10,abs(localAmount),0)) as October,
sum(IF(month(generalledgerdate)=11,abs(localAmount),0)) as November,
sum(IF(month(generalledgerdate)=12,abs(localAmount),0)) as December,
localAmount  from
generalledger group by
chartOfAccountCategoryDescription with rollup
union all
select 'Total' AS chartOfAccountCategoryDescription,
sum(IF(month(generalledgerdate)=1,abs(localAmount),0)) as January,
sum(IF(month(generalledgerdate)=2,abs(localAmount),0)) as February,
sum(IF(month(generalledgerdate)=3,abs(localAmount),0)) as March,
sum(IF(month(generalledgerdate)=4,abs(localAmount),0)) as April,
sum(IF(month(generalledgerdate)=5,abs(localAmount),0)) as May,
sum(IF(month(generalledgerdate)=6,abs(localAmount),0)) as Jun,
sum(IF(month(generalledgerdate)=7,abs(localAmount),0)) as July,
sum(IF(month(generalledgerdate)=8,abs(localAmount),0)) as August,
sum(IF(month(generalledgerdate)=9,abs(localAmount),0)) as September,
sum(IF(month(generalledgerdate)=10,abs(localAmount),0)) as October,
sum(IF(month(generalledgerdate)=11,abs(localAmount),0)) as November,
sum(IF(month(generalledgerdate)=12,abs(localAmount),0)) as December,
sum(abs(localAmount))  from
generalledger


** SQL Server USING IIF ( statement, TRUE,FALSE) OR Case When for older version then 2012

Grouping Sub Select AND UNION (Sub Total, Grant Total)

Select chartOfAccountCategoryDescription,generalLedgerDescription,
localAmount from
(
SELECT chartOfAccountCategoryDescription ,generalLedgerDescription,ABS(localAmount) as localAmount
FROM generalledger
union
SELECT chartOfAccountCategoryDescription ,'SUB Total' AS generalLedgerDescription,
SUM(ABS(localAmount)) AS localAmount
FROM generalledger
group by chartOfAccountCategoryDescription
) t
order by chartOfAccountCategoryDescription 


Grouping with rollup and GROUPING (SQL Server Only) (Sub Total), (Grant Total)

select
    case
        when grouping(chartOfAccountCategoryDescription) = 1 then 'Grand Total'
        when grouping(chartOfAccountDescription) = 1 then ' Total'
        else chartOfAccountCategoryDescription
    end as Region,
    isnull(cast(chartOfAccountDescription as nvarchar(max)), '') as chartOfAccountTypeDescription,
    sum(abs(localAmount))
from generalledger
group by rollup(chartOfAccountCategoryDescription, chartOfAccountDescription)

























Grouping With Pivot (SQL Server Only)

	

DECLARE @financialYear INT;
DECLARE @financialPeriod INT;
SET @financialYear = 2013;
SET @financialPeriod = 7;
SELECT chartOfAccountCategoryDescription,
chartOfAccountDescription
	,coalesce([1], 0) as [1]
	,coalesce([2], 0) as [2]
	,coalesce([3], 0) as [3]
	,coalesce([4], 0)as [4]
	,coalesce([5], 0)as [5]
	,coalesce([6], 0)as [6]
	,coalesce([7], 0)as [7]
	,coalesce([8], 0)as [8]
	,coalesce([9], 0)as [9]
	,coalesce([10], 0)as [10]
	,coalesce([11], 0) as [11]
	,coalesce([12], 0) as [12]
	,coalesce([13], 0) as [13]
	,coalesce([14], 0)as [14]
	,coalesce([15], 0)as [15]
	,coalesce([16], 0)as [16]
	,coalesce([17], 0)as [17]
	,coalesce([18], 0)as [18]
	,coalesce([19], 0)as [19]
	,coalesce([20], 0)as [20]
	,coalesce([21], 0) as [21]
	,coalesce([22], 0) as [22]
	,coalesce([23], 0) as [23]
	,coalesce([24], 0)as [24]
	,coalesce([25], 0)as [25]
	,coalesce([26], 0)as [26]
	,coalesce([27], 0)as [27]
	,coalesce([28], 0)as [28]
	,coalesce([29], 0)as [29]
	,coalesce([30], 0)as [30]
	,coalesce([30], 0)as [31]
	,coalesce([Total], 0)as [Total]
FROM (
	SELECT chartOfAccountCategoryDescription,CONCAT (
			chartOfAccountNumber
			,CONCAT (
				' - '
				,chartOfAccountDescription
				)
			) AS chartOfAccountDescription
		,ISNULL(DATENAME(DAY, generalLedgerDate), 'Total') AS theMonth
		,SUM(ABS(localAmount)) AS items
	FROM generalLedger
	WHERE DATEPART(YEAR, generalLedgerDate) = @financialYear
	GROUP BY chartOfAccountCategoryDescription,CONCAT (
			chartOfAccountNumber
			,CONCAT (
				' - '
				,chartOfAccountDescription
				)
			)
		,DATENAME(DAY, generalLedgerDate)

	WITH ROLLUP
	HAVING GROUPING(CONCAT (
				chartOfAccountNumber
				,CONCAT (
					' - '
					,chartOfAccountDescription
					)
				)) = 0


	) AS s
PIVOT(SUM(items) FOR theMonth IN (
		[1], [2], [3], [4], [5], [6], [7], [8], [9],
				[10], [11], [12], [13], [14], [15], [16], [17], [18], [19],
				[20], [21], [22], [23], [24], [25], [26], [27], [28], [29],
				[30], [31], [Total]
			)) AS p
union all
SELECT chartOfAccountCategoryDescription,
chartOfAccountDescription
	,coalesce([1], 0) as [1]
	,coalesce([2], 0) as [2]
	,coalesce([3], 0) as [3]
	,coalesce([4], 0)as [4]
	,coalesce([5], 0)as [5]
	,coalesce([6], 0)as [6]
	,coalesce([7], 0)as [7]
	,coalesce([8], 0)as [8]
	,coalesce([9], 0)as [9]
	,coalesce([10], 0)as [10]
	,coalesce([11], 0) as [11]
	,coalesce([12], 0) as [12]
	,coalesce([13], 0) as [13]
	,coalesce([14], 0)as [14]
	,coalesce([15], 0)as [15]
	,coalesce([16], 0)as [16]
	,coalesce([17], 0)as [17]
	,coalesce([18], 0)as [18]
	,coalesce([19], 0)as [19]
	,coalesce([20], 0)as [20]
	,coalesce([21], 0) as [21]
	,coalesce([22], 0) as [22]
	,coalesce([23], 0) as [23]
	,coalesce([24], 0)as [24]
	,coalesce([25], 0)as [25]
	,coalesce([26], 0)as [26]
	,coalesce([27], 0)as [27]
	,coalesce([28], 0)as [28]
	,coalesce([29], 0)as [29]
	,coalesce([30], 0)as [30]
	,coalesce([30], 0)as [31]
	,coalesce([Total], 0)as [Total]
FROM (
	SELECT 'Total' AS chartOfAccountCategoryDescription,
		   ' ' AS chartOfAccountDescription
		,ISNULL(DATENAME(DAY, generalLedgerDate), 'Total') AS theMonth
		,ABS(localAmount) AS items
	FROM generalLedger
	WHERE DATEPART(YEAR, generalLedgerDate) = @financialYear

	) AS s
PIVOT(SUM(items) FOR theMonth IN (
		[1], [2], [3], [4], [5], [6], [7], [8], [9],
				[10], [11], [12], [13], [14], [15], [16], [17], [18], [19],
				[20], [21], [22], [23], [24], [25], [26], [27], [28], [29],
				[30], [31], [Total]
			)) AS p

Cross Tab Dynamic (Mysql)

BEGIN
DECLARE x  INT;
DECLARE str  VARCHAR(4096);
DECLARE eom INT;
set @financialYear=2013;
set @financialPeriod=7;
SELECT
day(last_day(generalledgerdate)) eo
FROM generalledger
WHERE YEAR(generalLedgerDate) = @financialYear
AND month(generalledgerdate) = @financialPeriod
LIMIT 1
INTO eom;
SET x = 1;
SET str =  '';
WHILE x  <= eom DO
SET  str = CONCAT (
COALESCE(str,''),
'SUM(IF(day(generalLedgerDate)=',
COALESCE(x,''),
',abs(localAmount),0)) AS `' ,
COALESCE(x,''),
'`,'
);
SET  x = x + 1;
END WHILE;

SET @sqlCrossTab = concat(' SELECT ',

substring(str,1,LENGTH(str) - 1),
' FROM generalledger WHERE month(generalledgerdate)=',
@financialPeriod,
' AND year(generalledgerdate)=',
@financialYear);
prepare statement from @sqlCrossTab;
execute statement ;
deallocate prepare statement;

END


Cross Tab Dynamic (SQL Server)

DECLARE @financialYear INT;
DECLARE @financialPeriod INT;
DECLARE @counter INT;
DECLARE @eom INT;
DECLARE @stringMonth varchar(2048);
DECLARE @sqlCrossTab NVARCHAR(MAX);

SET @financialYear = 2013;
SET @financialPeriod = 7;
SET @counter = 1;
SET @eom = (
		SELECT TOP 1 day(eomonth(generalledgerdate)) AS eo
		FROM generalledger
		WHERE DATEPART(YEAR, generalLedgerDate) = @financialYear
			AND datepart(month, generalledgerdate) = @financialPeriod
		)

WHILE (@counter <= @eom)
BEGIN
	SET @stringMonth = CONCAT (
			@stringMonth
			,' SUM(IIF(day(generalLedgerDate)=' + CAST(@counter AS VARCHAR(10)) + ',abs(localAmount),0)) AS [' + CAST(@counter AS VARCHAR(10)) + '],'
			);

	SELECT @counter = @counter + 1;
END

SET @sqlCrossTab = ' SELECT ' + left(@stringMonth,DATALENGTH(@stringMonth) - 1) + ' FROM generalledger WHERE datepart(month,generalledgerdate)=' + cast(@financialPeriod AS VARCHAR(2)) + ' AND datepart(
year,generalledgerdate)=' + CAST(@financialYear AS VARCHAR(4));

EXEC SP_EXECUTESQL @sqlCrossTab;

















Searching 
	Searching Normal
	select * from generalledger 
where generalledgerdescription like '%a%'

	Searching In detail Query (Do avoid left query)
select * from generalledger WHERE
generalLedgerID IN
(
SELECT * FROM generalLedger
JOIN product
USING( productId)
WHERE productId IN (1, 3, 4)
		)













Store Procedure
	SQL SERVER
	DECLARE @menuId INT;
DECLARE @roleId INT;
DECLARE @isAdministrator INT;
DECLARE @menuAccessValue INT;
DECLARE @menuCursor AS CURSOR;
DECLARE @roleCursor AS CURSOR;

SET @menuCursor = CURSOR
FOR

SELECT menuId
FROM menu;

OPEN @menuCursor;

FETCH NEXT
FROM @menuCursor
INTO @menuId;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @roleCursor = CURSOR
FOR

SELECT roleId
	,isadministrator
FROM ROLE;
	OPEN @roleCursor;

	FETCH NEXT
	FROM @roleCursor
	INTO @roleId
		,@isAdministrator;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @isAdministrator = 1
			SET @menuAccessValue = 0;
		ELSE
			SET @menuAccessValue = 1;

	INSERT INTO menuaccess (
		menuId
		,roleId
		,menuAccessValue
		,isNew
		,isUpdate
		,isDelete
		,isActive
		,executeBy
		,executeTime
		)
	VALUES (
		@menuId
		,@roleId
		,@menuAccessValue
		,1
		,0
		,0
		,1
		,'administrator'
		,CURRENT_TIMESTAMP
		);

	FETCH NEXT
	FROM @roleCursor
	INTO @roleId
		,@isAdministrator;
END;

CLOSE @roleCursor;

DEALLOCATE @roleCursor;

FETCH NEXT
FROM @menuCursor
INTO @menuId;END;

CLOSE @menuCursor;

DEALLOCATE @menuCursor;

** Never “using” and normal cross join in one statement. It would output error if MySQL.. Order are base normal cross join then using. 
** using in oracle is not the same with MySQL “using”
** pivot SQL server just short hand... Proper still using case when. Lot of limitation.