June 29, 2011 at 2:26 pm
Hi Guru,
How can I write a case statement to substract these below dates to be one singe value?
DistributedDate
-------------------
2011-05-16 23:50:14.080
2011-05-16 23:50:32.407
2011-05-16 23:54:10.883
2011-05-17 00:14:12.333
2011-05-17 00:47:27.463
2011-05-17 00:50:32.757
Here is my query:
select CONVERT(VARCHAR(20), DistributedDate, 112) from FROM [Fact].[OrderDetails] S with(nolock)where CustomerWID =2034
and DistributedDate>='2011-05-16 9:00:00.000'
and DistributedDate<'2011-05-17 9:00:00.000'
group by CONVERT(VARCHAR(20), DistributedDate, 112)
It gives me result below:
20110516
20110517
I need only one date value (20110516) by substracting 20110517 to 20110516.
Thanks much,
Attopeu
June 29, 2011 at 3:01 pm
Attopeu (6/29/2011)
Hi Guru,How can I write a case statement to substract these below dates to be one singe value?
DistributedDate
-------------------
2011-05-16 23:50:14.080
2011-05-16 23:50:32.407
2011-05-16 23:54:10.883
2011-05-17 00:14:12.333
2011-05-17 00:47:27.463
2011-05-17 00:50:32.757
Here is my query:
select CONVERT(VARCHAR(20), DistributedDate, 112) from FROM [Fact].[OrderDetails] S with(nolock)where CustomerWID =2034
and DistributedDate>='2011-05-16 9:00:00.000'
and DistributedDate<'2011-05-17 9:00:00.000'
group by CONVERT(VARCHAR(20), DistributedDate, 112)
It gives me result below:
20110516
20110517
I need only one date value (20110516) by substracting 20110517 to 20110516.
Thanks much,
Attopeu
Can you help me understand what you mean by this?
I need only one date value (20110516) by substracting 20110517 to 20110516.
Here is test bed code for others dropping by:
IF OBJECT_ID(N'tempdb..#tmp') > 0
DROP TABLE #tmp ;
GO
CREATE TABLE #tmp
(
DistributedDate DATETIME
) ;
GO
INSERT INTO #tmp
(DistributedDate)
VALUES ('2011-05-16 23:50:14.080'),
('2011-05-16 23:50:32.407'),
('2011-05-16 23:54:10.883'),
('2011-05-17 00:14:12.333'),
('2011-05-17 00:47:27.463'),
('2011-05-17 00:50:32.757') ;
GO
--Here is my query:
SELECT CONVERT(VARCHAR(20), DistributedDate, 112)
FROM #tmp S
WHERE DistributedDate >= '2011-05-16 9:00:00.000'
AND DistributedDate < '2011-05-17 9:00:00.000'
GROUP BY CONVERT(VARCHAR(20), DistributedDate, 112) ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 3:08 pm
You are converting the dates which are near midnight and after to whole dates and then grouping.
The results are whats to be expected, two dates in yyyymmdd format.
I guess my first question is what is the purpose of the query?
Why do you want the two dates to be represented as 20110516?
You could always use the TOP 1 after the select to always return the earliest date.
- John
John Miner
Crafty DBA
www.craftydba.com
June 30, 2011 at 6:47 am
DistributedDate OrderNumber
---------------------------------
2011-05-16 23:50:14.0801.00
2011-05-16 23:50:32.4070.00
2011-05-16 23:54:10.8831.00
2011-05-17 00:14:12.3331.00
2011-05-17 00:47:27.4631.00
2011-05-17 00:50:32.7571.00
Here is my query:
select CONVERT(VARCHAR(20), DistributedDate, 112),SUM(OrderNumber) AS TotalOrders from FROM [Fact].[OrderDetails] S with(nolock)where CustomerWID =2034
and DistributedDate>='2011-05-16 9:00:00.000'
and DistributedDate<'2011-05-17 9:00:00.000'
group by CONVERT(VARCHAR(20), DistributedDate, 112)
Here is what I really want the result:
DistributedDate TotalOrders
------------------------------
20110516 5
It looks like I really a CASE statement to get the above results.
Please help.
Thanks much,
Attopeu
June 30, 2011 at 7:21 am
CREATE SCHEMA Fact
CREATE TABLE OrderDetails
(
CustomerWIDINTEGER NOT NULL,
DistributedDateDATETIME NOT NULL,
OrderNumberDECIMAL(5,2) NOT NULL
);
GO
INSERT Fact.OrderDetails
(CustomerWID, DistributedDate, OrderNumber)
VALUES
(2034, '2011-05-16 23:50:14.080' , 1.00),
(2034, '2011-05-16 23:50:32.407' , 0.00),
(2034, '2011-05-16 23:54:10.883' , 1.00),
(2034, '2011-05-17 00:14:12.333' , 1.00),
(2034, '2011-05-17 00:47:27.463' , 1.00),
(2034, '2011-05-17 00:50:32.757' , 1.00);
SELECT
Adjusted.DateValue,
TotalOrders = SUM(od.OrderNumber)
FROM Fact.OrderDetails AS od
CROSS APPLY
(
SELECT
CONVERT(DATE, DATEADD(HOUR, -9, od.DistributedDate))
) AS Adjusted (DateValue)
WHERE
od.DistributedDate >= '2011-05-16 9:00:00.000'
AND od.DistributedDate <'2011-05-17 9:00:00.000'
GROUP BY
Adjusted.DateValue;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 8:51 am
Nice example Crazy Eights,
I guess my question is why use the cross apply?
Is there any advantage to using that statement?
I turned on statistics for both your query and mine below, both have the same timing.
I look at the plans via the graphic edit, they seem to be the same.
- John
-- Get time & io
SET STATISTICS IO ON
SET STATISTICS TIME ON
-- Used to get plans as xml
SET SHOWPLAN_XML ON;
SET SHOWPLAN_XML OFF;
-- Same query w/o cross apply
SELECT
CONVERT(DATE, DATEADD(HOUR, -9, O.DistributedDate)) AS AdjustedDate,
SUM(O.OrderNumber) AS TotalOrders
FROM
SandBox2.Fact.OrderDetails AS O
WHERE
O.DistributedDate >= '2011-05-16 9:00:00.000' AND
O.DistributedDate < '2011-05-17 9:00:00.000'
GROUP BY
CONVERT(DATE, DATEADD(HOUR, -9, O.DistributedDate))
Table 'OrderDetails'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
John Miner
Crafty DBA
www.craftydba.com
June 30, 2011 at 8:58 am
Hi,
I just use the apply to avoid having to duplicate the expression in the GROUP BY. It's one of the examples from my SSC article on APPLY. There's no performance advantage, and it results in the same query plan...it's just neater, at least I think it is 🙂
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 9:16 am
Hi Paul,
Thanks so much for answering my question. However, It works perfectly on SQL2008 but not sql2005. Is that possible for you to rewrite it to work for SQL2005?
Thanks again.
Attopeu
June 30, 2011 at 9:48 am
Hi Paul,
Don't worry about it and I got it working now.
Thanks again.
Attopeu
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply