Help on combine these 4 quries

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)

Help on combine these 4 quries

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2011-06-20 : 17:08:05

Is this possible to combine these 4 queries into one.
I want to run a query to find out a history of total shipment, revenue and the last date the shipment

Here is what I have:

1-- I get the total shipment from the shipper into the temp table
SELECT b.shipper_id as CustId
,count(b.bill_num) as billCnt
,sum(b.freight_net as FrtAmt
,max(b.pickup_date) as LastPuDate
INTO
#ShipAcct
FROM
bill b
WHERE
b.pickup_date between '1/6/2011' and '1/6/2011'
GROUP BY b.Shipper_id

2-- I get the total shipment from the consignee into the temp table2
SELECT b.Consign_id as CustId
,count(b.bill_num) as billCnt
,sum(b.freight_net as FrtAmt
,max(b.pickup_date) as LastPuDate
INTO
#ConsAcct
FROM
bill b
WHERE
b.pickup_date between '1/6/2011' and '1/6/2011'
GROUP BY b.Consign_id

3- I combine these two tables
INSERT INTO #ConsAcct
SELECT * FROM #ShipAcct

4 - I group this new combine table to get the result.
SELECT CustId, sum(billCnt), sum(FrtAmt), max(LastPuDate)
FROM #ConsAcct
group by CustId

Here is the result after I ran these query
PLEASE NOTE: The Acct 260864 is return on 1st and 2nd query, and I want to combine this acct as a total on single line

1-- I get the total shipment from the shipper into the temp table
222796 1 145.97 01/06/11
260864 3 342.36 01/06/11
273672 1 104.84 01/06/11
337342 1 130.22 01/06/11

2nd query from the consignee acct
100346 1 1493.18 01/06/11
260864 1 10 01/06/11

3rd query to combine those two results
222796 1 145.97 01/06/11
260864 3 342.36 01/06/11
273672 1 104.84 01/06/11
337342 1 130.22 01/06/11
100346 1 1493.18 01/06/11
260864 1 10 01/06/11

4th query to group the accts together
100346 1 1493.18 01/06/11
222796 1 145.97 01/06/11
260864 4 352.36 01/06/11
273672 1 104.84 01/06/11
337342 1 130.22 01/06/11

Thank you for you help

nizguy
Starting Member

37 Posts

Posted - 2011-06-21 : 16:35:51

Anyone has the answer for this?

Thanks

super_lucky_dog
Starting Member

7 Posts

Posted - 2011-06-22 : 05:32:35

USE [sqlteam_learn]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---ddl definition
drop table [dbo].[bill];
CREATE TABLE [dbo].[bill](
[shipper_id] [int] NOT NULL,
[consign_id] [int] NOT NULL,
[bill_num] [int] NOT NULL,
[freight_net] [int] NOT NULL,
[pickup_date] [datetime] NOT NULL,
) ON [PRIMARY]

GO
--insert test data
insert into [bill] values(1,1,1,5,CURRENT_TIMESTAMP);
insert into [bill] values(2,12,1,10,CURRENT_TIMESTAMP);
insert into [bill] values(3,13,1,8,CURRENT_TIMESTAMP);
insert into [bill] values(4,4,1,2,CURRENT_TIMESTAMP);
insert into [bill] values(5,15,1,11,CURRENT_TIMESTAMP);
insert into [bill] values(6,16,1,12,CURRENT_TIMESTAMP);
insert into [bill] values(7,17,1,13,CURRENT_TIMESTAMP);

---Method 1
select CustId, sum(billCnt), sum(FrtAmt), max(LastPuDate)
from
(
SELECT b.shipper_id as CustId
,count(b.bill_num) as billCnt
,sum(b.freight_net) as FrtAmt
,max(b.pickup_date) as LastPuDate
FROM
bill b
WHERE
cast(b.pickup_date as DATE) ='2011-06-22'
GROUP BY b.Shipper_id
union all
SELECT b.Consign_id as CustId
,count(b.bill_num) as billCnt
,sum(b.freight_net) as FrtAmt
,max(b.pickup_date) as LastPuDate
FROM
bill b
WHERE
cast(b.pickup_date as DATE) ='2011-06-22'
GROUP BY b.Consign_id
)tmp
group by CustId
EXCEPT
--Method2
SELECT TMP.CustId
,count(TMP.bill_num) as billCnt
,sum(TMP.freight_net) as FrtAmt
,max(TMP.pickup_date) as LastPuDate
FROM
(
SELECT b.shipper_id AS CustId,b.bill_num,b.freight_net,b.pickup_date
FROM
bill b
UNION ALL
SELECT b.Consign_id AS CustId,b.bill_num,b.freight_net,b.pickup_date
FROM
bill b
)TMP
GROUP BY CustId

My two methods posted. Using except to verify it, it works fine.
I do not know if you are satisfied with my anwsers. Hope it helps.

I'd like to see other methods too.

v-jinxli

super_lucky_dog

nizguy
Starting Member

37 Posts

Posted - 2011-06-22 : 13:34:09

Both methods works great!

Thank you so much...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources