Insert a record even if no records for that month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert a record even if no records for that month

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2010-08-01 : 14:06:31

I have a temp table in a stored proc..

I insert totals by grouping by month but when i dont have any records for that month I still want to enter a record for that month

INSERT INTO ##Output
SELECT Summary AS Summary,
SUM(hired_ind) AS Total,
MonthNumber(HiredDate) AS Month#
FROM ##CoreInfo

What I have:
##Output
Summary Total Month#
Position1 5 1
Position1 3 2
Position1 3 5
Position1 6 6
Position1 2 7
Position1 1 9
Position1 1 10
Position1 2 11
Position1 3 12
Position2 3 2
Position2 3 3
Position2 6 4
Position2 3 5
Position2 2 7
Position2 1 8
Position2 1 9
Position2 1 10
Position2 2 11

Desired Result
##Output
Summary Total Month#
Position1 5 1
Position1 3 2
Position1 0 3
Position1 0 4
Position1 3 5
Position1 6 6
Position1 2 7
Position1 0 8
Position1 1 9
Position1 1 10
Position1 2 11
Position1 3 12
Position2 0 1
Position2 3 2
Position2 3 3
Position2 6 4
Position2 3 5
Position2 0 6
Position2 2 7
Position2 1 8
Position2 1 9
Position2 1 10
Position2 2 11
Position2 0 12

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-01 : 14:58:25

[code]INSERT INTO ##Output
SELECT Summary,
SUM(hird_ind) AS Total,
mn AS [Month#]
FROM (
SELECT Summary,
hired_ind,
DATEPART(MONTH, HiredDate) AS mn
FROM ##CoreInfo

UNION ALL

SELECT s.Summary,
0 AS hired_ind,
m.m AS mn
FROM (
SELECT Summary
FROM #CoreInfo
GROUP BY Summary
) AS s
CROSS JOIN (
SELECT 1 AS m UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12
) AS m
) AS d
GROUP BY Summary,
mn
ORDER BY Summary,
mn[/code]

N 56°04'39.26"
E 12°55'05.63"

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2010-08-01 : 17:13:23

Great! I have a question. PLease see the query below.
For some reason when I run this I ALMOST :) get desired result..
I do have some summaries that dont have all 12 months? ?

INSERT INTO	##Output
SELECT		Summary,
		SUM(hired_ind) AS Total,
		SUM(PrevMonthHired_ind) AS Total,
		SUM(PrevYearHired_ind) AS Total,
		mn AS [Month#]
FROM		(      SELECT	Summary,
				1 AS hired_ind,
                                0 AS PrevMonthHired_Ind,
                                0 AS PrevYearHired_Ind,
				DATEPART(MONTH, HiredDate) AS mn
			FROM	##CoreInfo
                        WHERE HiredDate = @param_Dt
                        GROUP BY Summary, DATEPART(MONTH, HiredDate)

                        UNION
 
                        SELECT	Summary,
				0 AS hired_ind,
                                1 AS PrevMonthHired_Ind,
                                0 AS PrevYearHired_Ind,
				DATEPART(MONTH, HiredDate) AS mn
			FROM	##CoreInfo
                        WHERE HiredDate = DATEADD(MONTH,@param_Dt,-1)
                        GROUP BY Summary, DATEPART(MONTH, HiredDate)

                        UNION

			SELECT	Summary,
				0 AS hired_ind,
                                0 AS PrevMonthHired_Ind,
                                1 AS PrevYearHired_Ind,
				DATEPART(MONTH, HiredDate) AS mn
			FROM	##CoreInfo
                        WHERE HiredDate = DATEADD(YEAR,@param_Dt,-1)
                        GROUP BY Summary, DATEPART(MONTH, HiredDate)

			UNION ALL

			SELECT		s.Summary,
				        0 AS hired_ind,
                                        0 AS PrevMonthHired_Ind,
                                        0 AS PrevYearHired_Ind,
					m.m AS mn
			FROM		(
						SELECT		Summary
						FROM		#CoreInfo
						GROUP BY	Summary
					) AS s
			CROSS JOIN	(
						SELECT	 1 AS m UNION ALL
						SELECT	 2 UNION ALL
						SELECT	 3 UNION ALL
						SELECT	 4 UNION ALL
						SELECT	 5 UNION ALL
						SELECT	 6 UNION ALL
						SELECT	 7 UNION ALL
						SELECT	 8 UNION ALL
						SELECT	 9 UNION ALL
						SELECT	10 UNION ALL
						SELECT	11 UNION ALL
						SELECT	12
					) AS m
		) AS d
GROUP BY	Summary,
		mn
ORDER BY	Summary,
		mn

parody
Posting Yak Master

111 Posts

Posted - 2010-08-02 : 04:33:32

Something like this should do it, or alternatively you could union the cte with your table and group over it.

;WITH Months AS
(
SELECT 'Missing' AS Summary,0 AS hired_ind,1 AS MonthNumber
UNION ALL
SELECT 'Missing',0 AS hired_ind,MonthNumber + 1
FROM Months
WHERE MonthNumber < 12
)

--INSERT INTO ##Output
SELECT ISNULL(b.Summary,a.Summary) AS Summary,
SUM(ISNULL(b.hired_ind,a.hired_ind)) AS Total,
ISNULL(month(b.HiredDate),a.MonthNumber) AS Month#
FROM Months a
LEFT JOIN ##CoreInfo b
ON a.MonthNumber = Month(b.HiredDate)
GROUP BY ISNULL(b.Summary,a.Summary),ISNULL(month(b.HiredDate),a.MonthNumber)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources