Author 
Topic 

keyursoni85
Posting Yak Master
India
233 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
8764 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
52317 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
2206 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
233 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
233 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
52317 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
Flowing Fount of Yak Knowledge
Pakistan
1052 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 


