Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

SQL -Do not repeat QTD, MTD & YTD values

I have the following data:
Store      Dept            Amt            MTD            QTD            YTD
127               0            1.00            913.50      2157.18      7203.18
127               0            6.00            913.50      2157.18      7203.18
127               0            7.00            913.50      2157.18      7203.18
127               2            5.00            134.00      431.00       1298.00
129               0            13.00          155.00      661.00       3343.00
355               2            3.00            706.00      231.00       798.00
355               2            2.00            706.00      231.00       798.00

My client wants the QTD, MTD & YTD to show only on the 1st line
WHERE Store and Dept are the same as the record above it
The desired results shown below:
Store      Dept            Amt            MTD            QTD            YTD
127            0            1.00            913.50      2157.18      7203.18
127            0            6.00            0                  0                  0
127            0            7.00            0                  0                  0
127            2            5.00            134.00      431.00       1298.00
129            0            13.00          155.00      661.00       3343.00
355            2            3.00            706.00      231.00       798.00
355            2            2.00            0                  0                  0

Is there a simple sql statement that will query the top table and return the results above?
0
n2dweb
Asked:
n2dweb
1 Solution
 
cyberkiwiCommented:
select Store, Dept, Amt,
case when RN=1 then MTD else 0 end MTD,
case when RN=1 then QTD else 0 end QTD,
case when RN=1 then YTD else 0 end YTD
from
(
      select *, rn=ROW_NUMBER() over (
            partition by Store, Dept
            order by Dept, Amt) -- or whatever order you like
      from tbl
) X
0
 
n2dwebprogrammerAuthor Commented:
Thanks That Worked!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now