BrookK
asked on
T-SQL query
Hello All,
I have to build the query to get the OrderHistory data for a user based on region, Customer and OrderContract.
Here is the table structure-
OrdHistory
OrdNum Location Customer OrderContract
A0001 L1 C001 100-1
A0002 L2 C003 10A-1
A0003 L3 C021 120-1
A0004 L1 C041 1B1-1
A0005 L2 C003 1DD-2
A0005 L2 C013 1DD-5
A0005 L2 C013 1DA-5
A0005 L2 C013 1DE-5
UserLocation
UserID Location AllLoc
8790 L1 1
8790 L2 0
8790 L3 0
UserCust
UserID Location Customer AllCont
8790 L2 C003 1
8790 L2 C013 0
8790 L3 C021 0
UserCont
UserID Location Customer AllCont
8790 L2 C013 1DD-5
8790 L2 C013 1DA-5
8790 L3 C021 120-1
I have to display the OrderHistory based on the user logged in.
If you see all three tables have similar data but it restricts permissions based.
The last column in these tables starting with 'All' if that is true then show all the orders for that location for that user. If the last column is false then for that region see in UserCust table which customers he has access to. If that is true then show all the contracts for that customer. If it is false then for that region + customer in AllCont table see the contracts the user has access to. Show the data based on permissions. First Region then by region+customer and then by Region+customer+contract.
For the given example here would be the output-
Please let me know if you have any questions aginst my question or if you don't understand.
Thanks,
-B
I have to build the query to get the OrderHistory data for a user based on region, Customer and OrderContract.
Here is the table structure-
OrdHistory
OrdNum Location Customer OrderContract
A0001 L1 C001 100-1
A0002 L2 C003 10A-1
A0003 L3 C021 120-1
A0004 L1 C041 1B1-1
A0005 L2 C003 1DD-2
A0005 L2 C013 1DD-5
A0005 L2 C013 1DA-5
A0005 L2 C013 1DE-5
UserLocation
UserID Location AllLoc
8790 L1 1
8790 L2 0
8790 L3 0
UserCust
UserID Location Customer AllCont
8790 L2 C003 1
8790 L2 C013 0
8790 L3 C021 0
UserCont
UserID Location Customer AllCont
8790 L2 C013 1DD-5
8790 L2 C013 1DA-5
8790 L3 C021 120-1
I have to display the OrderHistory based on the user logged in.
If you see all three tables have similar data but it restricts permissions based.
The last column in these tables starting with 'All' if that is true then show all the orders for that location for that user. If the last column is false then for that region see in UserCust table which customers he has access to. If that is true then show all the contracts for that customer. If it is false then for that region + customer in AllCont table see the contracts the user has access to. Show the data based on permissions. First Region then by region+customer and then by Region+customer+contract.
For the given example here would be the output-
A0001 L1 C001 100-1
A0004 L1 C041 1B1-1
A0002 L2 C003 10A-1
A0005 L2 C003 1DD-2
A0005 L2 C013 1DD-5
A0005 L2 C013 1DA-5
A0003 L3 C021 120-1Please let me know if you have any questions aginst my question or if you don't understand.
Thanks,
-B
ASKER
CREATE TABLE OrdHistory
(
OrdNum VARCHAR(25),
Location CHAR(5),
CustomerID VARCHAR(25),
OrderContract VARCHAR(25)
)
I need to see all four tables.
Please try the following query:
Result with your sample data:
SELECT OH.OrdNum, OH.Location, OH.Customer, OH.OrderContract
FROM OrdHistory AS OH
INNER JOIN UserLocation AS UL
ON OH.Location = UL.Location
LEFT OUTER JOIN UserCust AS UC
ON UL.UserID = UC.UserID AND OH.Location = UC.Location
AND OH.Customer = UC.Customer
LEFT OUTER JOIN UserCont AS UCO
ON UC.UserID = UCO.UserID AND OH.Location = UCO.Location
AND OH.Customer = UCO.Customer
AND OH.OrderContract = UCO.AllCont
WHERE (UL.UserID = 8790)
AND UL.AllLoc IS NOT NULL
AND ( UL.AllLoc = 1
OR ( UC.AllCont IS NOT NULL
AND ( UC.AllCont = 1
OR UCO.AllCont IS NOT NULL)))
ORDER BY OH.Location, OH.Customer, OH.OrderContract, OH.OrdNumResult with your sample data:
OrdNum Location Customer OrderContract
---------- ---------- ---------- -------------
A0001 L1 C001 100-1
A0004 L1 C041 1B1-1
A0002 L2 C003 10A-1
A0005 L2 C003 1DD-2
A0005 L2 C013 1DA-5
A0005 L2 C013 1DD-5
A0003 L3 C021 120-1
(7 row(s) affected)
Check this.
SELECT DISTINCT oh.*
FROM OrdHistory oh
LEFT JOIN UserLocation ul
ON oh.Location = ul.Location
LEFT JOIN UserCust ucu
ON oh.Location = ucu.Location
LEFT JOIN UserCont uco
ON oh.Location = ucu.Location
WHERE (ul.AllLoc = 1)
OR (ul.AllLoc = 0
AND ul.Location = ucu.Location
AND ucu.AllCont = 1
AND ucu.Customer = oh.Customer)
OR (ul.AllLoc = 0
AND ul.Location = ucu.Location
AND ucu.AllCont = 0
AND ucu.Customer = oh.Customer
AND uco.AllCont = oh.OrderContract)
ORDER BY oh.Location,
oh.CustomerDECLARE @OrdHistory TABLE(
OrdNum VARCHAR(20),
Location VARCHAR(10),
Customer VARCHAR(10),
OrderContract VARCHAR(10)
)
INSERT @OrdHistory
VALUES('A0001','L1','C001','100-1')
INSERT @OrdHistory
VALUES('A0002','L2','C003','10A-1')
INSERT @OrdHistory
VALUES('A0003','L3','C021','120-1')
INSERT @OrdHistory
VALUES('A0004','L1','C041','1B1-1')
INSERT @OrdHistory
VALUES('A0005','L2','C003','1DD-2')
INSERT @OrdHistory
VALUES('A0005','L2','C013','1DD-5')
INSERT @OrdHistory
VALUES('A0005','L2','C013','1DA-5')
INSERT @OrdHistory
VALUES('A0005','L2','C013','1DE-5')
DECLARE @UserLocation TABLE(
UserID INT,
Location VARCHAR(10),
AllLoc INT
)
INSERT @UserLocation
VALUES(8790,'L1',1)
INSERT @UserLocation
VALUES(8790,'L2',0)
INSERT @UserLocation
VALUES(8790,'L3',0)
DECLARE @UserCust TABLE(
UserID INT,
Location VARCHAR(10),
Customer VARCHAR(10),
AllCont INT
)
INSERT @UserCust
VALUES(8790,'L2','C003',1)
INSERT @UserCust
VALUES(8790,'L2','C013',0)
INSERT @UserCust
VALUES(8790,'L3','C021',0)
DECLARE @UserCont TABLE(
UserID INT,
Location VARCHAR(10),
Customer VARCHAR(10),
AllCont VARCHAR(10)
)
INSERT @UserCont
VALUES(8790,'L2','C013','1DD-5')
INSERT @UserCont
VALUES(8790,'L2','C013','1DA-5')
INSERT @UserCont
VALUES(8790,'L3','C021','120-1')
SELECT DISTINCT oh.*
FROM @OrdHistory oh
LEFT JOIN @UserLocation ul
ON oh.Location = ul.Location
LEFT JOIN @UserCust ucu
ON oh.Location = ucu.Location
LEFT JOIN @UserCont uco
ON oh.Location = ucu.Location
WHERE (ul.AllLoc = 1)
OR (ul.AllLoc = 0
AND ul.Location = ucu.Location
AND ucu.AllCont = 1
AND ucu.Customer = oh.Customer)
OR (ul.AllLoc = 0
AND ul.Location = ucu.Location
AND ucu.AllCont = 0
AND ucu.Customer = oh.Customer
AND uco.AllCont = oh.OrderContract)
ORDER BY oh.Location,
oh.Customer
/*
OrdNum Location Customer OrderContract
A0001 L1 C001 100-1
A0004 L1 C041 1B1-1
A0002 L2 C003 10A-1
A0005 L2 C003 1DD-2
A0005 L2 C013 1DA-5
A0005 L2 C013 1DD-5
A0003 L3 C021 120-1
*/
Select OrdNum , Location , Customer , OrderContract from OrderHistory left outer join UserCont on OrderHistory.OrderContract
ASKER
Thank you wdosanjos. Your suggestion worked.
Now there is a slight modification. The user should have access based on the permissions set on these three tables which is working fine.
But now there is addtional column in UserConts called EnteredBy. And if user 8790 entered any order contracts then those also need to be listed irrespective of location and his permissions. Location is not related here because in some contracts the location could be negative if not approved yet. As long as the User = EnteredBy and OH.OrderContract = UCO.AllCont
Thanks much,
-B
Now there is a slight modification. The user should have access based on the permissions set on these three tables which is working fine.
But now there is addtional column in UserConts called EnteredBy. And if user 8790 entered any order contracts then those also need to be listed irrespective of location and his permissions. Location is not related here because in some contracts the location could be negative if not approved yet. As long as the User = EnteredBy and OH.OrderContract = UCO.AllCont
Thanks much,
-B
I think it would something like this, but I can only be sure with some sample data:
SELECT OH.OrdNum, OH.Location, OH.Customer, OH.OrderContract
FROM OrdHistory AS OH
LEFT OUTER JOIN UserLocation AS UL
ON OH.Location = UL.Location
LEFT OUTER JOIN UserCust AS UC
ON UL.UserID = UC.UserID AND OH.Location = UC.Location
AND OH.Customer = UC.Customer
LEFT OUTER JOIN UserCont AS UCO
ON UC.UserID = UCO.UserID AND OH.Location = UCO.Location
AND OH.Customer = UCO.Customer
AND OH.OrderContract = UCO.AllCont
WHERE (UL.UserID = 8790)
AND (
( UL.AllLoc IS NOT NULL
AND ( UL.AllLoc = 1
OR ( UC.AllCont IS NOT NULL
AND ( UC.AllCont = 1
OR UCO.AllCont IS NOT NULL))))
OR (UL.UserID = UC.EnteredBy AND OH.OrderContract = UCO.AllCont)
)
ORDER BY OH.Location, OH.Customer, OH.OrderContract, OH.OrdNumASKER
I don't think this join will give the correct results as Location in OH could be -1 but that site (-1) will not be in the UL table anytime because that is not the location. It is just the dummy site before the contract allocation actually happens.
LEFT OUTER JOIN UserLocation AS UL
ON OH.Location = UL.Location
LEFT OUTER JOIN UserLocation AS UL
ON OH.Location = UL.Location
Please provide some sample data to illustrate this scenario.
ASKER
Additional data
OrdHistory
OrdNum Location Customer EnteredBy OrderContract
A0021 L3 C021 9980 120-1 --allocated Order
A0014 L2 C041 9980 1B3-1 --allocated Order
A0015 L2 C003 9980 1D4-2 --allocated Order
A0215 -1 C021 8790 120-1 --non allocated Order
A0415 -1 C123 8790 AA4-2 --non allocated Order
UserLocation
UserID Location AllLoc
8790 L1 1
8790 L2 0
8790 L3 0
UserCust
UserID Location Customer AllCont
8790 L2 C003 1 --access to all contracts of this customer
8790 L3 C021 0 --access to specific contracts of this customer
8790 L3 C123 0 --access to specific contracts of this customer
UserCont
UserID Location Customer AllCont
8790 L2 C041 AB3-1
8790 L3 C021 120-1
8790 L3 C123 AA4-2
Final result will be what already we have for the old data plus
A0021 L3 C021 120-1 --User has access to this cust + contract
A0015 L2 C003 1D4-2 --User has access to all the contracts of this cust
A0215 -1 C021 120-1 --no location match with access but the protcol does
A0415 -1 C123 AA4-2 --no location match with access but the protcol does
Here the last two rows have no location matching the UserLocation table with the OrderHistory transaction table. but still shows because the user 8790 enterd those records and have access to those contratcs in the UserCont table.
Will this be helpful. The records should show as per the user permissions and the user runnign the query entered the records and have acccess to those contracts inrrespective of location (since those contracts are not allocated yet) nned to appear as well.
Thanks,
-B
OrdHistory
OrdNum Location Customer EnteredBy OrderContract
A0021 L3 C021 9980 120-1 --allocated Order
A0014 L2 C041 9980 1B3-1 --allocated Order
A0015 L2 C003 9980 1D4-2 --allocated Order
A0215 -1 C021 8790 120-1 --non allocated Order
A0415 -1 C123 8790 AA4-2 --non allocated Order
UserLocation
UserID Location AllLoc
8790 L1 1
8790 L2 0
8790 L3 0
UserCust
UserID Location Customer AllCont
8790 L2 C003 1 --access to all contracts of this customer
8790 L3 C021 0 --access to specific contracts of this customer
8790 L3 C123 0 --access to specific contracts of this customer
UserCont
UserID Location Customer AllCont
8790 L2 C041 AB3-1
8790 L3 C021 120-1
8790 L3 C123 AA4-2
Final result will be what already we have for the old data plus
A0021 L3 C021 120-1 --User has access to this cust + contract
A0015 L2 C003 1D4-2 --User has access to all the contracts of this cust
A0215 -1 C021 120-1 --no location match with access but the protcol does
A0415 -1 C123 AA4-2 --no location match with access but the protcol does
Here the last two rows have no location matching the UserLocation table with the OrderHistory transaction table. but still shows because the user 8790 enterd those records and have access to those contratcs in the UserCont table.
Will this be helpful. The records should show as per the user permissions and the user runnign the query entered the records and have acccess to those contracts inrrespective of location (since those contracts are not allocated yet) nned to appear as well.
Thanks,
-B
Thanks. Please try the following:
SELECT OH.OrdNum, OH.Location, OH.Customer, OH.OrderContract
FROM OrdHistory AS OH
LEFT OUTER JOIN UserLocation AS UL
ON OH.Location = UL.Location
LEFT OUTER JOIN UserCust AS UC
ON UL.UserID = UC.UserID AND OH.Location = UC.Location
AND OH.Customer = UC.Customer
LEFT OUTER JOIN UserCont AS UCO
ON UC.UserID = UCO.UserID AND OH.Location = UCO.Location
AND OH.Customer = UCO.Customer
AND OH.OrderContract = UCO.AllCont
WHERE (OH.EnteredBy = 8790 AND OH.Location = '-1')
OR ( UL.UserID = 8790
AND UL.AllLoc IS NOT NULL
AND ( UL.AllLoc = 1
OR ( UC.AllCont IS NOT NULL
AND ( UC.AllCont = 1
OR UCO.AllCont IS NOT NULL))))
ORDER BY OH.Location, OH.Customer, OH.OrderContract, OH.OrdNumASKER
It is not working. All the records in OrderHistory are appearing in the results but not where the EnteredBy = @UserID
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
this should do:
select oh.*
from OrdHistory oh
join ( select ul.Location
, case when ul.allloc = 1 then '%' else uc.customer end customer
, case when ul.allloc = 1 or uc.allloc = 1 then '%' else un.AllCont
end OrderContract
from UserLocation ul
left join UserCust uc
ON uc.UserID = ul.UserID
AND uc.Location = ul.Location
AND ul.AllLoc = 0
left join UserCont un
ON un.UserID = uc.UserID
AND un.Location = uc.Location
AND un.Customer = uc.Customer
AND uc.AllLoc = 0
where ul.userid= @userid
) v
ON oh.Location = v.Location
AND oh.Customer like v.Customer
AND oh.OrderContract like v.OrderContractASKER
Thanks angelIII for the reply.
But isn't CASE statement in JOIN more expensive and sometimes it doe snot work.
But isn't CASE statement in JOIN more expensive and sometimes it doe snot work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>But isn't CASE statement in JOIN more expensive
only the explain plan can tell for sure.
>and sometimes it doe snot work.
please clarify
only the explain plan can tell for sure.
>and sometimes it doe snot work.
please clarify
ASKER
Worked correctly
ASKER
wdosanjos,
This solution doe snot work if user has access to all the sites, customers and protocols. In this situation the user is a administrator and will have records only in the UserLocation table with-
UserLocation
UserID Location AllLoc
2234 L1 1
2234 L2 1
2234 L3 1
This user will not have any records in UserCust and UserCont table as he has full access. This is case the query does not return all the contracts for the sites the user has access to.
Can you please help?
Thanks,
-B
This solution doe snot work if user has access to all the sites, customers and protocols. In this situation the user is a administrator and will have records only in the UserLocation table with-
UserLocation
UserID Location AllLoc
2234 L1 1
2234 L2 1
2234 L3 1
This user will not have any records in UserCust and UserCont table as he has full access. This is case the query does not return all the contracts for the sites the user has access to.
Can you please help?
Thanks,
-B
select orderhistory.ordernum, orderhistory.Location, orderhistory.customer, orderhistory.contact
can you post the create table statements?