CODEWITHSUNDEEP
sqlsql-server
Kevin
Kevin

Reputation: 39

Divide in SQL - find daily amount

I have two tables where I need to find the daily budget.

In my table [sdk$G_L Budget Entry] I found the monthly budget. In my table [Salesboard Working Days] I found out, how many working days in the month.

Now I need to find out, the daily budget of each day. To do that, I need to take MonthBudget and divide with Working Days.

But I'm not sure, how I do that. I can do it like this, but think that's not a pretty way to do it.

(SELECT  CAST(ISNULL(ABS(SUM(Amount)),0) AS int) as MonthBudget FROM  [sdk$G_L Budget Entry] b  WHERE (MONTH(wd.Date) = MONTH(b.Date) AND YEAR(wd.date) = YEAR(b.Date)) AND b.[Budget Name] = '2020C' AND b.[G_L Account No_] LIKE '3%') / (SELECT SUM(w.WorkingDay) as WD FROM [Salesboard Working Days] w WHERE  MONTH(wd.Date) = MONTH(w.Date) AND YEAR(wd.date) = YEAR(w.Date) AND wd.WorkingDay = 1 ) AS DailyBudget

My SQL:

    SELECT 
            
        wd.[Date]
        ,wd.[WorkingDay]
        ,wd.[SalesPersonCode]
        ,wd.[companycode]
        ,(SELECT SUM(w.WorkingDay) as WD FROM [Salesboard Working Days] w WHERE  MONTH(wd.Date) = MONTH(w.Date) AND YEAR(wd.date) = YEAR(w.Date) AND wd.WorkingDay = 1 ) AS WorkingDays
        ,(SELECT  CAST(ISNULL(ABS(SUM(Amount)),0) AS int) as MonthBudget FROM  [sdk$G_L Budget Entry] b  WHERE (MONTH(wd.Date) = MONTH(b.Date) AND YEAR(wd.date) = YEAR(b.Date)) AND b.[Budget Name] = '2020C' AND b.[G_L Account No_] LIKE '3%') AS MonthBudget
        ,'??' As DailyBudget
      
    FROM 
      
        [Salesboard Working Days] wd 
      
    WHERE 
      
        wd.WorkingDay = 1 AND wd.SalesPersonCode IS NULL AND wd.companycode = 'sdk'  AND wd.[Date] >= '2020-07-27' AND wd.[Date] <= '2020-08-07'
    
        ORDER BY wd.Date

Result:

Date    WorkingDay  SalesPersonCode companycode WorkingDays MonthBudget DailyBudget
2020-07-27 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-07-28 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-07-29 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-07-30 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-07-31 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-08-03 00:00:00.000 1   NULL    sdk 21  2689775  ??
2020-08-04 00:00:00.000 1   NULL    sdk 21  2689775  ??
2020-08-05 00:00:00.000 1   NULL    sdk 21  2689775  ??
2020-08-06 00:00:00.000 1   NULL    sdk 21  2689775  ??
2020-08-07 00:00:00.000 1   NULL    sdk 21  2689775  ??

Upvotes: 0

Views: 103

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can rewrite the query using window functions and APPLY:

SELECT wd.*,
       SUM(wd.WorkingDay) OVER (PARTITION BY YEAR(wd.date), MONTH(wd.date)) as  WorkingDays,
       b.MonthBudget,
       (b.MonthBudget /
        SUM(w.WorkingDay) OVER (PARTITION BY YEAR(wd.date), MONTH(wd.date))
       ) as DalyBudget
FROM [Salesboard Working Days] wd OUTER APPLY
     (SELECT COALESCE(ABS(SUM(Amount)), 0) as MonthBudget
      FROM [sdk$G_L Budget Entry] b
      WHERE MONTH(wd.Date) = MONTH(b.Date) AND
            YEAR(wd.date) = YEAR(b.Date) AND
            b.[Budget Name] = '2020C' AND
            b.[G_L Account No_] LIKE '3%'
     ) b
WHERE wd.WorkingDay = 1 AND
      wd.SalesPersonCode IS NULL AND
      wd.companycode = 'sdk' AND
      wd.[Date] >= '2020-07-27' AND wd.[Date] <= '2020-08-07'    
ORDER BY wd.Date;

The window function simplifies the counting of the working days in the month. The APPLY implements a lateral join, which is like a correlated subquery in the FROM clause (except that it can return multiple rows and columns).

Upvotes: 1

Related Questions