Joining two Temp Tables to get correct data in both tables

Question

Post reply

Joining two Temp Tables to get correct data in both tables

Julian Louisy

SSC Rookie

Points: 46
More actions
May 25, 2009 at 5:45 am

#132573

Hi
I want to write a query that will show join both tables to extract all data from both tables, but because I am using weekday as part of my join when a day does not appear in one table then that data for that day is not returned.
Temp 0
dateYearWeekdayWeektypeOrdersValue
18/05/20092009Mondaytw111
18/05/20092009Mondaytw111
18/05/20092009Mondaytw111
20/05/20092009Wednesdaytw111
20/05/20092009Wednesdaytw111
20/05/20092009Wednesdaytw111
21/05/20092009Thursdaytw111
21/05/20092009Thursdaytw111
21/05/20092009Thursdaytw111
22/05/20092009Fridaytw111
22/05/20092009Fridaytw111
22/05/20092009Fridaytw111
Temp 1
dateYearWeekdayWeektypeCancel OrderCancel Value
18/05/20092009Monday tw1-11
21/05/20092009Thursdaytw1-11
21/05/20092009Thursdaytw1-11
21/05/20092009Thursdaytw1-11
21/05/20092009Thursdaytw1-11
21/05/20092009Thursdaytw1-11
19/05/20092009Tuesdaytw1-11
19/05/20092009Tuesdaytw1-11
19/05/20092009Tuesdaytw1-11
19/05/20092009Tuesdaytw1-11
19/05/20092009Tuesdaytw1-11
20/05/20092009Wednesdaytw1-11
20/05/20092009Wednesdaytw1-11
SELECT T1.weekday, T1.weektype, count(T1.orders) As 'OrderCount',sum(T1.CollectValue) as 'CollectValue', T5.CancelCount, T5.CancelValue
INTO #tempv
FROM #temp0 T1
LEFT JOIN -- changing to left join because when there are no temp1 records select wont work
(
SELECT T2.weekday, T2.weektype, count(T2.Cancelorders) As 'CancelCount',sum(t2.CancelValue)* -1 as CancelValue
FROM #temp1 T2-- * -1
WHERE T2.weekday is not null
GROUP BY T2.weekday,T2.weektype
) AS T5 ON T1.weekday = T5.weekday and T1.weektype=T5.weektype
GROUP BY T1.weekday,T1.weektype, T5.CancelCount, T5.CancelValue
ORDER BY T1.weektype
Temp 1 has Tuesday data but no Friday data
Temp 0 has weekdays including Friday but no Tuesday data
WeekdayWeektypeOrderCountCollect ValueCancel CountCancel Value
Mondaytw333111
Thursdaytw666555
Fridaytw0NULL24264
Wednesdaytw33318198
Joining tables temp 0 & temp 1 Tuesday data is not returned, because of my left join Friday is returned but not Tuesday, if I use a right join Tuesday will be returned but not Friday.
Help please !!
/****** Object: StoredProcedure [dbo].[uspBISWSReturnsCollectionChg] Script Date: 05/19/2009 09:42:50 ******/
-- ===================================================================================================================
-- Author:
-- Create date: 20 May 2009
-- Description: This stored procedure has been created as part of the SWS project.
-- The business especially finance want to see a report of the collection charges that have been levied
-- to pick up a customers returns parcel from their designated address.
-- And also see any cancellation of these collection orders.
-- ===================================================================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[uspBISWSReturnsCollectionChg]
-- exec uspBISWSReturnsCollectionChg
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
/* This Table must be created first and then enter the days of the week manually i.e. Monday, Tuesday - Sunday
Otherwise will get error
CREATE TABLE [dbo].[BaseWeeklyCollectOrders](
[WDID] [int] IDENTITY(1,1) NOT NULL,
[WeekDay] [char](10) NOT NULL,
[ThisWeekOrders] [int] NOT NULL CONSTRAINT [GVS_BaseDaliyOrderV4_ThisWeekOrders] DEFAULT ((0)),
[LastWeekOrders] [int] NOT NULL CONSTRAINT [GVS_BaseDaliyOrderV4_LastWeekOrders] DEFAULT ((0)),
[ThisWeekCancelOrders] [int] NOT NULL CONSTRAINT [GVS_BaseDaliyOrderV4_ThisWeekCancelOrders] DEFAULT ((0)),
[LastWeekCancelOrders] [int] NOT NULL CONSTRAINT [GVS_BaseDaliyOrderV4_LastWeekCancelOrders] DEFAULT ((0)),
[LastWeekOrderValue] [decimal](18, 0) NOT NULL CONSTRAINT [GVS_BaseDaliyOrderV4_LastWeekOrderValue] DEFAULT ((0)),
[ThisWeekOrderValue] [decimal](18, 0) NOT NULL CONSTRAINT [GVS_BaseDaliyOrderV4_ThisWeekOrderValue] DEFAULT ((0)),
[ThisWeekCancelValue] [decimal](18, 0) NOT NULL CONSTRAINT [GVS_BaseDaliyOrderV4_ThisWeekCancelValue] DEFAULT ((0)),
[LastWeekCancelValue] [decimal](18, 0) NOT NULL CONSTRAINT [GVS_BaseDaliyOrderV4_LastWeekCancelValue] DEFAULT ((0))
) ON [PRIMARY]
GO
*/
declare
@last_week datetime
,@this_week datetime
,@week_begining varchar(10)
,@lastweek_start_date datetime
,@lastweek_end_date datetime
,@thisweek_start_date datetime
,@thisweek_end_date datetime
select @week_begining = 'monday'
set datefirst 1
--get the week start date
select @last_week = dateadd(wk,-1,getdate())
select @this_week = dateadd(wk,0,getdate())
--select @week_begining = 'monday'
set datefirst 1
--get the week start date
select @lastweek_start_date = @last_week - (datepart(dw, @last_week) - 1)
select @thisweek_start_date = @this_week - (datepart(dw, @this_week) - 1)
select @lastweek_start_date = convert(varchar(8),@lastweek_start_date,112)
select @lastweek_start_date = convert(datetime,@lastweek_start_date)
select @thisweek_start_date = convert(varchar(8),@thisweek_start_date,112)
select @thisweek_start_date = convert(datetime,@thisweek_start_date)
--get the week end date
select @lastweek_end_date = @last_week + (7 - datepart(dw, @last_week)) + 1
select @thisweek_end_date = @this_week + (7 - datepart(dw, @this_week)) + 1
select @lastweek_end_date = convert(varchar(8),@lastweek_end_date,112)
select @lastweek_end_date = convert(datetime,@lastweek_end_date)
select @thisweek_end_date = convert(varchar(8),@thisweek_end_date,112)
select @thisweek_end_date = convert(datetime,@thisweek_end_date)
------------------------------- Collection Order & Value -------------------------------
SELECT
rd.DateEntered date
,datename(yy,rd.DateEntered)year
,datename(dw,rd.DateEntered)[weekday]
,(CASEWHEN rd.DateEntered between @lastweek_start_date and @lastweek_end_date
THEN 'lw'
WHEN rd.DateEntered between @thisweek_start_date and @thisweek_end_date
THEN 'tw'
ELSE 'er'
END) AS weektype
,rd.ReceiptDropId orderid
,count(rd.ReceiptDropId) Orders
,SUM(pos.PriceIncTax) AS CollectValue
INTO #temp0
FROM PROJECT01BO.AsosBackOffice.dbo.ReceiptDrop rd WITH (NOLOCK)
INNER JOIN PROJECT01BO.AsosBackOffice.dbo.POSItem pos WITH (NOLOCK)
ON rd.ReceiptDropId = pos.ReceiptDropId AND pos.VoidItemId IS NULL
INNER JOIN PROJECT01BO.AsosBackOffice.dbo.receipt r WITH (NOLOCK)
ON r.receiptid = rd.receiptid
LEFT JOIN PROJECT01BO.AsosBackOffice.dbo.paymentledger pl WITH (NOLOCK)
ON pos.paymentledgerID = pl.paymentledgerid
WHEREConvert(Varchar(8),pos.DateEntered,112) between @lastweek_start_date and @thisweek_end_date
ANDShippingTypeId = 2 and ShippingMethodId = 8
--ANDPOS.priceinctax>0
ANDPL.AuthCode NOT in ('','FAIL')
ANDPL.AuthAmount > 0
ANDR.StatusId NOT IN ('82000','80100','74700') -- <> '82000' 82000-Pre-auth Cancelled & 80100-order cancelled
GROUP BY
rd.ReceiptDropId
,rd.DateEntered
--select * from #temp0
------------------------------------------ Cancellation Order & Value -------------------------------------------
SELECT
rd.DateEntered date
,datename(yy,rd.DateEntered)year
,datename(dw,rd.DateEntered)[weekday]
,(CASEWHEN rd.DateEntered between @lastweek_start_date and @lastweek_end_date
THEN 'lw'
WHEN rd.DateEntered between @thisweek_start_date and @thisweek_end_date
THEN 'tw'
--else 'er'
END) as weektype
--,rd.ReceiptDropId orderid
, count(rd.ReceiptDropId) CancelOrders
,Sum(pos.PriceIncTax * pos.Quantity) AS CancelValue
INTO #temp1
FROM PROJECT01BO.AsosBackOffice.dbo.ReceiptDrop rd WITH (NOLOCK)
INNER JOIN PROJECT01BO.AsosBackOffice.dbo.POSItem pos WITH (NOLOCK)
ON rd.ReceiptDropId = pos.ReceiptDropId AND pos.VoidItemId IS NOT NULL
INNER JOIN PROJECT01BO.AsosBackOffice.dbo.receipt r WITH (NOLOCK)
ON r.receiptid = rd.receiptid
INNER JOIN PROJECT01BO.AsosBackOffice.dbo.VoidItem v WITH (NOLOCK)
ON rd.ReceiptDropId = v.ReceiptDropId
LEFT JOIN PROJECT01BO.AsosBackOffice.dbo.VoidAction va WITH (NOLOCK)
ON v.EnteredByActionId = va.VoidActionId
LEFT JOIN PROJECT01BO.AsosBackOffice.dbo.paymentledger pl WITH (NOLOCK)
ON pos.paymentledgerID = pl.paymentledgerid
WHERErd.ShippingTypeId = 2 and rd.ShippingMethodId = 8
AND R.StatusId IN ('82000','80100','74700')
GROUP BY
--rd.ReceiptDropId,
rd.DateEntered
CREATE INDEX weekday_id_ind
ON #temp1 (weekday)
CREATE INDEX weektype_id_ind
ON #temp1 (weektype)
--select * from #tempv
--select * from #temp0
SELECT T1.weekday, T1.weektype, count(T1.orders) As 'OrderCount',sum(T1.CollectValue) as 'CollectValue', T5.CancelCount, T5.CancelValue
INTO #tempv
FROM #temp0 T1
LEFT JOIN -- changing to left join because when there are no temp1 records select wont work
(
SELECT T2.weekday, T2.weektype, count(T2.Cancelorders) As 'CancelCount',sum(t2.CancelValue)* -1 as CancelValue
FROM #temp1 T2-- * -1
WHERE T2.weekday is not null
GROUP BY T2.weekday,T2.weektype
) AS T5 ON T1.weekday = T5.weekday and T1.weektype=T5.weektype
GROUP BY T1.weekday,T1.weektype, T5.CancelCount, T5.CancelValue
ORDER BY T1.weektype
--drop table #tempv
--select * from #tempv
UPDATE PROJECT01BO.AsosBackOffice.dbo.BaseWeeklyCollectOrders
SET
[ThisWeekOrders] = 0,
[LastWeekOrders] = 0,
[ThisWeekCancelOrders]= 0,
[LastWeekCancelOrders] = 0,
[LastWeekOrderValue] = 0,
[ThisWeekOrderValue] = 0,
[ThisWeekCancelValue] = 0,
[LastWeekCancelValue] = 0
--select * from #temp2
UPDATE cbds
SET[LastWeekOrders] = IsNull(v.OrderCount,0),
[LastWeekOrderValue] = IsNull(v.CollectValue,0),
[LastWeekCancelOrders] = IsNull(v.CancelCount,0),
[LastWeekCancelValue] = IsNull(v.CancelValue,0)
FROM PROJECT01BO.AsosBackOffice.dbo.BaseWeeklyCollectOrders cbds join
#tempv v on v.weekday = cbds.weekday
WHERE weektype = 'lw'
UPDATE cbds
SET [ThisWeekOrders] = IsNull(v2.OrderCount,0),
[ThisWeekOrderValue] = IsNull(v2.CollectValue,0),
[ThisWeekCancelOrders] = IsNull(v2.CancelCount,0),
[ThisWeekCancelValue] = IsNull(v2.CancelValue,0)
FROM PROJECT01BO.AsosBackOffice.dbo.BaseWeeklyCollectOrders cbds join
#tempv v2 on v2.weekday = cbds.weekday
WHERE weektype = 'tw'
--select * from PROJECT01BO.AsosBackOffice.dbo.BaseWeeklyCollectOrders
DROP TABLE #temp0
DROP TABLE #temp1
DROP TABLE #tempv

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Allister Reid SSCrazy Points: 2666 More actions · Answer 1

Hi Julian,

if I understand you correctly, what you need is a full join.

SELECT T1.weekday, T1.weektype, count(T1.orders) As 'OrderCount',sum(T1.CollectValue) as 'CollectValue', T5.CancelCount, T5.CancelValue

INTO #tempv

FROM #temp0 T1

FULL JOIN -- Changed to full join will get all from both sides of the join

(

SELECT T2.weekday, T2.weektype, count(T2.Cancelorders) As 'CancelCount',sum(t2.CancelValue)* -1 as CancelValue

FROM #temp1 T2 -- * -1

WHERE T2.weekday is not null

GROUP BY T2.weekday,T2.weektype

) AS T5 ON T1.weekday = T5.weekday and T1.weektype=T5.weektype

GROUP BY T1.weekday,T1.weektype, T5.CancelCount, T5.CancelValue

ORDER BY T1.weektype

I haven't tested this code, but if you can repost your data as per http://www.sqlservercentral.com/articles/Best+Practices/61537/ it'll be easier to run the test!

If you are looking to include all dates in output, regardless of whether they are on either temp table, you'll need a tally table.

Allister

Julian Louisy SSC Rookie Points: 46 More actions · Answer 2

Hi Grasshopper

Thanks for replying and offerring your help, I have tried a Full Join before, but when I use a full join the weekday Tuesday is replaced by a NULL field as shown below and the Weektype for Tuesday is tw(This week) is also replaced by NULL, but I need to display Tuesday as well as weektype to shown the data for Tuesday & tw.

In this example to show that Tuesday had cancelled orders.

WeekdayWeektypeOrderCountCollect ValueCancel CountCancel Value

NULLNULL0NULL24264

Fridaytw333NULLNULL

Mondaytw333111

Thursdaytw666555

Wednesdaytw33318198

I need to show All Days & week types for when I update my table below..

I need the weekday because I join by weekday

UPDATE cbds

SET[LastWeekOrders] = IsNull(v.OrderCount,0),

[LastWeekOrderValue] = IsNull(v.CollectValue,0),

[LastWeekCancelOrders] = IsNull(v.CancelCount,0),

[LastWeekCancelValue] = IsNull(v.CancelValue,0)

FROM AsosBackOffice.dbo.BaseWeeklyCollectOrderstest cbds join

#tempv v on v.weekday = cbds.weekday

WHERE weektype = 'lw'

Many Thanks for your help, do you have any other ideas...

Allister Reid SSCrazy Points: 2666 More actions · Answer 3

You fix this with an isnull function (again, this isn’t tested, as I don’t have any test data):

SELECT

ISNULL(T1.weekday, T2.weekday) AS Weekday,

ISNULL(T1.weektype, T2.weektype) AS Weektype, count(T1.orders) As 'OrderCount',sum(T1.CollectValue) as 'CollectValue', T5.CancelCount, T5.CancelValue

INTO #tempv

FROM #temp0 T1

FULL JOIN -- Changed to full join will get all from both sides of the join

(

SELECT T2.weekday, T2.weektype, count(T2.Cancelorders) As 'CancelCount',sum(t2.CancelValue)* -1 as CancelValue

FROM #temp1 T2 -- * -1

WHERE T2.weekday is not null

GROUP BY T2.weekday,T2.weektype

) AS T5 ON T1.weekday = T5.weekday and T1.weektype=T5.weektype

GROUP BY T1.weekday,T1.weektype, T5.CancelCount, T5.CancelValue

ORDER BY T1.weektype

Hope this helps!

Julian Louisy SSC Rookie Points: 46 More actions · Answer 4

Hi Grasshopper

Many Thanks for your help and replying, it makes sense what you said, but when I added the IsNULL as you kindly suggested I got this error..

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "T2.weekday" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "T2.weektype" could not be bound.

I dont why sql server is saying this as T2 is defined, any ideas thanks Julian.

Allister Reid SSCrazy Points: 2666 More actions · Answer 5

D’oh! Brain based sql parsing isn’t up to scratch!

SELECT

ISNULL(T1.weekday, T5.weekday) AS Weekday,

ISNULL(T1.weektype, T5.weektype) AS Weektype,

COUNT(T1.orders) As 'OrderCount',

SUM(T1.CollectValue) as 'CollectValue',

T5.CancelCount,

T5.CancelValue

INTO #tempv

FROM #temp0 T1

FULL JOIN -- Changed to full join will get all from both sides of the join

(

SELECT T2.weekday, T2.weektype, count(T2.Cancelorders) As 'CancelCount',sum(t2.CancelValue)* -1 as CancelValue

FROM #temp1 T2 -- * -1

WHERE T2.weekday is not null

GROUP BY T2.weekday,T2.weektype

) AS T5 ON T1.weekday = T5.weekday and T1.weektype=T5.weektype

GROUP BY T1.weekday,T1.weektype, T5.CancelCount, T5.CancelValue

ORDER BY T1.weektype

If you post your data as shown at http://www.sqlservercentral.com/articles/Best+Practices/61537/, then it’s easy to test for mistakes!

Allister

Julian Louisy SSC Rookie Points: 46 More actions · Answer 6

Hi Oh Great Grasshopper

It worked once I also added t5.weekday & t5.weektype to the group by clause.

Many Thanks, Very grateful for your help.

Enjoy the rest of your day. Julian

SELECT

--T1.weekday, T1.weektype,

ISNULL(T1.weekday, T5.weekday) AS Weekday,

ISNULL(T1.weektype, T5.weektype) AS Weektype,

count(T1.orders) As 'OrderCount',sum(T1.CollectValue) as 'CollectValue', T5.CancelCount, T5.CancelValue

INTO #tempv

FROM #temp0 T1

FULL JOIN -- changing to left join because when there are no temp1 records select wont work

(

SELECT T2.weekday, T2.weektype, count(T2.Cancelorders) As 'CancelCount',sum(t2.CancelValue)* -1 as CancelValue

FROM #temp1 T2-- * -1

WHERE T2.weekday is not null

GROUP BY T2.weekday,T2.weektype

) AS T5 ON T1.weekday = T5.weekday and T1.weektype=T5.weektype

GROUP BY T1.weekday,T1.weektype, T5.CancelCount, T5.CancelValue,T5.weekday,T5.weektype

ORDER BY T1.weektype

Allister Reid SSCrazy Points: 2666 More actions · Answer 7

Allister Reid

SSCrazy

Points: 2666

May 25, 2009 at 7:29 am

#999655

Thanks Newbie

All the best

Allister

Joining two Temp Tables to get correct data in both tables

Cookies on SQLServerCentral