SQL Query

DataSet: Style: Output: Font Size:

NRECS: NSKIP: NMINUTES:

That match:
Search for KeyCode:
Search for EMAIL:
Search for CardNumber:
Search for GroupID:
Search for ID:
Search for UserID:
Search for Name:

	LABEL=<center><h1>By AuthType</h1><BR>; TRANSFORM Count() as [The Value] SELECT DISTINCTROW TransDate, TransID,TransCode, SUM(TransTotal) AS [Amount],Count() AS [Qty] FROM Transactions WHERE TransDate=Date() GROUP BY TransDate, TransID,TransCode PIVOT(AuthType); LABEL=<HR><h1>Counts By ProductID</h1><BR>; TRANSFORM Count() as [The Value] SELECT DISTINCTROW TransDate, TransID,TransCode, sum(TransTotal) as [Amount], Count() AS [Qty] FROM Transactions WHERE TransDate=Date() GROUP BY TransDate, TransID,TransCode PIVOT(MerchantID & ' / ' & ProductID); LABEL=<HR><h1>Totals By ProductID</h1><BR>; TRANSFORM sum(TransTotal) as [The Value] SELECT DISTINCTROW TransDate, TransID,TransCode, Count() AS [Qty],sum(TransTotal) as [Amount] FROM Transactions WHERE TransDate=Date() GROUP BY TransDate, TransID,TransCode PIVOT(MerchantID & ' / ' & ProductID); LABEL=<HR><h1>Totals By TransID / Transcode / Authtype</h1><BR>; TRANSFORM sum(TransTotal) as [The Value] SELECT DISTINCTROW TransDate, MerchantID,ProductID, Count() AS [Qty],sum(TransTotal) as [Amount] FROM Transactions WHERE TransDate=Date() GROUP BY TransDate, MerchantID,ProductID PIVOT(TransID & ' / ' & TransCode & ' / ' & AuthType);
	TRANSFORM Count() as [The Value] SELECT DISTINCTROW TransDate, TransID, TransCode,AuthType, Count() AS [Qty] FROM Transactions WHERE TransDate=Date() AND TransID IN(2,3,4) GROUP BY TransDate, TransID,TransCode,AuthType PIVOT(AVS);
	SELECT * FROM Transactions WHERE TransDate = Date() ;
FROM: TO: MERCHANT:	LABEL=<center><h1>Charges</h1><BR>; transform sum(transtotal) as [the value] SELECT distinctrow transdate,count() as [QTY], SUM(TRANSTOTAL) AS [Grand Total] FROM Transactions WHERE transid=4 and authtype in ('1','5') and transdate >= #{FROMDATE}# and transdate <= #{TODATE}# {PRODUCTSPEC} group by transdate pivot(MerchantID & ' / ' & productid); LABEL=<HR><h1>Credits</h1><BR>; transform sum(transtotal)-1 as [the value] SELECT distinctrow transdate,count() as [QTY], SUM(TRANSTOTAL)-1 AS [Grand Total] FROM Transactions WHERE transid=8 and transdate >= #1999/01/01# {PRODUCTSPEC} group by transdate pivot(MerchantID & ' / ' & productid) ; LABEL=<HR><h1>Retrievals</h1><BR>; transform sum(transtotal) as [the value] SELECT distinctrow transdate,count() as [QTY], SUM(TRANSTOTAL) AS [Grand Total] FROM Transactions WHERE Retrieval=TRUE {PRODUCTSPEC} group by transdate pivot(MerchantID & ' / ' & productid); LABEL=<HR><h1>Chargebacks</h1><BR>; transform sum(transtotal)-1 as [the value] SELECT distinctrow transdate,count() as [QTY], SUM(TRANSTOTAL)-1 AS [Grand Total] FROM Transactions WHERE chargeback > ' ' {PRODUCTSPEC} group by transdate pivot(MerchantID & ' / ' & productid);
	LABEL=<center><h1>Merchant List for {PAYFROMDATE} to {PAYTODATE}</h1><BR>; SELECT distinctrow merchantid,count(*) as [Qty] FROM Transactions WHERE ((transdate >= #{PAYFROMDATE}# and transdate <= #{PAYTODATE}# and transid in (2,3,4)) or ((ISNULL(Pending) or Pending=0) AND transid=8 and transdate >= #1999/01/01#) or ((ISNULL(Pending) or Pending=0) AND chargeback > ' ' and cbdate >= #1999/01/01#)) group by merchantid;
FROM: TO: MERCHANT:	LABEL=<center><h1>Charges</h1><BR>; transform sum(transtotal) as [the value] SELECT distinctrow TransDate,count() as [QTY], SUM(TRANSTOTAL) AS [Grand Total] FROM Transactions WHERE transid=4 and authtype in ('1','5') and transdate >= #{PAYFROMDATE}# and transdate <= #{PAYTODATE}# {PAYPRODUCTSPEC} group by transdate pivot(MerchantID & ' / ' & productid); LABEL=<HR><h1>Credits</h1><BR>; transform sum(transtotal)-1 as [the value] SELECT distinctrow GroupId,TransDate,FirstName,LastName,Count() AS [Qty], SUM(TransTotal)-1 as [Amount] FROM Transactions WHERE (ISNULL(Pending) or Pending=0) AND transid=8 and transdate >= #1999/01/01# {PAYPRODUCTSPEC} group by GroupId,transdate,firstname,lastname pivot(MerchantID & ' / ' & productid) ; LABEL=<HR><h1>Transaction Counts</h1><BR>; transform count() as [the value] SELECT distinctrow TransDate,count() as [QTY] FROM Transactions WHERE transid in(2,3,4) and transdate >= #{PAYFROMDATE}# and transdate <= #{PAYTODATE}# {PAYPRODUCTSPEC} group by transdate pivot(MerchantID & ' / ' & productid); LABEL=<HR><h1>Credits Summary</h1><BR>; transform count() as [the value] SELECT distinctrow TransDate,Count() AS [Qty], SUM(TransTotal)-1 as [Amount] FROM Transactions WHERE (ISNULL(Pending) or Pending=0) AND transid=8 and transdate >= #1999/01/01# {PAYPRODUCTSPEC} group by transdate pivot(TransTotal) ; LABEL=<HR><h1>Chargebacks</h1><BR>; transform sum(transtotal)-1 as [the value] SELECT distinctrow id,CBDate,FirstName,LastName,Count(*) AS [Qty], SUM(TransTotal) as [Amount] FROM Transactions WHERE (ISNULL(Pending) or Pending=0) AND chargeback > ' ' and cbdate >= #1999/01/01# {PAYPRODUCTSPEC} group by id,cbdate,firstname,lastname pivot(MerchantID & ' / ' & productid) ;

ChargeBack Information

Transaction Record Information

DataSet: Style: Output:

	SELECT DISTINCTROW TransID, TransCode, Count(*) AS [Qty] FROM Transactions WHERE TransID = 8 GROUP BY TransID,TransCode ;
	label=TransIDs; SELECT DISTINCTROW TransID, Count() AS [Count Of Transactions] FROM Transactions GROUP BY TransID; label=TransID and TransCode; TRANSFORM Count() AS [THE VALUE] SELECT DISTINCTROW TransID, Count(*) AS [Count Of Transactions] FROM Transactions GROUP BY TransID PIVOT(TransCode);
	SELECT DISTINCTROW ActionCode, Count(*) AS [Qty] FROM Transactions WHERE TransID = 4 AND TransCode = 0 GROUP BY ActionCode ;

	SELECT DISTINCTROW ActionDate,ActionCode, Count(*) AS [Qty] FROM Transactions WHERE ActionDate >= Date() AND ActionCode = 4 GROUP BY ActionDate,ActionCode ;
	LABEL=<center><h2>CC and Web900 Members by Site, per ActionCode</h2>; TRANSFORM Count() AS [The Value] SELECT DISTINCTROW ProductID, Count() AS [Qty] FROM Transactions WHERE TransID = 4 AND TransCode = 0 AND Disconnected=FALSE AND Blocked=FALSE AND Credited=FALSE AND AuditID=0 AND (IsNull(ChargeBack) OR ChargeBack = ' ') AND (ActionCode IN(0,1,2,3,5,10) OR (ActionCode=4 AND ActionDate > Date())) AND (AuthType IN('1','5','O','9','I') OR (AuthType IN('4','6') AND Confirm > ' ')) GROUP BY ProductID PIVOT(ActionCode);

EXAMPLE:

WEB900 INFO

DataSet:

Font: Style: Output:

TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW TransDate,Count(*) AS [nItems] FROM Transactions WHERE TransID=4 AND TransDate=Date() AND AuthType='9' GROUP BY TransDate PIVOT(Source);
SELECT * FROM Transactions WHERE TransID=4 AND TransDate=Date() AND AuthType='9' ;

TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW TransDate,Count(*) AS [nItems] FROM Transactions WHERE AuthType='9' AND (((TransDate)>=Date()-30)) GROUP BY TransDate PIVOT(Source)
label=ALL WEB900 SALES PER PRODUCT; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW ProductID,Count(*) AS [ALL ITEMS] FROM Transactions WHERE TransID = 4 AND AuthType = '9' GROUP BY ProductID PIVOT(Source);

LABEL=<center><h1>WEB900 SALES PER MONTH</h1><BR>; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count(*) AS [CallCount] FROM Transactions WHERE TransID=4 AND AuthType = '9' GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(Source); LABEL=<HR><h1>Past Member Counts PER MONTH</h1><BR>; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count(*) AS [CallCount] FROM Transactions WHERE TransID = 4 AND AuthType='9' AND ActionCode = 4 AND Disconnected=TRUE GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(Source); LABEL=<HR><h1>Current Member Counts PER MONTH</h1><BR>; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count(*) AS [CallCount] FROM Transactions WHERE TransID = 4 AND AuthType = '9' AND Disconnected=FALSE GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(Source); LABEL=<HR><h1>Scheduled Membership Expirations</h1><BR>; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count(*) AS [CallCount] FROM Transactions WHERE TransID = 4 AND AuthType = '9' AND Disconnected=FALSE GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(YEAR(ActionDate) & '/' & RIGHT('0' & MONTH(ActionDate),2));

I-BILL SALES INFO

DataSet:

Font: Style: Output:

TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW TransDate,Count(*) AS [nItems] FROM Transactions WHERE TransID=4 AND TransDate=Date() AND AuthType='I' GROUP BY TransDate PIVOT(Source);
SELECT * FROM Transactions WHERE TransID=4 AND TransDate=Date() AND AuthType='I' ;

TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW TransDate,Count(*) AS [nItems] FROM Transactions WHERE AuthType='I' AND (((TransDate)>=Date()-30)) GROUP BY TransDate PIVOT(Source)
label=ALL I-BILL SALES PER PRODUCT; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW Source,ProductID,Count(*) AS [ALL ITEMS], SUM((TransTotal)-(TransTotal*.15)) FROM Transactions WHERE TransID = 4 AND AuthType = 'I' GROUP BY Source,ProductID PIVOT(TransTotal);

LABEL=<center><h1>I-BILL SALES PER MONTH</h1><BR>; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count(*) AS [Qty] FROM Transactions WHERE TransID=4 AND AuthType = 'I' GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(TransTotal); LABEL=<HR><h1>Past Member Counts PER MONTH</h1><BR>; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count(*) AS [Qty] FROM Transactions WHERE TransID = 4 AND AuthType='I' AND ActionCode = 4 AND Disconnected=TRUE GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(TransTotal); LABEL=<HR><h1>Current Member Counts PER MONTH</h1><BR>; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count(*) AS [Qty] FROM Transactions WHERE TransID = 4 AND AuthType = 'I' AND Disconnected=FALSE AND ActionCode <> 4 GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(TransTotal); LABEL=<HR><h1>Scheduled Membership Expirations</h1><BR>; TRANSFORM Count(*) AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count(*) AS [Qty] FROM Transactions WHERE TransID = 4 AND AuthType = 'I' AND Disconnected=FALSE AND ActionCode = 4 GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(YEAR(ActionDate) & '/' & RIGHT('0' & MONTH(ActionDate),2));

TRANSFORM Count() AS [THE VALUE] SELECT DISTINCTROW TransDate,Count() AS [nItems] FROM Transactions WHERE TransID=4 AND TransDate=Date() AND AuthType='9' GROUP BY TransDate PIVOT(Source);	SELECT * FROM Transactions WHERE TransID=4 AND TransDate=Date() AND AuthType='9' ;
TRANSFORM Count() AS [THE VALUE] SELECT DISTINCTROW TransDate,Count() AS [nItems] FROM Transactions WHERE AuthType='9' AND (((TransDate)>=Date()-30)) GROUP BY TransDate PIVOT(Source)	label=ALL WEB900 SALES PER PRODUCT; TRANSFORM Count() AS [THE VALUE] SELECT DISTINCTROW ProductID,Count() AS [ALL ITEMS] FROM Transactions WHERE TransID = 4 AND AuthType = '9' GROUP BY ProductID PIVOT(Source);
LABEL=<center><h1>WEB900 SALES PER MONTH</h1><BR>; TRANSFORM Count() AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count() AS [CallCount] FROM Transactions WHERE TransID=4 AND AuthType = '9' GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(Source); LABEL=<HR><h1>Past Member Counts PER MONTH</h1><BR>; TRANSFORM Count() AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count() AS [CallCount] FROM Transactions WHERE TransID = 4 AND AuthType='9' AND ActionCode = 4 AND Disconnected=TRUE GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(Source); LABEL=<HR><h1>Current Member Counts PER MONTH</h1><BR>; TRANSFORM Count() AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count() AS [CallCount] FROM Transactions WHERE TransID = 4 AND AuthType = '9' AND Disconnected=FALSE GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(Source); LABEL=<HR><h1>Scheduled Membership Expirations</h1><BR>; TRANSFORM Count() AS [THE VALUE] SELECT DISTINCTROW YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) AS [Month],Count() AS [CallCount] FROM Transactions WHERE TransID = 4 AND AuthType = '9' AND Disconnected=FALSE GROUP BY YEAR(TransDate) & '/' & RIGHT('0' & MONTH(TransDate),2) PIVOT(YEAR(ActionDate) & '/' & RIGHT('0' & MONTH(ActionDate),2));