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)
<div>
Month
Total Production -$$
2019 - August
30.00
2019 - October
45.00
in this table i want to show "2019 - September" with Total Production = 0 instead of displaying nothing. How ??
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.
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 (
[TransactionID] int
,[Date] date
,[Value] money
)
INSERT MyTable VALUES
(1,'2015/01/01','£10')
,(2,'2015/01/16','£15')
,(3,'2015/01/21','£5 ')
,(4,'2015/03/15','£20')
,(5,'2015/03/12','£15')
,(6,'2015/04/23','£10')
DECLARE @range_start date = '2015/01/01'
DECLARE @range_end date = '2015/04/30'
SELECT
[Year] = YEAR(DATEADD(month,[i],@range_start))
,[Month] = DATENAME(month,DATEADD(month,[i],@range_start))
,[Amount] = ISNULL(SUM([Value]),0)
FROM (
SELECT TOP (DATEDIFF(month,@range_start,@range_end)+1)
ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1 [i]
FROM master.dbo.spt_values
) t1
LEFT JOIN MyTable t2
ON (t1.[i] = DATEDIFF(month,@range_start,t2.[Date]) )
GROUP BY [i]
ORDER BY [i]
The result:
Best regards,
Sam
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
14 Points
53 Posts
a query displaying 0 instead of not showing
Nov 17, 2019 01:27 PM|ahmedsalahaddin|LINK
hello,
the following query is displaying the result i want except i want it to show 0 for each month with non production.
<div>in this table i want to show "2019 - September" with Total Production = 0 instead of displaying nothing. How ??
</div>All-Star
53711 Points
24042 Posts
Re: a query displaying 0 instead of not showing
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.
All-Star
52813 Points
15769 Posts
Re: a query displaying 0 instead of not showing
Nov 18, 2019 12:09 AM|oned_gk|LINK
You should have a list of months, join the list with the results
Suwandi - Non Graduate Programmer
Contributor
3370 Points
1409 Posts
Re: a query displaying 0 instead of not showing
Nov 19, 2019 08:26 AM|samwu|LINK
Hi ahmedsalahaddin,
According to your description, you can refer to this demo.
CREATE TABLE MyTable ( [TransactionID] int ,[Date] date ,[Value] money ) INSERT MyTable VALUES (1,'2015/01/01','£10') ,(2,'2015/01/16','£15') ,(3,'2015/01/21','£5 ') ,(4,'2015/03/15','£20') ,(5,'2015/03/12','£15') ,(6,'2015/04/23','£10') DECLARE @range_start date = '2015/01/01' DECLARE @range_end date = '2015/04/30' SELECT [Year] = YEAR(DATEADD(month,[i],@range_start)) ,[Month] = DATENAME(month,DATEADD(month,[i],@range_start)) ,[Amount] = ISNULL(SUM([Value]),0) FROM ( SELECT TOP (DATEDIFF(month,@range_start,@range_end)+1) ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1 [i] FROM master.dbo.spt_values ) t1 LEFT JOIN MyTable t2 ON (t1.[i] = DATEDIFF(month,@range_start,t2.[Date]) ) GROUP BY [i] ORDER BY [i]The result:
Best regards,
Sam