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';
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.