Sunday, April 17, 2016

Best use of cross Apply GL General ledger Debit Credit with negative values


CREATE TABLE #test
(GLID varchar(10), Amt int)
INSERT INTO #test
(GLID, Amt)
SELECT '1111', -100 UNION ALL
SELECT '2222', 250 UNION ALL
SELECT '3333', -300 UNION ALL
SELECT '4444', 150

select * from #test

select t1.GLID,

(case  when t1.Amt < 0 then 0
else t1.Amt
end

) AS 'DR',

(case  when t1.Amt > 0 then 0
else abs( t1.Amt  )
end

) AS 'CR' from #test t1 cross apply #test t2 where t1.GLID = t2. GLID

Un pivot example

Un pivot example
drop table MaheshUnPivotExample
CREATE TABLE dbo.MaheshUnPivotExample
(
  MaheshUnPivotExampleID INT PRIMARY KEY identity(1,1), -- FK
  PrimaryPhone VARCHAR(20),
  SecondPhone VARCHAR(20),
  OtherPhone VARCHAR(20) ,
  ProviderName VARCHAR(20)
);
truncate table MaheshUnPivotExample
INSERT dbo.MaheshUnPivotExample
  ( PrimaryPhone, SecondPhone, OtherPhone)
VALUES
  ('11111', '222222', '333333'),
  ('44444444444', NULL, NULL),
  ('55555', null, '666666666'),
  ('777777777777', '8888888888', null);

  select * from MaheshUnPivotExample


  SELECT MaheshUnPivotExampleID , UNPivotColumnName
FROM
(
  SELECT MaheshUnPivotExampleID, PrimaryPhone, SecondPhone, OtherPhone
  FROM dbo.MaheshUnPivotExample
) AS cp
UNPIVOT
(
  UNPivotColumnName FOR UnpivotObject IN (PrimaryPhone, SecondPhone, OtherPhone)
) AS up;
-- if you want to show null values
  SELECT MaheshUnPivotExampleID , UNPivotColumnName
FROM
(
  SELECT MaheshUnPivotExampleID, Isnull(PrimaryPhone,'N/A')PrimaryPhone, Isnull(SecondPhone,'N/A')SecondPhone, Isnull(OtherPhone,'N/A')OtherPhone
  FROM dbo.MaheshUnPivotExample
) AS cp
UNPIVOT
(
  UNPivotColumnName FOR UnpivotObject IN (PrimaryPhone, SecondPhone, OtherPhone)
) AS up;


--
update  MaheshUnPivotExample
set ProviderName  = 'Vodaphone'

update  MaheshUnPivotExample
set ProviderName  = 'AirTel'
where  len(Isnull(PrimaryPhone,'N/A'))> 5



  SELECT MaheshUnPivotExampleID,UNPivotColumnName , UNPivotColumnName2
FROM
(
  SELECT MaheshUnPivotExampleID, Isnull(PrimaryPhone,'N/A')PrimaryPhone, Isnull(SecondPhone,'N/A')SecondPhone, Isnull(OtherPhone,'N/A')OtherPhone, ProviderName
  FROM dbo.MaheshUnPivotExample
) AS cp
UNPIVOT
(
  UNPivotColumnName FOR UnpivotObject IN (PrimaryPhone, SecondPhone, OtherPhone)
) AS up
UNPIVOT
(
    UNPivotColumnName2 FOR UnpivotObject2 IN (ProviderName)
) AS up2;