CASE WHEN Formula in SQL

CASE WHEN Formula in SQL

By : pammirato
Date : November 21 2020, 07:01 PM
hope this fix your issue What is the datatype of your QtyOrdered column? If it's INT - then you're performing an integer division (QtyOrdered / 4) and if the QtyOrdered value is 1, 2, or 3, this would result in 0.
If you want your results to be with fractional values, you need to use a fractional dividend - like this:
code :
   WHEN dbo.WorkOrderDetails.ItemNo = 'Web' 
      THEN ROUND(dbo.WorkOrderDetails.QtyOrdered / 4.0, 0)  
      ELSE dbo.WorkOrderDetails.QtyOrdered         ^^^^
END AS NumberofItems

Share : facebook icon twitter icon
z3 times out in case of a formula with quantifiers

z3 times out in case of a formula with quantifiers

By : Kevin Smith
Date : March 29 2020, 07:55 AM
will be helpful for those in need For this example, the macro finder will be useful (I think often with forall quantifiers with implications), you can enable it with:
code :
(set-option :macro-finder true)
(set-option :macro-finder true)

(declare-const a (Array Int Bool))
(declare-const sz Int)
(declare-const n Int)
(declare-const d Int)
(declare-const r Bool)
(declare-const x Int)
(declare-const y Int)

(declare-fun ttff (Int Int Int) Bool)
  (forall ((x1 Int) (y1 Int) (n1 Int))
  (= (ttff x1 y1 n1)
  (forall ((i Int))
  (=> (and (<= x1 i) (< i y1))
  (= (select a i) true)))
  (forall ((i Int))
  (=> (and (<= y1 i) (< i n1))
  (= (select a i) false)))))))

;; A1
  (assert (and (<= 0 n) (<= n sz)))

;; A2
  (assert (< 0 d))

;; A3
  (assert (and (and (<= 0 x) (<= x y)) (<= y n)))

;; A4
  (assert (ttff x y n))

;; A6
  (=> (< 0 y)
  (= (select a (- y 1)) true)))

;; A7
  (=> (< 0 x)
  (= (select a (- x 1)) false)))

  (and (<= (* 2 d) (+ n 1)) (ttff (- (+ n 1) (* 2 d)) (- (+ n 1) d) (+ n 1)))
  (and (= (- (+ n 1) y) d) (<= d (- y x))))))
vba case doesn't insert formula

vba case doesn't insert formula

By : Dan
Date : March 29 2020, 07:55 AM
I wish this helpful for you If you're using D as the criteria for AP, your Select Case should take rCell.Value as the reference point. See code below.
code :
Sub DTCNFScontract()

    Dim CognosSht As Worksheet
    Dim LastRow As Long
    Dim rCell As Range
    Dim Rng As Range

    Set CognosSht = Sheets("Cognos Data")

    With CognosSht

        LastRow = CognosSht.UsedRange.Rows.Count    
        Set Rng = .Range("D2:D" & LastRow)

        'Turn off calculations first so that no calculations are triggered while inserting formula.
        'That will add too much execution time.
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
        End With

        'Insert Hash Calculation Based On SO Type and VLOOKUP
        For Each rCell In Rng
            Select Case Trim(rCell.Value)
                Case "Z006"
                  .Range("AP" & rCell.Row).Formula = "=IF(AND(VLOOKUP('Cognos Data'!C[-21],'AP Hash Build Pivot'!C[-41]:C[-40],2,0)<3,VLOOKUP('Cognos Data'!C[-21],'AP Hash Build Pivot'!C[-41]:C[-39],3,0)<50),""Hash"","""")"
                Case "Z002"
                  .Range("AP" & rCell.Row).Formula = "=IF(AND(VLOOKUP('Cognos Data'!C[-21],'AP Hash Build Pivot'!C[-37]:C[-36],2,0)<3,VLOOKUP('Cognos Data'!C[-21],'AP Hash Build Pivot'!C[-37]:C[-35],3,0)<50),""Hash"","""")"
                Case "Z013"
                  .Range("AP" & rCell.Row).Formula = "=IF(AND(VLOOKUP('Cognos Data'!C[-21],'AP Hash Build Pivot'!C[-37]:C[-36],2,0)<3,VLOOKUP('Cognos Data'!C[-21],'AP Hash Build Pivot'!C[-37]:C[-35],3,0)<50),""Hash"","""")"
                Case Else
                  .Range("AP" & rCell.Row).Formula = """"
            End Select

        'Calculate everything after inserting formulas.
        'An alternative is to use `Evaluate` instead while inserting formulas
        'but that can be slower.
        With Application
            .Calculation = xlCalculationAutomatic       
            .ScreenUpdating = True
        End With        

        'Copy Calculations and Paste As Values
        With .Columns("AP:AP")
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End With

        Application.CutCopyMode = False

    End With

End Sub
Changing the case of formula output

Changing the case of formula output

By : Andrew Bihner
Date : March 29 2020, 07:55 AM
wish of those help I'm using a JSON call to insert the city for a zip but the results are in all caps. The zip is in C2. Can I use PROPER to initial cap the results of the formula at the same time as the call so I don't have to run the formula on a different column? I know this is probably beginner stuff but, I can't figure it out. , Have you tried:
code :
=PROPER(ImportJSON(CONCATENATE("http://ziptasticapi.com/",C2), "/city","noHeaders"))
same case value but run different formula if first character is

same case value but run different formula if first character is

By : guest02253
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I am making a renaming script and all is working unless I have a specific file name. Which is common in every project.
code :
Sub Convert()
Application.ScreenUpdating = False

Dim rng As Range, aCell As Range
Dim val As String, check
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:A" & LastRow)

For Each aCell In rng.Cells
Select Case Len(aCell)
    Case 12
        'I added a check here
        check = Left(aCell, 1)
        If check = "0" Or check = "c" Or check = "e" Then  'Example: 01730101.pdf = S-173-0101.pdf
        val = "S-" & Mid(aCell, 2, Len(aCell) - 9) & "-" & Mid(aCell, 5, Len(aCell) - 8)
        Else 'Example: 173d0071.pdf = S-173-D7.pdf
        val = "S-" & Left(aCell, Len(aCell) - 9) & "-" & Mid(aCell, 4, Len(aCell) - 8)
        End If
        check = ""
    Case 13 'Example: 173d00710.pdf = S-173-D7.pdf
        val = "S-" & Left(aCell, Len(aCell) - 10) & "-" & Mid(aCell, 4, Len(aCell) - 9)
    Case 15 'Example: 173d170c071.pdf = SD-170-C7.pdf
        val = "SD-" & Left(aCell, Len(aCell) - 15) & Mid(aCell, 5, Len(aCell) - 12) & "-" & Mid(aCell, 8, Len(aCell) - 12)
    Case 16 'Example: REF-173d0071.pdf = REF-173-D7.pdf
        val = Left(aCell, Len(aCell) - 9) & "-" & (Mid(aCell, 8, Len(aCell) - 12))
    Case 17 'Example: REF173d00710.pdf = REF-173-D7.pdf
        val = Left(aCell, Len(aCell) - 10) & "-" & (Mid(aCell, 8, Len(aCell) - 13))
On Error GoTo whoa
    Case Else
        val = "_Mod " & Left(aCell, Len(aCell) - 4)
End Select

val = UCase(val)

val = val & " " & aCell.Offset(, 2) & aCell.Offset(, 3)

aCell.Offset(, 1).Value = val
Call RemoveZero
Call RemoveBadChar
    Application.ScreenUpdating = True
MsgBox "Please delete any empty rows."
Application.ScreenUpdating = True
Exit Sub
CASE WHEN Formula - NetSuite

CASE WHEN Formula - NetSuite

By : Fujiambi
Date : March 29 2020, 07:55 AM
Does that help The {subsidiary} field is expecting the entire parent-child hierarchy for a match, so 'Honeycomb Holding Inc. : Honeycomb Mfg.'. You can use `{subsidiarynohierarchy} to match just the base subsidiary name (i.e, Honeycomb Mfg.) the transaction is in. This formula should work:
code :
    when {subsidiarynohierarchy} = 'Honeycomb Mfg.' then {field1}  
    when {subsidiarynohierarchy} = 'test sub' then {field2}  
coalesce({field1}, {field2})
Related Posts Related Posts :
  • Error converting data type varchar to numeric
  • How to update two tables in a single query in MS SQL
  • About sql server rules
  • ALTER TABLE my_table ADD @column INT
  • sql server deadlock issue on a single table
  • Any tool to convert SQLite database to sql server?
  • SQL Server Windows Authentication
  • Msg 209, Level 16, State 1, Line 4 Ambiguous column name 'DonorID'. Msg 209, Level 16, State 1, Line 1 Ambiguous column
  • Union and If Exists - not working together - Please help
  • SQL Server Index cost
  • T-SQL Issue using lead or lag
  • How can I connect to SQL Server using (local)\INSTANCE_NAME?
  • How do I filter SQL Server traffic between app and DB servers using Wireshark?
  • How to protect my SQL Server database from unauthorized access, modification and distribution?
  • SQL Server 2008 Dead lock issue
  • writing an Dynamic query in sqlserver
  • How to lock a table for inserting in sql?
  • Disable TSQL script check?
  • is there a way to update multiple row using one update query?
  • Building Stored Procedure to group data into ranges with roughly equal results in each bucket
  • Update table with index is too slow
  • Atomic UPSERT in SQL Server 2005
  • How to run a ssis package using a sql statement?
  • SQL Server substring(substring())
  • Database schema last updated date
  • Fully automated SQL Server Restore
  • How to get identities of inserted data records using SQL bulk copy
  • How to determine whether a database has been changed or not?
  • What does # after a column name mean in SQL Server
  • Transaction Isolation Level of Serializable not working for me
  • SQL Server Management Studio Express 2005 has no Configuration Manager
  • Can't connect to SQL Server 2008 - looks like Shared Memory problem
  • SQL deadlock on delete then bulk insert
  • Join two tables in SQL Server
  • Rebuilding indexes does not change the fragmentation % for nonclustered indexes
  • Identifying connections and active SQL in SQL Server
  • How to pass a variable number of parameters to a SQL Server stored procedure?
  • How do I remove database name from SQL Server generated script?
  • Which method is best method for speed? in SQL Server, stored procedure
  • TSQL 'Invalid column name' error on value of sproc parameter
  • t-SQL grant permission for table drop and create
  • Stored procedure from VBA Excel not running
  • Stored procedure with dynamic SQL and ORDER BY
  • Using SQL Server for WSS 3.0 instead of Windows Internal database
  • Why better isolation level means better performance in SQL Server
  • How to we prevent SQL Injection by Regular Expression or any other way in asp.net
  • Removing characters from a alphanumeric field SQL
  • Using SSIS and T-SQL to convert date to dd.mm.yyyy
  • Is a SqlProfiler Scan Started bad?
  • Copy huge tables data from one database to another in SQL Server
  • Sample Employee SQL Server database with data
  • Programmatically creating linked tables in access
  • Is there meta data I can read from SQL Server to know the last changed row/table?
  • Combine two Column into One column with out union
  • Switch CASE in stored procedure in SQL
  • VS 2019 SSIS package can't add SQL Server
  • Partition or Index large table in SQL Server
  • How to remove only certain items within a column that has multiple words listed in cell
  • How to convert from DD/mm/yyyy in varchar(max) to yyyymmdd format in SQL Server
  • Need to calculate the sum of second using a function from a string variable using SQL
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk