Odtnhj

I need to get day total of Petty Cash (I'm already getting this) & need Monthly total relevant to Account Codes for today. Following I'm showing SQL for daily total. Someone Please help me to Calculate Monthly totals in the same row.

SELECT DPetAcNo as AcNo,
 SUM(DPetAmount) as DayTotal
 FROM PettyDetail
 WHERE DPetComCode='15'
 and DPetLocCode='01'
 and DPetDate=CONVERT(date,'20181113',111)
 GROUP BY DPetAcNo

Result for the above query show below

According to the result 2018/11/13 AcNo 009111 Total = 22,995.00
and actual cumulative (2018/11/01 - 2018/11/13) total for 009111 = 136,265.42

Modified version of regbas’ answer that should perform better because it doesn’t consider so many rows:

SELECT DPetAcNo as AcNo,
 SUM(case when DPetDate=CONVERT(date,'20181113',111) then DPetAmount else 0 end) as DayTotal,
 SUM(DPetAmount) as MonthTotal
 FROM PettyDetail
 WHERE DPetComCode='15'
 and DPetLocCode='01'
 and DPetDate >= '2018-11-01'
 AND DPetDate < '2018-12-01'
 AND DPetAcNo IN (select dpetacno from pettydetail WHERE DPetComCode='15'
 and DPetLocCode='01'
 and DPetDate = '2018-11-13')
 GROUP BY DPetAcNo

Sqlserver will implicitly convert a ‘yyyy-mm-dd’ string to a date if you want to skip the CONVERT code clutter. Having an index that covers date, comcode, loccode, account and includes amount will also help

——

Another way to approach this is to use the queries you seem to have already written for daytotal and cumtotal, like this:

Select * from
(/*Insert your query that does daytotal into these brackets*/) d
Inner join
(/*Insert your query that does cumtotal into these brackets*/) c
ON d.DPetAcNo = c.dpetacno

I am assuming your DPetDate is a Date or Datetime data type.

You will need to filter the rows for the 1st of the month to the required date.
for both day & month total on the same result, use CASE in the SUM ()

SELECT DPetAcNo as AcNo,
 SUM(CASE WHEN DPetDate = '20181113' THEN DPetAmount ELSE 0 END) as DayTotal,
 SUM(DPetAmount) as MonthTotal,
 FROM PettyDetail
 WHERE DPetComCode='15'
 and DPetLocCode='01'
 and DPetDate >= '20181101' -- from 1st of Nov
 and DPetDate <= '20181113' -- to 13 of Nov
 GROUP BY DPetAcNo

EDIT 1:
to only select AccNo based on the required date. Using subquery to get the Month Total. Or alternatively you can do it in a CROSS APPLY

SELECT *,
 ( SELECT SUM(DPetAmount) 
 FROM PettyDetail x 
 WHERE x.DPetAcNo = p.AcNo 
 AND x.DPetComCode = '15' 
 AND x.DPetLocCode = '01'
 AND x.DPetDate >= '20180101' 
 AND x.DPetDate <= '20181113') as MonthTotal
FROM
(
 SELECT DPetAcNo as AcNo,
 SUM(CASE WHEN DPetDate = '20181113' THEN DPetAmount ELSE 0 END) as DayTotal
 FROM PettyDetail p
 WHERE DPetComCode='15'
 and DPetLocCode='01'
 and DPetDate = '20181113' -- for 13 of Nov only
 GROUP BY DPetAcNo
) p

Try this

SELECT DPetAcNo as AcNo,
 SUM(case when DPetDate=CONVERT(date,'20181113',111) then DPetAmount else 0 end) as DayTotal,
 SUM(case when month(DPetDate)= 11 then DPetAmount else 0 end) as MonthTotal
 FROM PettyDetail
 WHERE DPetComCode='15'
 and DPetLocCode='01'
 GROUP BY DPetAcNo

How about a self join ?

SELECT 
 DPetAcNo AcNo
, DayTotal
, SUM(DPetAmount) MonthlyTotal
FROM PettyDetail pd
JOIN (
 SELECT 
 DPetAcNo AcNo
 , SUM(DPetAmount) DayTotal
 FROM 
 PettyDetail
 WHERE 
 DPetComCode='15'
 AND DPetLocCode='01'
 AND DPetDate = CONVERT(DATE,'20181113',111)
 GROUP BY DPetAcNo
) pd2 ON pd2.AcNo = dp.DPetAcNo
WHERE 
 MONTH(DPetDate) = MONTH(CONVERT(DATE,'20181113',111)) 
AND YEAR(DPetDate) = YEAR(CONVERT(DATE,'20181113',111))
GROUP BY DPetAcNo, DayTotal