Get Difference Between Timestamps Using Pivot

User_OMEF8 · Aug 30, 2019 2:45PM

Hello,

I am on Oracle 11g and I am trying to formulate a SQL query to produce results using a PIVOT (not sure if it is actually necessary). The report that I want to create has different "Groups" of information and I was wondering if this could be achieved with a single query. I know I can do this if I run multiple queries, then combine the information, then do some other operations to get what I need, but there are too many steps if I do it this way.

Here is what I want to do... I have many stores and different items sold at each store. For my purposes, I am only concerned with certain stores and certain items. I also have a timestamp field that tells me when the item arrived and when it was sold. I need to calculate the difference between the 2 timestamp fields (sold minus arrived, what is the difference between?)

Stores - varchar2
Items - varchar2
Arrived - Timestamp
Sold - Timestamp

Below is a sample output of what I want the report to look like. Note that the column headers (2019-08-01, 2019-08-02, and 2019-08-03) would be dynamic (so I could have more dates across), but effectively, this would be the date difference of when the ITEM was sold. For example, DOWNTOWN, PENCIL, 2019-08-01 ==> 5. This is the calculated total number of days for all PENCILS sold that date.

If I had 5 PENCILS and they all had an Arrival Date of 2019-07-31 and a Sold Date of 2019-08-01. This would equal 5 days.

If I had 3 PENCILS...

PENCIL 1 Arrival Date 2019-07-31 and Sold Date 2019-08-01 ==> 1 Day
PENCIL 2 Arrival Date 2019-07-30 and Sold Date 2019-08-01 ==> 2 Days
PENCIL 3 Arrival Date 2019-07-30 and Sold Date 2019-08-01 ==> 2 Days
Total Days ==> 5 days

STORE	ITEMS	2019-08-01	2019-08-02	2019-08-03
DOWNTOWN	PEN	1	2	3
DOWNTOWN	PENCIL	5	6	7
DOWNTOWN	ERASER	2	1	3
		2.67 (AVERAGE DATE DIFFERENCE)	3 (AVERAGE DATE DIFFERENCE)	4.33 AVERAGE DATE DIFFERENCE
4.SUBURB	PEN	9	8	7
SUBURB	PENCIL	3	2	1
SUBURB	ERASER	4	5	6
		5.33 AVERAGE DATE DIFFERENCE	5 AVERAGE DATE DIFFERENCE	4.67 AVERAGE DATE DIFFERENCE

Here is my attempt SQL query...

select * from (

select sn.storenamecolumn, i.itemcolumn, trunc(d.solddatecolumn)

from itemtable i

join storetable s on i.id = s.id

join storenametable sn on s.storeid = sn.storename

join itemdatetable d on i.itemid = d.id

where i.itemcolumn in ('PEN', 'PENCIL', 'ERASER')

and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')

)

pivot

(

count(*)

for trunc(solddatecolumn) in ('2019-08-01', '2019-08-02', '2019-08-03') <============ error missing IN keyword

)

where storenamecolumn in ('DOWNTOWN', 'SUBURB')

order by storenamecolumn;

I keep getting the error above and I believe it has to do with the trunc() method. If I remove the trunc() method from both the "for trunc()" and the SELECT Statement, then it will run, but the results are not right because I am only looking for a date and not the entire "timestamp". Any suggestions on how I can achieve my desired results? Thanks.

Also, is it possible to calculate the total average for each day and put it at the bottom of each Date column? I edited the table above.

Frank Kulash · Sep 5, 2019 2:41PM

Hi,

976563 wrote:Ahh, the INSERTs worked when I ran them. I am on Oracle Database 11g Release 11.2.0.4.0 - 64bit.The desired output would be like...STOREITEMS2019-08-26 Sum
2019-08-26 Avg2019-08-27 Sum2019-08-27 Avg2019-08-28 Sum2019-08-28 Avg2019-08-29 Sum2019-08-29 Avg2019-08-30 Sum2019-08-30 AvgDOWNTOWNERASER0000009900DOWNTOWNPENCIL35352512.5000000EASTPEN000066331212SUBURBPENCIL0000151528282323Row 1 ==> Only 1 found, the value is 9 because the (datesold minus itemarrivaldate [2019-09-07 minus 2019-08-29 ==> 9]). This becomes the SUM and AVG.Row 2 ==> 2019-08-26 only 1 account is found. The value is 35 because (datesold minus itemarrivaldate [2019-09-30 minus 2019-08-26 ==> 35]). This becomes the SUM and AVG.Row 2 ==> 2019-08-27 there are 2 accounts. 1st account (2019-09-15 minus 2019-08-27 ==> 19). 2nd account (2019-09-02 minus 2019-08-27 ==> 6). 19 + 6 => 25 SUM, 12.5 AVGRow 3 and 4 follow the same logic as Row 1 because there was only 1 row found.

Earlier, you were interested in a subtotal showing all items from each store, as well as the individual store/item combinations. Now you've changed the requirements to show only the individual items; is that right?

Here's one way to do that:

WITH    data_to_pivot    AS(    SELECT    st.storename    ,       it.itemname    ,         TRUNC (it.itemarrivaldate)  AS datearrived    ,         TRUNC (itd.datesold)            - TRUNC (it.itemarrivaldate)  AS ndays    FROM      z_itemtable         it    JOIN      z_mastertable        m  ON  it.masterid  = m.id    JOIN      z_storetable        st  ON  m.storecode  = st.code    JOIN      z_itemtabledetail  itd  ON  it.detailid  = itd.id    WHERE     it.itemarrivaldate  >= TO_DATE ('2019-08-26', 'yyyy-mm-dd')    AND       it.itemarrivaldate  <  TO_DATE ('2019-08-31', 'yyyy-mm-dd')    AND       st.storename        IN ('DOWNTOWN', 'SUBURB', 'EAST', 'WEST'))SELECT    *FROM      data_to_pivotPIVOT     (    SUM (ndays)   AS sum          ,    AVG (ndays)   AS avg          FOR  datearrived IN ( DATE '2019-08-26'  AS d_2019_08_26                              , DATE '2019-08-27'  AS d_2019_08_27                              , DATE '2019-08-28'  AS d_2019_08_28                              , DATE '2019-08-29'  AS d_2019_08_29                              , DATE '2019-08-30'  AS d_2019_08_30                              )   )ORDER BY  storename, itemname;

The results I get are a little different from what you posted:

                               D_2019 D_2019 D_2019 D_2019 D_2019 D_2019 D_2019 D_2019 D_2019 D_2019                               _08_26 _08_26 _08_27 _08_27 _08_28 _08_28 _08_29 _08_29 _08_30 _08_30STORENAME ITEMNAME               _SUM   _AVG   _SUM   _AVG   _SUM   _AVG   _SUM   _AVG   _SUM   _AVG--------- -------------------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------DOWNTOWN  ERASER                                                              9      9DOWNTOWN  PENCIL                   35     35     25   12.5EAST      PEN                                                   6      6      3      3     12     12SUBURB    PENCIL                                               15     15     28     28     23     23WEST      ERASER                                                             24     24     15     15

I get a row for storename='WEST'. Did you mean to include that row in your desired results?

As posted, the solution shows NULLs for missing data. You can use NVL to get 0's instead, if you want them.

mathguy · Aug 30, 2019 3:10PM

Forget everything else and focus on the "dynamic" part of "dynamic pivoting". What is your plan for doing that in a single SQL query? As you may or may not know, a "single SQL query" is considered static - in direct opposition to "dynamic".

User_OMEF8 · Aug 30, 2019 3:25PM

mathguy wrote:Forget everything else and focus on the "dynamic" part of "dynamic pivoting". What is your plan for doing that in a single SQL query? As you may or may not know, a "single SQL query" is considered static - in direct opposition to "dynamic".

I might have used wrong terminology then in the Oracle world. My end goal would be to have this available through a web app where the only information the user enters is a date range. The date range would then be used to "insert" into the query to retrieve data. For instance, if the user entered Start Date "2019-08-01" and End Date "2019-08-07", it would create "2019-08-01", "2019-08-02", "2019-08-03", "2019-08-04", "2019-08-05", "2019-08-06", "2019-08-07", as the column headers (along with the Store and Items), but I am trying to see if I can even get the query right.

Frank Kulash · Aug 30, 2019 4:49PM

Hi,

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

Explain, using specific examples, how you get those results from that data.

Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

See the forum FAQ:

If you want the number of columns in the output to depend on what the user enters at run-time, then you need Dynamic SQL. It's not very complicated, as dynamic SQL goes, but it's still something you might want to avoid. Your front end may have tools for making this easier. What front end are you using?

One way to avoid dynamic SQL is to put the output in a huge string column that is formatted to look like a dynamic number of columns. so a person reading the output thinks it is separate columns.

Another approach is to allow a maximum number of output columns. For example, you could set a limit of 14 columns, so the user could specify a date range of up to two weeks, and get a separate column of output for each calendar day. If the user specifies fewer than 14 days, then the output would have some empty columns at the end.

Do you want to try either of these static SQL approaches?

976563 wrote:...Here is my attempt SQL query...select * from ( select sn.storenamecolumn, i.itemcolumn, trunc(d.solddatecolumn) from itemtable i join storetable s on i.id = s.id join storenametable sn on s.storeid = sn.storename join itemdatetable d on i.itemid = d.id where i.itemcolumn in ('PEN', 'PENCIL', 'ERASER') and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd') ) pivot ( count(*) for trunc(solddatecolumn) in ('2019-08-01', '2019-08-02', '2019-08-03') <============ error missing IN keyword )where storenamecolumn in ('DOWNTOWN', 'SUBURB')order by storenamecolumn;I keep getting the error above and I believe it has to do with the trunc() method. If I remove the trunc() method from both the "for trunc()" and the SELECT Statement, then it will run, but the results are not right because I am only looking for a date and not the entire "timestamp". Any suggestions on how I can achieve my desired results? Thanks.

In the PIVOT clause, you can't use a function (like TRUNC) after the FOR keyword; only a column name is allowed. You can use TRUNC in the sub-query.

Also, don't try to compare TIMESTAMPs (like storenamecolumn) or DATEs (like TRUNC (storenamecolumn)) to strings (like '2019-08-01'). You can compare DATEs to DATEs like this:

FOR  truncsolddatecolumn IN ( DATE '2019-08-01'  AS d_2019_08_01                            , DATE '2019-08-02'  AS d_2019_08_02                            , DATE '2019-08-03'  AS d_2019_08_03                            )

where truncsolddatecolumn is defined in the sub-query as TRUNC (soldatecolumn).

[EDIT: I just noticed that you are using TRUNC in the sub-query already. That's good; give that column an alias; then you can use that alias in a PIVOT clause, after the FOR keyword.]

Also, is it possible to calculate the total average for each day and put it at the bottom of each Date column? I edited the table above.

GROUP BY ROLLUP (or GROUP BY GROUPING SETS) is good for getting sub-totals like that.

You can have a COUNT on some rows and an average on other rows; they're both numbers.

If you really want text on some rows (e.g. '2.67 (AVERAGE DATE DIFFERENCE)' ) then you'd need to make that column a string.

User_OMEF8 · Sep 3, 2019 12:53PM

Frank Kulash wrote:If you want the number of columns in the output to depend on what the user enters at run-time, then you need Dynamic SQL. It's not very complicated, as dynamic SQL goes, but it's still something you might want to avoid. Your front end may have tools for making this easier. What front end are you using?One way to avoid dynamic SQL is to put the output in a huge string column that is formatted to look like a dynamic number of columns. so a person reading the output thinks it is separate columns.Another approach is to allow a maximum number of output columns. For example, you could set a limit of 14 columns, so the user could specify a date range of up to two weeks, and get a separate column of output for each calendar day. If the user specifies fewer than 14 days, then the output would have some empty columns at the end.Do you want to try either of these static SQL approaches?

Initially, I wanted to build them a ASP page to be able to input their date range of what they would like. After I discussed it with them, we agreed to run it on a weekly basis. So, I can spool the file through a scheduled task.

Frank Kulash wrote:976563 wrote:...Here is my attempt SQL query...select * from ( select sn.storenamecolumn, i.itemcolumn, trunc(d.solddatecolumn) from itemtable i join storetable s on i.id = s.id join storenametable sn on s.storeid = sn.storename join itemdatetable d on i.itemid = d.id where i.itemcolumn in ('PEN', 'PENCIL', 'ERASER') and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd') ) pivot ( count(*) for trunc(solddatecolumn) in ('2019-08-01', '2019-08-02', '2019-08-03') <============ error missing IN keyword )where storenamecolumn in ('DOWNTOWN', 'SUBURB')order by storenamecolumn;I keep getting the error above and I believe it has to do with the trunc() method. If I remove the trunc() method from both the "for trunc()" and the SELECT Statement, then it will run, but the results are not right because I am only looking for a date and not the entire "timestamp". Any suggestions on how I can achieve my desired results? Thanks.In the PIVOT clause, you can't use a function (like TRUNC) after the FOR keyword; only a column name is allowed. You can use TRUNC in the sub-query.Also, don't try to compare TIMESTAMPs (like storenamecolumn) or DATEs (like TRUNC (storenamecolumn)) to strings (like '2019-08-01'). You can compare DATEs to DATEs like this:FOR truncsolddatecolumn IN ( DATE '2019-08-01' AS d_2019_08_01 , DATE '2019-08-02' AS d_2019_08_02 , DATE '2019-08-03' AS d_2019_08_03 ) where truncsolddatecolumn is defined in the sub-query as TRUNC (soldatecolumn).[EDIT: I just noticed that you are using TRUNC in the sub-query already. That's good; give that column an alias; then you can use that alias in a PIVOT clause, after the FOR keyword.]

Thanks for the alias suggestion. That part did the trick for the query to run. I see data in my results now, but when I try to compute the statistics, it keeps giving me 0. My updated query is below. Any idea why it keeps giving me 0?

select * from (

select sn.storenamecolumn, i.itemcolumn, trunc(d.solddatecolumn) - trunc(i.arrivaldatecolumn) as truncdifference

from itemtable i

join storetable s on i.id = s.id

join storenametable sn on s.storeid = sn.storename

join itemdatetable d on i.itemid = d.id

where i.itemcolumn in ('PEN', 'PENCIL', 'ERASER')

and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')

)

pivot

(

count(*)

for truncdifference in (DATE '2019-08-01' as d_2019_08_01,

DATE '2019-08-02' as d_2019_08_02,

DATE '2019-08-03' as d_2019_08_03)

)

where storenamecolumn in ('DOWNTOWN', 'SUBURB')

order by storenamecolumn;

Frank Kulash wrote:Also, is it possible to calculate the total average for each day and put it at the bottom of each Date column? I edited the table above.GROUP BY ROLLUP (or GROUP BY GROUPING SETS) is good for getting sub-totals like that.You can have a COUNT on some rows and an average on other rows; they're both numbers.If you really want text on some rows (e.g. '2.67 (AVERAGE DATE DIFFERENCE)' ) then you'd need to make that column a string.

Thanks for the rollup tip. I did not even know about this function. I am currently reading up on it right now on an Oracle page (https://www.oracletutorial.com/oracle-basics/oracle-rollup/ ). I haven't even been able to try this out yet though, since I am still stuck on the previous issue. I will only want it as a number value. I only added the (AVERAGE DATE DIFFERENCE) to depict what I wanted the value to represent.

Frank Kulash · Sep 3, 2019 1:33PM

Hi,

976563 wrote:... I see data in my results now, but when I try to compute the statistics, it keeps giving me 0. My updated query is below. Any idea why it keeps giving me 0?select * from ( select sn.storenamecolumn, i.itemcolumn, trunc(d.solddatecolumn) - trunc(i.arrivaldatecolumn) as truncdifference from itemtable i join storetable s on i.id = s.id join storenametable sn on s.storeid = sn.storename join itemdatetable d on i.itemid = d.id where i.itemcolumn in ('PEN', 'PENCIL', 'ERASER') and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd') ) pivot ( count(*) for truncdifference in (DATE '2019-08-01' as d_2019_08_01, DATE '2019-08-02' as d_2019_08_02, DATE '2019-08-03' as d_2019_08_03) )where storenamecolumn in ('DOWNTOWN', 'SUBURB')order by storenamecolumn;...

Once again, post a little sample data and the exact results you want to see from that data.

You're defining truncdifference as a NUMBER (the minus operator, - , returns a NUMBER), then comparing that NUMBER to DATEs. Display the results of your in-line view without pivoting them. Notice that truncdifference has values like 1 or 3, not DATE '2019-08-01' or DATE '2019-08-03'.

Frank Kulash · Sep 3, 2019 1:43PM

Hi,

By the way:

976563 wrote:... and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')...

It's more efficient (sometimes much, much more efficient) to use raw columns in conditions, like this:

and  d.solddatecolumn >= to_date('2019-08-01', 'yyyy-mm-dd')and  d.solddatecolumn  < to_date('2019-08-04', 'yyyy-mm-dd')

The main reason is that it allows the optimizer to use an index on solddatecolumn.

It's also faster because it avoids calling TRUNC twice for every row in the table.

User_OMEF8 · Sep 3, 2019 2:28PM

Frank Kulash wrote:Hi,By the way:976563 wrote:... and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')...It's more efficient (sometimes much, much more efficient) to use raw columns in conditions, like this:and d.solddatecolumn >= to_date('2019-08-01', 'yyyy-mm-dd')and d.solddatecolumn < to_date('2019-08-04', 'yyyy-mm-dd')The main reason is that it allows the optimizer to use an index on solddatecolumn. It's also faster because it avoids calling TRUNC twice for every row in the table.

Thanks for the tip again!

I apologize, but I am not sure what you mean by sample data. Is it from each table? The output of what I would like is in my original post. I see what you mean by comparing Numbers to Dates and I think the difficulty with this query is the calculation between my "Date Sold - Date Arrived" and using that Number value to apply for each "Item" and "Store", since I want to display the results on a per date basis. I did try the ROLLUP and it works when I do not use a PIVOT. I get the results, but I cannot seem to get it to display for each day within my specified date range. I am still trying to work through that.

Edit: The ROLLUP gives me the sum, but I am actually looking for the average.

Frank Kulash · Sep 3, 2019 8:31PM

Hi,

976563 wrote:Frank Kulash wrote:Hi,By the way:976563 wrote:... and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')...It's more efficient (sometimes much, much more efficient) to use raw columns in conditions, like this:and d.solddatecolumn >= to_date('2019-08-01', 'yyyy-mm-dd')and d.solddatecolumn < to_date('2019-08-04', 'yyyy-mm-dd')The main reason is that it allows the optimizer to use an index on solddatecolumn. It's also faster because it avoids calling TRUNC twice for every row in the table.Thanks for the tip again!I apologize, but I am not sure what you mean by sample data. .

Here's an example of how to post sample data:

CREATE TABLE    sales
(   store       VARCHAR2 (10)
,   item        VARCHAR2 (10)
);INSERT INTO sales (store, item) VALUES ('DOWNTOWN', 'PEN');
INSERT INTO sales (store, item) VALUES ('SUBURB',   'ERASER');

In this problem, the sample data will have more rows and more columns.

Is it from each table?

Yes. If you need two or more tables to show what the problem is, then post sample data for all of them.

…Edit: The ROLLUP gives me the sum, but I am actually looking for the average

ROLLUP works with SUM, AVG, MIN, LISTAGG and most (if not all) other aggregate functions.

If you can use it with SUM, but not AVG, then post both of your queries (the one using SUM that works, and the one using AVG that doesn't).

User_OMEF8 · Sep 4, 2019 2:20PM

Frank Kulash wrote:Hi,976563 wrote:Frank Kulash wrote:Hi,By the way:976563 wrote:... and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')...It's more efficient (sometimes much, much more efficient) to use raw columns in conditions, like this:and d.solddatecolumn >= to_date('2019-08-01', 'yyyy-mm-dd')and d.solddatecolumn < to_date('2019-08-04', 'yyyy-mm-dd')The main reason is that it allows the optimizer to use an index on solddatecolumn. It's also faster because it avoids calling TRUNC twice for every row in the table.Thanks for the tip again!I apologize, but I am not sure what you mean by sample data. .Here's an example of how to post sample data:CREATE TABLE sales
( store VARCHAR2 (10)
, item VARCHAR2 (10)
);INSERT INTO sales (store, item) VALUES ('DOWNTOWN', 'PEN');
INSERT INTO sales (store, item) VALUES ('SUBURB', 'ERASER');In this problem, the sample data will have more rows and more columns.Is it from each table? Yes. If you need two or more tables to show what the problem is, then post sample data for all of them.…Edit: The ROLLUP gives me the sum, but I am actually looking for the averageROLLUP works with SUM, AVG, MIN, LISTAGG and most (if not all) other aggregate functions.If you can use it with SUM, but not AVG, then post both of your queries (the one using SUM that works, and the one using AVG that doesn't).

Thanks, Frank!

I couldn't figure out how to do the query using PIVOT, so I actually re-wrote my query to use a WITH. The downside is that I still do not know how to get it to output the result across a date range, so I am having to do it on a per day basis instead. As for the Average, I discovered a built-in function called AVG() and I am using that without having to do a ROLLUP call.

Frank Kulash · Sep 4, 2019 2:40PM

Hi,

976563 wrote:Frank Kulash wrote:Hi,976563 wrote:Frank Kulash wrote:Hi,By the way:976563 wrote:... and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')...It's more efficient (sometimes much, much more efficient) to use raw columns in conditions, like this:and d.solddatecolumn >= to_date('2019-08-01', 'yyyy-mm-dd')and d.solddatecolumn < to_date('2019-08-04', 'yyyy-mm-dd')The main reason is that it allows the optimizer to use an index on solddatecolumn. It's also faster because it avoids calling TRUNC twice for every row in the table.Thanks for the tip again!I apologize, but I am not sure what you mean by sample data. .Here's an example of how to post sample data:CREATE TABLE sales
( store VARCHAR2 (10)
, item VARCHAR2 (10)
);INSERT INTO sales (store, item) VALUES ('DOWNTOWN', 'PEN');
INSERT INTO sales (store, item) VALUES ('SUBURB', 'ERASER');In this problem, the sample data will have more rows and more columns.Is it from each table? Yes. If you need two or more tables to show what the problem is, then post sample data for all of them.…Edit: The ROLLUP gives me the sum, but I am actually looking for the averageROLLUP works with SUM, AVG, MIN, LISTAGG and most (if not all) other aggregate functions.If you can use it with SUM, but not AVG, then post both of your queries (the one using SUM that works, and the one using AVG that doesn't).Thanks, Frank!I couldn't figure out how to do the query using PIVOT, so I actually re-wrote my query to use a WITH. The downside is that I still do not know how to get it to output the result across a date range, so I am having to do it on a per day basis instead. As for the Average, I discovered a built-in function called AVG() and I am using that without having to do a ROLLUP call.

If you'd like help, post CREATE TABLE and INSERT statements for a little sample data.

If the results you want from that data are not exactly what you posted in your original message (and clarified in reply #4), then post the desired results, too.

Forum Stats

Quick Links

Categories

Get Difference Between Timestamps Using Pivot

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Get Difference Between Timestamps Using Pivot

Best Answer

Answers