March 6, 2012 at 9:53 pm
I have the below query which fetches Items and the Sum of its Quantities ordered for a particular time period and for a single Region.
There is a new requirement where i need to get the same result set ,but for a group of regions which are available in a new table.
The new table has 2 columns,DistrictId and Regionid.
DistrictId RegionId
19 125
19 126
19 127
I will have the DistrictId on basis of which, I can determine Regionids.
So,how should I use that table in below query.The query should work for the old as well as new requirement.
The only thought coming to my mind is to use IF and ELSE Statement to differentiate on the basis of DistrictId.
Is there any other option like conditional joins or case statements.
My objective is to reuse the query so that there are less maintenance issues.
Kindly let me know if any additional details are required.
Regards
SELECT o.ItemId,SUM(o.Quantity)
FROM [Orders] o
INNER JOIN [Items] i ON (i.ItemId=o.ItemId)
WHERE o.RequestedDate BETWEEN @startDate AND @endDate
AND o.RegionId=@regionId
AND o.IsActive=1
GROUP BY o.ItemId
March 6, 2012 at 10:38 pm
/*
Declare a common variable which accepts both regionid and department
Left outer join to department table on regionid
where clause should now have filter for both regionid and department based on OR condition.
You might want to include one more parameter to help diffrentiate if the input variable is a
departmentid or a regionid else the filter will return incorrect results where the regionId
exists for the value being passed as department id
*/
create table region
( regions int
)
create table departments
(deparmentid int,
regions int
)
insert into region (regions)
select 1 union all
select 2 union all
select 3
insert into departments (deparmentid,regions)
select 1,1 union
select 1,2 union
select 2, 3
declare @var int
Declare @type int
set @var = 2
set @type = 2
select * from region r
left outer join departments d
on r.regions = d.regions
where ((r.regions = @var and @type=1 ) or (d.deparmentid = @var and @type =2))
March 7, 2012 at 7:15 am
If you are doing this in a stored procedure I'd probably do an IF statement like this:
IF @regionId IS NOT NULL
BEGIN;
SELECT
o.ItemId,
SUM(o.Quantity)
FROM
[Orders] o
INNER JOIN [Items] i
ON (i.ItemId = o.ItemId)
WHERE
o.RequestedDate BETWEEN @startDate AND @endDate AND
o.RegionId = @regionId AND
o.IsActive = 1
GROUP BY
o.ItemId ;
END ;
ELSE
BEGIN;
SELECT
o.ItemId,
SUM(o.Quantity)
FROM
[Orders] o
INNER JOIN [Items] i
ON (i.ItemId = o.ItemId)
INNER JOIN Districts AS D ON
o.regionId = D.regionID
WHERE
o.RequestedDate BETWEEN @startDate AND @endDate AND
D.districtId = @districtId AND
o.IsActive = 1
GROUP BY
o.ItemId
END ;
If you have to do it in one query you could do what Jayanth had suggested or this:
SELECT
o.ItemId,
SUM(o.Quantity)
FROM
[Orders] o
INNER JOIN [Items] i
ON (i.ItemId = o.ItemId)
INNER JOIN Districts AS D
ON o.regionId = D.regionID
WHERE
o.RequestedDate BETWEEN @startDate AND @endDate AND
o.IsActive = 1 AND
CASE WHEN @regionID IS NOT NULL AND
@regionId = o.regionId THEN 1
WHEN @districtId IS NOT NULL AND
@districtId = D.districtId THEN 1
ELSE 0
END = 1
GROUP BY
o.ItemId
Or you could use a temp table or table variable like this:
DECLARE @regions TABLE
(
districtID INT,
regionID INT
)
IF @regionId IS NOT NULL
BEGIN;
INSERT INTO @regions
(
districtID,
regionID
)
SELECT
districtID,
regionID
FROM
districts
WHERE
regionID = @regionId ;
END ;
ELSE
BEGIN;
INSERT INTO @regions
(
districtID,
regionID
)
SELECT
districtID,
regionID
FROM
districts
WHERE
districtID = @districtId ;
END ;
SELECT
o.ItemId,
SUM(o.Quantity)
FROM
[Orders] o
INNER JOIN [Items] i
ON (i.ItemId = o.ItemId)
INNER JOIN @regions AS R
ON o.regionId = R.regionID
WHERE
o.RequestedDate BETWEEN @startDate AND @endDate AND
o.IsActive = 1
GROUP BY
o.ItemId
I used a table variable because if you have small number of rows then it will work well. If you have a larger number of rows then a temp table with some indexing may perform better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 7, 2012 at 7:30 am
You can do that like this:
-- Input: @DistrictId or @RegionId, @startDate, @endDate
SELECT o.RegionId, o.ItemId, SUM(o.Quantity)
FROM dbo.Orders o
JOIN dbo.Items i ON i.ItemId=o.ItemId
JOIN dbo.RegionDistricts rd ON rd.RegionId = o.RegionId
WHERE o.RequestedDate BETWEEN @startDate AND @endDate
AND o.IsActive=1
AND o.RegionId=ISNULL(@RegionId, o.RegionId)
AND rd.DistrictId=ISNULL(@DistrictId, rd.DistrictId)
GROUP BY o.RegionId, o.ItemId
March 12, 2012 at 12:23 pm
Thanks guys for all the responses!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply