October 1, 2008 at 1:25 pm
I need to do an insert where the month is missing with zero amounts. For example, the data is...
group name month month_name amount
IT john 1 jan 10.00
IT john 3 mar 20.00
Sales gale 5 may 30.00
For John, I need to insert rows for all months except for jan and mar.
For Gale, I need to insert all rows except for may.
The result would be...
group name month month_name amount
IT john 1 jan 10.00
IT john 2 feb 0.00
IT john 3 mar 20.00
IT john 4 apr 0.00
IT john 5 may 0.00
IT john 6 jun 0.00
IT john 7 jul 0.00
IT john 8 aug 0.00
IT john 9 sep 0.00
IT john 10 oct 0.00
IT john 11 nov 0.00
IT john 12 dec 0.00
Sales gale 1 jan 10.00
Sales gale 2 feb 0.00
Sales gale 3 mar 0.00
Sales gale 4 apr 0.00
Sales gale 5 may 30.00
Sales gale 6 jun 0.00
Sales gale 7 jul 0.00
Sales gale 8 aug 0.00
Sales gale 9 sep 0.00
Sales gale 10 oct 0.00
Sales gale 11 nov 0.00
Sales gale 12 dec 0.00
Help please?
October 1, 2008 at 1:29 pm
What you have posted does not have enough information to provide you with a good answer. Please read the article in my signature block and add to your post.
October 2, 2008 at 6:27 am
As bitbucket noted, this question is somewhat limited on information. I went ahead and scripted up a solution(mainly because it gave me another opportunity to use a cross join), but I had to make a lot of assumptions, or it fails. The biggest of these is that name is uniquely tied to each employee in this table. If it is not, an employeeID would have to be used in my query instead of name.
Note that this solution as it is handles only getting the name/months into your original table. To be a complete solution, you'll need to add in the values to all the other fields when doing the final update back to your table. I would nest the final insert back into your table into a transaction so that you can roll it back if anything didn't go right.
CREATE TABLE #OriginalTable( -- Generate some test data similar to yours
[name]varchar(20),
[month]int)
INSERT INTO #OriginalTable([name],[month]) VALUES('john',1)
INSERT INTO #OriginalTable([name],[month]) VALUES('john',3)
INSERT INTO #OriginalTable([name],[month]) VALUES('steve',2)
INSERT INTO #OriginalTable([name],[month]) VALUES('steve',4)
INSERT INTO #OriginalTable([name],[month]) VALUES('steve',6)
INSERT INTO #OriginalTable([name],[month]) VALUES('gale',5)
SELECT DISTINCT [name] -- Grab distinct names into a temp table.
INTO #1
FROM #OriginalTable
CREATE TABLE #2(
Identint identity(1,1),
[name]varchar(20),
Nint)
INSERT INTO #2(name, N) -- Generate all the months for all the names
SELECT name, N -- If you don't have a tally table, just build a temp table with 1-12 in it.
FROM #1
CROSS JOIN tally
WHERE n < 13
SELECT Ident -- Figure out which name/month combos you already have in your Table
INTO #3
FROM #2 LEFT JOIN #OriginalTable O ON #2.[Name] = O.[Name] AND #2.N = O.[Month]
WHERE O.[Month] IS NOT NULL
DELETE FROM #2 -- Get rid of rows already in your table.
WHERE Ident IN (SELECT DISTINCT Ident FROM #3)
BEGIN TRAN
INSERT INTO #OriginalTable([name], [month])
SELECT Name, N FROM #2 -- Add name/months that aren't in your original.
---- Show that the data we added is correct ----
SELECT * FROM #originalTable
ORDER BY [name], [month]
---- If it is, commit the transaction ----
--COMMIT TRAN
---- If it is not, roll it back and figure out what went wrong
--ROLLBACK TRAN
October 2, 2008 at 10:57 am
This is exactly what I need. I totally fogot about the Identity field. Thanks.
October 6, 2008 at 9:01 am
With the understanding that bit et al replied to OP's request, can I ask a stupid question?
What is the need for inserting rows with no value into the table? Is it not an option to simply extract any records with data and display 0 if null?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 6, 2008 at 9:16 am
jcrawf02 (10/6/2008)
With the understanding that bit et al replied to OP's request, can I ask a stupid question?What is the need for inserting rows with no value into the table? Is it not an option to simply extract any records with data and display 0 if null?
Because I am doing Matrix report in SRS and need to report on months with zeros also.
October 6, 2008 at 9:45 am
is250sp (10/6/2008)
jcrawf02 (10/6/2008)
With the understanding that bit et al replied to OP's request, can I ask a stupid question?What is the need for inserting rows with no value into the table? Is it not an option to simply extract any records with data and display 0 if null?
Because I am doing Matrix report in SRS and need to report on months with zeros also.
Having never done that myself, I'm compelled to ask a followup stupid question:
SSRS isn't capable of something like: SELECT month_name, name, isnull(sum(amount),0) FROM #OriginalTable?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 6, 2008 at 9:54 am
Sure it is, but that's not going to add rows for months that aren't in the original data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2008 at 10:30 am
True, sorry, bad example. I was just thinking that it would be better to create the month names on the fly and sum up the amounts within the months on report generation, rather than hold empty data in the db.
I'm sure there's a cleaner way to do this display, I left in the month value to order the text names.
--============================================================================================
-- Create and populate a Tally table
--By Jeff Moden, 2008/05/07 http://qa.sqlservercentral.com/articles/TSQL/62867/
--============================================================================================
--===== Conditionally drop and create the table/Primary Key
IF OBJECT_ID('Tempdb..#Tally') IS NOT NULL BEGIN DROP TABLE #Tally END
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
--/*
ALTER TABLE #Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N)
WITH FILLFACTOR = 100
--*/
--===========================================END TALLY TABLE SETUP========================================
-- =============================================
-- Create temp table
-- =============================================
IF object_id('Tempdb..#origTable') IS NOT NULL
BEGIN DROP TABLE #origTable END
CREATE TABLE #origTable
(iRow int identity(1,1),
[group] varchar(5),
name varchar(15),
month int NULL,
month_name varchar(15),
amount float)
--===== Add a Primary Key to maximize performance
IF OBJECT_ID('Tempdb..PK_#origTable_iRow') IS NULL
BEGIN
ALTER TABLE #origTable
ADD CONSTRAINT PK_#origTable_iRow
PRIMARY KEY CLUSTERED (iRow)
WITH FILLFACTOR = 100
END
INSERT INTO #origTable VALUES ('IT','john',1,'jan',10.00)
INSERT INTO #origTable VALUES ('IT','john',3,'mar',20.00)
INSERT INTO #origTable VALUES ('Sales','gale',1,'jan',10.00)
INSERT INTO #origTable VALUES ('Sales','gale',5,'may',30.00)
SELECT month(dateadd(mm,N-1,'1/1/2008')),
datename(mm,dateadd(mm,N-1,'1/1/2008'))+' '+datename(yyyy,dateadd(mm,N-1,'1/1/2008')) AS "Month",
ISNULL([group],'') AS "Group",
ISNULL(name,'') AS "Name",
sum(ISNULL(amount,0)) AS Amount
FROM #Tally left join #origTable on #tally.N = #origTable.month
WHERE dateadd(mm,N-1,'1/1/2008')<getDate()
GROUP BY month(dateadd(mm,N-1,'1/1/2008')),
datename(mm,dateadd(mm,N-1,'1/1/2008'))+' '+datename(yyyy,dateadd(mm,N-1,'1/1/2008')),
#origTable.[group],
#origTable.name
ORDER BY month(dateadd(mm,N-1,'1/1/2008'))
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply