August 23, 2016 at 1:57 pm
I HAVE A TABLE RANGING FROM MONDAY, TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAY
THESE TABLES HAVE VALUES BETWEEN 0 AND 1 EG,
MONDAY : 0, TUESDAY :1,WEDNESDAY : 0,THURSDAY:1,FRIDAY:1. SATURDAY:1 SUNDAY:1
HOW CAN I SELECT ONLY THE FIRST DAY WITH field VALUE 1
(IN THE EXAMPLE ABOVE TUESDAY WOULD BE THE FIRSTDAY)AND ASSIGN THAT VALUE IN THAT DAY TO A PARAMETER?
I learn from the footprints of giants......
August 23, 2016 at 2:02 pm
JALLYKAMOZE (8/23/2016)
I HAVE A TABLE RANGING FROM MONDAY, TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAYTHESE TABLES HAVE VALUES BETWEEN 0 AND 1 EG,
MONDAY : 0, TUESDAY :1,WEDNESDAY : 0,THURSDAY:1,FRIDAY:1. SATURDAY:1 SUNDAY:1
HOW CAN I SELECT ONLY THE FIRST DAY WITH field VALUE 1
(IN THE EXAMPLE ABOVE TUESDAY WOULD BE THE FIRSTDAY)AND ASSIGN THAT VALUE IN THAT DAY TO A PARAMETER?
Are the days columns, rows or a single string?
August 23, 2016 at 2:04 pm
the days are columns
I learn from the footprints of giants......
August 23, 2016 at 2:06 pm
Luis Cazares (8/23/2016)
JALLYKAMOZE (8/23/2016)
I HAVE A TABLE RANGING FROM MONDAY, TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAYTHESE TABLES HAVE VALUES BETWEEN 0 AND 1 EG,
MONDAY : 0, TUESDAY :1,WEDNESDAY : 0,THURSDAY:1,FRIDAY:1. SATURDAY:1 SUNDAY:1
HOW CAN I SELECT ONLY THE FIRST DAY WITH field VALUE 1
(IN THE EXAMPLE ABOVE TUESDAY WOULD BE THE FIRSTDAY)AND ASSIGN THAT VALUE IN THAT DAY TO A PARAMETER?
Are the days columns, rows or a single string?
the days are in separate columns , each having values 0 or 1, i want to retrieve the first day with value 1
I learn from the footprints of giants......
August 23, 2016 at 2:08 pm
You can use a CASE expression for this:
SELECT CASE WHEN Monday = 1 THEN 'Monday'
WHEN Tuesday = 1 THEN 'Tuesday'
---etc
END
August 23, 2016 at 2:12 pm
Luis Cazares (8/23/2016)
You can use a CASE expression for this:
SELECT CASE WHEN Monday = 1 THEN 'Monday'WHEN Tuesday = 1 THEN 'Tuesday'
---etc
END
this will give all the columns that fullfill this expression, i want it to return only the first day that has the value as 1, irrespective of what day it falls on.
I learn from the footprints of giants......
August 23, 2016 at 2:16 pm
JALLYKAMOZE (8/23/2016)
Luis Cazares (8/23/2016)
You can use a CASE expression for this:
SELECT CASE WHEN Monday = 1 THEN 'Monday'WHEN Tuesday = 1 THEN 'Tuesday'
---etc
END
this will give all the columns that fullfill this expression, i want it to return only the first day that has the value as 1, irrespective of what day it falls on.
No, it won't. A CASE expression will only return one value.
August 23, 2016 at 3:00 pm
SEE MY SCRIPT BELOW,
IM POPULATING A TEMP TABLE
IF EXISTS ( SELECT * FROM tempdb..sysobjects
WHERE id = OBJECT_ID(N'tempdb..#updatecontractdetailtable'))
DROP table #updatecontractdetailtable
SELECT programID,Stationid,Monday,Tuesday,Wednesday,
Thursday,friday,saturday,sunday,StartTime,contractheaderid,DelaySeconds
into #updatecontractdetailtable
from ContractDetail
ORDER BY programid,Stationid
I WANT TO RETURN THESE COLUMNS , PROGRAMID,STATIONID
AND THE FIRST COLUMN FROM (Monday,Tuesday,Wednesday,
Thursday,friday,saturday,sunday) THAT HAS THE VALUE 1 AS "CONTRACTDETAILFIRSTDAY",
THERE ARE ONLY TWO VALUES ALLOWED IN THE DAY FIELDS , 1 OR 0,
AND THESE OTHER COLUMNS : StartTime,contractheaderid,DelaySeconds
I learn from the footprints of giants......
August 23, 2016 at 4:20 pm
If you want to create the table with a column named as the first occurrence, you need to use dynamic sql. That's just wanting problems, IMHO.
Otherwise, you could do this:
IF OBJECT_ID(N'tempdb..#updatecontractdetailtable') IS NOT NULL
DROP table #updatecontractdetailtable;
SELECT programID,
Stationid,
CASE WHEN Monday = 1 THEN 'Monday'
WHEN Tuesday = 1 THEN 'Tuesday'
WHEN Wednesday = 1 THEN 'Wednesday'
WHEN Thursday = 1 THEN 'Thursday'
WHEN friday = 1 THEN 'Friday'
WHEN saturday = 1 THEN 'Saturday'
WHEN sunday = 1 THEN 'Sunday'
END AS FirstDay,
StartTime,
contractheaderid,
DelaySeconds
into #updatecontractdetailtable
from ContractDetail
ORDER BY programid,Stationid;
August 24, 2016 at 8:03 am
Luis Cazares (8/23/2016)
If you want to create the table with a column named as the first occurrence, you need to use dynamic sql. That's just wanting problems, IMHO.Otherwise, you could do this:
IF OBJECT_ID(N'tempdb..#updatecontractdetailtable') IS NOT NULL
DROP table #updatecontractdetailtable;
SELECT programID,
Stationid,
CASE WHEN Monday = 1 THEN 'Monday'
WHEN Tuesday = 1 THEN 'Tuesday'
WHEN Wednesday = 1 THEN 'Wednesday'
WHEN Thursday = 1 THEN 'Thursday'
WHEN friday = 1 THEN 'Friday'
WHEN saturday = 1 THEN 'Saturday'
WHEN sunday = 1 THEN 'Sunday'
END AS FirstDay,
StartTime,
contractheaderid,
DelaySeconds
into #updatecontractdetailtable
from ContractDetail
ORDER BY programid,Stationid;
Thanks Sir,
This worked perfectly
I learn from the footprints of giants......
August 24, 2016 at 8:07 am
JALLYKAMOZE (8/24/2016)
Thanks Sir,This worked perfectly
Great! Do you understand why and how it works?
August 25, 2016 at 1:00 pm
Of course if you had to do this for 100 or more columns, then this is where I introduce Excel to help me quick and dirty.
I just query sys.columns to get the columns as use some text manipulation to formulate the text all the CASE statements . Another way is just to incorporate these results
select'when '+[name] +' =1 then ' +''''+ [name] +''''
from sys.columns
where [object_id]=object_id('yourTable')
from the results window into your query just as well. Just though it might help on those not so normalised wide tables.
----------------------------------------------------
August 25, 2016 at 2:58 pm
hello ,
This is what i came up with:
tested and it worked, but for some reason it is omitting some data from the base table..i am still doing some research on this.:
IF EXISTS ( SELECT * FROM tempdb..sysobjects
WHERE id = OBJECT_ID(N'tempdb..#updatedelaysecondstable'))
DROP table #updatedelaysecondstable
SELECT cd.ProgramID,p.ID,cd.ContractHeaderID,cd.StationID,cd.StartTime AS ContractStartTime,
p.StartTime AS ProgramStartTime,ABS (DATEDIFF (MINUTE, cd.StartTime , p.StartTime)) AS MinuteDiff,
CASE
WHEN cd.Monday = 1 THEN 7
WHEN cd.Tuesday = 1 THEN 6
WHEN cd.Wednesday = 1 THEN 5
WHEN cd.Thursday = 1 THEN 4
WHEN cd.Friday = 1 THEN 3
WHEN cd.Saturday = 1 THEN 2
WHEN cd.Sunday = 1 THEN 1
END AS ContractDetailFirstDay,
CASE
WHEN p.Monday = 1 THEN 7
WHEN p.Tuesday = 1 THEN 6
WHEN p.Wednesday = 1 THEN 5
WHEN p.Thursday = 1 THEN 4
WHEN p.Friday = 1 THEN 3
WHEN p.Saturday = 1 THEN 2
WHEN p.Sunday = 1 THEN 1
END AS ProgramFirstDay,
cd.DelaySeconds
INTO #updatedelaysecondstable
FROM ContractDetail cd
INNER JOIN Program p
ON cd.programID = p.ID
ORDER BY cd.ID
IF EXISTS ( SELECT * FROM tempdb..sysobjects
WHERE id = OBJECT_ID(N'tempdb..#FINALDELAYSECONDS'))
DROP table #FINALDELAYSECONDS
SELECT ProgramID,ID,ContractHeaderID,StationID,ContractStartTime,ProgramStartTime,
MinuteDiff,ContractDetailFirstDay,ProgramFirstDay, CASE WHEN ProgramFirstDay IS NOT NULL THEN (ContractDetailFirstDay- ProgramFirstDay)*24*60*60 + MinuteDiff*60 ELSE 0
END AS Delayseconds
INTO #FINALDELAYSECONDS
FROM #updatedelaysecondstable
I learn from the footprints of giants......
August 25, 2016 at 3:24 pm
WHEN cd.Monday = 1 THEN 7
WHEN cd.Tuesday = 1 THEN 6
WHEN cd.Wednesday = 1 THEN 5
WHEN cd.Thursday = 1 THEN 4
WHEN cd.Friday = 1 THEN 3
WHEN cd.Saturday = 1 THEN 2
WHEN cd.Sunday = 1 THEN 1
END AS ContractDetailFirstDay,
You may not have any say in this, but one thing that can be done is you can create a persisted calculated column in the contract details table. The calculation would be based on this logic and store one of the values of 1-7. That way you could just pull the value without any on the fly calculations.
----------------------------------------------------
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply