I am trying to generate a sales report on month wise. the proble is that , if there is not sales of a particuar item in a month then that month will be not show. i have browsed many blog , as a newbie i cant able to understand. many of them say to create
a temp table and left join with that. please find my table and provide me the complete code.
Note : there is no data for the month of Mar-2006 . so it hide the month in the report.
if posible provide a query to check the OCRNAME and ItmsGrpName for the date Range and if there is no data add a row for OCRNAME and ItmsGrpName and its DocTotalSy will be 0.
Please refer the Link
here. Go through all the example. I hope you can solve the problem yourself after you understand the importance of Joins, Join Type and Oder of the join. Execution sequence also helpful in some situation.
DECLARE @StartDate AS DATETIME
SET @StartDate = '01-JAN-2006'
DECLARE @EndDate AS DATETIME
SET @EndDate = '31-DEC-2006'
IF object_id('tempdb..#temp') is not null drop table #temp
CREATE TABLE #TEMP
(
[DATE] DATE
)
WHILE MONTH(@StartDate) < = MONTh(@EndDate) AND YEAR(@StartDate) = YEAR (@EndDate)
BEGIN
INSERT INTO #TEMP VALUES (@StartDate)
SET @StartDate = DATEADD(Month,1,@StartDate)
END
SELECT ISNULL(Sales.FirmName,'') AS FirmName ,ISNULL(Sales.OcrName,0) AS OcrName ,ISNULL(Sales.DocTotalSy,0) AS DocTotalSy
,CONVERT(VARCHAR(20),DATENAME(MONTH, T.[DATE])) + ' - ' + CONVERT(VARCHAR(5),YEAR(T.[DATE])) AS [MONTHYEAR]
FROM #TEMP AS T LEFT JOIN Table_2 AS Sales ON MONTH(T.[DATE]) = MONTH(Sales.DocDate) AND YEAR(T.[DATE]) = YEAR(Sales.DocDate)
IF object_id('tempdb..#temp') is not null drop table #temp
mdirshad20
Member
10 Points
29 Posts
Need to Show the months in the report where data is not present.
Nov 26, 2012 06:35 AM|LINK
Dear,
I am trying to generate a sales report on month wise. the proble is that , if there is not sales of a particuar item in a month then that month will be not show. i have browsed many blog , as a newbie i cant able to understand. many of them say to create a temp table and left join with that. please find my table and provide me the complete code.
Note : there is no data for the month of Mar-2006 . so it hide the month in the report.
if posible provide a query to check the OCRNAME and ItmsGrpName for the date Range and if there is no data add a row for OCRNAME and ItmsGrpName and its DocTotalSy will be 0.
Sirama
Member
285 Points
68 Posts
Re: Need to Show the months in the report where data is not present.
Nov 26, 2012 07:56 AM|LINK
Please refer the Link here. Go through all the example. I hope you can solve the problem yourself after you understand the importance of Joins, Join Type and Oder of the join. Execution sequence also helpful in some situation.
sandeepmitta...
Contributor
6779 Points
1058 Posts
Re: Need to Show the months in the report where data is not present.
Nov 26, 2012 08:40 AM|LINK
Refer this link
http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-months-in-sql-server.html
Sandeep Mittal | My Blog - IT Developer Zone
mdirshad20
Member
10 Points
29 Posts
Re: Need to Show the months in the report where data is not present.
Nov 26, 2012 09:58 AM|LINK
Thanks for your replay,
I have gone through your link.
But my report will be on date range like startdate enddate and for example 1/1/2006 to 1/1/2009.
please let me know any other example or code.
is it posible to hard code '0' in the DocTotalSy in table dynamically ,if there is no record in that month.
sandeepmitta...
Contributor
6779 Points
1058 Posts
Re: Need to Show the months in the report where data is not present.
Nov 26, 2012 12:24 PM|LINK
For date wise, refer this link
http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-dates-in-sql-server.html
Sandeep Mittal | My Blog - IT Developer Zone
nikunjnandan...
Participant
882 Points
223 Posts
Re: Need to Show the months in the report where data is not present.
Nov 26, 2012 12:42 PM|LINK
Hiii,
DECLARE @StartDate AS DATETIME SET @StartDate = '01-JAN-2006' DECLARE @EndDate AS DATETIME SET @EndDate = '31-DEC-2006' IF object_id('tempdb..#temp') is not null drop table #temp CREATE TABLE #TEMP ( [DATE] DATE ) WHILE MONTH(@StartDate) < = MONTh(@EndDate) AND YEAR(@StartDate) = YEAR (@EndDate) BEGIN INSERT INTO #TEMP VALUES (@StartDate) SET @StartDate = DATEADD(Month,1,@StartDate) END SELECT ISNULL(Sales.FirmName,'') AS FirmName ,ISNULL(Sales.OcrName,0) AS OcrName ,ISNULL(Sales.DocTotalSy,0) AS DocTotalSy ,CONVERT(VARCHAR(20),DATENAME(MONTH, T.[DATE])) + ' - ' + CONVERT(VARCHAR(5),YEAR(T.[DATE])) AS [MONTHYEAR] FROM #TEMP AS T LEFT JOIN Table_2 AS Sales ON MONTH(T.[DATE]) = MONTH(Sales.DocDate) AND YEAR(T.[DATE]) = YEAR(Sales.DocDate) IF object_id('tempdb..#temp') is not null drop table #tempReplace Table_2 with your sales table.
Still you can optimize this query.
Nikunj Nandaniya
My Blog
mdirshad20
Member
10 Points
29 Posts
Re: Need to Show the months in the report where data is not present.
Nov 26, 2012 02:01 PM|LINK
Hai ,
Thanks for Replay.. but the example you have given does not suits me..
My intestion is to display the columns in the Cross tab for every month even the data is not there.
For example if you see my table . there is no data for the month of March and May to Dec. if i use this table in my cross tab crystal report.
it will show only the column or month which have data.
SO My base requirement is To insert a default row every month for each group. In my table the group is ItmsGrpName.
No matter the data is present or not. i should insert a row with default vaule for the selected date range.
mdirshad20
Member
10 Points
29 Posts
Re: Need to Show the months in the report where data is not present.
Nov 26, 2012 02:49 PM|LINK
hai Sandeep / all,
I have reffered the link http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-dates-in-sql-server.html
. its suits me only if there is nested group by levels in that.
1. Group By OcrName
2. Group By FirmName
3. Group By ItmsGrpName
Can you modify the query and send me please..
sandeepmitta...
Contributor
6779 Points
1058 Posts
Re: Need to Show the months in the report where data is not present.
Nov 27, 2012 03:36 AM|LINK
Please share you table structure with sample data and expected output.
Sandeep Mittal | My Blog - IT Developer Zone
mdirshad20
Member
10 Points
29 Posts
Re: Need to Show the months in the report where data is not present.
Nov 27, 2012 07:19 AM|LINK
Dear All,
Following is my tables
SALES DATA TABLE:
THe Join i used to get those data is
SELECT "OMRC"."FirmName", "OITB"."ItmsGrpNam", "OOCR"."OcrName", "ORDR"."DocDate", "ORDR"."DocTotalSy" ,"RDR1"."BaseRef"
FROM (((("Demo"."dbo"."ORDR" "ORDR" INNER JOIN "Demo"."dbo"."RDR1" "RDR1" ON "ORDR"."DocEntry"="RDR1"."DocEntry") INNER JOIN "Demo"."dbo"."OITM" "OITM" ON "RDR1"."ItemCode"="OITM"."ItemCode") INNER JOIN "Demo"."dbo"."OOCR" "OOCR" ON "RDR1"."OcrCode"="OOCR"."OcrCode") INNER JOIN "Demo"."dbo"."OITB" "OITB" ON "OITM"."ItmsGrpCod"="OITB"."ItmsGrpCod") INNER JOIN "Demo"."dbo"."OMRC" "OMRC" ON "OITM"."FirmCode"="OMRC"."FirmCode"
WHERE "ORDR"."DocDate" >= '2006-01-01' AND "ORDR"."DocDate" <= '2006-12-30'
Structure: