Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How can I modify this SQL to do the following?

Posted on 2011-05-12
6
Medium Priority
?
431 Views
Last Modified: 2012-06-21
I want to query and sum the sales qty and sales amt based on the following time ranges

Segment_1 = 6am to 12:59pm
Segment_2 = 1pm to 3:59pm
Segment_3 = 4pm to 6:59pm
Segment_4 = 7pm to 11pm

How can the SQL be modified to do this?

Thanks
SELECT 2011 AS YearNo, 11 AS WeekNo,
NDW.CONS_TRANS_DTL.str_num, 1 AS RTL_CO_NUM,
TO_CHAR(NDW.CONS_TRANS_DTL.trans_dt, 'MM/DD/YYYY') AS TRANS_DT,
TO_CHAR(TO_DATE(CASE WHEN TO_NUMBER(TO_CHAR(trans_tms, 'mi')) > 59
THEN TO_CHAR(TRUNC(trans_tms, 'hh') + 1 / 48, 'hh24:mi:ss') ELSE TO_CHAR(TRUNC(trans_tms, 'hh'), 'hh24:mi:ss')
END,'hh24:mi:ss'),'hh24:mi:ss') AS trans_tm_interval,
NDW.NG_ITEM_UPC.ITEM_NUM,
NDW.CONS_TRANS_DTL.UPC_CD,
NDW.RETAIL_PRODUCT.ITEM_DESC,
NDW.RETAIL_PRODUCT.PCS_CAT_CD,
NDW.RETAIL_PRODUCT.PCS_GRP_CD,
NDW.PROD_CURR_VW.DELV_METH_CD,
TO_CHAR(NDW.PROD_CURR_VW.EFT_DT,'MM/DD/YYYY') AS EFT_DT,
SUM(NDW.CONS_TRANS_DTL.sale_qty) AS sum_sale_qty,
SUM(NDW.CONS_TRANS_DTL.sale_amt) AS sum_sale_amt
FROM NDW.CONS_TRANS_DTL, NDW.RETAIL_PRODUCT, NDW.PCS_VW, NDW.NG_ITEM_UPC, NDW.PROD_CURR_VW
WHERE NDW.CONS_TRANS_DTL.STR_NUM IN (1001,1003,1004,1010,1011,1014,1016,1019,1021,1022,1023,1027,1028,1029,1032,1035,1040,1050,1051,1064,1066,1079,1082,1083,1090,1092,1095,1099,1114,1127,1132,1142,1154,1155,1170,1174,1179,1188,1194,1200,1208,1212)
AND NDW.RETAIL_PRODUCT.PCS_CAT_CD IN(19,68,69)
AND NDW.CONS_TRANS_DTL.trans_dt >= TO_DATE('03/13/2011', 'MM/DD/YYYY')
AND NDW.CONS_TRANS_DTL.trans_dt <= TO_DATE('03/19/2011', 'MM/DD/YYYY')
AND NDW.CONS_TRANS_DTL.SALE_AMT > 0
AND NDW.CONS_TRANS_DTL.AT_RISK_DSCT_AMT = 0
AND NDW.CONS_TRANS_DTL.UPC_CD = NDW.RETAIL_PRODUCT.ITEM_UPC_CD
AND NDW.PCS_VW.PCS_CAT_CD = NDW.RETAIL_PRODUCT.PCS_CAT_CD
AND NDW.PCS_VW.PCS_CD = NDW.RETAIL_PRODUCT.PCS_CD
AND NDW.NG_ITEM_UPC.UPC_NUM  = NDW.CONS_TRANS_DTL.UPC_CD
AND NDW.NG_ITEM_UPC.UPC_NUM  = NDW.RETAIL_PRODUCT.ITEM_UPC_CD
AND NDW.NG_ITEM_UPC.ITEM_NUM = NDW.PROD_CURR_VW.NG_ITEM_NUM
GROUP BY 
2010, 
30,
NDW.CONS_TRANS_DTL.str_num, 
35,
TO_CHAR(NDW.CONS_TRANS_DTL.trans_dt, 'MM/DD/YYYY'),
TO_DATE(CASE WHEN TO_NUMBER(TO_CHAR(trans_tms, 'mi')) > 59
THEN TO_CHAR(TRUNC(trans_tms, 'hh') + 1 / 48, 'hh24:mi:ss') ELSE TO_CHAR(TRUNC(trans_tms, 'hh'), 'hh24:mi:ss')
END,'hh24:mi:ss'),
NDW.NG_ITEM_UPC.ITEM_NUM,
NDW.CONS_TRANS_DTL.UPC_CD,
NDW.RETAIL_PRODUCT.ITEM_DESC,
NDW.RETAIL_PRODUCT.PCS_CAT_CD,
NDW.RETAIL_PRODUCT.PCS_GRP_CD,
NDW.PROD_CURR_VW.DELV_METH_CD,
NDW.PROD_CURR_VW.EFT_DT

Open in new window

0
Comment
Question by:kosenrufu
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35747777
you need to specify a date column to check the times


then you can do something like this...

use the same idea for the amounts
SUM(
             CASE
                 WHEN some_date_column >= TRUNC(some_date_column) + 6 / 24
                      AND some_date_column < TRUNC(some_date_column) + 13 / 24
                 THEN
                     ndw.cons_trans_dtl.sale_qty
             END)
             AS segment_1_qty,
         SUM(
             CASE
                 WHEN some_date_column >= TRUNC(some_date_column) + 13 / 24
                      AND some_date_column < TRUNC(some_date_column) + 16 / 24
                 THEN
                     ndw.cons_trans_dtl.sale_qty
             END)
             AS segment_2_qty,
         SUM(
             CASE
                 WHEN some_date_column >= TRUNC(some_date_column) + 16 / 24
                      AND some_date_column < TRUNC(some_date_column) + 19 / 24
                 THEN
                     ndw.cons_trans_dtl.sale_qty
             END)
             AS segment_3_qty,
         SUM(
             CASE
                 WHEN some_date_column >= TRUNC(some_date_column) + 19 / 24
                      AND some_date_column < TRUNC(some_date_column) + 23 / 24
                 THEN
                     ndw.cons_trans_dtl.sale_qty
             END)
             AS segment_4_qty,

Open in new window

0
 
LVL 3

Expert Comment

by:cklautau
ID: 35751911
I suggest you to first identify the records you need to filter and generate it as a subquery with a new column, let's call it day_slice, as indicaed in the code below.

Replace in my example the subquery for your table which contains the  column trans_tms and include the PK columns. Then you use this subquery to join to the others tables of your original query.
select to_char(dt_tran,'dd/mm/yyyy hh24:mi') dt_tran,
case 
when to_char(dt_tran,'hh24') < '06' then 0
when to_char(dt_tran,'hh24') < '13' then 1
when to_char(dt_tran,'hh24') < '16' then 2
when to_char(dt_tran,'hh24') < '19' then 3
when to_char(dt_tran,'hh24') < '23' then 4
else 0
end day_slice
from (
select to_date('250320110834','ddmmyyyyhh24mi') as dt_tran from dual union
select to_date('250320111142','ddmmyyyyhh24mi') as dt_tran from dual union
select to_date('250320111325','ddmmyyyyhh24mi') as dt_tran from dual union
select to_date('250320111415','ddmmyyyyhh24mi') as dt_tran from dual union
select to_date('250320111621','ddmmyyyyhh24mi') as dt_tran from dual)
;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 35754211
Can anything occur between 11pm on one date and 6am the following morning? Can you post some sample relevant data (no need for the complete select statement) and your expected results?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:kosenrufu
ID: 35755901
awking00:

No, nothing occurs between 11pm and 6am.

Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35755931
how did http:#35747777

work out for you?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35755953
if it didn't work, please specify exactly what didn't work, and what date column(s) you used to define your date/time ranges
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question