asked
kindly help me to get the following result
Hi, my table data: Date Code open in out close 07/04/2017 1001 1 2 2 1 08/07/2017 1001 1 4 2 3 08/08/2017 1001 3 0 1 2 07/04/2017 1002 1 2 3 0 08/07/2017 1002 0 4 2 2 08/08/2017 1002 2 0 3 -1 i want the result like Code open in out close 1001 1 6 5 2 1002 1 6 8 -1 open=Opening in min date in=sum of in out=sum of out close=closing of max date
Well, you could try something like this: WITH d AS (SELECT Date, Code, [open], [in], out, [close], --these next two give us the ability to identify particular date rows in a single group by clause ROW_NUMBER() OVER (PARTITION BY [Code] ORDER BY [Date]) AS odt, ROW_NUMBER() OVER (PARTITION BY [Code] ORDER BY [Date] DESC) AS cdt FROM ) SELECT Code, MAX( CASE WHEN odt = 1 THEN d.[open] ELSE NULL END ) AS [open], SUM(d.[in]) AS [in], SUM(d.[out]) AS [out], MAX( CASE WHEN cdt = 1 THEN [close] ELSE NULL END ) AS [close] FROM d GROUP BY d.Code;
17 People are following this question.
