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.
BrockAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
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.
0
Dustin SaundersDirector of OperationsCommented:
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

0
PortletPaulEE Topic AdvisorCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

BrockAuthor 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].
0
Dustin SaundersDirector of OperationsCommented:
Just a side note, CONCAT_WS is available in SQL Server, though it is new in 2017
0
Scott PletcherSenior DBACommented:
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...
0
BrockAuthor 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.
0
Scott PletcherSenior DBACommented:
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...
0
BrockAuthor Commented:
Thanks, Scott.  I will make note of your change.  Brock.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.