Any one help me to solve this i try my best but did not solve this?

Question

ItemName    Price   CreatedDateTime  
New Card    50.00   2014-05-26 19:17:09.987  
Recharge    110.00  2014-05-26 19:17:12.427  
Promo       90.00   2014-05-27 16:17:12.427  
Membership  70.00   2014-05-27 16:17:12.427  
New Card    50.00   2014-05-26 19:20:09.987

Out Put : Need a query which Sum the sale of Current hour and sale of item which have maximum sale in that hour in breakdownofSale Column.

Hour    SaleAmount  BreakDownOfSale  
19      210         Recharge  
16      160         Promo

You may need to take a look at [ask], [tour], [formatting](/help/formatting), etc. — fedorqui, Jan 20 '17 at 10:28
Current hour means hour of that particular day, right? If yes, then date also should be considered. — Aasish Kr. Sharma, Jan 20 '17 at 11:09

score 0 · Accepted Answer · answered Jan 20 '17 at 10:41

This should do it

create table #t
(
    ItemName varchar(50),
    Price decimal(18,2),
    CreatedDateTime datetime
);

set dateformat ymd;

insert into #t values('New Card', 50.00, '2014-05-26 19:17:09.987');
insert into #t values('Recharge', 110.00, '2014-05-26 19:17:12.427');
insert into #t values('Promo', 90.00, '2014-05-27 16:17:12.427');
insert into #t values('Membership', 70.00, '2014-05-27 16:17:12.427');
insert into #t values('New Card', 50.00, '2014-05-26 19:20:09.987');

with cte as
(
    select datepart(hh, CreatedDateTime) as [Hour], 
        ItemName,
        Price,
        sum(Price) over (partition by datepart(hh, CreatedDateTime)) SaleAmount,
        ROW_NUMBER() over (partition by datepart(hh, CreatedDateTime) order by Price desc) rn
    from #t
)
select Hour, 
    SaleAmount,
    ItemName
from cte
where rn = 1

Thanks a lot @James Casey – farooq shahid Jan 20 '17 at 11:00 — farooq shahid, Jan 20 '17 at 11:00

score 0 · Answer 2 · answered Jan 20 '17 at 10:42

Though i am not clear with the question, based on your desired output, you may use the query as below.

SELECT DATEPART(HOUR,CreatedDateTime) AS Hour, sum(Price) AS Price, ItemName AS BreakDownOfSale  from TableName WHERE  BY ItemName,DATEPART(HOUR,CreatedDateTime)

Replace table name and column name with the actual one.

Hope this helps!

Aasish Kr. Sharma · Answer 3 · 2017-01-20T11:06:07.633

Here is the sample query.

You can use SQL Server Windows functions to get the result you need.

DECLARE @Table TABLE
(
    ItemName NVARCHAR(40),
    Price DECIMAL(10,2),
    CreatedDatetime DATETIME
)

-- Fill table.
INSERT INTO @Table
    ( ItemName, Price, CreatedDatetime )
VALUES           
    ( N'New Card'  ,  50.00 ,  '2014-05-26 19:17:09.987'   ),
    ( N'Recharge'  ,  110.00 , '2014-05-26 19:17:12.427' )  ,
    ( N'Promo'     ,  90.00 ,  '2014-05-27 16:17:12.427' ) , 
    ( N'Membership' , 70.00  , '2014-05-27 16:17:12.427' ) , 
    ( N'New Card'  ,  50.00 ,  '2014-05-26 19:20:09.987' )


-- Check record(s).
SELECT * FROM @Table

-- Get record(s) in required way.
;WITH T1 AS 
(
    SELECT 
        DATEPART(HOUR, T.CreatedDatetime) AS Hour, 
        CONVERT(DATE, T.CreatedDatetime) AS Date,
        T.ItemName AS BreakDownOfSales, 
        -- Date and hour both will give unique record(s)
        SUM(Price) OVER (PARTITION BY CONVERT(DATE, T.CreatedDatetime), DATEPART(HOUR, CreatedDateTime)) AS SaleAmount, 
        ROW_NUMBER() OVER(PARTITION BY CONVERT(DATE, T.CreatedDatetime), DATEPART(HOUR, T.CreatedDatetime) ORDER BY T.Price DESC) AS RN
    FROM 
        @Table T
) 
SELECT 
    T1.Date ,
    T1.Hour ,
    T1.SaleAmount,
    T1.BreakDownOfSales 
FROM 
    T1 
WHERE T1. RN = 1
ORDER BY 
    T1.Hour

score 0 · Answer 4 · answered Jan 20 '17 at 11:13

Check this simple solution, Please convert it to SQL Server Query.

This will give you perfect result even if you have multiple date data.

SELECT HOUR(CreatedDateTime), SUM(Price), 
    (SELECT itemname FROM t it WHERE HOUR(ot.CreatedDateTime) = HOUR(it.CreatedDateTime) AND 
        DATE(ot.CreatedDateTime) = DATE(it.CreatedDateTime) 
        GROUP BY itemname
        ORDER BY price DESC
        LIMIT 1
    ) g
FROM t ot
GROUP BY HOUR(CreatedDateTime);

Any one help me to solve this i try my best but did not solve this?

4 Answers4