| Author |
 Topic  |
|
|
keyursoni85
Posting Yak Master
India
216 Posts |
 Posted - 03/01/2013 : 08:09:33
|
I have multiple conditions wise stored procedures and I want to combine result sets into single procedure...
ALTER PROCEDURE [dbo].[usp_TotalReceivedUnits_Country]
@ContractCode NVARCHAR (20),
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S
WHERE S.ContractCode = @ContractCode AND
S.[OrigCountryCode] = @CountryCode AND
S.ClosedYN = 0 AND
S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
END
GO
ALTER PROCEDURE [dbo].[usp_TotalProcessedUnits_Country]
@ContractCode NVARCHAR (20),
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S with (nolock)
WHERE S.ContractCode = @ContractCode AND
S.OrigCountryCode = @CountryCode AND
S.ClosedYN = 1 AND
S.Shipped = 1 AND
S.FinanceDetailsYN = 0 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
END
GO
I have multiple conditions wise stored procedures and I want to combine result sets into single procedure...
ALTER PROCEDURE [dbo].[usp_TotalReceivedUnits_Country]
@ContractCode NVARCHAR (20),
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S
WHERE S.ContractCode = @ContractCode AND
S.[OrigCountryCode] = @CountryCode AND
S.ClosedYN = 0 AND
S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
END
GO
ALTER PROCEDURE [dbo].[usp_TotalProcessedUnits_Country]
@ContractCode NVARCHAR (20),
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S with (nolock)
WHERE S.ContractCode = @ContractCode AND
S.OrigCountryCode = @CountryCode AND
S.ClosedYN = 1 AND
S.Shipped = 1 AND
S.FinanceDetailsYN = 0 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
END
GO
Above two procedures have same result counts by processed and received summary but How can I combine them to one query with multiple conditions?
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/01/2013 : 08:16:01
|
First glance:
select ...
from ...
where ...
group by ...
UNION ALL
select ...
from ...
where ...
group by ...
So the 2 selects will give 1 result set
Too old to Rock'n'Roll too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/01/2013 : 08:21:54
|
ALTER PROCEDURE [dbo].[usp_Total_Country]
@ContractCode NVARCHAR (20),
@CountryCode NVARCHAR (10),
@Years NVARCHAR(1000)
AS
BEGIN
SELECT ISNULL(SUM(CASE WHEN S.ClosedYN = 1 AND S.FinanceDetailsYN = 0 THEN S.NettoWeight ELSE 0 END), 0) TotalNettoWeightProcessed,
COUNT(CASE WHEN S.ClosedYN = 1 AND S.FinanceDetailsYN = 0 THEN S.ShipmentID ELSE NULL END) LoadsProcessed,
ISNULL(SUM(CASE WHEN S.ClosedYN = 0 THEN S.NettoWeight THEN 0 END), 0) TotalNettoWeightreceived,
COUNT(CASE WHEN S.ClosedYN = 0 THEN S.ShipmentID ELSE NULL END) LoadsReceived,
ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S with (nolock)
WHERE S.ContractCode = @ContractCode AND
S.OrigCountryCode = @CountryCode AND
S.Shipped = 1 AND
S.OutInbound = 2 AND
S.DeletedYN = 0 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
END
GO
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/01/2013 : 08:24:31
|
SELECT ISNULL(SUM(CASE WHEN S.ClosedYN = 0 THEN S.NettoWeight ELSE 0 END), 0) TotalNettoWeightReceived,
ISNULL(SUM(CASE WHEN S.ClosedYN = 1 AND S.FinanceDetailsYN = 0 THEN S.NettoWeight ELSE 0 END), 0) TotalNettoWeightProcessed,
COUNT(CASE WHEN S.ClosedYN = 0 THEN S.ShipmentID ELSE 0 END ) LoadsReceived,
COUNT(CASE WHEN S.ClosedYN = 1 AND S.FinanceDetailsYN = 0 THEN S.ShipmentID ELSE 0 END) LoadsProcessed,
ReceivalDateYear = YEAR(S.ReceivalDate)
FROM Shipment S
WHERE S.ContractCode = @ContractCode AND
S.[OrigCountryCode] = @CountryCode AND
S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ','))
GROUP BY YEAR(S.ReceivalDate)
--
Chandu |
|
|
|
keyursoni85
Posting Yak Master
India
216 Posts |
Posted - 03/01/2013 : 08:30:30
|
Actually, I have 4 procedures with different conditions in where.
one where:
S.ClosedYN = 0 AND
S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
second where clause:
S.ClosedYN = 1 AND
S.Shipped = 1 AND
S.FinanceDetailsYN = 0 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND
third where clause:
S.ClosedYN = 1 AND
S.Shipped = 1 AND
S.FinanceDetailsYN = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2 AND |
|
|
|
keyursoni85
Posting Yak Master
India
216 Posts |
Posted - 03/01/2013 : 08:50:28
|
| As I have different where clauses how can I combine in a row..? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/01/2013 : 08:54:49
|
see the way i gave the query like that you need to extend it to include other queries too
the rule of thumb is to put common conditions in where and add the additional ones inside case when
in your case put
S.Shipped = 1 AND
S.DeletedYN = 0 AND
S.OutInbound = 2
in where
and rest inside case...when
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
823 Posts |
Posted - 03/01/2013 : 08:59:24
|
I think you can acheive as Webfred explained only if the number of columns are same..
Alternatively, create a table and insert data into it
Cheers
MIK |
|
|
| |
Topic |
|
|
|
Combine multiple procedures resultsets
