Solved

SQL -Do not repeat QTD, MTD & YTD values

Posted on 2010-11-15
2
908 Views
Last Modified: 2012-05-10
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
Comment
Question by:n2dweb
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34140329
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
 
LVL 1

Author Comment

by:n2dweb
ID: 34140478
Thanks That Worked!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question