[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

Create a second table in a stored proc ?

Could you show me if it's possible to creaet a table in the last query that follows in this sproc ?

The reason I ask is because after this stored procedure executes, the next statement in my
Access Application is to run a report which uses a recordsource of a table or query and I planned on
using the result of this stored procedure as the input source for my report.

CREATE PROCEDURE procDateRngRpt
AS
if exists(select * from dbo.sysobjects where name = 'tblCust' and type = 'U')
drop table tblCust
SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
CONVERT(char(10), GETDATE() - Day(GETDATE()), 121) AS DFrom,    
CONVERT(char(10), DATEADD(Month, -[MthTo], GETDATE() - Day(GETDATE())  + 1), 121)   AS DateFromC,
tblMthRanges.MthTo,
CONVERT(char(10),DATEADD(DAY, -1, DATEADD(MONTH, -MthFrom, CONVERT(varchar(8), GETDATE(), 102)+ '01')), 121) AS DateToC
INTO tblCust
FROM tblMthRanges, tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber=tblProducts.CustomerNumber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))
GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()))
--
SELECT tblCust.MthFrom, tblCust.MthTitle, tblDollarRanges.DollarTitle, Count(CustomerNumber) AS CountOfCustomerNumber, Sum(AcctValue) AS SumOfAcctValue,
tblCust.DFrom, tblCust.DateFromC, tblCust.DateToC, tblDollarRanges.DollarFrom
FROM tblCust, tblDollarRanges
WHERE (((.tblCust.AcctValue)>=[DollarFrom] And (tblCust.AcctValue)<[DollarTo]))
GROUP BY tblCust.MthFrom, tblCust.MthTitle, tblDollarRanges.DollarTitle, tblCust.DFrom, tblCust.DateFromC, tblCust.DateToC, tblDollarRanges.DollarFrom
ORDER BY.tblCust.MthFrom, tblDollarRanges.DollarTitle DESC
--
SELECT
 Q.MthFrom As MthF, Q.MthTitle As MthTitl, DR.DollarTitle as DollTitl,
 Count(Q.CustomerNumber) AS CountOfCustomerNumber,
 Sum(Q.AcctValue) AS SumOfAcctValue, Q.DFrom As DFr, Q.DateFromC As DFrC,
 Q.DateToC As DToC, DR.DollarFrom
 FROM tblcust As Q, tblDollarRanges As DR
 WHERE (Q.AcctValue>=DR.DollarFrom) And (Q.AcctValue<DR.DollarTo)
 GROUP BY Q.MthFrom, Q.MthTitle, DR.DollarTitle, Q.DFrom, Q.DateFromC, Q.DateToC, DR.DollarFrom
UNION ALL SELECT
 S.MthF, S.MthTitl, S.DollTitl, S.CountOfCustNum, S.SumOfAcVal, S.DFr, S.DFrC,S.DToC, S.DollarFrom
 FROM tblSumm As S
 WHERE NOT EXISTS
 (SELECT Q2.MthFrom, Q2.MthTitle, DR2.DollarTitle
  FROM tblCust As Q2, tblDollarRanges As DR2
  WHERE (Q2.AcctValue>=DR2.DollarFrom) And (Q2.AcctValue<DR2.DollarTo)
  and S.MthF=Q2.MthFrom and S.MthTitl=Q2.MthTitle and S.DollTitl=DR2.DollarTitle
 )
ORDER BY 1, 3;
GO
0
zimmer9
Asked:
zimmer9
1 Solution
 
ispalenyCommented:
Put INTO in the first select of UNION ALL

SELECT
 Q.MthFrom As MthF, Q.MthTitle As MthTitl, DR.DollarTitle as DollTitl,
 Count(Q.CustomerNumber) AS CountOfCustomerNumber,
 Sum(Q.AcctValue) AS SumOfAcctValue, Q.DFrom As DFr, Q.DateFromC As DFrC,
 Q.DateToC As DToC, DR.DollarFrom
INTO NewTable
 FROM tblcust As Q, tblDollarRanges As DR
 ....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now