as i've recently moved from being a dude that develops software for not much pay to someone that is a glorified sql statement writer that's paid way too fucking much i find myself writing a lot of god awful sql queries, of which i will post here for the lot of you to enjoy

feel free to post your own

CREATE VIEW "V_XW3_JOB_LAST_SEQ" AS
(SELECT "A" ."JOB" ,"A" ."SUFFIX" ,MAX ("A" ."SEQ" )"LAST_SEQ"
FROM "V_JOB_OPERATIONS_WC" "A"
INNER JOIN "V_JOB_HEADER" "B" ON "A" ."JOB" = "B" ."JOB" AND "A" ."SUFFIX" = "B" ."SUFFIX"
WHERE "A" ."FLAG_CLOSED" = 'Y' AND "B" ."DATE_CLOSED" = '1900-01-01' AND "A" ."LMO" = 'L'
AND "B" ."DATE_RELEASED" <> '1900-01-01' GROUP BY "A" ."JOB" ,"A" ."SUFFIX")
UNION
(SELECT "A" ."JOB" ,"A" ."SUFFIX" , '' AS "LAST_SEQ"
FROM "V_JOB_OPERATIONS_WC" "A"
INNER JOIN "V_JOB_HEADER" "B" ON "A" ."JOB" = "B" ."JOB" AND "A" ."SUFFIX" = "B" ."SUFFIX"
WHERE "B" ."DATE_CLOSED" = '1900-01-01' AND "A" ."LMO" = 'L' AND "B" ."DATE_RELEASED" <> '1900-01-01'
GROUP BY "A" ."JOB" ,"A" ."SUFFIX" HAVING MAX(A.FLAG_CLOSED) <> 'Y')

CREATE VIEW "V_XW3_JOB_NEXT_SEQ" AS SELECT "A" ."JOB" ,"A" ."SUFFIX" ,MIN ("A" ."SEQ" )"NEXT_SEQ" 
FROM "V_JOB_OPERATIONS_WC" "A" 
INNER JOIN "V_XW3_JOB_LAST_SEQ" "B" ON "A" ."JOB" = "B" ."JOB" AND "A" ."SUFFIX" = "B" ."SUFFIX" 
WHERE "A" ."FLAG_CLOSED" <> 'Y' AND "A" ."SEQ" > "B" ."LAST_SEQ" AND "A" ."LMO" = 'L' 
GROUP BY "A" ."JOB" ,"A" ."SUFFIX"

you can't join on dates in pervasive sql and accountants make very poor organizational decisions !

SELECT B.FISCALYEAR, A.CUSTOMER, SUM(A.EXTENSION) AS SALES FROM ORDER_HIST_LINE A 
LEFT JOIN XW3_CALENDAR445 B ON CONCAT(CONCAT(YEAR(A.DATE_INVOICE), RIGHT(CONCAT('0', MONTH(A.DATE_INVOICE)), 2)), RIGHT(CONCAT('0', DAY(A.DATE_INVOICE)), 2)) = B.CALDATE
WHERE B.FISCALYEAR = CAST(RIGHT('{?ENDING_DATE}', 4) AS INT) - 1
AND B.FISCALMONTH <= LEFT('{?ENDING_DATE}', 2)
GROUP BY B.FISCALYEAR, A.CUSTOMER
ORDER BY B.FISCALYEAR, A.CUSTOMER

im too much of a redis shill to use any sql

that's one of the few bad opinions i've heard coming from your general direction

SELECT 
"V_ORDER_HIST_LINE" ."CUSTOMER" "CUSTOMER_NUMBER" ,
"V_ORDER_HIST_LINE" ."CUSTOMER_SHIP" ,
"V_CUSTOMER_MASTER" ."NAME_CUSTOMER" ,
"V_ORDER_HIST_LINE" ."NAME_CUST_SHIP" ,
"XTWB_V_CRM_UF" ."UF9" "CUST_GROUP" ,
"V_ORDER_HIST_HEAD" ."CONTACT" ,
"V_ORDER_HIST_LINE" ."ADDRESS1_SHIP" ,"V_ORDER_HIST_LINE" ."ADDRESS2_SHIP" ,"V_ORDER_HIST_LINE" ."CITY_SHIP" ,"V_ORDER_HIST_LINE" ."STATE_SHIP" ,"V_ORDER_HIST_LINE" ."ZIP_SHIP" ,IF (("ifnull" ("V_OE_MULTI_SHIP" ."SHIP_COUNTY" ,'A' )= 'A' OR "V_OE_MULTI_SHIP" ."SHIP_COUNTY" = '' ),"V_CUSTOMER_SHIPTO" ."COUNTY_SHIP" ,"V_OE_MULTI_SHIP" ."SHIP_COUNTY" )"SHIP_COUNTY" ,"V_CUSTOMER_SHIPTO" ."COUNTY_SHIP" "SHIP_COUNTY_CM" ,
"V_ORDER_HIST_LINE" ."CUSTOMER_PO" ,
"V_ORDER_HIST_LINE" ."INVOICE" ,
"V_ORDER_HIST_LINE" ."ORDER_NO" ,
"V_ORDER_HIST_LINE" ."ORDER_LINE" ,
"V_ORDER_HIST_LINE" ."SALESPERSON" ,
"XTWB_SP_TERRITORY" ."TERRITORY" "SP_TERRITORY" ,"XTWB_SP_TERRITORY_1" ."TERRITORY" "CS_TERRITORY" ,IF (("V_ORDER_HIST_LINE" ."SALESPERSON" = 'HSE' AND "XTWB_SP_TERRITORY_1" ."TERRITORY" <> '' ),"XTWB_SP_TERRITORY_1" ."TERRITORY" ,IF (("V_ORDER_HIST_LINE" ."SALESPERSON" <> 'HSE' AND "XTWB_SP_TERRITORY" ."TERRITORY" <> '' ),"XTWB_SP_TERRITORY" ."TERRITORY" ,IF (("XTWB_SP_TERRITORY" ."TERRITORY" = '' AND "XTWB_SP_TERRITORY_1" ."TERRITORY" = '' ),"V_ORDER_HIST_LINE" ."LOCATION" ,'WI' )))"TERRITORY" ,"XTWB_SP_TERRITORY" ."REGION" ,"V_ORDER_HIST_LINE" ."DATE_ORDER" ,IF ("V_ORDER_BOOKING" ."DATE_ORDER" IS NULL OR "V_ORDER_BOOKING" ."DATE_ORDER" = '1900-01-01' ,"V_ORDER_HIST_LINE" ."DATE_ORDER" ,"V_ORDER_BOOKING" ."DATE_ORDER" )"DATE_BOOKED" ,"V_ORDER_HIST_LINE" ."DATE_INVOICE" ,"V_ORDER_HIST_LINE" ."DATE_DUE" "DATE_ITEM_PROM" ,'' "CHG_DATE",
"V_ORDER_HIST_LINE" ."PART" ,"V_ORDER_HIST_LINE" ."LOCATION" ,
"V_ORDER_HIST_LINE" ."DESCRIPTION" ,
"V_INVENTORY_MST2" ."TEXT_INFO2" "INVENTORY_TYPE" ,
"THS_ORDER_LINES_COST" ."REPAIR_ITEM" ,
"ifnull" ("XTWB_V_TOOL_TYPES" ."TEXT_INFO1" ,"V_INVENTORY_MST2" ."TEXT_INFO1" )"TOOL_TYPE" ,
"V_ORDER_HIST_HEAD" ."CODE_SORT" "CSR" ,"V_ORDER_HIST_HEAD" ."ORDER_SORT_2" "SHARED_SALES" ,"V_ORDER_HIST_LINE" ."PRODUCT_LINE" ,"V_PRODUCT_LINE" ."PRODUCT_LINE_NAME" ,"V_PRODUCT_LINE" ."PRODUCT_LINE_DESC" ,"V_ORDER_HIST_LINE" ."QTY_ORDERED" ,"V_ORDER_HIST_LINE" ."QTY_SHIPPED" ,"V_ORDER_HIST_LINE" ."QTY_BO" ,"ifnull" ("THS_ORDER_LINES_COST" ."COST_REVIEWED" ,0 )"COST" ,("V_ORDER_HIST_LINE" ."QTY_SHIPPED" *"ifnull" ("THS_ORDER_LINES_COST" ."COST_REVIEWED" ,0 ))"EXTENDED_COST" ,"ifnull" ("V_ORDER_HIST_LINE" ."COST" ,0 )"INV_COST" ,("V_ORDER_HIST_LINE" ."QTY_SHIPPED" *"ifnull" ("V_ORDER_HIST_LINE" ."COST" ,0 ))"INV_EXTENDED_COST" ,"V_ORDER_HIST_LINE" ."UM" ,"V_INVENTORY_MST2" ."NAME_VENDOR" "VENDOR_NUMBER" ,"V_VENDOR_MASTER" ."NAME_VENDOR" ,"V_INVENTORY_MSTR" ."code_sort" "BRAND" ,"XTWB_V_VEN_CRM_UF" ."UF1" "VENDOR_TYPE" ,"ifnull" ("V_ORDER_HIST_LINE" ."PRICE_DISCOUNT" ,0 )"PRICE_DISCOUNT" ,"ifnull" ("THS_ORDER_LINES_COST" ."PRICE_ORIGINAL" ,0 )"PRICE_ORIGINAL" ,"V_ORDER_HIST_LINE" ."TAX_AMT_1_ORDER" +"V_ORDER_HIST_LINE" ."TAX_AMT_2_ORDER" +"V_ORDER_HIST_LINE" ."TAX_AMT_3_ORDER" +"V_ORDER_HIST_LINE" ."TAX_AMT_4_ORDER" +"V_ORDER_HIST_LINE" ."TAX_AMT_5_ORDER" "SALES_TAX" ,("V_ORDER_HIST_LINE" ."QTY_SHIPPED" *"V_ORDER_HIST_LINE" ."PRICE_DISCOUNT" )"EXTENDED_PRICE" ,(("V_ORDER_HIST_LINE" ."QTY_SHIPPED" *"ifnull" ("V_ORDER_HIST_LINE" ."PRICE_DISCOUNT" ,0 ))-("V_ORDER_HIST_LINE" ."QTY_SHIPPED" *"ifnull" ("THS_ORDER_LINES_COST" ."COST_REVIEWED" ,0 )))"GROSS_MARGIN_$" ,"abs" ((("V_ORDER_HIST_LINE" ."QTY_SHIPPED" *"ifnull" ("V_ORDER_HIST_LINE" ."PRICE_DISCOUNT" ,0 ))-("V_ORDER_HIST_LINE" ."QTY_SHIPPED" *"ifnull" ("THS_ORDER_LINES_COST" ."COST_REVIEWED" ,0 ))))"GROSS_MARGIN_ABS_$" ,IF ("V_ORDER_HIST_LINE" ."PRICE_DISCOUNT" = 0.0 ,0.0 ,IF (("abs" ("V_ORDER_HIST_LINE" ."PRICE_DISCOUNT" )-"abs" ("THS_ORDER_LINES_COST" ."COST_REVIEWED" ))= 0.0 ,0.0 ,((("ifnull" ("V_ORDER_HIST_LINE" ."PRICE_DISCOUNT" ,0 ))-("ifnull" ("THS_ORDER_LINES_COST" ."COST_REVIEWED" ,0 )))/("V_ORDER_HIST_LINE" ."PRICE_DISCOUNT" ))))"GROSS_MARGIN_%" 
FROM (SELECT * FROM "V_ORDER_HIST_LINE" WHERE "DATE_INVOICE" >= '2019-01-01' AND "DATE_INVOICE" <= '2019-02-01') "V_ORDER_HIST_LINE" 
LEFT OUTER JOIN "THS_ORDER_LINES_COST" "THS_ORDER_LINES_COST" ON "THS_ORDER_LINES_COST" ."ORDER_NO" = "V_ORDER_HIST_LINE" ."ORDER_NO" AND "THS_ORDER_LINES_COST" ."RECORD_NO" = "V_ORDER_HIST_LINE" ."ORDER_LINE" AND "THS_ORDER_LINES_COST" ."PART" = "V_ORDER_HIST_LINE" ."PART" 
LEFT OUTER JOIN "V_INVENTORY_MST2" "V_INVENTORY_MST2" ON "V_INVENTORY_MST2" ."PART" = "V_ORDER_HIST_LINE" ."PART" AND "V_INVENTORY_MST2" ."LOCATION" = "V_ORDER_HIST_LINE" ."LOCATION" 
LEFT OUTER JOIN "V_VENDOR_MASTER" "V_VENDOR_MASTER" ON "V_VENDOR_MASTER" ."VENDOR" = "V_INVENTORY_MST2" ."NAME_VENDOR" 
LEFT OUTER JOIN "XTWB_V_VEN_CRM_UF" "XTWB_V_VEN_CRM_UF" ON "XTWB_V_VEN_CRM_UF" ."COMPID" = "V_INVENTORY_MST2" ."NAME_VENDOR" 
LEFT OUTER JOIN "V_PRODUCT_LINE" "V_PRODUCT_LINE" ON "V_PRODUCT_LINE" ."PRODUCT_LINE" = "V_ORDER_HIST_LINE" ."PRODUCT_LINE" 
LEFT OUTER JOIN "V_CUSTOMER_MASTER" "V_CUSTOMER_MASTER" ON "V_CUSTOMER_MASTER" ."CUSTOMER" = "V_ORDER_HIST_LINE" ."CUSTOMER" 
LEFT OUTER JOIN "V_ORDER_HIST_HEAD" "V_ORDER_HIST_HEAD" ON "V_ORDER_HIST_HEAD" ."ORDER_NO" = "V_ORDER_HIST_LINE" ."ORDER_NO" AND "V_ORDER_HIST_HEAD" ."INVOICE" = "V_ORDER_HIST_LINE" ."INVOICE" 
LEFT OUTER JOIN "V_OE_MULTI_SHIP" "V_OE_MULTI_SHIP" ON "V_OE_MULTI_SHIP" ."CUSTOMER" = "V_ORDER_HIST_LINE" ."CUSTOMER" AND "V_OE_MULTI_SHIP" ."SHIP_SEQ" = "V_ORDER_HIST_LINE" ."CUSTOMER_SHIP" 
LEFT OUTER JOIN "XTWB_SP_TERRITORY" "XTWB_SP_TERRITORY" ON "XTWB_SP_TERRITORY" ."SP_CODE" = "V_ORDER_HIST_LINE" ."SALESPERSON" 
LEFT OUTER JOIN "XTWB_SP_TERRITORY" "XTWB_SP_TERRITORY_1" ON "XTWB_SP_TERRITORY_1" ."SP_CODE" = "V_ORDER_HIST_HEAD" ."CODE_SORT" 
LEFT OUTER JOIN "V_INVENTORY_MSTR" "V_INVENTORY_MSTR" ON "V_INVENTORY_MSTR" ."part" = "V_ORDER_HIST_LINE" ."part" AND "V_INVENTORY_MSTR" ."location" = "V_ORDER_HIST_LINE" ."location" 
--LEFT OUTER JOIN "V_INVENTORY_MST2" "V_INVENTORY_MST2_1" ON "V_INVENTORY_MST2_1" ."PART" = "THS_ORDER_LINES_COST" ."REPAIR_ITEM" AND "V_INVENTORY_MST2_1" ."LOCATION" = "THS_ORDER_LINES_COST" ."LOCATION" 
LEFT OUTER JOIN "V_CUSTOMER_SHIPTO" "V_CUSTOMER_SHIPTO" ON "V_CUSTOMER_SHIPTO" ."CUSTOMER" = "V_ORDER_HIST_LINE" ."CUSTOMER" 
LEFT OUTER JOIN "XTWB_V_CRM_UF" "XTWB_V_CRM_UF" ON "XTWB_V_CRM_UF" ."COMPID" = "V_ORDER_HIST_LINE" ."CUSTOMER" 
LEFT OUTER JOIN "V_ORDER_BOOKING" "V_ORDER_BOOKING" ON "V_ORDER_HIST_LINE" ."ORDER_NO" = "V_ORDER_BOOKING" ."ORDER_NO" AND "V_ORDER_HIST_LINE" ."ORDER_LINE" = "V_ORDER_BOOKING" ."ORDER_LINE"
--LEFT OUTER JOIN "THS_TOOL_REPAIR" "THS_TOOL_REPAIR" ON "THS_TOOL_REPAIR" ."ORDER_NO" = "V_ORDER_HIST_LINE" ."ORDER_NO"
--LEFT OUTER JOIN "V_INVENTORY_MST2" "V_INVENTORY_MST2_2" ON "V_INVENTORY_MST2_2" ."PART" = "THS_TOOL_REPAIR" ."ITEM_NO" AND "V_INVENTORY_MST2_2" ."LOCATION" = "V_ORDER_HIST_HEAD" ."LOCATION"
LEFT OUTER JOIN "XTWB_V_TOOL_TYPES" "XTWB_V_TOOL_TYPES" ON "XTWB_V_TOOL_TYPES"."ORDER_NO" = "V_ORDER_HIST_LINE" ."ORDER_NO" AND "XTWB_V_TOOL_TYPES"."LOCATION" = "V_ORDER_HIST_HEAD" ."LOCATION"
ORDER BY "V_ORDER_HIST_LINE" ."ORDER_NO" ,"V_ORDER_HIST_LINE" ."ORDER_LINE" 

been trying to figure out a way to get this to run in under thirty minutes, after four hours of trying random things nothing has worked

the sheer power...i feel like i just smashed a mirror, walked under a ladder and met a black cat all at once looking at this

i feel like i need to call an exorcist

i write all my sql lowercase does this count

YES

enjoy