Hi,
I'm not sure if this is possible to obtain hence my request for help. Basically I need to know if it's possible to run an SQL query which returns the column name where the value stored is 1.
Attached are an example of the table and its content and the result I need to obtain from the query if it's always possible.
Thank you for your help.
Your database is not normalised. SQL is very suitable to process normalised databases. Processing a denormalised database is far far harder.
Step one: normalise the table:
DROP TABLE IF EXISTS dbo.DaTable;
CREATE TABLE dbo.DaTable(
CUSTSYSREF int NOT NULL PRIMARY KEY,
REPSSYSREF int NOT NULL,
MON bit NOT NULL DEFAULT 0,
TUE bit NOT NULL DEFAULT 0,
WED bit NOT NULL DEFAULT 0,
THU bit NOT NULL DEFAULT 0,
FRI bit NOT NULL DEFAULT 0,
SAT bit NOT NULL DEFAULT 0,
SUN bit NOT NULL DEFAULT 0
)
INSERT INTO dbo.DaTable(CUSTSYSREF, REPSSYSREF, MON, TUE, WED, THU, FRI, SAT, SUN) VALUES
(231, 15, 0, 0, 1, 0, 0, 0, 0),
(232, 15, 0, 1, 0, 0, 0, 0, 0),
(233, 16, 1, 0, 1, 0, 0, 0, 0),
(234, 17, 0, 0, 1, 0, 0, 1, 0)
SELECT * FROM dbo.DaTable
DROP TABLE IF EXISTS dbo.ParentTable;
DROP TABLE IF EXISTS dbo.ChildTable;
DROP TABLE IF EXISTS dbo.DaDaysTable;
CREATE TABLE dbo.DaDaysTable(
Day_Of_Week_id int NOT NULL PRIMARY KEY,
Day_Of_Week CHAR(3)
)
INSERT INTO dbo.DaDaysTable(Day_Of_Week_id, Day_Of_Week) VALUES
(1, 'MON'), (2, 'TUE'), (3, 'WED'), (4, 'THU'), (5, 'FRI'), (6, 'SAT'), (7, 'SUN');
CREATE TABLE dbo.ParentTable(
parent_id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CUSTSYSREF int NOT NULL,
REPSSYSREF int NOT NULL,
)
INSERT INTO dbo.ParentTable(CUSTSYSREF, REPSSYSREF)
SELECT DISTINCT CUSTSYSREF, REPSSYSREF
FROM dbo.DaTable
GO
SELECT * from dbo.ParentTable
CREATE TABLE dbo.ChildTable(
id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
parent_id int NOT NULL,
Day_Of_Week_id int NOT NULL
)
INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
, DOW.day_of_week_id
FROM dbo.DaTable as DT
INNER JOIN dbo.ParentTable as PT
ON DT.CUSTSYSREF = PT.CUSTSYSREF
AND DT.REPSSYSREF = PT.REPSSYSREF
INNER JOIN dbo.DaDaysTable as DOW
ON DOW.Day_Of_Week = 'MON'
AND DT.MON = 1
INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
, DOW.day_of_week_id
FROM dbo.DaTable as DT
INNER JOIN dbo.ParentTable as PT
ON DT.CUSTSYSREF = PT.CUSTSYSREF
AND DT.REPSSYSREF = PT.REPSSYSREF
INNER JOIN dbo.DaDaysTable as DOW
ON DOW.Day_Of_Week = 'TUE'
AND DT.TUE = 1
INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
, DOW.day_of_week_id
FROM dbo.DaTable as DT
INNER JOIN dbo.ParentTable as PT
ON DT.CUSTSYSREF = PT.CUSTSYSREF
AND DT.REPSSYSREF = PT.REPSSYSREF
INNER JOIN dbo.DaDaysTable as DOW
ON DOW.Day_Of_Week = 'WED'
AND DT.WED = 1
INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
, DOW.day_of_week_id
FROM dbo.DaTable as DT
INNER JOIN dbo.ParentTable as PT
ON DT.CUSTSYSREF = PT.CUSTSYSREF
AND DT.REPSSYSREF = PT.REPSSYSREF
INNER JOIN dbo.DaDaysTable as DOW
ON DOW.Day_Of_Week = 'THU'
AND DT.THU = 1
INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
, DOW.day_of_week_id
FROM dbo.DaTable as DT
INNER JOIN dbo.ParentTable as PT
ON DT.CUSTSYSREF = PT.CUSTSYSREF
AND DT.REPSSYSREF = PT.REPSSYSREF
INNER JOIN dbo.DaDaysTable as DOW
ON DOW.Day_Of_Week = 'FRI'
AND DT.FRI = 1
INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
, DOW.day_of_week_id
FROM dbo.DaTable as DT
INNER JOIN dbo.ParentTable as PT
ON DT.CUSTSYSREF = PT.CUSTSYSREF
AND DT.REPSSYSREF = PT.REPSSYSREF
INNER JOIN dbo.DaDaysTable as DOW
ON DOW.Day_Of_Week = 'SAT'
AND DT.SAT = 1
INSERT INTO dbo.ChildTable(parent_id, Day_Of_Week_id)
SELECT PT.parent_id
, DOW.day_of_week_id
FROM dbo.DaTable as DT
INNER JOIN dbo.ParentTable as PT
ON DT.CUSTSYSREF = PT.CUSTSYSREF
AND DT.REPSSYSREF = PT.REPSSYSREF
INNER JOIN dbo.DaDaysTable as DOW
ON DOW.Day_Of_Week = 'SUN'
AND DT.SUN = 1
Step two: get the result you want, based on a normalised table:
;WITH CTE AS(
SELECT CT.parent_id
, DOW.day_of_week
, ROW_NUMBER() OVER (PARTITION BY CT.parent_id ORDER BY DOW.Day_Of_Week_id ASC) as RowNum
FROM dbo.ChildTable as CT
INNER JOIN dbo.DaDaysTable as DOW
ON CT.Day_Of_Week_id = DOW.Day_Of_Week_id
)
SELECT PT.CUSTSYSREF
, PT.REPSSYSREF
, COALESCE(FirstDay.Day_Of_Week, '') as DayOfCall1
, COALESCE(SecondDay.Day_Of_Week, '') as DayOfCall2
FROM dbo.ParentTable as PT
LEFT OUTER JOIN CTE as FirstDay
ON PT.parent_id = FirstDay.parent_id
AND FirstDay.RowNum = 1
LEFT OUTER JOIN CTE as SecondDay
ON PT.parent_id = SecondDay.parent_id
AND SecondDay.RowNum = 2Hi
i had to re arrange the data using SQL ...
before getting the answer
DROP TABLE dbo.DaTable;
CREATE TABLE dbo.DaTable(
CUSTSYSREF int NOT NULL PRIMARY KEY,
REPSSYSREF int NOT NULL,
MON bit NOT NULL DEFAULT 0,
TUE bit NOT NULL DEFAULT 0,
WED bit NOT NULL DEFAULT 0,
THU bit NOT NULL DEFAULT 0,
FRI bit NOT NULL DEFAULT 0,
SAT bit NOT NULL DEFAULT 0,
SUN bit NOT NULL DEFAULT 0
)
INSERT INTO dbo.DaTable(CUSTSYSREF, REPSSYSREF, MON, TUE, WED, THU, FRI, SAT, SUN) VALUES
(231, 15, 0, 0, 1, 0, 0, 0, 0),
(232, 15, 0, 1, 0, 0, 0, 0, 0),
(233, 16, 1, 0, 1, 0, 0, 0, 0),
(234, 17, 0, 0, 1, 0, 0, 1, 0)
SELECT 'table data ', * FROM dbo.DaTable
; with cte_weekday as
(
select 'MON' as weekday ,1 as OrderBy
union all
select 'TUE',2
union all
select 'WED',3
union all
select 'THU',4
union all
select 'FRI',5
union all
select 'SAT',6
union all
select 'SUN',7
) ,cte_daycall as
(
select CUSTSYSREF, REPSSYSREF,mon as Weekday,'MON' as day from dbo.DaTable
union all
select CUSTSYSREF, REPSSYSREF,tue ,'TUE' from dbo.DaTable
union all
select CUSTSYSREF, REPSSYSREF,wed ,'WED' from dbo.DaTable
union all
select CUSTSYSREF, REPSSYSREF,thu ,'THU' from dbo.DaTable
union all
select CUSTSYSREF, REPSSYSREF,FRI ,'FRI' from dbo.DaTable
union all
select CUSTSYSREF, REPSSYSREF,SAT ,'SAT' from dbo.DaTable
union all
select CUSTSYSREF, REPSSYSREF,SUN ,'SUN' from dbo.DaTable
) , cte_rn as
(
select
a.*
, b.OrderBy
, ROW_NUMBER() over (partition by CUSTSYSREF,REPSSYSREF order by orderby ) as rn
from
cte_daycall a
join cte_weekday b
on
a.day = b.weekday
where
a.Weekday = 1
), cte_callday as
(
select
CUSTSYSREF
,REPSSYSREF
,case when rn = 1 then Day end as CallDay1
,case when rn = 2 then Day end as CallDay2
from
cte_rn
)
select
CUSTSYSREF
, REPSSYSREF
, max(CallDay1)
, max(CallDay2)
from
cte_callday
group by
CUSTSYSREF
, REPSSYSREF
Hi Harish,
You are basically doing the same normalisation - at runtime - in CTE's.
At the end his data structure stays flawed and as there has been no structural improvement of his data model.
Un Fortunately Wim
In Real Life Office Work Scenario's
We don't have any choices
Have to find solutions ... in fixed environments !!!
Would be Very Nice if we had a lot of flexibility and freedom to do WHAT WE think and WANT !!!
SELECT
T.CUSTSYSREF,
T.REPSYSREF,
MAX(CASE WHEN DayCounter = 1 THEN Day ELSE '' END) AS DayOfCall1,
MAX(CASE WHEN DayCounter = 2 THEN Day ELSE '' END) AS DayOfCall2,
MAX(CASE WHEN DayCounter = 3 THEN Day ELSE '' END) AS DayOfCall3,
MAX(CASE WHEN DayCounter = 4 THEN Day ELSE '' END) AS DayOfCall4,
MAX(CASE WHEN DayCounter = 5 THEN Day ELSE '' END) AS DayOfCall5,
MAX(CASE WHEN DayCounter = 6 THEN Day ELSE '' END) AS DayOfCall6,
MAX(CASE WHEN DayCounter = 7 THEN Day ELSE '' END) AS DayOfCall7
FROM #Table T
CROSS APPLY (
SELECT *, ROW_NUMBER() OVER(ORDER BY DayNumber) AS DayCounter
FROM ( VALUES(1, 'MON', MON), (2, 'TUE', TUE), (3, 'WED', WED), (4, 'THU', THU),
(5, 'FRI', FRI), (6, 'SAT', SAT), (7, 'SUN', SUN) ) AS days(DayNumber, Day, DayValue)
WHERE DayValue = 1
) AS ca1
GROUP BY T.CUSTSYSREF, T.REPSYSREF
ORDER BY CUSTSYSREF