September 22, 2014 at 4:05 pm
Hi i need help in generating a weekely trading report per trader ,
1. We have a trading table which reords TraderID and trading date
2. I Need to Generate a trading report and disaplay trading details of each user by weekly
a) If user trades in any day of a week mark that Week as "Yes" else "No"
3. I'm attaching the sample Data and expected results of my query.
Any help will be truely appriciated
CREATE TABLE #Trade
(
Trader INT
,TradingDate DATETIME
)
INSERT INTO #Trade(Trader,TradingDate)
VALUES ('200306',GETDATE())
,('200306',GETDATE()+ 8)
,('200306',GETDATE()+15)
,('200306',GETDATE()+24)
,('200306',GETDATE()+28)
,('200286',GETDATE()+20)
,('200286',GETDATE()+28)
SELECT 200306 AS Trader ,'Yes' AS '22-SEP', 'Yes' AS '15-SEP' , 'Yes' AS '08-SEP', 'Yes' AS '01-SEP', 'Yes' AS '25-AUG'from #Trade
UNION
SELECT 200286 AS Trader ,'No' AS '22-SEP', 'No' AS '15-SEP' , 'No' AS '08-SEP', 'Yes' AS '01-SEP', 'Yes' AS '25-AUG'from #Trade
DROP table #Trade
September 23, 2014 at 11:05 am
So you need to pivot the data based on the date. I'm also assuming you need a dynamic pivot because you won't know the dates when the code is run, like you do now. Here's some code that does what you have asked (I didn't format the date dd-mmm, but I think you should be able to do that if you really need it that way):
CREATE TABLE #Trade
(
Trader INT,
TradingDate DATETIME
)
INSERT INTO #Trade
(Trader, TradingDate)
VALUES
('200306', GETDATE())
,
('200306', GETDATE() - 8)
,
('200306', GETDATE() - 15)
,
('200306', GETDATE() - 24)
,
('200306', GETDATE() - 28)
,
('200286', GETDATE() - 24)
,
('200286', GETDATE() - 28)
SELECT
200306 AS Trader,
'Yes' AS '22-SEP',
'Yes' AS '15-SEP',
'Yes' AS '08-SEP',
'Yes' AS '01-SEP',
'Yes' AS '25-AUG'
FROM
#Trade
UNION
SELECT
200286 AS Trader,
'No' AS '22-SEP',
'No' AS '15-SEP',
'No' AS '08-SEP',
'Yes' AS '01-SEP',
'Yes' AS '25-AUG'
FROM
#Trade
/* Here's the code based on Jeff Moden's Dynamic Cross Tab Article (http://www.sqlservercentral.com/articles/Crosstab/65048/) which
is also a link in my signature */
DECLARE
@sql1 NVARCHAR(1000),
@sql2 NVARCHAR(2000),
@sql3 NVARCHAR(1000),
@completeSQL NVARCHAR(4000)
SET @sql1 = N'SELECT
theData.Trader,' + NCHAR(10)
SET @sql3 = N'FROM
(
SELECT
Trader,
LEFT(CONVERT(VARCHAR(25), TradingDate, 107), 6) AS theDay
FROM
#Trade AS T
) AS theData
GROUP BY
theData.Trader
ORDER BY
theData.Trader DESC ;'
SELECT DISTINCT
LEFT(CONVERT(NVARCHAR(25), TradingDate, 107), 6) AS theDate
FROM
#Trade AS T
SELECT
@sql2 = ISNULL(@sql2, '') + N'CASE WHEN SUM(CASE WHEN theData.theDay = ' +
QUOTENAME(T.theDate, '''') + N' THEN 1
ELSE 0 ' + N' END) > 0 THEN ''Yes''
ELSE ''No''
END AS [' + T.theDate + N'], ' + NCHAR(10)
FROM
(
SELECT DISTINCT
LEFT(CONVERT(NVARCHAR(25), TradingDate, 107), 6) AS theDate
FROM
#Trade AS T
) AS T ORDER BY theDate DESC;
SET @completeSQL = @sql1 + LEFT(@sql2, LEN(@sql2) - 3) + @sql3;
EXEC sys.sp_executesql @completeSQL;
DROP TABLE #Trade
I had to change your source data to subtracting days from GETDATE() in order to almost match your expected output. The dates vary slightly because I'm running this the next day after you posted.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2014 at 3:09 pm
Hi Jack ,
Thank you very much for your response , your example solved my issue
September 23, 2014 at 8:48 pm
ramineni.suresh661 (9/23/2014)
Hi Jack ,Thank you very much for your response , your example solved my issue
Do you understand how it works so you can support it in the future?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2014 at 12:05 pm
Jeff Moden (9/23/2014)
ramineni.suresh661 (9/23/2014)
Hi Jack ,Thank you very much for your response , your example solved my issue
Do you understand how it works so you can support it in the future?
This is a serious question that Jeff is asking as code like his this can be almost impossible to maintain if not fully understood:-D
Humour aside, do you need a dynamic output structure or a fixed calendar?
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply