on 01-02-2017 7:39 AM
Dear Experts,
To know order wise GP report I am using following query.
SELECT
T0.[DocDate]As 'Order Date',
T0.[U_VSPORDSR] As 'EyeCare No',
T0.[DocNum] As ' SO No',
T0.[NumAtCard] As 'Job No',
T0.[CardCode] As'Customer ID',
Sum(T1.[OpenSum]) AS 'Total Before Disc & Tax',
Sum(T1.[LineVat]) AS 'Tax(INR)',
T0.[DiscSum] AS 'Discount (INR)',
Sum(T1.[StockValue]) AS 'COGS Value',
(Sum(T1.[OpenSum]) - (T0.[DiscSum])) As 'Sales Amount (INR)',
(Sum(T1.[OpenSum]) - (T0.[DiscSum])) - Sum(T1.[StockValue]) As 'Gross Profit',
(Sum(T1.[OpenSum]) - (T0.[DiscSum]) - Sum(T1.[StockValue])) / (Sum(T1.[OpenSum]) - T0.[DiscSum]) * 100 As 'GP%'
FROM [Assalama_Institute_Calicut].[dbo].[ORDR] T0 INNER JOIN [Assalama_Institute_Calicut].[dbo].[RDR1] T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] >= '[%0]' AND T0.[DocDate] <= '[%1]' AND T1.[WhsCode] = 'ALIOCAL'
GROUP BY T0.[DocDate], T0.[U_VSPORDSR], T0.[DocNum], T0.[NumAtCard], T0.[CardCode],T0.[DiscSum] ORDER BY T0.[DocDate]
In this query I am getting detailed report with each order.
Now I wand to get consolidated report for a period.
Eg: From Date : 01-04-16 To Date : 31-12-2016
Total Discount Total Tax Total COGS Value Total Sales Value Total Gross Profit GP %
2500.00 196939.87 1844376.23 3938797.38 2094421.15 53.17
How to get this kind of consolidated report using query?
Hi,
Since I am not having the UDF's or associated values in the DEMO database, tried the following. Check if it works as expected:
SELECT
Sum(T0.[DiscSum]) AS 'Total Discount (INR)', Sum(T1.[LineVat]) AS 'Total Tax(INR)',
Sum(T1.[StockValue]) AS 'Total COGS Value', (Sum(Sum(T1.[OpenSum])) - (sum(T0.[DiscSum]))) As 'Total Sales Amount (INR)', (Sum(sum(T1.[OpenSum])) - (sum(T0.[DiscSum]))) - Sum(T1.[StockValue]) As 'Total Gross Profit',
(Sum(sum(T1.[OpenSum])) - (sum(T0.[DiscSum])) - Sum(T1.[StockValue])) / (Sum(T1.[OpenSum]) - sum(T0.[DiscSum])) * 100 As 'GP%'
FROM [Assalama_Institute_Calicut].[dbo].[ORDR] T0
INNER JOIN [Assalama_Institute_Calicut].[dbo].[RDR1] T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] >= '[%0]' AND T0.[DocDate] <= '[%1]'
AND T1.[WhsCode] = 'ALIOCAL'
ORDER BY T0.[DocDate]
Regards,
Jitin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Mr.Jitin for your comment.
While executing the query I am getting following error message.
1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'User-Defined Values' (CSHS) (s) c
User | Count |
---|---|
96 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.