Brock
asked on
T-Sql update statement - Best way to write.
Hi,
I want to create a field called MtgPat
It would be updated with the following :
This was formerly a crystal field formula. I want to put it in a batch file using t-sql.
Thanks Brock.
PS. -- Will be available tomorrow for comments.
I want to create a field called MtgPat
It would be updated with the following :
If {NCSR1001A.MON}='Y' AND {NCSR1001A.TUES}='Y' AND {NCSR1001A.WED}='Y' AND {NCSR1001A.THURS}='Y' AND {NCSR1001A.FRI}='Y' AND {NCSR1001A.SAT}='Y'
AND {NCSR1001A.SUN}='Y' then 'M/T/W/T/F/S/S'
else if {NCSR1001A.MON}='Y' AND {NCSR1001A.TUES}='Y' AND {NCSR1001A.WED}='Y' AND {NCSR1001A.THURS}='Y' AND
{NCSR1001A.FRI}='Y' AND {NCSR1001A.SAT}='N' AND {NCSR1001A.SUN}='N' then 'M/T/W/T/F'
else If {NCSR1001A.MON}='Y' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='Y' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' then 'Mon/Wed'
else if {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='Y' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='Y' AND {NCSR1001A.FRI}='N' then 'Tue/Thu'
else if {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='Y' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='Y' then 'Wed/Fri'
else if {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' AND
{NCSR1001A.SAT}='Y' AND {NCSR1001A.SUN}='Y' then 'Sat/Sun'
else if {NCSR1001A.MON}='Y' AND {NCSR1001A.TUES}='Y' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' then 'Mon/Tue'
else if {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='Y' AND {NCSR1001A.WED}='Y' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' then 'Tue/Wed'
else if {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='Y' AND {NCSR1001A.THURS}='Y' AND {NCSR1001A.FRI}='N' then 'Wed/Thu'
else if {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='Y' AND {NCSR1001A.FRI}='Y' then 'Thu/Fri'
else if {NCSR1001A.MON}='Y' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' then 'Mon'
else If {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='Y' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' then 'Tue'
else If {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='Y' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' then 'Wed'
else If {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='Y' AND {NCSR1001A.FRI}='N' then 'Thu'
else If {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='Y' then 'Fri'
else If {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' AND
{NCSR1001A.SAT}='Y' AND {NCSR1001A.SUN}='N' then 'Sat'
else If {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND {NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' AND
{NCSR1001A.SAT}='N' AND {NCSR1001A.SUN}='Y' then 'Sun'
else If {NCSR1001A.ACAD_GROUP}='CE' AND {NCSR1001A.CLASS_STAT} = 'X' AND {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND
{NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' AND {NCSR1001A.SAT} ='N' AND {NCSR1001A.SUN}='N' then 'n/a '
else If {NCSR1001A.ACAD_GROUP}='CE' AND {NCSR1001A.TIME_TO}='1900-01-01 01:00:00.000' AND {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND
{NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' AND {NCSR1001A.SAT} ='N' AND {NCSR1001A.SUN}='N' then 'OLC'
else If {NCSR1001A.ACAD_GROUP}='ON' AND {NCSR1001A.MON}='N' AND {NCSR1001A.TUES}='N' AND {NCSR1001A.WED}='N' AND
{NCSR1001A.THURS}='N' AND {NCSR1001A.FRI}='N' AND {NCSR1001A.SAT} ='N' AND {NCSR1001A.SUN}='N' then 'On-line'
else 'Irreg.'+{NCSR1001A.MON}+{NCSR1001A.TUES}+{NCSR1001A.WED}+{NCSR1001A.THURS}+{NCSR1001A.FRI}+{NCSR1001A.SAT}+{NCSR1001A.SUN}
What is the best standards for creating a sql update statement. This was formerly a crystal field formula. I want to put it in a batch file using t-sql.
Thanks Brock.
PS. -- Will be available tomorrow for comments.
Yes, you can do it neatly in a case statement. I don't have much time atm for a big example, but to get the idea:
With this being the actual select:
---/SAMPLE DATA
DECLARE @table TABLE (ID INT IDENTITY(1,1) NOT NULL,MON varchar(50), TUE varchar(50), WED varchar(50), THU varchar(50), FRI varchar(50), SAT varchar(50), SUN varchar(50))
DECLARE @x INT, @y INT
SET @x = 1
SET @y = 100
WHILE @x <= @y
BEGIN
INSERT INTO @table(MON,TUE,WED,THU,FRI,SAT,SUN)
VALUES (
(SELECT CASE WHEN CHECKSUM(NewId())%2 = 0 THEN 'N' ELSE 'Y' END),
(SELECT CASE WHEN CHECKSUM(NewId())%2 = 0 THEN 'N' ELSE 'Y' END),
(SELECT CASE WHEN CHECKSUM(NewId())%2 = 0 THEN 'N' ELSE 'Y' END),
(SELECT CASE WHEN CHECKSUM(NewId())%2 = 0 THEN 'N' ELSE 'Y' END),
(SELECT CASE WHEN CHECKSUM(NewId())%2 = 0 THEN 'N' ELSE 'Y' END),
(SELECT CASE WHEN CHECKSUM(NewId())%2 = 0 THEN 'N' ELSE 'Y' END),
(SELECT CASE WHEN CHECKSUM(NewId())%2 = 0 THEN 'N' ELSE 'Y' END)
)
SET @x = @x + 1
END
---./SAMPLE DATA
SELECT CONCAT_WS('/',
CASE WHEN MON='Y' THEN 'M' ELSE NULL END,
CASE WHEN TUE='Y' THEN 'T' ELSE NULL END,
CASE WHEN WED='Y' THEN 'W' ELSE NULL END,
CASE WHEN THU='Y' THEN 'T' ELSE NULL END,
CASE WHEN FRI='Y' THEN 'F' ELSE NULL END,
CASE WHEN SAT='Y' THEN 'S' ELSE NULL END,
CASE WHEN SUN='Y' THEN 'S' ELSE NULL END)
FROM @table
With this being the actual select:
SELECT CONCAT_WS('/',
CASE WHEN MON='Y' THEN 'M' ELSE NULL END,
CASE WHEN TUE='Y' THEN 'T' ELSE NULL END,
CASE WHEN WED='Y' THEN 'W' ELSE NULL END,
CASE WHEN THU='Y' THEN 'T' ELSE NULL END,
CASE WHEN FRI='Y' THEN 'F' ELSE NULL END,
CASE WHEN SAT='Y' THEN 'S' ELSE NULL END,
CASE WHEN SUN='Y' THEN 'S' ELSE NULL END)
FROM @table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, everyone. I am back at my desk and will try all your solutions.
Brock.
P.S. - Paul - I will try and be more diligent when asking my question. [also- I will not do it at the end of a long day].
Brock.
P.S. - Paul - I will try and be more diligent when asking my question. [also- I will not do it at the end of a long day].
Just a side note, CONCAT_WS is available in SQL Server, though it is new in 2017
You don't need complex string aggregation here, so I'd avoid the complexity and overhead of it.
I think the code below exactly matches your original logic, but naturally tweak it if/as needed.
SELECT MtgPat, *
FROM NCSR1001A
CROSS APPLY (
SELECT ISNULL(STUFF(
CASE WHEN NCSR1001A.MON = 'Y' THEN '/M' ELSE '' END +
CASE WHEN NCSR1001A.TUES = 'Y' THEN '/T' ELSE '' END +
CASE WHEN NCSR1001A.WED = 'Y' THEN '/W' ELSE '' END +
CASE WHEN NCSR1001A.THURS = 'Y' THEN '/T' ELSE '' END +
CASE WHEN NCSR1001A.FRI = 'Y' THEN '/F' ELSE '' END +
CASE WHEN NCSR1001A.SAT = 'Y' THEN '/S' ELSE '' END +
CASE WHEN NCSR1001A.SUN = 'Y' THEN '/S' ELSE '' END, 1, 1, ''), '') AS days_short,
ISNULL(STUFF(
CASE WHEN NCSR1001A.MON = 'Y' THEN '/Mon' ELSE '' END +
CASE WHEN NCSR1001A.TUES = 'Y' THEN '/Tue' ELSE '' END +
CASE WHEN NCSR1001A.WED = 'Y' THEN '/Wed' ELSE '' END +
CASE WHEN NCSR1001A.THURS = 'Y' THEN '/Thu' ELSE '' END +
CASE WHEN NCSR1001A.FRI = 'Y' THEN '/Fri' ELSE '' END +
CASE WHEN NCSR1001A.SAT = 'Y' THEN '/Sat' ELSE '' END +
CASE WHEN NCSR1001A.SUN = 'Y' THEN '/Sun' ELSE '' END, 1, 1, ''), '') AS days_long
) AS alias1
CROSS APPLY (
SELECT CASE
WHEN days_short IN ('M/T/W/T/F', 'M/T/W/T/F/S/S') THEN days_short /*weekdays|all days*/
WHEN LEN(days_long) = 3 THEN days_long /*one day*/
WHEN days_long IN ('Mon/Wed', 'Mon/Tue', 'Tue/Wed', 'Tue/Thu', /*custom two-days*/
'Wed/Thu', 'Wed/Fri', 'Thu/Fri', 'Sat/Sun') THEN days_long
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='CE' AND /*no day*/
NCSR1001A.CLASS_STAT='X' THEN 'n/a '
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='CE' AND /*no day*/
NCSR1001A.TIME_TO='1900-01 -01 01:00:00.000' THEN 'OLC'
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='ON' THEN 'On-line' /*no day*/
ELSE 'Irreg.'+NCSR1001A.MON+NCS R1001A.TUE S+NCSR1001 A.WED+NCSR 1001A.THUR S+
NCSR1001A.FRI+NCSR1001A.SA T+NCSR1001 A.SUN
END AS MtgPat
) AS alias2
WHERE ...your_query_conditions_h ere...
I think the code below exactly matches your original logic, but naturally tweak it if/as needed.
SELECT MtgPat, *
FROM NCSR1001A
CROSS APPLY (
SELECT ISNULL(STUFF(
CASE WHEN NCSR1001A.MON = 'Y' THEN '/M' ELSE '' END +
CASE WHEN NCSR1001A.TUES = 'Y' THEN '/T' ELSE '' END +
CASE WHEN NCSR1001A.WED = 'Y' THEN '/W' ELSE '' END +
CASE WHEN NCSR1001A.THURS = 'Y' THEN '/T' ELSE '' END +
CASE WHEN NCSR1001A.FRI = 'Y' THEN '/F' ELSE '' END +
CASE WHEN NCSR1001A.SAT = 'Y' THEN '/S' ELSE '' END +
CASE WHEN NCSR1001A.SUN = 'Y' THEN '/S' ELSE '' END, 1, 1, ''), '') AS days_short,
ISNULL(STUFF(
CASE WHEN NCSR1001A.MON = 'Y' THEN '/Mon' ELSE '' END +
CASE WHEN NCSR1001A.TUES = 'Y' THEN '/Tue' ELSE '' END +
CASE WHEN NCSR1001A.WED = 'Y' THEN '/Wed' ELSE '' END +
CASE WHEN NCSR1001A.THURS = 'Y' THEN '/Thu' ELSE '' END +
CASE WHEN NCSR1001A.FRI = 'Y' THEN '/Fri' ELSE '' END +
CASE WHEN NCSR1001A.SAT = 'Y' THEN '/Sat' ELSE '' END +
CASE WHEN NCSR1001A.SUN = 'Y' THEN '/Sun' ELSE '' END, 1, 1, ''), '') AS days_long
) AS alias1
CROSS APPLY (
SELECT CASE
WHEN days_short IN ('M/T/W/T/F', 'M/T/W/T/F/S/S') THEN days_short /*weekdays|all days*/
WHEN LEN(days_long) = 3 THEN days_long /*one day*/
WHEN days_long IN ('Mon/Wed', 'Mon/Tue', 'Tue/Wed', 'Tue/Thu', /*custom two-days*/
'Wed/Thu', 'Wed/Fri', 'Thu/Fri', 'Sat/Sun') THEN days_long
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='CE' AND /*no day*/
NCSR1001A.CLASS_STAT='X' THEN 'n/a '
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='CE' AND /*no day*/
NCSR1001A.TIME_TO='1900-01
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='ON' THEN 'On-line' /*no day*/
ELSE 'Irreg.'+NCSR1001A.MON+NCS
NCSR1001A.FRI+NCSR1001A.SA
END AS MtgPat
) AS alias2
WHERE ...your_query_conditions_h
ASKER
Thank you to all - I tested the solution on my end and it is working. This will go in a peoplesoft batch program or application engine and then I will used the generated xml file to create the rtf template using a bi publisher plugin in. The xml report will then substitute for the crystal report.
Sorry - I took awhile.
Brock.
p.s - I will use the other solution for other Crystal formula's I need to bring over on the sql server side.
Sorry - I took awhile.
Brock.
p.s - I will use the other solution for other Crystal formula's I need to bring over on the sql server side.
I did need to make minor corrections to my code, in bold below, to make sure entries with no days are handled correctly:
SELECT MtgPat, *
FROM NCSR1001A
CROSS APPLY (
SELECT ISNULL(STUFF(
CASE WHEN NCSR1001A.MON = 'Y' THEN '/M' ELSE '' END +
CASE WHEN NCSR1001A.TUES = 'Y' THEN '/T' ELSE '' END +
CASE WHEN NCSR1001A.WED = 'Y' THEN '/W' ELSE '' END +
CASE WHEN NCSR1001A.THURS = 'Y' THEN '/T' ELSE '' END +
CASE WHEN NCSR1001A.FRI = 'Y' THEN '/F' ELSE '' END +
CASE WHEN NCSR1001A.SAT = 'Y' THEN '/S' ELSE '' END +
CASE WHEN NCSR1001A.SUN = 'Y' THEN '/S' ELSE '' END, 1, 1, ''), '') AS days_short,
ISNULL(STUFF(
CASE WHEN NCSR1001A.MON = 'Y' THEN '/Mon' ELSE '' END +
CASE WHEN NCSR1001A.TUES = 'Y' THEN '/Tue' ELSE '' END +
CASE WHEN NCSR1001A.WED = 'Y' THEN '/Wed' ELSE '' END +
CASE WHEN NCSR1001A.THURS = 'Y' THEN '/Thu' ELSE '' END +
CASE WHEN NCSR1001A.FRI = 'Y' THEN '/Fri' ELSE '' END +
CASE WHEN NCSR1001A.SAT = 'Y' THEN '/Sat' ELSE '' END +
CASE WHEN NCSR1001A.SUN = 'Y' THEN '/Sun' ELSE '' END, 1, 1, ''), '') AS days_long
) AS alias1
CROSS APPLY (
SELECT CASE
WHEN days_short IN ('M/T/W/T/F', 'M/T/W/T/F/S/S') THEN days_short /*weekdays|all days*/
WHEN LEN(days_long) = 3 THEN days_long /*one day*/
WHEN days_long IN ('Mon/Wed', 'Mon/Tue', 'Tue/Wed', 'Tue/Thu', /*custom two-days*/
'Wed/Thu', 'Wed/Fri', 'Thu/Fri', 'Sat/Sun') THEN days_long
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='CE' AND /*no day*/
NCSR1001A.CLASS_STAT='X' THEN 'n/a '
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='CE' AND /*no day*/
NCSR1001A.TIME_TO='1900-01 -01 01:00:00.000' THEN 'OLC'
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='ON' THEN 'On-line' /*no day*/
ELSE 'Irreg.'+NCSR1001A.MON+NCS R1001A.TUE S+NCSR1001 A.WED+NCSR 1001A.THUR S+
NCSR1001A.FRI+NCSR1001A.SA T+NCSR1001 A.SUN
END AS MtgPat
) AS alias2
WHERE ...your_query_conditions_h ere...
SELECT MtgPat, *
FROM NCSR1001A
CROSS APPLY (
SELECT ISNULL(STUFF(
CASE WHEN NCSR1001A.MON = 'Y' THEN '/M' ELSE '' END +
CASE WHEN NCSR1001A.TUES = 'Y' THEN '/T' ELSE '' END +
CASE WHEN NCSR1001A.WED = 'Y' THEN '/W' ELSE '' END +
CASE WHEN NCSR1001A.THURS = 'Y' THEN '/T' ELSE '' END +
CASE WHEN NCSR1001A.FRI = 'Y' THEN '/F' ELSE '' END +
CASE WHEN NCSR1001A.SAT = 'Y' THEN '/S' ELSE '' END +
CASE WHEN NCSR1001A.SUN = 'Y' THEN '/S' ELSE '' END, 1, 1, ''), '') AS days_short,
ISNULL(STUFF(
CASE WHEN NCSR1001A.MON = 'Y' THEN '/Mon' ELSE '' END +
CASE WHEN NCSR1001A.TUES = 'Y' THEN '/Tue' ELSE '' END +
CASE WHEN NCSR1001A.WED = 'Y' THEN '/Wed' ELSE '' END +
CASE WHEN NCSR1001A.THURS = 'Y' THEN '/Thu' ELSE '' END +
CASE WHEN NCSR1001A.FRI = 'Y' THEN '/Fri' ELSE '' END +
CASE WHEN NCSR1001A.SAT = 'Y' THEN '/Sat' ELSE '' END +
CASE WHEN NCSR1001A.SUN = 'Y' THEN '/Sun' ELSE '' END, 1, 1, ''), '') AS days_long
) AS alias1
CROSS APPLY (
SELECT CASE
WHEN days_short IN ('M/T/W/T/F', 'M/T/W/T/F/S/S') THEN days_short /*weekdays|all days*/
WHEN LEN(days_long) = 3 THEN days_long /*one day*/
WHEN days_long IN ('Mon/Wed', 'Mon/Tue', 'Tue/Wed', 'Tue/Thu', /*custom two-days*/
'Wed/Thu', 'Wed/Fri', 'Thu/Fri', 'Sat/Sun') THEN days_long
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='CE' AND /*no day*/
NCSR1001A.CLASS_STAT='X' THEN 'n/a '
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='CE' AND /*no day*/
NCSR1001A.TIME_TO='1900-01
WHEN days_short = '' AND NCSR1001A.ACAD_GROUP='ON' THEN 'On-line' /*no day*/
ELSE 'Irreg.'+NCSR1001A.MON+NCS
NCSR1001A.FRI+NCSR1001A.SA
END AS MtgPat
) AS alias2
WHERE ...your_query_conditions_h
ASKER
Thanks, Scott. I will make note of your change. Brock.
Without some significant reworking, the best way out I can see is to make your column [MgtPat] a VARCHAR long enough to hold the maximum possible string length you would need. Then use an inline CASE statement in your update query to switch between the appropriate values.
I think a better way would be to tokenize your values and use some kind of lookup or enumeration to get the values back at the business logic layer but that's far beyond the scope of your question.