August 17, 2010 at 3:41 pm
Hi
I have a Query:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2010-06-20'
SET @EndDate = '2010-07-25'
DECLARE @SwapDate DATETIME
SELECT @SwapDate = @EndDate,
@EndDate = @StartDate,
@StartDate = @SwapDate
WHERE @EndDate < @StartDate
SELECT @StartDate = DATEADD(ww,DATEDIFF(ww,0,@StartDate),0),
@EndDate = DATEADD(ww,DATEDIFF(ww,0,@EndDate)+1,0)
DECLARE @SQL1 NVARCHAR(4000),
@SQL2 NVARCHAR(4000),
@SQL3 NVARCHAR(4000)
SELECT @SQL1 = 'SELECT CASE WHEN GROUPING([CP Code]) = 1 THEN ''TotalPageviews'' ELSE [CP Code] END AS [CP Code],'+CHAR(10)
SELECT @SQL3 =
' SUM(TotalPageviews) AS TotalPageviews
FROM
(
SELECT DATEADD(WW,DATEDIFF(WW,0,Date),0)-8 AS WeekEndDate,
[CP Code],
SUM(TotalPageviews) AS TotalPageviews
FROM View_DSA
WHERE Date >= ' + QUOTENAME(@StartDate,'''') + '
AND Date < ' + QUOTENAME(@EndDate,'''') + '
GROUP BY DATEADD(WW,DATEDIFF(WW,0,Date),0), [CP Code]
) d
GROUP BY [CP Code]
'
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' SUM(CASE WHEN WeekEndDate = ' + QUOTENAME(d.WeekEndDate,'''')
+ ' THEN TotalPageviews ELSE 0 END) AS [' + d.WeekEndDate + '],' + CHAR(10)
FROM (
SELECT N,
STUFF(CONVERT(CHAR(11),DATEADD(WW, N,@StartDate)-8,101),1,0,'') AS WeekEndDate
FROM Tally
WHERE N <= DATEDIFF(WW,@StartDate,@EndDate)
) d
ORDER BY d.N
EXEC (@SQL1 + @SQL2 + @SQL3)
GO
My question is..How can I create a view which gives me the result of this query.
Thanks
August 18, 2010 at 2:00 pm
You've done all the hard work. To create the VIEW you just need
EXEC('CREATE VIEW YourGeneratedView AS ' + @SQL1 + @SQL2 + @SQL3)
August 18, 2010 at 2:21 pm
LoL....
I'm just new to all this ....
Thankyou very much ....
October 18, 2020 at 12:00 pm
Hi
I have a Query:
DECLARE @baseQuery NVARCHAR(max)
DECLARE @baseQueryFilterString NVARCHAR(max)
DECLARE @bankTransferNoteQuery NVARCHAR(max)
DECLARE @bankTransferNoteFilterString NVARCHAR(max)
DECLARE @payableNotes NVARCHAR(max)
DECLARE @payableNotesFilterString NVARCHAR(max)
DECLARE @prePaidNotes NVARCHAR(max)
DECLARE @bankAccountInfo NVARCHAR(max)
DECLARE @bankAccountInfoFilterString NVARCHAR(max)
DECLARE @orderByClause NVARCHAR(max) = ' Order By BankName,BankBranchName,BankAccountNumber'
IF (null IS NOT NULL)
SET @orderByClause = N'Order By ' +null
SET @bankAccountInfoFilterString = 'AND '
if null IS NOT NULL
SET @bankAccountInfoFilterString = @bankAccountInfoFilterString + ' b.BankID IN ('+ null + ') AND '
if null IS NOT NULL
SET @bankAccountInfoFilterString = @bankAccountInfoFilterString + ' ba.CurrencyRef IN (' + null + ') AND '
if null IS NOT NULL
SET @bankAccountInfoFilterString = @bankAccountInfoFilterString + ' ba.BankAccountTypeRef IN (' + null + ') AND '
if null IS NOT NULL
SET @bankAccountInfoFilterString = @bankAccountInfoFilterString + ' ba.State IN (' + null + ') AND '
SET @bankAccountInfoFilterString = substring(@bankAccountInfoFilterString, 1, len(@bankAccountInfoFilterString) - 3)
SET @bankAccountInfo = N'(SELECT
ba.BankAccountID,
ba.ParentRef,
ba.Number AS BankAccountNumber,
b.Name as BankName,
bb.Name as BankBranchName,
cur.Title as CurrencyTitle,
cur.Precision as CurrencyPrecision
FROM
RPA3.BankAccount as ba
INNER JOIN GNR3.Currency AS cur ON ba.CurrencyRef = cur.CurrencyID
INNER JOIN RPA3.BankBranch as bb on ba.BankBranchRef = bb.BankBranchID
INNER JOIN RPA3.Bank as b on bb.BankRef = b.BankID '+ @bankAccountInfoFilterString +') As ba '
SET @bankTransferNoteFilterString = ' AND'
SET @payableNotesFilterString = ' AND'
SET @baseQueryFilterString = ' AND'
IF null IS NOT NULL
BEGIN
SET @bankTransferNoteFilterString= @bankTransferNoteFilterString + ' [rnt].[LedgerRef] in(' + null + ') AND'
SET @payableNotesFilterString = @payableNotesFilterString + ' [pnt].[LedgerRef] in(' + null + ') AND'
SET @baseQueryFilterString = @baseQueryFilterString + ' [bt].[LedgerRef] in(' + null + ') AND'
END
IF null IS NOT NULL
BEGIN
SET @bankTransferNoteFilterString= @bankTransferNoteFilterString + ' [rnt].[BranchRef] in(' + null + ') AND'
SET @payableNotesFilterString = @payableNotesFilterString + ' [pnt].[BranchRef] in(' + null + ') AND'
SET @baseQueryFilterString = @baseQueryFilterString + ' [bt].[BranchRef] in(' + null + ') AND'
END
IF null IS NOT NULL
BEGIN
SET @bankTransferNoteFilterString= @bankTransferNoteFilterString + ' [rnt].[FiscalYearRef] in(' + null + ') AND'
SET @payableNotesFilterString = @payableNotesFilterString + ' [pnt].[FiscalYearRef] in(' + null + ') AND'
SET @baseQueryFilterString = @baseQueryFilterString + ' [bt].[FiscalYearRef] in(' + null + ') AND'
END
SET @bankTransferNoteFilterString = substring(@bankTransferNoteFilterString, 1, len(@bankTransferNoteFilterString) - 3)
SET @payableNotesFilterString = substring(@payableNotesFilterString, 1, len(@payableNotesFilterString) - 3)
SET @baseQueryFilterString = substring(@baseQueryFilterString, 1, len(@baseQueryFilterString) - 3)
SET @bankTransferNoteQuery=N'(SELECT SUM(rn.Amount) AS TotalBankTransfer, bt.BankAccountRef
FROM RPA3.BankTransferReceivableNote AS btn
JOIN RPA3.BankTransfer as bt ON btn.BankTransferRef = bt.BankTransferID
JOIN RPA3.ReceivableNote AS rn ON btn.ReceivableNoteRef = rn.ReceivableNoteID
JOIN RPA3.ReceivableNoteTransaction AS rnt ON btn.ReceivableNoteTransactionRef = rnt.ReceivableNoteTransactionID
WHERE rnt.ReceivableNoteTransactionID IN
(SELECT
ReceivableNoteTransactionID
FROM (
SELECT row_number() OVER (PARTITION BY ReceivableNoteRef ORDER BY date DESC , documentDate) as rn , ReceivableNoteTransactionID
FROM RPA3.ReceivableNoteTransaction rnt
WHERE (rnt.DocumentDate <= cast(getdate() as date)) AND (rnt.DocumentDate >=cast(Dateadd(day,-30,cast(getdate() as date)) as date)) AND rnt.State > 0
)orderedReceivableNoteTransaction where rn = 1
)
AND rnt.State = 2 '+ @bankTransferNoteFilterString +' GROUP BY bt.BankAccountRef) AS btNotes '
SET @payableNotes = N'(SELECT
pn.BankAccountRef,
SUM((CASE WHEN (pnt.DurationType=2 AND pnt.NormalORGuarantee=1) THEN pn.Amount ELSE 0 END)) AS TotalShortTermDurationPaid,
SUM((CASE WHEN (pnt.DurationType=3 AND pnt.NormalORGuarantee=1) THEN pn.Amount ELSE 0 END)) AS TotalLongTermDurationPaid,
SUM((CASE WHEN (pnt.NormalORGuarantee=2) THEN pn.Amount ELSE 0 END)) AS TotalGuaranteePaid
FROM RPA3.PayableNote AS pn
INNER JOIN RPA3.PayableNoteTransaction as pnt ON pn.PayableNoteID = pnt.PayableNoteRef
WHERE pnt.PayableNoteTransactionID IN
(SELECT
PayableNoteTransactionID
FROM (
SELECT row_number() OVER (PARTITION BY PayableNoteRef ORDER BY date DESC , documentDate) as rn , PayableNoteTransactionID
FROM RPA3.PayableNoteTransaction pnt
WHERE (pnt.DocumentDate <= cast(getdate() as date)) AND (pnt.DocumentDate >=cast(Dateadd(day,-30,cast(getdate() as date)) as date)) AND pnt.State > 0
)orderedPayableNoteTransaction where rn = 1
) AND pnt.State = 11' + @payableNotesFilterString +
' GROUP BY pn.BankAccountRef) AS pNotes '
SET @prePaidNotes = N' (SELECT
[pn].[BankAccountRef],
SUM([pn].[Amount]) AS [TotalPrePaid]
FROM [RPA3].[PayableNote] AS [pn]
INNER JOIN [RPA3].[PayableNoteTransaction] as [pnt] ON [pn].PayableNoteID = [pnt].PayableNoteRef
WHERE [pnt].[PayableNoteTransactionID] IN
(SELECT
PayableNoteTransactionID
FROM (
SELECT row_number() OVER (PARTITION BY [PayableNoteRef] ORDER BY [date] DESC , [documentDate]) as rn , PayableNoteTransactionID
FROM RPA3.PayableNoteTransaction pnt
WHERE ([pnt].[DocumentDate] <= cast(getdate() as date)) AND ([pnt].[DocumentDate] >= cast(Dateadd(day,-30,cast(getdate() as date)) as date))
)orderedPayableNoteTransaction where rn = 1
) AND ([pnt].[State] = -11 OR [pnt].[State] = -28)' + @payableNotesFilterString +
' GROUP BY [pn].[BankAccountRef])prePaidNotes '
SET @baseQuery = N'SELECT * FROM (SELECT
ba.BankAccountID,
ba.BankName,
ba.BankBranchName,
ba.BankAccountNumber,
ba.CurrencyTitle,
ba.CurrencyPrecision,
(CASE WHEN ba.ParentRef is null then '''' else
(Select Bank.Name+''/''+BankBranch.Name+''/''+BankAccount.Number
from RPA3.BankAccount join RPA3.BankBranch on BankBranchRef = BankBranchID join RPA3.Bank on RPA3.BankBranch.BankRef=BankID
where BankAccountID = ba.ParentRef) END) AS ParentName,
bt.BeginningBalance BeginingBalance,
bt.TotalDebit,
bt.TotalCredit,
bt.EndingBalance,
btNotes.TotalBankTransfer,
pNotes.TotalShortTermDurationPaid,
pNotes.TotalLongTermDurationPaid,
pNotes.TotalGuaranteePaid,
prePaidNotes.TotalPrePaid
FROM
'+ @bankAccountInfo + '
LEFT OUTER JOIN
(SELECT
(SUM(CASE WHEN (bt.DocumentDate < cast(Dateadd(day,-30,cast(getdate() as date)) as date) OR (bt.DocumentDate>= cast(Dateadd(day,-30,cast(getdate() as date)) as date) AND bt.DocumentDate<=cast(getdate() as date) AND bt.IsDeployment=1)) THEN bt.Debit ELSE 0 END)-
SUM(CASE WHEN (bt.DocumentDate < cast(Dateadd(day,-30,cast(getdate() as date)) as date) OR (bt.DocumentDate>= cast(Dateadd(day,-30,cast(getdate() as date)) as date) AND bt.DocumentDate<=cast(getdate() as date) AND bt.IsDeployment=1)) THEN bt.Credit ELSE 0 END)) AS BeginningBalance,
SUM(CASE WHEN (bt.DocumentDate >= cast(Dateadd(day,-30,cast(getdate() as date)) as date) AND bt.DocumentDate <= cast(getdate() as date) AND bt.IsDeployment=0) THEN bt.Debit ELSE 0 END) AS TotalDebit,
SUM(CASE WHEN (bt.DocumentDate >= cast(Dateadd(day,-30,cast(getdate() as date)) as date) AND bt.DocumentDate <= cast(getdate() as date) AND bt.IsDeployment=0) THEN bt.Credit ELSE 0 END) AS TotalCredit,
(SUM(CASE WHEN (bt.DocumentDate <= cast(getdate() as date)) THEN bt.Debit ELSE 0 END)- SUM(CASE WHEN (bt.DocumentDate <= cast(getdate() as date)) THEN bt.Credit ELSE 0 END)) AS EndingBalance,
BankAccountRef from rpa3.BankAccountTransaction AS bt
WHERE 1=1' + @baseQueryFilterString + ' GROUP BY BankAccountRef)
bt ON bt.BankAccountRef = ba.BankAccountID
LEFT OUTER JOIN '+ @bankTransferNoteQuery+' ON ba.BankAccountID = btNotes.BankAccountRef
LEFT OUTER JOIN '+ @payableNotes+ ' ON ba.BankAccountID = pNotes.BankAccountRef
LEFT OUTER JOIN '+ @prePaidNotes+ ' ON ba.BankAccountID= prePaidNotes.BankAccountRef
WHERE bt.BeginningBalance>0 OR bt.TotalDebit>0 OR bt.TotalCredit>0 OR bt.EndingBalance>0
OR btNotes.TotalBankTransfer>0 OR pNotes.TotalShortTermDurationPaid>0
OR pNotes.TotalLongTermDurationPaid>0 OR pNotes.TotalGuaranteePaid >0
OR prePaidNotes.TotalPrePaid >0
)x ' + N' WHERE 1=1 /*{{AND(InlineFilter,InlineFilter,2,BankName,BankBranchName,BankAccountNumber,CurrencyTitle,BeginingBalance,TotalDebit,TotalCredit,EndingBalance,TotalBankTransfer,TotalShortTermDurationPaid,TotalLongTermDurationPaid,TotalGuaranteePaid,TotalPrePaid)}}*/'
SET @baseQuery = @baseQuery + @orderByClause
exec sp_executesql @baseQuery;
My question is..How can I create a view which gives me the result of this query.
Thanks
October 18, 2020 at 7:49 pm
Firstly, It may be better to start a new thread - adding to a ten-year-old one with different code may not be the best way of getting a response
The short answer is that you can't - a view can't accept parameters. It does, however, ask the question why does the query have to be a view? It's probably better to create a stored procedure - you might want to look at https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15, especially the "Using Transact-SQL" section.
Looking at the code , it's a little difficult to know where to start. The number of NVARCHAR(MAX) parameters looks like an attempt to create a piece of code that tries to deal with any kind of enquiry - it's probably better to create a procedure for each parameter and call the procedure depending on the parameter. Building SQL strings and executing them is usually a recipe for ensuring really bad performance. Also, "if null IS NOT NULL" and "WHERE 1=1 ": NULL is always NULL and 1 is always 1- what was the author of the code trying to achieve?
It's better to let the forum know what the problem is rather than decide what the solution is and then ask how to achieve it - for example, if you say "I have to provide data from bank accounts which is in the same format but for a variety of different queries, but I'm new to SQL and don't know where to start" I'm sure you'll get a lot of helpful and constructive guidance. You might also want to ask your employer for some training and/or mentoring
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply