T
tbonehwd
We currently have a query that lists the details of paid promotions and in
another database we have our customers POS (Point of Sale) sales imported on
a weekly basis always on a sunday for the prior week so the sales week is Mon
- Sun. What I want is to list the items included in the promotion along with
the the POS rings for that item by week while they are on promotion along
with two weeks prior to the promotion starting and two weeks after the
promotion ends.
Query 1 is the details on the promotion the important thing here is the
EFT_BEG and EFT_END (Begin Date & End Date)
"SELECT CPNIMD_M.MKTG_ID, CPNIMD_M.ITM_NUM, CDSITM_M.ITM_DSC1 " & _
", CDSITM_M.ITM_DSC3, CPNIMD_M.ALCT_PCT " & _
", CPNIMH_M!TOTL_CST*CPNIMD_M!ALCT_PCT/100 AS Unit_Cost " & _
", CPNIMH_M.DESCR_1, CPNIMH_M.DESCR_2, CPNIMH_M.CTM_NBR " & _
", CDSADR_M.CMP_NME, CPNIMH_M.EFT_BEG, CPNIMH_M.EFT_END " & _
", CPNIMH_M.MKTG_PRD, CPNIMH_M.MKTG_MED, CPNIMH_M.EXPN_CAT " & _
", CPNIMH_M.CIRC_QTY, CPNIMH_M.QTY_MAIL, CPNIMH_M.MKTG_CDE " & _
", CPNIMH_M.MKTG_PUB, CPNIMH_M.TOTL_CST " & _
"FROM ((CPNIMD_M INNER JOIN CDSITM_M ON CPNIMD_M.ITM_NUM = CDSITM_M.ITM_NUM)
" & _
"INNER JOIN CPNIMH_M ON CPNIMD_M.MKTG_ID = CPNIMH_M.MKTG_ID) " & _
"INNER JOIN CDSADR_M ON CPNIMH_M.CTM_NBR = CDSADR_M.CTM_NBR " & _
"WHERE CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' " & _
"AND CPNIMD_M.MKTG_ID " & strCOOPCODE & _
"ORDER BY CPNIMD_M.MKTG_ID; "
Query 2 is what I have been working on to get the information on sale
through by week. This is still work in progress (how to pull dates from Query
1.)
"SELECT DATA.ISBN, DATA.CYP_UNITS_SOLD AS Week1 " & _
", DATA_1.CYP_UNITS_SOLD AS Week 2" & _
", DATA_2.CYP_UNITS_SOLD AS Week 3 " & _
"FROM ((DATA LEFT JOIN DATA AS DATA_1 ON DATA.ISBN=DATA_1.ISBN) " & _
"LEFT JOIN DATA AS DATA_2 ON DATA.ISBN=DATA_2.ISBN)" & _
"WHERE ((DATA.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")) " & _
"AND ((DATA_1.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")-1) " & _
"AND ((DATA_2.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")-2) " & _
"AND DATA.CMT_CODE='AMZ' " & _
"AND DATA_1.CMT_CODE='AMZ' " & _
"AND DATA_2.CMT_CODE='AMZ'" & _
"AND ((YEAR(DATA.WEEK))=YEAR(" & strFRMDATE & "))" & _
"AND ((YEAR(DATA_1.WEEK))=YEAR(" & strFRMDATE & "))" & _
"AND ((YEAR(DATA_2.WEEK))=YEAR(" & strFRMDATE & "))" & _
";"
What I would like to do is have my code look at Query 1 for the begin date
and end date and make Query 2 pull the weekly sales numbers for each week
during the promotion and two weeks prior and two weeks post the promotion.
One more hitch the promotions are not always the same length could be a week
could three months.
Any suggestions?
Thanks,
Terry
another database we have our customers POS (Point of Sale) sales imported on
a weekly basis always on a sunday for the prior week so the sales week is Mon
- Sun. What I want is to list the items included in the promotion along with
the the POS rings for that item by week while they are on promotion along
with two weeks prior to the promotion starting and two weeks after the
promotion ends.
Query 1 is the details on the promotion the important thing here is the
EFT_BEG and EFT_END (Begin Date & End Date)
"SELECT CPNIMD_M.MKTG_ID, CPNIMD_M.ITM_NUM, CDSITM_M.ITM_DSC1 " & _
", CDSITM_M.ITM_DSC3, CPNIMD_M.ALCT_PCT " & _
", CPNIMH_M!TOTL_CST*CPNIMD_M!ALCT_PCT/100 AS Unit_Cost " & _
", CPNIMH_M.DESCR_1, CPNIMH_M.DESCR_2, CPNIMH_M.CTM_NBR " & _
", CDSADR_M.CMP_NME, CPNIMH_M.EFT_BEG, CPNIMH_M.EFT_END " & _
", CPNIMH_M.MKTG_PRD, CPNIMH_M.MKTG_MED, CPNIMH_M.EXPN_CAT " & _
", CPNIMH_M.CIRC_QTY, CPNIMH_M.QTY_MAIL, CPNIMH_M.MKTG_CDE " & _
", CPNIMH_M.MKTG_PUB, CPNIMH_M.TOTL_CST " & _
"FROM ((CPNIMD_M INNER JOIN CDSITM_M ON CPNIMD_M.ITM_NUM = CDSITM_M.ITM_NUM)
" & _
"INNER JOIN CPNIMH_M ON CPNIMD_M.MKTG_ID = CPNIMH_M.MKTG_ID) " & _
"INNER JOIN CDSADR_M ON CPNIMH_M.CTM_NBR = CDSADR_M.CTM_NBR " & _
"WHERE CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' " & _
"AND CPNIMD_M.MKTG_ID " & strCOOPCODE & _
"ORDER BY CPNIMD_M.MKTG_ID; "
Query 2 is what I have been working on to get the information on sale
through by week. This is still work in progress (how to pull dates from Query
1.)
"SELECT DATA.ISBN, DATA.CYP_UNITS_SOLD AS Week1 " & _
", DATA_1.CYP_UNITS_SOLD AS Week 2" & _
", DATA_2.CYP_UNITS_SOLD AS Week 3 " & _
"FROM ((DATA LEFT JOIN DATA AS DATA_1 ON DATA.ISBN=DATA_1.ISBN) " & _
"LEFT JOIN DATA AS DATA_2 ON DATA.ISBN=DATA_2.ISBN)" & _
"WHERE ((DATA.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")) " & _
"AND ((DATA_1.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")-1) " & _
"AND ((DATA_2.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")-2) " & _
"AND DATA.CMT_CODE='AMZ' " & _
"AND DATA_1.CMT_CODE='AMZ' " & _
"AND DATA_2.CMT_CODE='AMZ'" & _
"AND ((YEAR(DATA.WEEK))=YEAR(" & strFRMDATE & "))" & _
"AND ((YEAR(DATA_1.WEEK))=YEAR(" & strFRMDATE & "))" & _
"AND ((YEAR(DATA_2.WEEK))=YEAR(" & strFRMDATE & "))" & _
";"
What I would like to do is have my code look at Query 1 for the begin date
and end date and make Query 2 pull the weekly sales numbers for each week
during the promotion and two weeks prior and two weeks post the promotion.
One more hitch the promotions are not always the same length could be a week
could three months.
Any suggestions?
Thanks,
Terry