Last post Nov 19, 2019 08:26 AM by samwu
Nov 17, 2019 01:27 PM|ahmedsalahaddin|LINK
the following query is displaying the result i want except i want it to show 0 for each month with non production.
SELECT DATENAME(MONTH, DATEADD(M, MONTH(PolicyDetails.IssuedDate), - 1)) AS Month, SUM(PolicyDetails.Premium) AS TotalProduction, DATENAME(YEAR, PolicyDetails.IssuedDate) AS Year FROM PolicyDetails INNER JOIN Clients ON PolicyDetails.ClientId = Clients.ClientId WHERE (Clients.Username = @Username) GROUP BY MONTH(PolicyDetails.IssuedDate), DATENAME(YEAR, PolicyDetails.IssuedDate)
in this table i want to show "2019 - September" with Total Production = 0 instead of displaying nothing. How ??
Nov 17, 2019 01:59 PM|mgebhard|LINK
Most likely there are no records for September in the result set. Create a table that contains the months and years you want in the report and do a left join with the new months/years table.
Nov 18, 2019 12:09 AM|oned_gk|LINK
You should have a list of months, join the list with the results
Nov 19, 2019 08:26 AM|samwu|LINK
the following query is displaying the result i want except i want it to show 0 for each month with non production
According to your description, you can refer to this demo.
CREATE TABLE MyTable (
INSERT MyTable VALUES
DECLARE @range_start date = '2015/01/01'
DECLARE @range_end date = '2015/04/30'
[Year] = YEAR(DATEADD(month,[i],@range_start))
,[Month] = DATENAME(month,DATEADD(month,[i],@range_start))
,[Amount] = ISNULL(SUM([Value]),0)
SELECT TOP (DATEDIFF(month,@range_start,@range_end)+1)
ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1 [i]
LEFT JOIN MyTable t2
ON (t1.[i] = DATEDIFF(month,@range_start,t2.[Date]) )
GROUP BY [i]
ORDER BY [i]