SQL to Export template

V5 Question
1

Hi all,

can anyone help me with how exactly to do export of SQL report? Script works fine and gets me what I need, but I am trying to get it to export into csv format. Any ideas?

DECLARE @Start DATETIME = CONVERT(VARCHAR(25), '{Start}', 126);
DECLARE @End DATETIME = CONVERT(VARCHAR(25), '{End}', 126);

SELECT
      FORMAT(CAST(p.Date AS Date),'MMM-dd') AS Date
      ,Sum (CASE WHEN p.Name = 'Cash' THEN [Amount] ELSE 0 END) Cash
	  ,Sum (CASE WHEN p.Name = 'Credit Card' THEN [Amount] ELSE 0  END) CreditCard
	  ,Sum (CASE WHEN p.Name = 'ABAPay' THEN [Amount] ELSE 0  END) ABAPay
	  ,Sum (CASE WHEN p.Name = 'ABAPay QR' THEN [Amount] ELSE 0  END) ABAPayQR
	  ,Sum (CASE WHEN p.PaymentTypeId = 4  THEN [Amount] ELSE 0  END) DebtorAccount
	  ,Sum (CASE WHEN [Amount] >= 0  THEN [Amount] ELSE 0  END) Total

  FROM Payments p

  WHERE 
  p.Date >= @Start 
  AND
  p.Date <= @End


  GROUP BY FORMAT(CAST(p.Date AS Date),'MMM-dd')
  ORDER BY FORMAT(CAST(p.Date AS Date),'MMM-dd')

Result

Date Cash CreditCard ABAPay ABAPayQR DebtorAccount
Apr-09 271.50 0.00 0.00 73.36 0.00
Apr-11 286.72 37.95 0.00 151.30 0.00
Apr-12 1.00 1.00 1.00 5.00 13.25
2

Never mind, figured it out

DATA EXPORT


[Export:0]
Date,Cash,CreditCard,ABAPay,ABAPayQR,DebtorAccount,Total

{REPORT SQL DETAILS:
SELECT
      FORMAT(CAST(p.Date AS Date),'MMM-dd') AS Date
      ,Sum (CASE WHEN p.Name = 'Cash' THEN [Amount] ELSE 0 END) Cash
	  ,Sum (CASE WHEN p.Name = 'Credit Card' THEN [Amount] ELSE 0  END) CreditCard
	  ,Sum (CASE WHEN p.Name = 'ABAPay' THEN [Amount] ELSE 0  END) ABAPay
	  ,Sum (CASE WHEN p.Name = 'ABAPay QR' THEN [Amount] ELSE 0  END) ABAPayQR
	  ,Sum (CASE WHEN p.PaymentTypeId = 4  THEN [Amount] ELSE 0  END) DebtorAccount
	  ,Sum (CASE WHEN [Amount] >= 0 THEN [Amount] ELSE 0  END) Total

  FROM Payments p

  WHERE 
  p.Date >= '{Start}' 
  AND
  p.Date <= '{End}'


  GROUP BY FORMAT(CAST(p.Date AS Date),'MMM-dd')
  ORDER BY FORMAT(CAST(p.Date AS Date),'MMM-dd')
  :F.Date,F.Cash,F.CreditCard,F.ABAPay,F.ABAPayQR,F.DebtorAccount,F.Total
  ::"{0}","{1}","{2}","{3}","{4}","{5}","{6}"
};

{REPORT SQL DETAILS:
SELECT
       Sum (CASE WHEN p.Name = 'Cash' THEN [Amount] ELSE 0 END) CashSum
	  ,Sum (CASE WHEN p.Name = 'Credit Card' THEN [Amount] ELSE 0  END) CreditCardSum
	  ,Sum (CASE WHEN p.Name = 'ABAPay' THEN [Amount] ELSE 0  END) ABAPaySum
	  ,Sum (CASE WHEN p.Name = 'ABAPay QR' THEN [Amount] ELSE 0  END) ABAPayQRSum
	  ,Sum (CASE WHEN p.PaymentTypeId = 4  THEN [Amount] ELSE 0  END) DebtorAccountSum
	  ,Sum(CASE WHEN p.Amount > 0 THEN [Amount] ELSE 0 END) TotalSum

  FROM Payments p

  WHERE 
  p.Date >= '{Start}' 
  AND
  p.Date <= '{End}'

  :F.CashSum,F.CreditCardSum,F.ABAPaySum,F.ABAPayQRSum,F.DebtorAccountSum,F.TotalSum
  ::"Total","{0}","{1}","{2}","{3}","{4}","{5}"
}
1 Like