SQL newbie Query assistance - help

Other Tech Topics
1

I have created a query that pulls a 365 day online order history for customers that placed an online order in May. The output is grouped by state and customer ID and counts # of premium items ordered and # of orders. I need to refine the query to only output those customers that had 2 or more orders within 30 days, including order in May. I've been trying to figure out how incorporate these statements into the current query:
MAX("COMP_USERV"."ORDER_MART_V"."ORDER_DT")

and then use the MAX (ORDER_DT) to subtract ORDER_DT from 365 history.

Here is the current query I was hoping to refine:

SELECT ALL
("COMP_USERV"."ORDER_MART_V"."STATE_CD") "STATE_CD" ,
("COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID") "CUSTOMER_ID" ,
SUM("COMP_USERV"."ORDER_MART_V"."PREMIUM_ITEM_ORDERD") "PREMIUM_ITEM_ORDERD" ,
COUNT("COMP_USERV"."ORDER_MART_V"."ORDER_ID") "ORDERS"

FROM "COMP_USERV"."ORDER_MART_V"
WHERE (("COMP_USERV"."ORDER_MART_V"."ORDER_DT" >= CAST(CURRENT_DATE - INTERVAL '365' DAY AS FORMAT 'YYYY-MM-DD'))
AND ("COMP_USERV"."ORDER_MART_V"."ONLINE_INDICATOR" = '1')
AND ("COMP_USERV"."ORDER_MART_V"."TELMKT_IND" = '0'))
AND ("COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID" IN
(SELECT ALL
("COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID")

FROM "COMP_USERV"."ORDER_MART_V"
WHERE (("COMP_USERV"."ORDER_MART_V"."ORDER_DT" BETWEEN '2015-05-01' AND '2015-05-31')
AND ("COMP_USERV"."ORDER_MART_V"."ONLINE_INDICATOR" = '1')
AND ("COMP_USERV"."ORDER_MART_V"."TELMKT_IND" = '0'))
GROUP BY "COMP_USERV"."ORDER_MART_V"."STATE_CD","COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID" ))

GROUP BY "COMP_USERV"."ORDER_MART_V"."STATE_CD","COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID"

2

This is a Microsoft SQL Server forum. If you are using another RDBMS, you would get faster/better responses at a forum for that specific vendor (or a general database forum).

EDIT: Please ignore. Something in your query made me think it is an Oracle query. Perhaps it is not.

3

JamesK,

Yes, this is an Oracle query. I didn't realize this forum was specific to Microsoft SQL.

Thanks for enlightening me :smile: