SQL Script, basically it does retrieve the sum of loans to an entity by last week period
SET DATEFIRST 1 – Define beginning of week as Monday
SELECT ( sum([Debit])) as [LOAN]
FROM [AccountTransactionValues] tv
LEFT JOIN [Accounts] a ON a.[Id] = tv.[AccountId]
LEFT JOIN [Entities] e ON e.[AccountId] = a.[Id]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
WHERE 1=1
AND a.[Name] = ‘GUILLERMO ZAMBRANO’
AND tv.[AccountTransactionTypeId] = 60
AND Date >= dateadd(day, 0-datepart(dw, getdate()), CONVERT(date,getdate()))
AND Date < dateadd(day, 7-datepart(dw, getdate()), CONVERT(date,getdate()))
But Functions doesnt work with my code, says null
function prestamo(accountName) {
var qry = “”;
qry += " SET DATEFIRST 1 – Define beginning of week as Monday";
qry += " SELECT ( sum([Debit])) as [Balance]“;
qry += " FROM [AccountTransactionValues] tv”;
qry += " LEFT JOIN [Accounts] a ON a.[Id] = tv.[AccountId]“;
qry += " LEFT JOIN [Entities] e ON e.[AccountId] = a.[Id]”;
qry += " LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]“;
qry += " LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]”;
qry += " WHERE 1=1";
qry += " AND a.[Name] = ‘" + accountName + "’“;
qry += " AND tv.[AccountTransactionTypeId] = 60”;
qry += " AND Date >= dateadd(day, 0-datepart(dw, getdate()), CONVERT(date,getdate()))“;
qry += " AND Date < dateadd(day, 7-datepart(dw, getdate()), CONVERT(date,getdate()))”;
var prestado = sql.Query(qry).First;
return prestado;
}
I can’t see the error in my function can someone point it out?