Joining Temp Tables to get from both tables

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 2005 Forums

Transact-SQL (2005)

Joining Temp Tables to get from both tables

Author

Topic

Badjjl
Starting Member

6 Posts

Posted - 2009-05-25 : 07:38:22

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
date Year Weekday Weektype Orders Value
18/05/2009 2009 Monday tw 1 11
18/05/2009 2009 Monday tw 1 11
18/05/2009 2009 Monday tw 1 11
20/05/2009 2009 Wednesday tw 1 11
20/05/2009 2009 Wednesday tw 1 11
20/05/2009 2009 Wednesday tw 1 11
21/05/2009 2009 Thursday tw 1 11
21/05/2009 2009 Thursday tw 1 11
21/05/2009 2009 Thursday tw 1 11
22/05/2009 2009 Friday tw 1 11
22/05/2009 2009 Friday tw 1 11
22/05/2009 2009 Friday tw 1 11

Temp 1
date Year Weekday Weektype Cancel Order Cancel Value
18/05/2009 2009 Monday tw 1 -11
21/05/2009 2009 Thursday tw 1 -11
21/05/2009 2009 Thursday tw 1 -11
21/05/2009 2009 Thursday tw 1 -11
21/05/2009 2009 Thursday tw 1 -11
21/05/2009 2009 Thursday tw 1 -11
19/05/2009 2009 Tuesday tw 1 -11
19/05/2009 2009 Tuesday tw 1 -11
19/05/2009 2009 Tuesday tw 1 -11
19/05/2009 2009 Tuesday tw 1 -11
19/05/2009 2009 Tuesday tw 1 -11
20/05/2009 2009 Wednesday tw 1 -11
20/05/2009 2009 Wednesday tw 1 -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

Weekday Weektype OrderCount Collect Value Cancel Count Cancel Value
Monday tw 3 33 1 11
Thursday tw 6 66 5 55
Friday tw 0 NULL 24 264
Wednesday tw 3 33 18 198

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]
,(CASE WHEN 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

WHERE Convert(Varchar(8),pos.DateEntered,112) between @lastweek_start_date and @thisweek_end_date
AND ShippingTypeId = 2 and ShippingMethodId = 8
--AND POS.priceinctax > 0
AND PL.AuthCode NOT in ('','FAIL')
AND PL.AuthAmount > 0
AND R.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]
,(CASE WHEN 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

WHERE rd.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

Badjjl
Starting Member

6 Posts

Posted - 2009-05-25 : 08:17:25

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.

Weekday Weektype OrderCount Collect Value Cancel Count Cancel Value
NULL NULL 0 NULL 24 264
Friday tw 3 33 NULL NULL
Monday tw 3 33 1 11
Thursday tw 6 66 5 55
Wednesday tw 3 33 18 198

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources