SQL Server Performance

Joining two temp tables together to get the correct data from both tables

Discussion in 'Getting Started' started by Badjjl, May 25, 2009.

  1. Badjjl New Member

    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
  2. Adriaan New Member

    FULL JOIN can be used to show data from both tables, with and without matching rows on either side.
    You can insert rows from the first table into your temp table, then insert rows from the second table with a LEFT JOIN on the temp table to find unmatched rows in the second table. You'll need to decide which table goes first.
  3. Badjjl New Member

    Hi Adriaan
    Thanks for replying and thanks for the advice, I will give that a try.
    Many Thanks
    Julian

Share This Page