T-Sql update statement - Best way to write.

Brock
Brock used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russ SuterSenior Software Developer

Commented:
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.
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
In my view it is easier to "unpivot" the one-column-per-day table first, then calculate the wanted string, then update. The following uses "for xml path" to concatenate the string with delimiter.
with unpiv as (
    select
        id, ca.day, ca.val, ca.dnum
    from NCSR1001A
    cross apply (
        values
           ('Mon',mon,1), ('Tue',tue,2), ('Wed',wed,3), ('Thu',thu,4), ('Fri',fri,5), ('Sat',sat,6), ('Sun',sun,7)
        ) ca (day,val,dnum)
    where ca.val = 'Y'
    )
, wanted as (
    select distinct
        id, case when ca.MtgPat = 'Mon\Tue\Wed\Thu\Fri\Sat\Sun' then 'M\T\W\T\F\S\S' else ca.MtgPat end as NewMtgPat
    from unpiv
    cross apply (
         select
            stuff((
                  select
                        '\' + p.day
                  from unpiv AS p
                  where p.id = unpiv.id
                  order by p.dnum
                  FOR XML PATH ('')
                  )
                 , 1, 1, '')
             ) ca (MtgPat)
    )
update NCSR1001A
set  NCSR1001A.MtgPat = wanted.NewMtgPat
from NCSR1001A
inner join wanted ON NCSR1001A.id = wanted.id
;

Open in new window

select
      *
from NCSR1001A


       id   mon   tue   wed   thu   fri   sat   sun           MtgPat           
 ---- ---- ----- ----- ----- ----- ----- ----- ----- ------------------------- 
   1    1   Y     Y     Y     Y     Y     Y     Y     M\T\W\T\F\S\S            
   2    2   N     N     Y     N     N     Y     Y     Wed\Sat\Sun              
   3    3   Y     N     Y     N     N     N     Y     Mon\Wed\Sun              
   4    4   N     Y     Y     N     N     N     N     Tue\Wed                  
   5    5   Y     N     N     N     Y     Y     Y     Mon\Fri\Sat\Sun          
   6    6   Y     Y     N     N     N     N     Y     Mon\Tue\Sun              
   7    7   N     Y     N     Y     Y     Y     N     Tue\Thu\Fri\Sat          
   8    8   Y     Y     Y     Y     Y     Y     Y     M\T\W\T\F\S\S            
   9    9   Y     Y     N     Y     N     N     Y     Mon\Tue\Thu\Sun          
  10   10   N     N     Y     N     N     N     N     Wed                      
  11   11   Y     Y     Y     N     N     N     N     Mon\Tue\Wed              
  12   12   Y     N     N     N     N     Y     N     Mon\Sat                  
  13   13   N     N     Y     Y     Y     Y     N     Wed\Thu\Fri\Sat          
  14   14   N     N     Y     N     N     Y     N     Wed\Sat                  
  15   15   N     Y     N     N     N     N     Y     Tue\Sun                  
  16   16   N     Y     N     N     Y     Y     N     Tue\Fri\Sat              
  17   17   Y     Y     N     Y     N     N     N     Mon\Tue\Thu              
  18   18   N     Y     N     N     N     N     Y     Tue\Sun                  
  19   19   N     Y     N     Y     Y     Y     Y     Tue\Thu\Fri\Sat\Sun      
  20   20   N     Y     N     N     N     N     Y     Tue\Sun                  
  21   21   Y     N     N     Y     N     N     Y     Mon\Thu\Sun              
  22   22   Y     N     Y     Y     Y     N     Y     Mon\Wed\Thu\Fri\Sun      
  23   23   N     Y     Y     Y     Y     Y     Y     Tue\Wed\Thu\Fri\Sat\Sun  
  24   24   Y     Y     N     N     Y     N     Y     Mon\Tue\Fri\Sun          
  25   25   Y     Y     N     Y     Y     N     N     Mon\Tue\Thu\Fri          

;

Open in new window

IF you have access to SQL Server 2017 you can use STRING_AGG instead of the "for xml path" approach
with unpiv as (
    select
        id, ca.day, ca.val, ca.dnum
    from NCSR1001A
    cross apply (
        values
           ('Mon',mon,1), ('Tue',tue,2), ('Wed',wed,3), ('Thu',thu,4), ('Fri',fri,5), ('Sat',sat,6), ('Sun',sun,7)
        ) ca (day,val,dnum)
    where ca.val = 'Y'
    )
, wanted as (
    select
        id, string_agg(day,'\') as NewMtgPat
    from unpiv
    group by id
    ) 
update NCSR1001A
set  NCSR1001A.MtgPat = case when wanted.NewMtgPat = 'Mon\Tue\Wed\Thu\Fri\Sat\Sun' then 'M\T\W\T\F\S\S' else NewMtgPat end
from NCSR1001A
inner join wanted ON NCSR1001A.id = wanted.id

Open in new window


also see: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=79a80c256b30aff6b4909199c062cdbc
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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].
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
Just a side note, CONCAT_WS is available in SQL Server, though it is new in 2017
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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...
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:

Author

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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...

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial