November 15, 2017 at 1:46 pm
Hello All,
I have master data, based on that i am trying to prepare query in such way that rows can be combined by applying grouping and data validation condition in where clause.
I am facing problem to create view;
Here i am attaching excel which contains master data and expected result.
Is it possible to get result in single SQL Statement?
If anyone knows the solution/ suggestion please help me.
November 16, 2017 at 4:12 am
Hi Kapil,
When posting T-SQL questions, please avoid supplying your data and expected results in an excel Document; you'll find many people will not be happy to open the file (as many of us use these forums at work!). Instead you should provide DDL, sample data and expected results in readily consumable format. I've done this for you on this occasion, but have a read of the link in my signature (this'll help you get answers much more quickly).
On the subject of your expected results, you don't explain the reasoning/logic for how you get to them. Could you please?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 16, 2017 at 7:52 am
Hello Thom,
Thanks for guidance! will follow the practice in future.
Logic
1. Result Expected from MeetingDetails master table.
2. StateId and DistrictId mapping defined in StateDistrict Master table
3. DistrictId and ProjectId mapping defined in DistrictProject
4. From MeetingDetails result is grouped by MeetingId
5. If stateId and destrictId mapping present in StateDistrict then after check for DistrictId and ProjectId present in DistrictProject master.
Both Mapping present then consider Record like StateId, DistrictId and projectId.
6.If StateId and DestrictId mapping present in StateDistrict but DistrictId and ProjectId mapping not present then consider
StateId and districtId with ProjectId null
and Consider ProjectId as seperate record with stateId and disctrictId null.
7. Finally ally CROSS join with DepartmentId.
November 16, 2017 at 8:07 am
KapilT - Thursday, November 16, 2017 7:52 AMHello Thom,Thanks for guidance! will follow the practice in future.
Logic
1. Result Expected from MeetingDetails master table. --Meeting Details looks nothing like your Expected result
2. StateId and DistrictId mapping defined in StateDistrict Master table --Defined where? What mapping? You've not defined anything
3. DistrictId and ProjectId mapping defined in DistrictProject --Defined where? What mapping? You've not defined anything
4. From MeetingDetails result is grouped by MeetingId --If you're using GROUP BY what are your aggragations?
5. If stateId and destrictId mapping present in StateDistrict then after check for DistrictId and ProjectId present in DistrictProject master.
Both Mapping present then consider Record like StateId, DistrictId and projectId. --Mapping? What mapping?
6.If StateId and DestrictId mapping present in StateDistrict but DistrictId and ProjectId mapping not present then consider
StateId and districtId with ProjectId null
and Consider ProjectId as seperate record with stateId and disctrictId null. --Still no idea
7. Finally ally CROSS join with DepartmentId. -- CROSS "JOIN"? Do you mean CROSS APPLY? DepartmentId is a column, not a table. You can't CROSS APPLY to a column.
Sorry, there might be a language barrier, I have no idea what you mean here. I've added a comments, but you need to define your logic.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 16, 2017 at 8:49 am
It's not just a language barrier. The expected results are logically inconsistent.
For example, this combination produces three results:
INSERT INTO #StateDistrict
VALUES
(1,40,50)
INSERT INTO #DistrictProject
VALUES
(1,50,60)
SELECT *
FROM (VALUES (100,40,50,60,70),
(100,40,50,NULL,70),
(100,NULL,NULL,60,70)
But this exactly parallel situation produces three completely different results:INSERT INTO #StateDistrict
VALUES
(1,41,51)
INSERT INTO #DistrictProject
VALUES
(1,51,61)
SELECT *
FROM (VALUES (100,41,51,61,70),
(100,41,51,61,71),
(100,41,51,61,72)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 16, 2017 at 1:03 pm
DECLARE @MeetingDetails TABLE
(MeetingId int,
PointId int,
UserId int,
StateId int NULL,
DistrictId int NULL,
ProjectId int NULL,
DepartmentId int NULL); INSERT INTO @MeetingDetails
VALUES
(100,200,300,40,NULL,NULL,NULL),
(100,200,300,NULL,50,NULL,NULL),
(100,200,300,NULL,NULL,60,NULL),
(100,200,300,NULL,NULL,NULL,70),
(100,201,301,41,NULL,NULL,NULL),
(100,201,301,42,NULL,NULL,NULL),
(100,201,301,NULL,51,NULL,NULL),
(100,201,301,NULL,52,NULL,NULL),
(100,201,301,NULL,NULL,60,NULL),
(100,201,301,NULL,NULL,61,NULL),
(100,201,301,NULL,NULL,63,NULL),
(100,201,301,NULL,NULL,65,NULL),
(100,201,301,NULL,NULL,NULL,70),
(100,201,301,NULL,NULL,NULL,71),
(100,201,301,NULL,NULL,NULL,72)
DECLARE @StateDistrict TABLE
(StateDistrictId int,
StateId int,
DistrictId int);
INSERT INTO @StateDistrict
VALUES
(1,40,50),
(2,41,51),
(3,42,51)
DECLARE @DistrictProject TABLE
(DistrictProjectId int,
DistrictId int,
ProjectId int);
INSERT INTO @DistrictProject
VALUES
(1,50,60),
(2,51,61),
(3,51,62),
(4,51,63),
(5,52,64),
(6,53,65)
DECLARE @ExpectedResult TABLE
(MeetingId int,
StateId int NULL,
DistrictId int NULL,
ProjectId int NULL,
DepartmentId int NULL);
INSERT INTO @ExpectedResult
VALUES
--CASE 1
-- StateId & DistrictId From @MeetingDetails (MeetingId 100 & PointId 200) Relation present in @StateDestrict (StateDestrictId 1)
-- Same Way DistrictId & ProjectId From @MeetingDetails (MeetingId 100 & PointId 200) Relation present in @DestrictProject (DestrictProject 1)
---- Merge the Rows With DepartmentId from @MeetingDetails (MeetingId 100 & PointId 200)
(100,40,50,60,70),
--CASE 2
-- StateId & DistrictId From @MeetingDetails (MeetingId 100 & PointId 200) Relation present in @StateDestrict (StateDestrictId 1)
---- Merge the Values of StateId & DistrictId in one row With DepartmentId from @MeetingDetails (MeetingId 100 & PointId 200)
(100,40,50,NULL,70),
--CASE 3
-- Each ProjectId from @MeetingDetails (MeetingId 100 & PointId 200) individually consider With DepartmentId from @MeetingDetails (MeetingId 100 & PointId 200)
(100,NULL,NULL,60,70),
--CASE 4
-- Considered Individual DepartmentId from @MeetingDetails (MeetingId 100 & PointId 200)
(100,NULL,NULL,NULL,70),
-- SAME AS CASE 1 For @MeetingDetails (MeetingId 100 & PointId 201)
-- DepartmentId considered distinct from @MeetingDetails (MeetingId 100 & PointId 201)
-- StateId & DistrictId Relation in @StateDestrict (StateDestrictId 2 & 3)
-- DistrictId & ProjectId Relation present in @DestrictProject (DestrictProject 2 & 4)
(100,41,51,61,70),
(100,41,51,61,71),
(100,41,51,61,72),
(100,41,51,63,70),
(100,41,51,63,71),
(100,41,51,63,72),
---------
(100,42,51,61,70),
(100,42,51,61,71),
(100,42,51,61,72),
(100,42,51,63,70),
(100,42,51,63,71),
(100,42,51,63,72),
-- SAME AS CASE 2 For @MeetingDetails (MeetingId 100 & PointId 201)
-- Department considered distinct from @MeetingDetails (MeetingId 100 & PointId 201)
-- StateId & DistrictId Relation in @StateDestrict (StateDestrictId 2)
(100,41,51,NULL,70),
(100,41,51,NULL,71),
(100,41,51,NULL,72),
(100,42,51,NULL,70),
(100,42,51,NULL,71),
(100,42,51,NULL,72),
-- CASE 5
-- StateId & DistrictId From @MeetingDetails (MeetingId 100 & PointId 201) Relation NOT present in @StateDestrict
-- ALSO DistrictId & ProjectId Relation NOT present in @DestrictProject
-- SO Considered With DepartmentId distinct from @MeetingDetails (MeetingId 100 & PointId 201)
(100,NULL,52,NULL,70),
(100,NULL,52,NULL,71),
(100,NULL,52,NULL,72),
-- SAME AS CASE 3
-- Each ProjectId from @MeetingDetails (MeetingId 100 & PointId 201) individual consider With DepartmentId from @MeetingDetails (MeetingId 100 & PointId 201)
(100,NULL,NULL,60,71),
(100,NULL,NULL,60,72),
(100,NULL,NULL,61,70),
(100,NULL,NULL,61,71),
(100,NULL,NULL,61,72),
(100,NULL,NULL,63,70),
(100,NULL,NULL,63,71),
(100,NULL,NULL,63,72),
-- CASE 6
-- StateId & DistrictId from @MeetingDetails (MeetingId 100 & PointId 201) NOT having any direct or indirect relation with ProjectID
-- Also comes under CASE 3
(100,NULL,NULL,65,70),
(100,NULL,NULL,65,71),
(100,NULL,NULL,65,72),
-- SAME AS CASE 4
(100,NULL,NULL,NULL,70),
(100,NULL,NULL,NULL,71),
(100,NULL,NULL,NULL,72)
SELECT * FROM @MeetingDetails
SELECT * FROM @StateDistrict
SELECT * FROM @DistrictProject
SELECT * FROM @ExpectedResult
I hope description will help you to understand expected result
November 17, 2017 at 12:41 pm
SELECT DISTINCT M.MeetingId,M.StateId,M.DistrictId,M.ProjectId, M.DepartmentId FROM
(
SELECT MS.MeetingId, MS.PointId,MS.StateId,MS.DistrictId,MS.ProjectId, D.DepartmentId
FROM ( SELECT MS.MeetingId, MS.PointId,MS.StateId,MS.DistrictId,MS.ProjectId
FROM ( SELECT DISTINCT MS.MeetingId, MS.PointId,MS.StateId,MD.DistrictId,MP.ProjectId
FRoM @MeetingDetails MS, @MeetingDetails MD, @MeetingDetails MP
Group By MS.MeetingId, MS.PointId,MS.StateId,MD.DistrictId,MP.ProjectId
) MS
INNER JOIN (SELECT SD.StateId,SD.DistrictId,DP.ProjectId
FROM @StateDistrict SD
INNER JOIN @DistrictProject DP ON SD.DistrictId = DP.DistrictId
) SDP ON MS.StateId = SDP.StateId AND MS.DistrictId = SDP.DistrictId AND MS.ProjectId = SDP.ProjectId
UNION ALL
SELECT MS.MeetingId, MS.PointId,MS.StateId,MS.DistrictId, NULL
FROM ( SELECT DISTINCT MS.MeetingId, MS.PointId,MS.StateId,MD.DistrictId
FRoM @MeetingDetails MS, @MeetingDetails MD
Group By MS.MeetingId, MS.PointId,MS.StateId,MD.DistrictId
) MS
INNER JOIN @StateDistrict SD ON MS.StateId = SD.StateId AND MS.DistrictId = SD.DistrictId
UNION ALL
SELECT MS.MeetingId, MS.PointId,NULL,MS.DistrictId,NULL
FROM @MeetingDetails MS
WHERE MS.DistrictId NOT IN (SELECT MD.DistrictId
FROM ( SELECT MS.MeetingId, MS.PointId,MS.StateId,MS.DistrictId
FROM ( SELECT DISTINCT MS.MeetingId, MS.PointId,MS.StateId,MD.DistrictId
FRoM @MeetingDetails MS, @MeetingDetails MD
Group By MS.MeetingId, MS.PointId,MS.StateId,MD.DistrictId
) MS
INNER JOIN @StateDistrict SD ON MS.StateId = SD.StateId AND MS.DistrictId = SD.DistrictId
)MD
)
GROUP BY MS.MeetingId, MS.PointId,MS.DistrictId
UNION ALL
SELECT DISTINCT MS.MeetingId, MS.PointId,NULL,NULL,MS.ProjectId
FROM @MeetingDetails MS
WHERE MS.ProjectId IS NOT NULL
GROUP BY MS.MeetingId, MS.PointId, MS.ProjectId
) MS
JOIN ( SELECT DISTINCT MS.MeetingId, MS.PointId,NULL AS StateId,NULL AS DistrictId,NULL AS ProjectId,MS.DepartmentId
FROM @MeetingDetails MS
WHERE MS.DepartmentId IS NOT NULL
) D ON MS.MeetingId = D.MeetingId AND MS.PointId = D.PointId
UNION ALL
SELECT DISTINCT MS.MeetingId, MS.PointId,NULL AS StateId,NULL AS DistrictId,NULL AS ProjectId,MS.DepartmentId
FROM @MeetingDetails MS
WHERE MS.DepartmentId IS NOT NULL
GROUP BY MS.MeetingId, MS.PointId, MS.DepartmentId
) M
November 20, 2017 at 3:34 pm
The main problem that you're encountering here is that your MeetingDetails table is a weird hybrid of an EAV and normalized data. You still have a separate row for each value, but they're in their own columns instead of having a field that tells you what the attribute is.
The following gives the same results with your sample data at a third to a fourth the cost depending on which measure you use.
;WITH PointStates AS
(
SELECT md.MeetingId, md.PointId, sd.StateId, sd.DistrictId
FROM @MeetingDetails md
INNER JOIN @StateDistrict sd
ON md.StateId = sd.StateId
)
, PointDistricts AS
(
SELECT md.MeetingID, md.PointID, ps.StateId, md.DistrictID, dp.ProjectId
FROM @MeetingDetails md
LEFT OUTER JOIN @StateDistrict sd
ON md.DistrictId = sd.DistrictId
LEFT OUTER JOIN PointStates ps
ON md.MeetingId = ps.MeetingId
AND md.PointId = ps.PointId
AND sd.StateId = ps.StateId
OUTER APPLY
(
SELECT dp.ProjectId
FROM @DistrictProject dp
WHERE md.DistrictId = dp.DistrictId
--UNION
--SELECT NULL
) dp
)
, PointProjects AS
(
SELECT MeetingID, PointID, ProjectId
FROM @MeetingDetails
)
, PointDepartments AS
(
SELECT MeetingID, PointID, DepartmentId
FROM @MeetingDetails
WHERE DepartmentId IS NOT NULL
)
SELECT DISTINCT
pde.MeetingId
, pd.StateId
, pd.DistrictId
, pp.ProjectId
, pde.DepartmentId
FROM PointDepartments pde
LEFT OUTER JOIN PointDistricts pd
ON pde.MeetingId = pd.MeetingId
AND pde.PointId = pd.PointId
OUTER APPLY
(
SELECT pp.ProjectId
FROM PointProjects pp
WHERE pde.MeetingId = pp.MeetingId
AND pde.PointId = pp.PointId
AND ( pp.ProjectId = pd.ProjectId
OR pd.DistrictId IS NULL )
UNION ALL
SELECT NULL
) pp
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply