Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure 1

Status
Not open for further replies.
bburnell

bburnell

Programmer
Sep 15, 2000
560
US
Hi all,

I am using SQL 2005. I have a stored procedure that does 13 separate selects into a temp table and then does an full outer join to merge them together. It seems like the server is not waiting for one select/insert to end before it starts the next one as I get data for January, March, and May, but not February, April, etc. I know the data for February is there since I have checked with a select statement to verify. Any help would be greatly appreciated.

Thanks!
Brett

Quick Overview:
Pull Jan records to Temp Table 1.
Pull Feb records to Temp Table 2.
Merge Jan & Feb records to Temp Table 3.
Delete #1 and #2
Copy #3 to #1
Delete #3
Pull Mar records to Temp Table 2.
Merge #1 (Jan/Feb) with #2 (Mar) into #3.
and so on...
...
Return * from #3
 
Sort by date Sort by votes
Code: 
USE [Membership]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_NWAgentSignUp_DstSales]
	-- Stored Procedure Parameters --
	@MemYear	NVARCHAR(4)
AS
BEGIN
	-- Added to Prevent Extra Result Sets From Interfering With "SELECT" Statements --
	SET NOCOUNT ON;

	-- Variable Declaration --
	DECLARE @LoDateStamp DATETIME
	DECLARE @HiDateStamp DATETIME
	DECLARE @QuotaYear INT

	-- Initialize Quota Year --
	SET @QuotaYear = YEAR(CAST(@MemYear+' 12:00:00' AS DATETIME))

    -- Create the Main Table (Returned) --
	DECLARE @MainTable TABLE
		(tt_distcode	nvarchar(6),
		tt_mgrname		nvarchar(30),
		tt_distquota	int,
		tt_agentno		nvarchar(9),
		tt_ctyname		nvarchar(20),
		tt_agtfullname	nvarchar(65),
		tt_count1		int NOT NULL DEFAULT 0,
		tt_count2		int NOT NULL DEFAULT 0,
		tt_count3		int NOT NULL DEFAULT 0,
		tt_count4		int NOT NULL DEFAULT 0,
		tt_count5		int NOT NULL DEFAULT 0,
		tt_count6		int NOT NULL DEFAULT 0,
		tt_count7		int NOT NULL DEFAULT 0,
		tt_count8		int NOT NULL DEFAULT 0,
		tt_count9		int NOT NULL DEFAULT 0,
		tt_count10		int NOT NULL DEFAULT 0,
		tt_count11		int NOT NULL DEFAULT 0,
		tt_count12		int NOT NULL DEFAULT 0,
		tt_Totalct		int NOT NULL DEFAULT 0)

    -- Create Temp Table #1 --
	DECLARE @TempTable1 TABLE
		(tt_distcode	nvarchar(6),
		tt_mgrname		nvarchar(30),
		tt_distquota	int,
		tt_agentno		nvarchar(9),
		tt_ctyname		nvarchar(20),
		tt_agtfullname	nvarchar(65),
		tt_count1		int NOT NULL DEFAULT 0,
		tt_count2		int NOT NULL DEFAULT 0,
		tt_count3		int NOT NULL DEFAULT 0,
		tt_count4		int NOT NULL DEFAULT 0,
		tt_count5		int NOT NULL DEFAULT 0,
		tt_count6		int NOT NULL DEFAULT 0,
		tt_count7		int NOT NULL DEFAULT 0,
		tt_count8		int NOT NULL DEFAULT 0,
		tt_count9		int NOT NULL DEFAULT 0,
		tt_count10		int NOT NULL DEFAULT 0,
		tt_count11		int NOT NULL DEFAULT 0,
		tt_count12		int NOT NULL DEFAULT 0,
		tt_Totalct		int NOT NULL DEFAULT 0)

    -- Create Temp Table #2 --
	DECLARE @TempTable2 TABLE
		(tt_distcode	nvarchar(6),
		tt_mgrname		nvarchar(30),
		tt_distquota	int,
		tt_agentno		nvarchar(9),
		tt_ctyname		nvarchar(20),
		tt_agtfullname	nvarchar(65),
		tt_count1		int NOT NULL DEFAULT 0,
		tt_count2		int NOT NULL DEFAULT 0,
		tt_count3		int NOT NULL DEFAULT 0,
		tt_count4		int NOT NULL DEFAULT 0,
		tt_count5		int NOT NULL DEFAULT 0,
		tt_count6		int NOT NULL DEFAULT 0,
		tt_count7		int NOT NULL DEFAULT 0,
		tt_count8		int NOT NULL DEFAULT 0,
		tt_count9		int NOT NULL DEFAULT 0,
		tt_count10		int NOT NULL DEFAULT 0,
		tt_count11		int NOT NULL DEFAULT 0,
		tt_count12		int NOT NULL DEFAULT 0,
		tt_Totalct		int NOT NULL DEFAULT 0)

	-- Set January Date(s) --
	SET @LoDateStamp = '01/01/' + @MemYear
	SET @LoDateStamp = CAST(@LoDateStamp+' 12:00:00 AM' AS DATETIME)
	SET @HiDateStamp = '01/31/' + @MemYear
	SET @HiDateStamp = CAST(@HiDateStamp+' 11:59:59 PM' AS DATETIME)

	-- Insert January Data Here (Temp Table #1) --
	INSERT INTO	@TempTable1
	(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1)
	SELECT	t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname, count(*)
	FROM (SELECT RIGHT('000000' + CAST(tiMNDdistr AS varchar(6)), 6) AS t_distcode,
			RIGHT('000000' + CAST(tiMNAagtno AS varchar(6)), 6) + '-00' AS t_agentno_str,
			UPPER(tcMNDmangr) as t_mgrname, tsMNDquota as t_distquota,
			tiMNSmbrno AS t_membno, ttMNSsincr AS t_signinc, tsMNSmyear AS t_membyr,
			UPPER(dbo.fn_MemberCounty(tiMNAmbrno)) AS t_membcty,
			UPPER(dbo.fn_MNMNameLFM(tiMNAmbrno, ttMNAnincr)) AS t_agtfullname
		FROM dbo.t_MNS_Nationwide_Signups AS MNS
			INNER JOIN dbo.t_MNA_Nationwide_Agents AS MNA ON tiMNAagtno = tiMNSagtno AND ttMNAagtin = 0
            INNER JOIN dbo.t_MND_Nationwide_Districts AS MND ON tiMNDdistr = tiMNAdistr AND tsMNDsyear = @QuotaYear
            INNER JOIN dbo.t_MNM_Names AS MNM ON tiMNMmbrno = tiMNAmbrno AND ttMNMnincr = ttMNAnincr
		--WHERE	(tdMNSadded between '01/01/xxxx 12:00:00 AM' and '01/31/xxxx 11:59:59 PM') AND 
		WHERE	(tdMNSadded between @LoDateStamp and @HiDateStamp) AND 
				(dbo.fn_MPDPreviousYear(tiMNSmbrno,tsMNSmyear) < (tsMNSmyear - 1)) AND
				(dbo.fn_MPDPaymentDate(tiMNSmbrno,tsMNSmyear) <> '' ) ) AS tmptable
		GROUP BY t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname
		ORDER BY t_distcode, t_agtfullname

	-- Set February Date(s) --
	SET @LoDateStamp = '02/01/' + @MemYear
	SET @LoDateStamp = CAST(@LoDateStamp+' 12:00:00 AM' AS DATETIME)

	-- Check For Leap Year --
	DECLARE @p_leap_date SMALLDATETIME
    DECLARE @p_check_day TINYINT
    SET @p_leap_date = CONVERT(VARCHAR(4), @MemYear) + '0228'
    SET @p_check_day = DATEPART(d, DATEADD(d, 1, @p_leap_date))

    IF (@p_check_day = 29)
		-- LEAP Year --
		BEGIN
			SET @HiDateStamp = '02/29/' + @MemYear
		END
	ELSE
		-- NON LEAP Year --
		BEGIN
			SET @HiDateStamp = '02/28/' + @MemYear
		END
	SET @HiDateStamp = CAST(@HiDateStamp+' 11:59:59 PM' AS DATETIME)

	-- Insert February Data Here (Temp Table #2) --
	INSERT INTO	@TempTable2
	(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count2)
	SELECT	t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname, count(*)
	FROM (SELECT RIGHT('000000' + CAST(tiMNDdistr AS varchar(6)), 6) AS t_distcode,
			RIGHT('000000' + CAST(tiMNAagtno AS varchar(6)), 6) + '-00' AS t_agentno_str,
			UPPER(tcMNDmangr) as t_mgrname, tsMNDquota as t_distquota,
			tiMNSmbrno AS t_membno, ttMNSsincr AS t_signinc, tsMNSmyear AS t_membyr,
			UPPER(dbo.fn_MemberCounty(tiMNAmbrno)) AS t_membcty,
			UPPER(dbo.fn_MNMNameLFM(tiMNAmbrno, ttMNAnincr)) AS t_agtfullname
		FROM dbo.t_MNS_Nationwide_Signups AS MNS
			INNER JOIN dbo.t_MNA_Nationwide_Agents AS MNA ON tiMNAagtno = tiMNSagtno AND ttMNAagtin = 0
            INNER JOIN dbo.t_MND_Nationwide_Districts AS MND ON tiMNDdistr = tiMNAdistr AND tsMNDsyear = @QuotaYear
            INNER JOIN dbo.t_MNM_Names AS MNM ON tiMNMmbrno = tiMNAmbrno AND ttMNMnincr = ttMNAnincr
		--WHERE	(tdMNSadded between '02/01/xxxx 12:00:00 AM' and '02/29/xxxx 11:59:59 PM') AND
		WHERE	(tdMNSadded between @LoDateStamp and @HiDateStamp) AND 
				(dbo.fn_MPDPreviousYear(tiMNSmbrno,tsMNSmyear) < (tsMNSmyear - 1)) AND
				(dbo.fn_MPDPaymentDate(tiMNSmbrno,tsMNSmyear) <> '' ) ) AS tmptable
		GROUP BY t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname
		ORDER BY t_distcode, t_agtfullname

	-- Merge the January & February Totals Into the Main Table --
	INSERT INTO @MainTable (tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1, tt_count2)
	SELECT	coalesce(a.tt_distcode, b.tt_distcode) AS tt_distcode,
			coalesce(a.tt_mgrname, b.tt_mgrname) AS tt_mgrname,
			coalesce(a.tt_distquota, b.tt_distquota) AS tt_distquota,
			coalesce(a.tt_agentno, b.tt_agentno) AS tt_agentno,
			coalesce(a.tt_ctyname, b.tt_ctyname) AS tt_ctyname,
			coalesce(a.tt_agtfullname, b.tt_agtfullname) AS tt_agtfullname,
			coalesce(a.tt_count1, b.tt_count1) AS count1,
			coalesce(b.tt_count2, a.tt_count2) AS count2
	FROM @TempTable1 a
	FULL OUTER JOIN @TempTable2 b
	ON		(a.tt_distcode = b.tt_distcode) AND (a.tt_mgrname = b.tt_mgrname) AND
			(a.tt_distquota = b.tt_distquota) AND (a.tt_agentno = b.tt_agentno) AND
			(a.tt_ctyname = b.tt_ctyname) AND (a.tt_agtfullname = b.tt_agtfullname)

	-- Delete the Data in the Temporary Table(s) --
	DELETE FROM @TempTable1
	DELETE FROM @TempTable2

	-- Copy the Main Table to Temp Table #1 --
	INSERT INTO @TempTable1
	(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1,
	 tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8, tt_count9,
	 tt_count10, tt_count11, tt_count12, tt_Totalct)
	SELECT tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname,
	 tt_count1, tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8,
	 tt_count9, tt_count10, tt_count11, tt_count12, tt_Totalct
	FROM		@MainTable
	ORDER BY	tt_distcode

	-- Delete the Data in the Main Table --
	DELETE FROM @MainTable

	-- Set March Date(s) --
	SET @LoDateStamp = '03/01/' + @MemYear
	SET @LoDateStamp = CAST(@LoDateStamp+' 12:00:00 AM' AS DATETIME)
	SET @HiDateStamp = '03/31/' + @MemYear
	SET @HiDateStamp = CAST(@HiDateStamp+' 11:59:59 PM' AS DATETIME)

	-- Insert March Data Here (Temp Table #2) --
	INSERT INTO	@TempTable2
	(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count3)
	SELECT	t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname, count(*)
	FROM (SELECT RIGHT('000000' + CAST(tiMNDdistr AS varchar(6)), 6) AS t_distcode,
			RIGHT('000000' + CAST(tiMNAagtno AS varchar(6)), 6) + '-00' AS t_agentno_str,
			UPPER(tcMNDmangr) as t_mgrname, tsMNDquota as t_distquota,
			tiMNSmbrno AS t_membno, ttMNSsincr AS t_signinc, tsMNSmyear AS t_membyr,
			UPPER(dbo.fn_MemberCounty(tiMNAmbrno)) AS t_membcty,
			UPPER(dbo.fn_MNMNameLFM(tiMNAmbrno, ttMNAnincr)) AS t_agtfullname
		FROM dbo.t_MNS_Nationwide_Signups AS MNS
			INNER JOIN dbo.t_MNA_Nationwide_Agents AS MNA ON tiMNAagtno = tiMNSagtno AND ttMNAagtin = 0
            INNER JOIN dbo.t_MND_Nationwide_Districts AS MND ON tiMNDdistr = tiMNAdistr AND tsMNDsyear = @QuotaYear
            INNER JOIN dbo.t_MNM_Names AS MNM ON tiMNMmbrno = tiMNAmbrno AND ttMNMnincr = ttMNAnincr
		--WHERE	(tdMNSadded between '03/01/xxxx 12:00:00 AM' and '03/31/xxxx 11:59:59 PM') AND 
		WHERE	(tdMNSadded between @LoDateStamp and @HiDateStamp) AND 
				(dbo.fn_MPDPreviousYear(tiMNSmbrno,tsMNSmyear) < (tsMNSmyear - 1)) AND
				(dbo.fn_MPDPaymentDate(tiMNSmbrno,tsMNSmyear) <> '' ) ) AS tmptable
		GROUP BY t_distcode, t_mgrname, t_distquota, t_agentno_str, t_membcty, t_agtfullname
		ORDER BY t_distcode, t_agtfullname

	-- Merge the March Totals Into the Main Table --
	INSERT INTO @MainTable (tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname,
				tt_count1, tt_count2, tt_count3)
	SELECT	coalesce(a.tt_distcode, b.tt_distcode) AS tt_distcode,
			coalesce(a.tt_mgrname, b.tt_mgrname) AS tt_mgrname,
			coalesce(a.tt_distquota, b.tt_distquota) AS tt_distquota,
			coalesce(a.tt_agentno, b.tt_agentno) AS tt_agentno,
			coalesce(a.tt_ctyname, b.tt_ctyname) AS tt_ctyname,
			coalesce(a.tt_agtfullname, b.tt_agtfullname) AS tt_agtfullname,
			coalesce(a.tt_count1, b.tt_count1) AS count1,
			coalesce(b.tt_count2, a.tt_count2) AS count2,
			coalesce(b.tt_count3, a.tt_count3) AS count3
	FROM @TempTable1 a
	FULL OUTER JOIN @TempTable2 b
	ON		(a.tt_distcode = b.tt_distcode) AND (a.tt_mgrname = b.tt_mgrname) AND
			(a.tt_distquota = b.tt_distquota) AND (a.tt_agentno = b.tt_agentno) AND
			(a.tt_ctyname = b.tt_ctyname) AND (a.tt_agtfullname = b.tt_agtfullname)

	-- Delete the Data in the Temporary Table(s) --
	DELETE FROM @TempTable1
	DELETE FROM @TempTable2

	-- Copy the Main Table to Temp Table #1 --
	INSERT INTO @TempTable1
	(tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1,
	 tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8, tt_count9,
	 tt_count10, tt_count11, tt_count12, tt_Totalct)
	SELECT tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname,
	 tt_count1, tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8,
	 tt_count9, tt_count10, tt_count11, tt_count12, tt_Totalct
	FROM		@MainTable
	ORDER BY	tt_distcode

	-- Delete the Data in the Main Table --
	DELETE FROM @MainTable

etc... through all 12 months and one final select for "all" months totaled. Then...

Code: 
	-- Return the Final Result Table --
	SELECT * FROM @MainTable
	ORDER BY tt_distcode, tt_mgrname, tt_agentno, tt_agtfullname, tt_ctyname, tt_distquota

	-- Delete the Data in the Temporary Table(s) --
	DELETE FROM @TempTable1
	DELETE FROM @TempTable2
END
 
Upvote 0 Downvote

Here is the screwup, MainTable only holds one month of data because you are joining jan and february data and inserting whichever is not null first in this case a.tt_mgrname etc etc which comes from the January table

then you delete from @TempTable1 and @TempTable2
the you add march data but you don't have februarty data anymore


also




also yoo don't need to do all that leapyear junk, use what is built in and >= and <



Code: 
DECLARE @LoDateStamp DATETIME
DECLARE @HiDateStamp DATETIME
DECLARE @MemYear    NVARCHAR(4)

select @MemYear    = 2008


SET @LoDateStamp = '02/01/' + @MemYear
SET @HiDateStamp = dateadd(m,1,@LoDateStamp)

select @LoDateStamp,@HiDateStamp

and then in the code you would have

where tdMNSadded >= @LoDateStamp
and tdMNSadded < @HiDateStamp

Please read Do You Know How Between Works With Dates?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
Here is the screwup, MainTable only holds one month of data because you are joining jan and february data and inserting whichever is not null first in this case a.tt_mgrname etc etc which comes from the January table

then you delete from @TempTable1 and @TempTable2
the you add march data but you don't have februarty data anymore
Click to expand...

That is why I transfer the merged file to Temp Table 1 and delete Main File. Then I populate Temp Table 2 with March's data and merge #1 (Jan/Feb) and #2 (Mar) into Main (empty).

Code: 
    -- Copy the Main Table to Temp Table #1 --
    INSERT INTO @TempTable1
    (tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1,
     tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8, tt_count9,
     tt_count10, tt_count11, tt_count12, tt_Totalct)
    SELECT tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname,
     tt_count1, tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8,
     tt_count9, tt_count10, tt_count11, tt_count12, tt_Totalct
    FROM        @MainTable
    ORDER BY    tt_distcode

    -- Delete the Data in the Main Table --
    DELETE FROM @MainTable

Thanks for the leap year code. That works great! :)

~Brett
 
Upvote 0 Downvote
Look


this block only holds one month of data alias a = TempTable1

Code: 
INSERT INTO @MainTable (tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1, tt_count2)
    SELECT    coalesce(a.tt_distcode, b.tt_distcode) AS tt_distcode,
            coalesce(a.tt_mgrname, b.tt_mgrname) AS tt_mgrname,
            coalesce(a.tt_distquota, b.tt_distquota) AS tt_distquota,
            coalesce(a.tt_agentno, b.tt_agentno) AS tt_agentno,
            coalesce(a.tt_ctyname, b.tt_ctyname) AS tt_ctyname,
            coalesce(a.tt_agtfullname, b.tt_agtfullname) AS tt_agtfullname,
            coalesce(a.tt_count1, b.tt_count1) AS count1,
            coalesce(b.tt_count2, a.tt_count2) AS count2
    FROM @TempTable1 a
    FULL OUTER JOIN @TempTable2 b
    ON        (a.tt_distcode = b.tt_distcode) AND (a.tt_mgrname = b.tt_mgrname) AND
            (a.tt_distquota = b.tt_distquota) AND (a.tt_agentno = b.tt_agentno) AND
            (a.tt_ctyname = b.tt_ctyname) AND (a.tt_agtfullname = b.tt_agtfullname)

now you delete TempTable2 and TempTable1
Code: 
   -- Delete the Data in the Temporary Table(s) --
    DELETE FROM @TempTable1
    DELETE FROM @TempTable2

now you copy into TempTable1 the data from @MainTable which only holds January data
Code: 
    -- Copy the Main Table to Temp Table #1 --
    INSERT INTO @TempTable1
    (tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname, tt_count1,
     tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8, tt_count9,
     tt_count10, tt_count11, tt_count12, tt_Totalct)
    SELECT tt_distcode, tt_mgrname, tt_distquota, tt_agentno, tt_ctyname, tt_agtfullname,
     tt_count1, tt_count2, tt_count3, tt_count4, tt_count5, tt_count6, tt_count7, tt_count8,
     tt_count9, tt_count10, tt_count11, tt_count12, tt_Totalct
    FROM        @MainTable
    ORDER BY    tt_distcode


    -- Delete the Data in the Main Table --
    DELETE FROM @MainTable


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
@MainTable does a "FULL OUTER JOIN" on @TempTable1 and @TempTable2, so it takes both January and February data and merges it into one table. I've verified that this is working correctly. The problem is the following code is starting before this code can finish. Is there an easy way to do something like "DoEvents" in VB? I even tried putting "BEGIN" and "END" around each block with no luck.

For Year "2008," I only have 4 months of data. When it gets to the end "SELECT," I get January data, (NO) February data, March data, and (NO) April data. Of course all the other months are blank. But I hope this explains it. I have verified the selects for each month are correct and returning the correct data. Somewhere the merge is happening before the month selects can happen. It's annoying!

Thanks for all of your help!
~Brett
 
Upvote 0 Downvote
>>@MainTable does a "FULL OUTER JOIN" on @TempTable1 and @TempTable2, so it takes both January and February data and merges it into one table.

No it will only insert the first it encounters

run this

Code: 
create table january (id int,value decimal(12,3))
insert january values(1,23.00)
insert january values(2,23.00)

create table february (id int,value decimal(12,3))
insert february values(1,55.00)
insert february values(2,55.00)


select coalesce(j.id,f.id),coalesce(j.value,f.value) 
from january j full outer join february f on j.id = f.id

now where are the 55.00 values?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
Correct. But count #1 in February table will not exist and count #2 in January table will not exist. Like so:

Code: 
create table january (id int,value int)
insert january values(1,23.00)
insert january values(2,NULL)

create table february (id int,value decimal(12,3))
insert february values(1,NULL)
insert february values(2,55.00)


select coalesce(j.id,f.id),coalesce(j.value,f.value) 
from january j full outer join february f on j.id = f.id

Is there any way to do a "DoEvents" in SQL?
~Brett
 
Upvote 0 Downvote
The code written as it is above won't work this way because I have:
Code: 
tt_count1        int NOT NULL DEFAULT 0,
It needs to be:
Code: 
tt_count1        int,

That might be my problem!
~Brett
 
Upvote 0 Downvote
Denis,

The "NOT NULL DEFAULT 0" was the problem. Thanks for the help!

~Brett
 
Upvote 0 Downvote
what is your expected output for those two tables 4 rows like this?
Click to expand...

Code: 
With the union I get the following:
1	23.000
2	0.000
1	0.000
2	55.000


Code: 
With the full outer join I get:
1	23.000
2	55.000

Thanks again!
~Brett

P.S. From what I've read and seen, there is a new command "MERGE" in SQL 2008 that is supposed to do roughly the same thing as what I am doing with the full outer join.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Field change as caused issues to store procedure
Replies
19
Views
650
Andrzejek
Andrzejek
Auguy
  • Locked
  • Question
Passing TVP instead of Individual Parameters to Procedure
Replies
0
Views
208
Auguy
Auguy
ghiehotamares
  • Locked
  • Question
How to call stored procedure to display records in fron-end data entry
Replies
1
Views
227
fredericofonseca
fredericofonseca
BeatriceC
  • Locked
  • Question
SQL Linked server StoredProcefure job creates temp files
Replies
1
Views
262
pjw001
pjw001
AlphaMann
  • Locked
  • Question
Stored Procedure
Replies
1
Views
189
simian336
simian336

Part and Inventory Search

Sponsor

Back
Top