DECLARE @REPORT_GENERATION_DATE DATE = '2019-07-31'
DECLARE @REPORT_START_PERIOD DATE
IF MONTH(@REPORT_GENERATION_DATE) > 3
BEGIN
SET @REPORT_START_PERIOD =CAST(CAST(YEAR(@REPORT_GENERATION_DATE) AS VARCHAR)+'-'+'04'+'-'+'01' AS DATE)
PRINT @REPORT_START_PERIOD
END
ELSE
BEGIN
SET @REPORT_START_PERIOD = CAST(CAST(YEAR(GETDATE())-1 AS VARCHAR)+'-'+'04'+'-'+'01' AS DATE)
PRINT @REPORT_START_PERIOD
END
PRINT @REPORT_GENERATION_DATE
IF OBJECT_ID('TEMPDB..#TEMP_Insurance') IS NOT NULL DROP TABLE #TEMP_Insurance
select [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],YEAR(CONVERT(VARCHAR(11), CONVERT(datetime, T2.InsPolicyCreatedDate ) , 106)) AS FY,LEFT(DATENAME(MONTH,CONVERT(VARCHAR(11), CONVERT(datetime, T2.InsPolicyCreatedDate
) , 106)),3) AS [Month],InsPolicyCreatedDate AS PDATE,count(T2. policyNo) as [Insurance Sales]
into #TEMP_Insurance
from [dbo].[re_policy_transaction] T2
WHERE CAST(T2.InsPolicyCreatedDate AS DATE) BETWEEN @REPORT_START_PERIOD AND @REPORT_GENERATION_DATE
and (ltrim(rtrim(ISCANCELLEDSTATUS)) = 0 ) AND T2.Policystatus='New'
group by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],YEAR(CONVERT(VARCHAR(11), CONVERT(datetime, T2.InsPolicyCreatedDate ) , 106)),LEFT(DATENAME(MONTH,CONVERT(VARCHAR(11), CONVERT(datetime, T2.InsPolicyCreatedDate
) , 106)),3),InsPolicyCreatedDate
IF OBJECT_ID('TEMPDB..#TEMP_Addon') IS NOT NULL DROP TABLE #TEMP_Addon
select [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],YEAR(CONVERT(VARCHAR(11), CONVERT(datetime, T2.InsPolicyCreatedDate ) , 106)) AS FY,LEFT(DATENAME(MONTH,CONVERT(VARCHAR(11), CONVERT(datetime, T2.InsPolicyCreatedDate
) , 106)),3) AS [Month],InsPolicyCreatedDate AS PDATE,count(T2. policyNo) as [Add on Sales]
into #TEMP_Addon
from [dbo].[re_policy_transaction] T2
WHERE CAST(T2.InsPolicyCreatedDate AS DATE) BETWEEN @REPORT_START_PERIOD AND @REPORT_GENERATION_DATE
and (ltrim(rtrim(ISCANCELLEDSTATUS)) = 0 ) AND T2.addon=1 and T2.Policystatus='New'
group by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],YEAR(CONVERT(VARCHAR(11), CONVERT(datetime, T2.InsPolicyCreatedDate ) , 106)),LEFT(DATENAME(MONTH,CONVERT(VARCHAR(11), CONVERT(datetime, T2.InsPolicyCreatedDate
) , 106)),3),InsPolicyCreatedDate
IF OBJECT_ID('TEMPDB..#TEMP_Vehicle') IS NOT NULL DROP TABLE #TEMP_Vehicle
select [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],YEAR(T2.created_date) AS FY,LEFT(DATENAME(MONTH,T2.created_date),3) AS [Month],t2.INVOICE_DATE AS PDATE,count(T2.id) as [Vehicle Sales]
into #TEMP_Vehicle
from RE_DMS_DATA_TRANSACTION T2 Where CAST(T2.INVOICE_DATE AS DATE) BETWEEN @REPORT_START_PERIOD AND @REPORT_GENERATION_DATE
group by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],YEAR(T2.created_date),LEFT(DATENAME(MONTH,T2.created_date),3),t2.INVOICE_DATE
--Insurance MTD/YTD
IF OBJECT_ID('TEMPDB..#FINAL_Insurance') IS NOT NULL DROP TABLE #FINAL_Insurance
Count([Insurance Sales]) over (partition by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],[FY],[Month] order by [FY],[Month] rows between
unbounded preceding and unbounded following ) as [Insurance Sales MTD],
Count([Insurance Sales]) over (partition by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],[FY] order by [FY] rows between
unbounded preceding and unbounded following ) as [Insurance Sales YTD]
FROM #TEMP_Insurance WHERE [FY] <= YEAR(@REPORT_GENERATION_DATE) AND cast(PDATE as date ) < = @REPORT_GENERATION_DATE
) T
--Add on MTD/YTD
IF OBJECT_ID('TEMPDB..#FINAL_Addon') IS NOT NULL DROP TABLE #FINAL_Addon
Sum([Add on Sales]) over (partition by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],[FY],[Month] order by [FY],[Month] rows between
unbounded preceding and unbounded following ) as [Ad on Sales MTD],
Sum([Add on Sales]) over (partition by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],[FY] order by [FY] rows between
unbounded preceding and unbounded following ) as [Ad on Sales YTD]
FROM #TEMP_Addon WHERE [FY] <= YEAR(@REPORT_GENERATION_DATE) AND cast(PDATE as date ) < = @REPORT_GENERATION_DATE
) T
--Vehicle MTD/YTD
IF OBJECT_ID('TEMPDB..#FINAL_Vehicle') IS NOT NULL DROP TABLE #FINAL_Vehicle
Sum([Vehicle Sales]) over (partition by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],[FY],[Month] order by [FY],[Month] rows between
unbounded preceding and unbounded following ) as [Vehicle Sales MTD],
Sum([Vehicle Sales]) over (partition by [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],[FY] order by [FY] rows between
unbounded preceding and unbounded following ) as [Vehicle Sales YTD]
FROM #TEMP_Vehicle WHERE [FY] <= YEAR(@REPORT_GENERATION_DATE) AND cast(PDATE as date ) < = @REPORT_GENERATION_DATE
) T
SELECT
[Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code] ,T1.[FY], T1.[Month],
sum (T1.[Vehicle Sales MTD] ) AS [Vehicle Sales MTD] ,
sum ( T1.[Vehicle Sales YTD]) AS [Vehicle Sales YTD],
NULL as [Insurance Sales MTD],
NULL as [Insurance Sales YTD],
NULL AS [Ad on Sales MTD] ,
NULL AS [Ad on Sales YTD] ,
count(T1.[Vehicle Sales MTD] ) over (partition by T1.[FY] order by [FY],[Month] rows between
unbounded preceding and unbounded following ) as [sum Vehicle Sales MTD] ,
Sum(T1.[Vehicle Sales MTD] ) over (partition by T1.[FY] order by [FY] rows between
unbounded preceding and unbounded following ) as [sum Vehicle Sales YTD] ,
NULL as [ sum Insurance Sales MTD] ,
NULL as [ sum Insurance Sales ytd] ,
NULL AS [sum Ad on Sales MTD] ,
NULL AS [sum Ad on Sales YTD]
FROM #FINAL_Vehicle T1 GROUP BY [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code] ,T1.[FY], T1.[Month] ,T1.[Vehicle Sales MTD]
union
SELECT
[Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code] ,T2.[FY], T2.[Month],
NULL as [Vehicle Sales MTD],
NULL as [Vehicle Sales YTD],
count (T2.[Insurance Sales MTD] ),
sum (T2.[Insurance Sales YTD] ),
NULL AS [Ad on Sales MTD] ,
NULL AS [Ad on Sales YTD] ,
NULL as [sum Vehicle Sales MTD],
NULL as [sum Vehicle Sales YTD],
count(T2.[Insurance Sales MTD]) over (partition by t2.[FY] order by [FY],[Month] rows between
unbounded preceding and unbounded following ) as [ sum Insurance Sales MTD] ,
SUM(T2.[Insurance Sales MTD]) over (partition by t2.[FY] order by [FY] rows between
unbounded preceding and unbounded following ) as [ sum Insurance Sales ytd] ,
NULL AS [sum Ad on Sales MTD] ,
NULL AS [sum Ad on Sales YTD]
FROM #FINAL_Insurance T2 GROUP BY [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code] ,T2.[FY], T2.[Month], T2.[Insurance Sales MTD]
UNION
SELECT [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code] ,T3.[FY], T3.[Month],
NULL AS [Vehicle Sales MTD] ,
NULL AS [Vehicle Sales YTD],
NULL as [Insurance Sales MTD],
NULL as [Insurance Sales YTD],
SUM (T3.[Ad on Sales MTD] ),
SUM (T3.[Ad on Sales YTD]) ,
NULL as [sum Vehicle Sales MTD],
NULL as [sum Vehicle Sales YTD],
NULL as [ sum Insurance Sales MTD] ,
NULL as [ sum Insurance Sales ytd] ,
count(T3.[Ad on Sales MTD] ) over (partition by T3.[FY] order by [FY],[Month] rows between
unbounded preceding and unbounded following ) as [sum Ad on Sales MTD] ,
Sum(T3.[Ad on Sales MTD] ) over (partition by T3.[FY] order by [FY] rows between
unbounded preceding and unbounded following ) as [sum Ad on Sales YTD]
FROM #FINAL_Addon T3 GROUP BY [Dealermasterzone],[DealermasterRegion],[Dealermasterstate],DealermasterName,[Dealer Code],T3.[FY], T3.[Month] ,T3.[Ad on Sales MTD]
DROP TABLE #TEMP_Insurance
DROP TABLE #TEMP_Addon
DROP TABLE #TEMP_Vehicle
DROP TABLE #FINAL_Insurance
DROP TABLE #FINAL_Addon
DROP TABLE #FINAL_Vehicle
SET NOCOUNT OFF
GO
Moved byDave PatrickMVPWednesday, May 13, 2020 12:09 PMlooking for forum
Please put a bit more effort in your question before asking others to do it and help you on their free time
1. Please format the code in a readable way (especially importation in long code)
1.1 Use tabs/space to indent the start of the line
1.2 use code editor. Above the text-box where you add the content of the message you have few formatting option. There is a button there to "Insert Code Block". Please use it
2. Elaborate what you need need and what you get and not just short title like"Getting wrong sum of mtd and sum of ytd".
The content of the message should be use for content and not just for code sample
3. We cannot do a lot with your query if it is based on entities which we do not have! for example how can I test you code if I have no table named [re_policy_transaction] ?!?
4. Try to provide the simplest demonstration which lead to the problematic scenario. If you can replicate the scenarion with a simpler queries and entities (tables) then please do so
Getting wrong sum of mtd and sum of ytd
