sqlsql-server

query multiple IIF statement


How make result kondisi if query like this
Query result need like this, but condition using IIF

no+Induk Jam_In Kondisi
xx123 08:00 37500
xx123 08:00 37500
xx123 08:00 37500
xx123 00:00 0
xx123 08:00 37500
xx123 08:00 37500
xx123 08:00 37500
xx123 08:00 37500
xx123 08:00 37500
xx123 08:00 37500
xx123 08:00 37500
xx123 08:00 37500
xx123 00:00 0
xx123 08:00 37500
xx123 07:00 0
xx123 07:06 22500
xx123 07:10 22500
xx123 07:15 22500
xx123 08:00 37500
xx123 08:00 37500
xx123 08:00 37500
xx123 00:00 0
xx123 08:00 37500
xx123 08:00 37500
xx123 00:00 0
xx123 08:00 37500
xx123 08:00 37500
 DECLARE @kondisi INT;
 
 SELECT 
     @kondisi = IIF(jam_in >= '07:06' AND jam_in < '07:20', (75000 * 30 / 100),
         IIF(jam_in >= '07:20' AND jam_in < '08:56', (75000 * 50 / 100),
             IIF(jam_in >= '15:06' AND jam_in < '15:20', (75000 * 30 / 100),
                 IIF(jam_in >= '15:21' AND jam_in < '15:31', (75000 * 30 / 100), 0))))
 FROM tblabsen
 WHERE no_induk = 'xx123';
 
 -- Debugging output
 SELECT no_induk, jam_in, @kondisi AS kondisi
 FROM tblabsen
 WHERE no_induk = 'xx123';

Solution

  • I would definitely NOT use anything like IIF() for this.

    There are a number of different things you could do instead that would be better, but what I've been doing lately is defining your jam_in => formula mapping as data I can JOIN to in a table-value constructor. This sets me up to convert this to a real table later on that could even be indexed and perhaps doesn't require programming ability to maintain.

    SELECT no_induk, jam_in, coalesce((75000 * map.Multiplier) / 100, 0) as kondisi 
    FROM tblabsen a
    LEFT JOIN (VALUES 
            ('07:06', '07:20', 30),
            ('07:20', '08:56', 50),
            ('15:06', '15:20', 30),
            ('15:21', '15:31', 30)
        ) map(MinTime, MaxTax, Multiplier) ON a.jam_in >= map.MinTime AND a.jam_in < map.MaxTime
    WHERE no_induk = 'xx123';
    

    If you really want an IIF() -like solution, for SQL Server you need to write it as a CASE expression. The nice thing about the CASE expression is you don't have to nest it so many levels deep. Each of those options can have it's own WHEN at the top level:

    @kondisi = CASE WHEN jam_in >= '07:06' AND jam_in < '07:20' THEN (75000 * 30 / 100)
                    WHEN jam_in >= '07:20' AND jam_in < '08:56' THEN (75000 * 50 / 100)
                    WHEN jam_in >= '15:06' AND jam_in < '15:20' THEN (75000 * 30 / 100)
                    WHEN jam_in >= '15:21' AND jam_in < '15:31' THEN (75000 * 30 / 100)
                    ELSE 0 END
    

    Or we can use a little math and reduce it:

    @kondisi = CASE WHEN jam_in >= '07:06' AND jam_in < '07:20' THEN 30
                    WHEN jam_in >= '07:20' AND jam_in < '08:56' THEN 50
                    WHEN jam_in >= '15:06' AND jam_in < '15:31' THEN 30
                    ELSE 0 END * 750
    

    We can see this is waaaay easier to understand and maintain than nested IIF() operations.


    I'm further concerned because it appears you have a number of things defined as string types that should really be numeric or time-based types. This can dramatically hurt performance by limiting index usefulness and cause errors because you end up doing string comparisons instead of the expected numeric or date comparisons. Data types matter, especially in SQL.


    Finally, it's odd you want to put this into a single INT variable, when you have many rows. That won't work. Just run the SELECT query. If this is just one step in a larger process, use this query a CTE you can JOIN to or replace the original table in the next step.