Skip to content

Instantly share code, notes, and snippets.

@anandpdoshi
Last active Dec 11, 2015
Embed
What would you like to do?
select *,
(`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`) as tot1,
(case
when (`1D:Currency:50` + `2B:Currency:50`) < (`SO:Currency:50`) then "1 CORD"
when (`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`) < (`SO:Currency:50` + `ROL:Currency:50`) then "2 CSTK"
end) as something
from (
select
`tabItem`.name as "Item Code:Link/Item:150",
`tabItem`.description as "Description::300",
ifnull(`tabItem`.re_order_level,0) as "ROL:Currency:50",
ifnull(`tabBin`.reserved_qty,0) as "SO:Currency:50",
ifnull(`tabBin`.ordered_qty,0) as "PO:Currency:50",
ifnull(min(case when `tabBin`.warehouse="DEL20A" then `tabBin`.actual_qty end),0) as "1D:Currency:50",
ifnull(min(case when `tabBin`.warehouse="BGH655" then `tabBin`.actual_qty end),0) as "2B:Currency:50",
ifnull(min(case when `tabBin`.warehouse="RG-BGH655" then `tabBin`.actual_qty end),0) as "BRG:Currency:50",
ifnull(min(case when `tabBin`.warehouse="HT-BGH655" then `tabBin`.actual_qty end),0) as "BHT:Currency:50",
ifnull(min(case when `tabBin`.warehouse="FG-BGH655" then `tabBin`.actual_qty end),0) as "BFG:Currency:50",
ifnull(min(case when `tabBin`.warehouse="SLIT-DEL20A" then `tabBin`.actual_qty end),0) as "DSL:Currency:50",
ifnull(min(case when `tabBin`.warehouse="RG-DEL20A" then `tabBin`.actual_qty end),0) as "DRG:Currency:50",
ifnull(min(case when `tabBin`.warehouse="FG-DEL20A" then `tabBin`.actual_qty end),0) as "DFG:Currency:50",
ifnull(min(case when `tabBin`.warehouse="TEST-DEL20A" then `tabBin`.actual_qty end),0) as "DTS:Currency:50",
ifnull(min(case when `tabBin`.warehouse="REJ-DEL20A" then `tabBin`.actual_qty end),0) as "DRJ:Currency:50",
ifnull(min(case when `tabBin`.warehouse="RM-DEL20A" then `tabBin`.actual_qty end),0) as "DRM:Currency:50",
ifnull(min(case when `tabBin`.warehouse="RM-BGH655" then `tabBin`.actual_qty end),0) as "BRM:Currency:50"
from `tabItem` left join `tabBin` on (`tabItem`.name = `tabBin`.item_code)
where
`tabBin`.item_code != ""
and `tabBin`.item_code NOT REGEXP '^CN'
and `tabBin`.item_code NOT REGEXP '^J'
and `tabBin`.item_code = `tabItem`.name
group by `tabItem`.name
order by `tabItem`.name asc
) t
@adityaduggal
Copy link

Works great, thanks for the help.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment