August 27, 2014 at 6:48 am
Hi All
I have been going crazy with this problem and am now hoping someone can help me.
I have a table CombinedSales script below (CreateCombSalesTable.sql) which contains the details of a sale.
A row in the table has two flags online/offline
SalesAmount is the financial amount,
online = 1 (online sales)
online = 0 (combined sales off and online).
The table also contains the product and the client etc.,
What I am trying to do is subtract onlinesales from combinedsales within the query or outside I don't mind, to return the result as (below)
HERE is HOW I want the results to appear
skuid(No column name) onlineClientName
200437215183.350Asda
20043716816.651Asda
I query the table using:
select skuid,SUM(SalesAmount),online,ClientName from t_CombinedSalesDetails
where ClientName ='Asda'
and SkuId = 200437
group by Skuid,Online,ClientName
order by skuid
THIS is what I get below the online = 0 is the total sales of this product.
skuid(No column name) onlineClientName
200437232000.000Asda
20043716816.651Asda
Does anyone have any idea how I could achieve the required results
Many thanks
Paul
/****** Object: Table [dbo].[t_CombinedSalesDetails] Script Date: 27/08/2014 13:23:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_CombinedSalesDetails](
[SkuId] [int] NULL,
[Period] [datetime] NULL,
[SalesAmount] [money] NULL,
[aClientId] [int] NULL,
[Online] [bit] NOT NULL,
[ClientName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INto t_CombinedSalesDetails
(SkuId,Period,SalesAmount,Online,ClientName)
VALUES
(200437,'2014-01-25 00:00:00.000',232000.00,0,'Asda')
INSERT INto t_CombinedSalesDetails
(SkuId,Period,SalesAmount,Online,ClientName)
VALUES
(200437,'2014-01-25 00:00:00.000',16816.65,1,'Asda')
August 27, 2014 at 7:08 am
SELECT skuid,
SUM(CASE WHEN Online = 1 THEN -SalesAmount
ELSE SalesAmount
END) AS [Both],
SUM(CASE WHEN Online = 1 THEN SalesAmount
ELSE 0
END) AS [Online],
ClientName
FROM t_CombinedSalesDetails
WHERE ClientName = 'Asda'
AND SkuId = 200437
GROUP BY Skuid,
ClientName
ORDER BY skuid;
Returns: -
skuid Both Online ClientName
----------- --------------------- --------------------- --------------------------------------------------
200437 215183.35 16816.65 Asda
Or. . .
SELECT a.skuid,
SUM(a.SalesAmount - ISNULL(b.SalesAmount, 0)),
a.Online,
a.ClientName
FROM t_CombinedSalesDetails a
LEFT OUTER JOIN t_CombinedSalesDetails b ON a.SkuId = b.SkuId
AND a.Online < b.Online
WHERE a.ClientName = 'Asda'
AND a.SkuId = 200437
GROUP BY a.Skuid,
a.ClientName,
a.Online
ORDER BY a.skuid;
Returns: -
skuid Online ClientName
----------- --------------------- ------ --------------------------------------------------
200437 215183.35 0 Asda
200437 16816.65 1 Asda
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply