February 7, 2017 at 7:44 pm
Hi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank you
February 8, 2017 at 1:57 am
Ravi Teja - Tuesday, February 7, 2017 7:44 PMHi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank you
Can you knock up a sample table please Ravi?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 8, 2017 at 2:18 am
Ravi Teja - Tuesday, February 7, 2017 7:44 PMHi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank you
Why are you not allowed to use the obvious DATEPART function?
Thomas Rushton
blog: https://thelonedba.wordpress.com
February 8, 2017 at 3:16 am
ThomasRushton - Wednesday, February 8, 2017 2:18 AMRavi Teja - Tuesday, February 7, 2017 7:44 PMHi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank youWhy are you not allowed to use the obvious DATEPART function?
I have a feeling that the OP has implied that their date and time columns are not stored in as date and time datatypes, but as (n)varchar fields in the format he has described. Of course, like Chris said, a Sample data would be nice, as that would tell us if my guess was right.
Also, I actually have no idea what the OP means by format "hr:min:sec". Does that mean that you could have a time like 1:1:1??? (which on a 24 clock would be 01:01:01). Not sure if hr implies 12 hour, 24 hour, and leading zero or not.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 8, 2017 at 5:01 am
Ravi Teja - Tuesday, February 7, 2017 7:44 PMHi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank you
Such a requirement is likely to be repeated for many different reasons. Add a persisted, computed, indexed column that isolates the hour. If, as Thom suggests, your date/time column(s) are character based, the folks there should seriously reconsider how they've built the table because it's going to be a thorn in everyone's side.
To keep from having to request "urgent" help in the future, realize that dates and times are a really common and important part of SQL Server and study them deeply. Do lots of practice problems like the one you posted. Not saying that to be mean, either. It's just a fact that will help you keep the job you have and get a new one when you need to. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2017 at 5:41 pm
ChrisM@Work - Wednesday, February 8, 2017 1:57 AMRavi Teja - Tuesday, February 7, 2017 7:44 PMHi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank youCan you knock up a sample table please Ravi?
REATE TABLE "ORDERMASTERS"
( "ORDER_NBR" VARCHAR2(10 BYTE),
"ORDER_LINE_NBR" NUMBER(1,0),
"CUSTOMER_NBR" VARCHAR2(9 BYTE),
"PRODUCT_NBR" VARCHAR2(10 BYTE),
"ORDER_PLATFORM" VARCHAR2(7 BYTE),
"ORDER_DATE" VARCHAR2(10 BYTE),
"ORDER_TIME" VARCHAR2(8 BYTE),
"TOTAL_LINE_AMT" NUMBER(6,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into ORDERMASTERS
SET DEFINE OFF;
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O151463570',1,'151522211','P150055867','On Air','2015-02-23','23:44:48',16);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O153561656',5,'150869580','P150002209','QVC.COM','2015-02-13','09:59:56',23);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O152245970',1,'153782354','P150065659','QVC.COM','2015-01-29','00:58:34',40);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O153170319',1,'150471438','P150064339','QVC.COM','2015-02-08','14:35:28',36);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O152026611',1,'151596739','P150061772','QVC.COM','2015-01-25','22:26:09',40);
Test.txt
Open with Google Docs
Displaying Test.txt.
February 8, 2017 at 5:43 pm
ThomasRushton - Wednesday, February 8, 2017 2:18 AMRavi Teja - Tuesday, February 7, 2017 7:44 PMHi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank youWhy are you not allowed to use the obvious DATEPART function?
Hi Thomas,
Can we use substr and get the hour from that and sort using the number 0-23
Ravi
February 8, 2017 at 6:51 pm
Ravi Teja - Wednesday, February 8, 2017 5:41 PMChrisM@Work - Wednesday, February 8, 2017 1:57 AMRavi Teja - Tuesday, February 7, 2017 7:44 PMHi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank youCan you knock up a sample table please Ravi?
REATE TABLE "ORDERMASTERS"
( "ORDER_NBR" VARCHAR2(10 BYTE),
"ORDER_LINE_NBR" NUMBER(1,0),
"CUSTOMER_NBR" VARCHAR2(9 BYTE),
"PRODUCT_NBR" VARCHAR2(10 BYTE),
"ORDER_PLATFORM" VARCHAR2(7 BYTE),
"ORDER_DATE" VARCHAR2(10 BYTE),
"ORDER_TIME" VARCHAR2(8 BYTE),
"TOTAL_LINE_AMT" NUMBER(6,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into ORDERMASTERS
SET DEFINE OFF;
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O151463570',1,'151522211','P150055867','On Air','2015-02-23','23:44:48',16);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O153561656',5,'150869580','P150002209','QVC.COM','2015-02-13','09:59:56',23);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O152245970',1,'153782354','P150065659','QVC.COM','2015-01-29','00:58:34',40);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O153170319',1,'150471438','P150064339','QVC.COM','2015-02-08','14:35:28',36);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O152026611',1,'151596739','P150061772','QVC.COM','2015-01-25','22:26:09',40);
Test.txt
Open with Google Docs
Displaying Test.txt.
Is this a SQL Server question? It's been a while, but the varchar2 column was a data type in Oracle back in version 8. Then again, I don't remember ever seeing an Oracle table created with that syntax before.
February 8, 2017 at 9:47 pm
Ed Wagner - Wednesday, February 8, 2017 6:51 PMRavi Teja - Wednesday, February 8, 2017 5:41 PMChrisM@Work - Wednesday, February 8, 2017 1:57 AMRavi Teja - Tuesday, February 7, 2017 7:44 PMHi Friends need some help here. I am having a table with order date(yyyy-mm-dd) order time (hr:min:sec),and another column which gives the dollar amount for each order. I am trying to write a query to show the total dollar amount that has been grouped by hours(0-23),ranked by total dollar amount. Option given is we can use sql character or text string function to handle the order_time column. Need some urgent help..thank youCan you knock up a sample table please Ravi?
REATE TABLE "ORDERMASTERS"
( "ORDER_NBR" VARCHAR2(10 BYTE),
"ORDER_LINE_NBR" NUMBER(1,0),
"CUSTOMER_NBR" VARCHAR2(9 BYTE),
"PRODUCT_NBR" VARCHAR2(10 BYTE),
"ORDER_PLATFORM" VARCHAR2(7 BYTE),
"ORDER_DATE" VARCHAR2(10 BYTE),
"ORDER_TIME" VARCHAR2(8 BYTE),
"TOTAL_LINE_AMT" NUMBER(6,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into ORDERMASTERS
SET DEFINE OFF;
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O151463570',1,'151522211','P150055867','On Air','2015-02-23','23:44:48',16);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O153561656',5,'150869580','P150002209','QVC.COM','2015-02-13','09:59:56',23);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O152245970',1,'153782354','P150065659','QVC.COM','2015-01-29','00:58:34',40);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O153170319',1,'150471438','P150064339','QVC.COM','2015-02-08','14:35:28',36);
Insert into ORDERMASTERS (ORDER_NBR,ORDER_LINE_NBR,CUSTOMER_NBR,PRODUCT_NBR,ORDER_PLATFORM,ORDER_DATE,ORDER_TIME,TOTAL_LINE_AMT) values ('O152026611',1,'151596739','P150061772','QVC.COM','2015-01-25','22:26:09',40);
Test.txt
Open with Google Docs
Displaying Test.txt.Is this a SQL Server question? It's been a while, but the varchar2 column was a data type in Oracle back in version 8. Then again, I don't remember ever seeing an Oracle table created with that syntax before.
this is a oracle question. but how can we do the same in sql to get the query results... can we use substr
February 9, 2017 at 2:00 am
The DDL and DLM you've provided is for Oracle, not SQL, so we can't use it.
But why do wyou WANT to do do it in string? It would be awful for the optimiser. Store Time as time, dates as date.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 9, 2017 at 10:27 am
Thom A - Thursday, February 9, 2017 2:00 AMBut why do wyou WANT to do do it in string? It would be awful for the optimiser. Store Time as time, dates as date.
Precisely. Data types are important for both performance and data integrity. When you have a date data type in Oracle, the function you want is TO_CHAR.
February 10, 2017 at 1:34 pm
This is clearly an Oracle table creation statement. Given that the values coming from Oracle will be character-based, I'd strongly recommend that the data transfer to SQL Server be done via SSIS, and then having pumped the data into a staging table, run a SQL Server query to take the two character fields and combine them with a space in the middle and make a new datetime field. Then, using DATEPART is a piece of cake for any portion of either the date or time. Heck, you could even use an Oracle date function on the way across, e..g.:
SELECT ORDER_NBR, ORDER_LINE, CUSTOMER_NBR, PRODUCT_NBR, ORDER_PLATFORM, TO_DATE(ORDER_DATE || ' ' || ORDER_TIME, 'YYYY-MM-DD HH24:MI:SS') AS ORDER_DATETIME
FROM ORDERMASTERS
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply