Need help writing a complex SQL query
CREATE TABLE #tmpTableA
(
[Id] [int] NOT NULL,
RegionId INT,
DistrictId INT,
NurseryDays INT,
TransplantDays INT
)
INSERT #tmpTableA ([Id], RegionId,DistrictId,NurseryDays,TransplantDays) VALUES (1,1,NULL,2,2)
INSERT #tmpTableA ([Id], RegionId,DistrictId,NurseryDays,TransplantDays) VALUES (2,1,2,NULL,2)
Sample output code,
Id RegionId DistrictId NurseryDays TransplantDays
2 1 2 NULL 2
1 1 NULL 2 2
My desired result: 1.) If RegionId and DistrictId exist, then earn transplant days of this combination to get transplant days for RegionId and DistrictId (NULL).
2.) If RegionId and DistrictId exist, then get Nursery days of this combination to get Nursery days for RegionId and DistrictId (NULL).
Desired output:
NurseryDays TransplantDays
2 2
Is it possible in one request?
Thanks and Regards, Faisal Nasir
source to share
The description of your result and the actual result do not match. If you want cattery days regionid and disctrictid from your data, this should be null.
You will need to create a stored procedure as shown to get your results.
create PROCEDURE ProcGetDays
@regionid int
AS
BEGIN
SET NOCOUNT ON;
declare @nurserydays int
declare @transplantdays int
select @transplantdays = transplantdays from tmpTableA where regionid=@regionid and districtid is not null
if @transplantdays is null
select @transplantdays = transplantdays from tmpTableA where regionid=@regionid
select @nurserydays=nurserydays from tmpTableA where regionid=@regionid and districtid is not null
if @nurserydays is null
select @nurserydays=nurserydays from tmpTableA where regionid=@regionid
select @nurserydays as nurserydays,@transplantdays as transplantdays
END
GO
If you need to use a temporary table, you will need to create it in a stored procedure so that it is still available. In the sample, I used a persistent table.
END GO
source to share
First use a filter that fetches rows with specific RegionId
and specific or NULL DistrictId
. Then arrange the lines so that the more specific line comes before the other, and select the top-most line.
In SQL it might look like this:
SELECT TOP 1
NurseryDays,
TransplantDays
FROM YourTable
WHERE RegionId = @RegionId
AND (DistrictId = @DistrictId OR @DistrictId IS NULL)
ORDER BY
CASE DistrictId
WHEN @DistrictId THEN 0
ELSE 1
END
;
source to share