Time range

Question

Hi all,

Random question.

Is there a way i can combine date ranges within my SQL?

at the moment I have 2 sets running for separate time ranges and would like to combine these if possible

1st set - pulling Year to date data

Select r.STORE_NBR, i.DEPT_NBR As OD, Sum((r.SAT_QTY * c.SAT_MULT) + (r.SUN_QTY * c.SUN_MULT) + (r.MON_QTY * c.MON_MULT) + (r.TUE_QTY * c.TUE_MULT) + (r.WED_QTY * c.WED_MULT) + (r.THU_QTY * c.THU_MULT) + (r.FRI_QTY * c.FRI_MULT)) As Volume, Sum((c.SAT_MULT * r.SAT_SALES_AMT) + (c.SUN_MULT * r.SUN_SALES_AMT) + (c.MON_MULT * r.MON_SALES_AMT) + (c.TUE_MULT * r.TUE_SALES_AMT) + (c.WED_MULT * r.WED_SALES_AMT) + (c.THU_MULT * r.THU_SALES_AMT) + (c.FRI_MULT * r.FRI_SALES_AMT)) As Sales, i.ITEM1_DESC From gb_wm_vm.sku_dly_pos r, gb_wm_vm.item i, gb_wm_vm.calendar_day c Where r.ITEM_NBR = i.ITEM_NBR And c.WM_YR_WK = r.WM_YR_WK And i.DEPT_NBR In (38) And c.GREGORIAN_DATE Between Date '2020-01-01' And '2020-12-10' And i.OLD_NBR In

2nd set - pulling Week to date data

Select c.GREGORIAN_DATE, c.DAY_OF_WK, c.WM_YR_WK, Trim(c.LY_COMP_YEAR) || Trim(c.LY_COMP_WEEK (Format '9(2)')) As LY, i.DEPT_NBR As OD, r.STORE_NBR, Sum((r.SAT_QTY * c.SAT_MULT) + (r.SUN_QTY * c.SUN_MULT) + (r.MON_QTY * c.MON_MULT) + (r.TUE_QTY * c.TUE_MULT) + (r.WED_QTY * c.WED_MULT) + (r.THU_QTY * c.THU_MULT) + (r.FRI_QTY * c.FRI_MULT)) As Volume, Sum((c.SAT_MULT * r.SAT_SALES_AMT) + (c.SUN_MULT * r.SUN_SALES_AMT) + (c.MON_MULT * r.MON_SALES_AMT) + (c.TUE_MULT * r.TUE_SALES_AMT) + (c.WED_MULT * r.WED_SALES_AMT) + (c.THU_MULT * r.THU_SALES_AMT) + (c.FRI_MULT * r.FRI_SALES_AMT)) As Sales, i.ITEM1_DESC From gb_wm_vm.sku_dly_pos r, gb_wm_vm.item i, gb_wm_vm.calendar_day c Where r.ITEM_NBR = i.ITEM_NBR And c.WM_YR_WK = r.WM_YR_WK And c.WM_YR_WK In (week1)...

clmc9601 · Answer

Hi @novice1,

I'm sure there is a way, likely with subqueries if you continue to use SQL. However, I am having a hard time understanding the specifics of your problem. Where are you going to be typing the SQL? What does your incoming data look like? Are you open to using Alteryx instead of SQL? (This is much easier to accomplish within Alteryx) What does the resulting data from your current queries look like?

The community is much more likely to be able to find a solution if you can provide more information 🙂

novice1 · Answer

hi. I have multiple SQLs for different date ranges that later on are joined to produce data like Store, YTD, WTD and MTD sales

I was wondering if there is a way to write 1 SQL with all the data ranges within it and still get required outcome

clmc9601 · Answer

Hi @novice1,

Could you please provide a little more context for where and why you're combining these queries? Like is this going within a tool in an Alteryx workflow, and if so, where? What do you want the output to look like? What does the incoming data look like?

Generally, when combining two different levels of aggregation within SQL, you'll need to use a subquery. That might help you here, but it depends on your use case.