Solved

SQL -Do not repeat QTD, MTD & YTD values

Posted on 2010-11-15
2
898 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now