x

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

more ▼

asked Sep 12 at 07:42 AM in Default

avatar image

S9844673241
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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 <<yourtablenamehere>>
    )
 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;
more ▼

answered Sep 12 at 08:23 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x68

asked: Sep 12 at 07:42 AM

Seen: 22 times

Last Updated: Sep 13 at 02:29 PM

Copyright 2017 Redgate Software. Privacy Policy