CODEWITHSUNDEEP
sql-server
davidjwest
davidjwest

Reputation: 546

Update a Table Based on Count from Another Table

I am trying to do a COUNT against a table using a date range, from another table. The count has to also match with a reference number, here's the data format:

[Rev Old New] (Table 1)
Id   Start        Finish      Amount    Calls
41   2018-01-01   2018-06-01  111.01

[Calls]  (Table 2)
Id   Date         Amount
3    2018-05-05   12.1
41   2018-01-03   11.7
41   2018-06-11   12.9

I am quite new to MS SQL so apologies for my rather basic knowledge!

So, I want the count of rows in [Calls], where the Date is between the Start and Finish dates in [Rev Old New] and the ID is the same in both tables (it's a client ref)

I want to UPDATE [Rev Old New] with this value in [Calls]

Here's what I have so far, not working and probably nowhere near the right syntax!

UPDATE [Insight].[dbo].[Rev Old New]. t2
SET [Calls] = 
(SELECT COUNT(CASE WHERE t1.Date BETWEEN t2.[Start] AND t2.[Finish])
FROM [Insight].[dbo].[Calls] t1
WHERE t1.[Id] = t2.[Id])

The error I get is this:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 't2'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.

Upvotes: 3

Views: 282

Answers (3)

Diado
Diado

Reputation: 2257

You don't need the CASE statement, a simple WHERE will suffice:

UPDATE [Insight].[dbo].[Rev Old New]
SET [Rev Old New].[Calls] = (SELECT COUNT(*) FROM [Insight].[dbo].[Calls] t1
                  WHERE t1.Date BETWEEN [Rev Old New].[Start] AND [Rev Old New].[Finish])

Upvotes: 1

Patrick Vanhuyse
Patrick Vanhuyse

Reputation: 41

First you should avoid spaces in table names. It's not a good practice.

Then a query which solves your problem is :

update [Rev Old New] set Calls=(select count(*) from Calls where [Rev Old New].id = Calls.id and Calls.date >= [Rev Old New].Start and Calls.date <= [Rev Old New].Finish)

where

select count(*) from Calls where [Rev Old New].id = Calls.id and Calls.date >= [Rev Old New].Start and Calls.date <= [Rev Old New].Finish

count the lines from Calls with the id in [Rev Old New] with date in Calls between Finish and Start (included) in [Rev Old New]

I hope this helps.

Upvotes: 1

Sahi
Sahi

Reputation: 1484

this may help

    CREATE TABLE #RevOldNew(Id BIGINT, Start DATETIME,  Finish DATETIME,  Amount BIGINT,  Calls INT)

    CREATE TABLE #Calls(Id BIGINT,[Date] DATETIME, AMOUNT BIGINT)



    INSERT INTO #RevOldNew
    SELECT 1,'2018-06-01','2018-06-15',NULL,NULL UNION ALL
    SELECT 1,'2018-07-16','2018-07-31',NULL,NULL UNION ALL
    SELECT 1,'2018-08-01','2018-08-15',NULL,NULL UNION ALL
    SELECT 1,'2018-08-16','2018-08-31',NULL,NULL UNION ALL

    SELECT 2,'2018-07-01','2018-07-15',NULL,NULL UNION ALL
    SELECT 2,'2018-08-01','2018-08-15',NULL,NULL UNION ALL
    SELECT 2,'2018-08-16','2018-08-31',NULL,NULL UNION ALL

    SELECT 3,'2018-07-16','2018-07-31',NULL,NULL UNION ALL
    SELECT 3,'2018-08-01','2018-08-15',NULL,NULL UNION ALL
    SELECT 3,'2018-08-16','2018-08-31',NULL,NULL 


    INSERT INTO #Calls
    SELECT 1,'2018-07-16',23 UNION ALL
    SELECT 1,'2018-07-21',534 UNION ALL
    SELECT 1,'2018-07-28',456 UNION ALL

    SELECT 1,'2018-08-02',43 UNION ALL
    SELECT 1,'2018-08-11',565 UNION ALL

    SELECT 1,'2018-08-20',56 UNION ALL

    SELECT 2,'2018-07-05',576 UNION ALL

    SELECT 2,'2018-08-22',54 UNION ALL
    SELECT 2,'2018-08-29',676 UNION ALL

    SELECT 3,'2018-07-17',32 UNION ALL
    SELECT 3,'2018-08-15',43


    ;with cte
    As (
        SELECT r.id,r.Start,r.Finish, SUM(c.AMOUNT) Amount, COUNT(c.id) calls
        FROM #RevOldNew r
        LEFT JOIN #Calls c on r.id=c.id and c.Date between r.Start and r.Finish
        Group by r.id,r.Start,r.Finish
    )


    UPDATE r
    SET r.Amount=c.Amount,
            r.Calls=c.calls 
    FROM #RevOldNew r
    JOIN cte c on c.id=r.id and c.Start=r.Start and c.Finish=r.Finish


    SELECT * from #RevOldNew

    DROP TABLE #RevOldNew
    DROP TABLE #Calls

Upvotes: 1

Related Questions