convert dax query to sql query

[shabanashaik88]

Bucket =

var _start = CALCULATE([min],ALLEXCEPT(MFC_FC_TREND_ANALYSIS_VIEW,MFC_FC_TREND_ANALYSIS_VIEW[MFCName]))

var _end = CALCULATE([max],ALLEXCEPT(MFC_FC_TREND_ANALYSIS_VIEW,MFC_FC_TREND_ANALYSIS_VIEW[MFCName]))

var min_bucket = CALCULATE([max]/20,ALLEXCEPT(MFC_FC_TREND_ANALYSIS_VIEW,MFC_FC_TREND_ANALYSIS_VIEW[MFCName]))

var a = min_bucket*2

var b = min_bucket*3

var c = min_bucket*4

var d=min_bucket*5

var e=min_bucket*6

var f=min_bucket*7

var g=min_bucket*8

var h=min_bucket*9

var i=min_bucket*10

var j=min_bucket*11

var k=min_bucket*12

var l=min_bucket*13

var m=min_bucket*14

var n=min_bucket*15

var o=min_bucket*16

var p=min_bucket*17

var q=min_bucket*18

var r=min_bucket*19

var result =  

IF(ISBLANK(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]),BLANK()

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>=_start &&MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=min_bucket,min_bucket

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>min_bucket && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=a,a

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>a && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=b,b

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>b && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=c,c

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>c && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=d,d

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>d && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=e,e

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>e && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=f,f

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>f && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=g,g

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>g && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=h,h

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>h && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=i,i

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>i && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=j,j

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>j && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=k,k

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>k && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=l,l

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>l && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=m,m

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>m && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=n,n

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>n && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=o,o

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>o && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=p,p

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>p && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=q,q

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>q && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=r,r

,IF(MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]>r && MFC_FC_TREND_ANALYSIS_VIEW[MachineHours]<=_end,_end

)))))))))))))))))))))

return

result

[VahidDM]

Hey @shabanashaik88 

Try this:

WITH MinMax AS (
    SELECT
        MFCName,
        MIN(MachineHours) AS MinHours,
        MAX(MachineHours) AS MaxHours
    FROM MFC_FC_TREND_ANALYSIS_VIEW
    GROUP BY MFCName
),
Buckets AS (
    SELECT
        MFCName,
        MinHours,
        MaxHours,
        (MaxHours / 20) AS MinBucket,
        (MaxHours / 20) * 2 AS a,
        (MaxHours / 20) * 3 AS b,
        (MaxHours / 20) * 4 AS c,
        (MaxHours / 20) * 5 AS d,
        (MaxHours / 20) * 6 AS e,
        (MaxHours / 20) * 7 AS f,
        (MaxHours / 20) * 8 AS g,
        (MaxHours / 20) * 9 AS h,
        (MaxHours / 20) * 10 AS i,
        (MaxHours / 20) * 11 AS j,
        (MaxHours / 20) * 12 AS k,
        (MaxHours / 20) * 13 AS l,
        (MaxHours / 20) * 14 AS m,
        (MaxHours / 20) * 15 AS n,
        (MaxHours / 20) * 16 AS o,
        (MaxHours / 20) * 17 AS p,
        (MaxHours / 20) * 18 AS q,
        (MaxHours / 20) * 19 AS r
    FROM MinMax
)
SELECT 
    v.MFCName,
    v.MachineHours,
    CASE 
        WHEN v.MachineHours IS NULL THEN NULL
        WHEN v.MachineHours BETWEEN b.MinHours AND b.MinBucket THEN b.MinBucket
        WHEN v.MachineHours > b.MinBucket AND v.MachineHours <= b.a THEN b.a
        WHEN v.MachineHours > b.a AND v.MachineHours <= b.b THEN b.b
        WHEN v.MachineHours > b.b AND v.MachineHours <= b.c THEN b.c
        WHEN v.MachineHours > b.c AND v.MachineHours <= b.d THEN b.d
        WHEN v.MachineHours > b.d AND v.MachineHours <= b.e THEN b.e
        WHEN v.MachineHours > b.e AND v.MachineHours <= b.f THEN b.f
        WHEN v.MachineHours > b.f AND v.MachineHours <= b.g THEN b.g
        WHEN v.MachineHours > b.g AND v.MachineHours <= b.h THEN b.h
        WHEN v.MachineHours > b.h AND v.MachineHours <= b.i THEN b.i
        WHEN v.MachineHours > b.i AND v.MachineHours <= b.j THEN b.j
        WHEN v.MachineHours > b.j AND v.MachineHours <= b.k THEN b.k
        WHEN v.MachineHours > b.k AND v.MachineHours <= b.l THEN b.l
        WHEN v.MachineHours > b.l AND v.MachineHours <= b.m THEN b.m
        WHEN v.MachineHours > b.m AND v.MachineHours <= b.n THEN b.n
        WHEN v.MachineHours > b.n AND v.MachineHours <= b.o THEN b.o
        WHEN v.MachineHours > b.o AND v.MachineHours <= b.p THEN b.p
        WHEN v.MachineHours > b.p AND v.MachineHours <= b.q THEN b.q
        WHEN v.MachineHours > b.q AND v.MachineHours <= b.r THEN b.r
        ELSE b.MaxHours
    END AS Bucket
FROM 
    MFC_FC_TREND_ANALYSIS_VIEW v
JOIN 
    Buckets b ON v.MFCName = b.MFCName

Keep in mind that this example assumes a simplified structure of your database, and the exact query might vary depending on your specific database schema and SQL dialect.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Fowmy]

@shabanashaik88 

Can you try this pattern?

WITH BucketCTE AS (
    SELECT
        MFCName,
        MachineHours,
        [min] = MIN(MachineHours) OVER (PARTITION BY MFCName),
        [max] = MAX(MachineHours) OVER (PARTITION BY MFCName),
        min_bucket = MAX(MachineHours) OVER (PARTITION BY MFCName) / 20
    FROM
        MFC_FC_TREND_ANALYSIS_VIEW
)
SELECT
    MFCName,
    MachineHours,
    CASE
        WHEN MachineHours IS NULL THEN NULL
        WHEN MachineHours >= [min] AND MachineHours <= min_bucket THEN min_bucket
        WHEN MachineHours > min_bucket AND MachineHours <= min_bucket * 2 THEN min_bucket * 2
        WHEN MachineHours > min_bucket * 2 AND MachineHours <= min_bucket * 3 THEN min_bucket * 3
        WHEN MachineHours > min_bucket * 3 AND MachineHours <= min_bucket * 4 THEN min_bucket * 4
        WHEN MachineHours > min_bucket * 4 AND MachineHours <= min_bucket * 5 THEN min_bucket * 5
        WHEN MachineHours > min_bucket * 5 AND MachineHours <= min_bucket * 6 THEN min_bucket * 6
        WHEN MachineHours > min_bucket * 6 AND MachineHours <= min_bucket * 7 THEN min_bucket * 7
        WHEN MachineHours > min_bucket * 7 AND MachineHours <= min_bucket * 8 THEN min_bucket * 8
        WHEN MachineHours > min_bucket * 8 AND MachineHours <= min_bucket * 9 THEN min_bucket * 9
        WHEN MachineHours > min_bucket * 9 AND MachineHours <= min_bucket * 10 THEN min_bucket * 10
        WHEN MachineHours > min_bucket * 10 AND MachineHours <= min_bucket * 11 THEN min_bucket * 11
        WHEN MachineHours > min_bucket * 11 AND MachineHours <= min_bucket * 12 THEN min_bucket * 12
        WHEN MachineHours > min_bucket * 12 AND MachineHours <= min_bucket * 13 THEN min_bucket * 13
        WHEN MachineHours > min_bucket * 13 AND MachineHours <= min_bucket * 14 THEN min_bucket * 14
        WHEN MachineHours > min_bucket * 14 AND MachineHours <= min_bucket * 15 THEN min_bucket * 15
        WHEN MachineHours > min_bucket * 15 AND MachineHours <= min_bucket * 16 THEN min_bucket * 16
        WHEN MachineHours > min_bucket * 16 AND MachineHours <= min_bucket * 17 THEN min_bucket * 17
        WHEN MachineHours > min_bucket * 17 AND MachineHours <= min_bucket * 18 THEN min_bucket * 18
        WHEN MachineHours > min_bucket * 18 AND MachineHours <= min_bucket * 19 THEN min_bucket * 19
        WHEN MachineHours > min_bucket * 19 AND MachineHours <= [max] THEN [max]

        -- Add more WHEN clauses if needed for additional buckets

        ELSE [max]
    END AS Bucket
FROM
    BucketCTE;