Link to home
Start Free TrialLog in
Avatar of Brock
BrockFlag for Canada

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 :

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}

Open in new window

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.
Avatar of Russ Suter
Russ Suter
Ugh. This isn't pretty.

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.
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image
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:

---/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

Open in new window


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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brock
Brock
Flag of Canada image

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].
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image
Just a side note, CONCAT_WS is available in SQL Server, though it is new in 2017
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
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+NCSR1001A.TUES+NCSR1001A.WED+NCSR1001A.THURS+
              NCSR1001A.FRI+NCSR1001A.SAT+NCSR1001A.SUN
        END AS MtgPat
) AS alias2
WHERE ...your_query_conditions_here...
Avatar of Brock
Brock
Flag of Canada image

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.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
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+NCSR1001A.TUES+NCSR1001A.WED+NCSR1001A.THURS+
              NCSR1001A.FRI+NCSR1001A.SAT+NCSR1001A.SUN
        END AS MtgPat
) AS alias2
WHERE ...your_query_conditions_here...
Avatar of Brock
Brock
Flag of Canada image

ASKER

Thanks, Scott.  I will make note of your change.  Brock.