ORACLE SQL gets a store that sells for 28 consecutive days

Recently, the company a SSRS reporting requirements change, the selection of the store for 28 consecutive days have sales shop, think for a long while, found can use row_number () over (PARTITION by COLUMN01 ORDER by COLUMN02) to achieve, ROW _number () is sorted by COLUMN01 grouping and COLUMN02, starting with 1.

1: First look at the data source, request to take out the above 28 consecutive days sales amount greater than 0 Xf_storecode:

2: Decomposition, as seen from the figure, as long as Take Xf_storecode,count (1) >28 can be.

3: Complete code:

4: Text Code

1  withStore_comp as2(SELECTT.xf_storecode, T.xf_txdate,SUM(t.xf_amtsold) Xf_amtsold3      fromxf_vipitemdm T, Xf_itemmas T14    WHERET.xf_plu=T1. Xf_plu5       andSUBSTR (T.xf_storecode,3,2)inch('NK','SC','NS','MB')6       andT1. Xf_group2= 'NIKE'7       andT1. Xf_group9<> 'Chartered Equipment'8       andT.xf_txdate>=To_date ('20140501','YYYYMMDD')9       andT.xf_txdate<=To_date ('20140531','YYYYMMDD')Ten    GROUP  byT.xf_storecode, T.xf_txdate One    having SUM(T.xf_amtsold)> 0) A SELECTXf_storecode -    from(SELECTXf_storecode, - Xf_txdate, theRow_number () Over(PARTITION byXf_storecodeORDER  byxf_txdate) Row_num, -TRUNC (xf_txdate-row_number () -                       Over(PARTITION byXf_storecodeORDER  byxf_txdate)) Rowdiff -            fromStore_comp) +  GROUP  byXf_storecode, Rowdiff -  having COUNT(1)>=  -

5: If there is a better way, welcome to enlighten, thank you!

6: I would like to build a sports industry website, Welcome to have a Web page production technology friends use spare time to work together. Interested can contact me QQ 568625626.