Looping throught a query and adding results to snapshot table or temp table

Hi there,
I would like to create a query which looks at sales during a particularly large period of time, and the results are then passed into a temp table to view these results.

For example.
Over the past 60 days I want to check our sales where the actual customers phone number would have appeared in this group (introduce a sub query which will then hold all of those phone numbers for those same 60 days) - what is important here is that the phone number that appears in, for example, a sale on day 1 should be checked against the sub query for the same day 1 (not any other day). Does that make sense?



Select salesdate, sum(salesrevenue) from SalesTable where salesdate between '2017-01-01 and '2017-01-02'
And phonenumber in (select phonenumber from customertraffic table where customer traffic hitdate between '2017-01-01' and '2017-01-02')
Group by salesdate

However I want to be able to have this query run over a longer period of time not just one day, but it needs to be day to day,
so I'm thinking if I could loop the query to run
day+1 type thing, I'm just not sure how to go about it.
and then insert the results into a temp table so I can see all the dates and their results.

declare @datefrom  datetime, @dateto datetime
SEt @datefrom = '2017-01-01'
SET @dateto = @datefrom+1

--how Stop the loop when 60 days of data is returned. ?


Insert into #TempTable (salesdate, salesrevenue)
Select salesdate, sum(salesrevenue) from SalesTable where salesdate between @datefrom  and @Dateto
And phonenumber in (select phonenumber from customertraffic table where customer traffic hitdate between @datefrom  and @Dateto)
Group by salesdate

Thank you,
PutochAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
You can do like below , Explanation is given in the inline query itself using /**/

SET @datefrom = '2017-01-01'
SET @dateto = DATEADD(Day,60,@datefrom) /*Add 60 days to the Start Date*/

WHILE (@datefrom <= @dateto) /*Loop while start date is less than or equal to enddate*/
BEGIN
      ----Your loop code here...

/*Here you can add one day in the @Datefrom and insert the data in the temp table*/

      SET @datefrom = DATEADD(DAY,1,@datefrom) /*Loop through and add 1 day after each iteration*/
END

Also i think this case be achieved using Numbers tables without recursion which will be fast than the row by row version.

Could you please provide some sample input and the expected output in an excel.
0
PutochAuthor Commented:
Thank you Pawan Kumar for a quick response.
Here is some sample input and expected output, using a very simple table so you can see what I'm trying to achieve.
The sales table can hold lots of sales
The Traffichit table can hold lots of transactions.
I need to be able to match the phone number and the dates of when those sales and transactions of traffic hit map.
I hope this helps clarify what I'm trying to achieve

CREATE TABLE SALESTABLE (
salesdate datetime,
salesrevenue money
Phonenumber      nvarchar(20))

INSERT INTO SALESTABLE values ('2017-01-01','10.00','0123456879')
INSERT INTO SALESTABLE values ('2017-01-01','20.00','0126456879') -- * Even though this appears in Sales it didn't appear in trafficHit table
INSERT INTO SALESTABLE values ('2017-02-01','10.00','0123456879')
INSERT INTO SALESTABLE values ('2017-03-01','100.00','0123456888')

CREATE TABLE customertraffic (
Phonenumber nvarchar(20),
HitDate datetime)

INSERT INTO customertraffic ('0123456879','2017-01-01')
INSERT INTO customertraffic ('0123456879','2017-02-01')
INSERT INTO customertraffic ('0123456669','2017-02-01') -- * Even though this appears here it doesn't appear in sales table.
INSERT INTO customertraffic ('0123456888','2017-03-01')

Expected Results inserted into temp table or snapshot table of sorts.
DATE , Sum of salesrevenue
'2017-01-01' -  = 10.00
'2017-02-01' -  = 10.00
'2017-03-01' -  = 100.00
0
ste5anSenior DeveloperCommented:
First of all: include such concise and complete examples in the question. But use correct SQL (date literals, numbers not quoted) and prefer table variables over temp tables over real tables, e.g.

DECLARE @Sales TABLE
    (
        SalesDate DATE ,
        SalesRevenue MONEY ,
        PhoneNumber NVARCHAR(20)
    );

INSERT INTO @Sales ( SalesDate ,
                     SalesRevenue ,
                     PhoneNumber )
VALUES ( '20170101', 10.00, '0123456879' ) ,
       ( '20170101', 20.00, '0126456879' ) ,
       ( '20170201', 10.00, '0123456879' ) ,
       ( '20170301', 100.00, '0123456888' );

DECLARE @CustomerTraffic TABLE
    (
        PhoneNumber NVARCHAR(20) ,
        HitDate DATE
    );

INSERT INTO @CustomerTraffic ( PhoneNumber ,
                               HitDate )
VALUES ( '0123456879', '20170101' ) ,
       ( '0123456879', '20170201' ) ,
       ( '0123456669', '20170201' ) ,
       ( '0123456888', '20170301' );

Open in new window


And embed it into [code][/code] tags (use the CODE button).

In this case, it seems you're looking for

SELECT S.*
FROM   @Sales S
WHERE  EXISTS (   SELECT *
                  FROM   @CustomerTraffic CT
                  WHERE  CT.PhoneNumber = S.PhoneNumber
                         AND CT.HitDate = S.SalesDate );

Open in new window


Depending on the cardinality of the relation a LEFT JOIN does it also:

SELECT S.*
FROM   @Sales S
       LEFT JOIN @CustomerTraffic CT ON CT.PhoneNumber = S.PhoneNumber
                                        AND CT.HitDate = S.SalesDate
WHERE  NOT CT.PhoneNumber IS NULL;

Open in new window

No need for loops. Sets rule.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PutochAuthor Commented:
Thanks ste5an,
I seem to have totally over complicated the whole query and as you say by linking phone and date this gives me the information I need.
Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.