Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql help

Posted on 2014-07-29
7
366 Views
Last Modified: 2014-07-30
My query is my rank is not working properly.
See below my sql code.(MS Sql server 2008)
<code>
SELECT a.ID as SalesID, a.SalesMonth as SalesMonth, a.SalesValue as SalesValue,a.sitename,a.SalesType,
ranking=(SELECT Count(DISTINCT u.SalesMonth)
FROM  SalesTarget  u   WHERE u.SalesMonth<=a.SalesMonth

 and u.ID=a.ID  and u.sitename=a.sitename and u.SalesType =a.SalesType )

FROM  SalesTarget a
</code>

see sample data below

ID      SalesMonth  SalesValue      sitename SalesType                  ranking
G2      01/04/2015      8      Test1      Forecast                                            13
G2      01/05/2015      9      Test1      Forecast                                     14
G2      01/06/2015      6      Test1      Forecast                                     15
G2      01/07/2015      9      Test1      Forecast                                   16
G2      01/08/2015      7      Test1      Forecast                  17
G2      01/09/2015      8      Test1      Forecast                  18
G2      01/10/2015      10      Test1      Forecast                  19
G2      01/11/2015      9      Test1      Forecast                  20
G2      01/12/2015      8      Test1      Forecast                  21
G2      01/01/2016      7      Test1      Forecast                  22
G2      01/02/2016      4      Test1      Forecast                  23
G2      01/03/2016      5      Test1      Forecast                  24
G2      01/04/2014      4      Test2      Forecast                  1
G2      01/05/2014      6      Test2      Forecast                  2
G2      01/06/2014      3      Test2      Forecast                  3
G2      01/07/2014      5      Test2      Forecast                  4
G2      01/08/2014      8      Test2      Forecast                  5
G2      01/09/2014      8      Test2      Forecast                  6
G2      01/10/2014      8      Test2      Forecast                  7
G2      01/11/2014      8      Test2      Forecast                  8
G2      01/12/2014      8      Test2      Forecast                  9
G2      01/01/2015      8      Test2      Forecast                  10
G2      01/02/2015      1      Test2      Forecast                  11
G2      01/03/2015      8      Test2      Forecast                  12

According to above example rank is ranking if 2015,2016,2017...its continue going on

But I need
according to financial year for example
2014-4-1 - 2015-3-31 need rank 1,2,3... 12
2015-4-1 - 2016-3-31 need rank 1,2,3... 12
2016-4-1 - 2017-3-31 need rank 1,2,3... 12

Not continues counting whole years like 13,14....etc, I need to stop from 12  each end of financial year and start new financial year rank start from 1

Please see attached excel for sample data
Example-sql.xlsx
0
Comment
Question by:ukerandi
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40228117
Is this what you mean:
SELECT  a.ID AS SalesID,
        a.SalesMonth AS SalesMonth,
        a.SalesValue AS SalesValue,
        a.sitename,
        a.SalesType,
        ranking = ROW_NUMBER() OVER (PARTITION BY a.ID, a.sitename, a.SalesType, YEAR(a.SalesMonth) ORDER BY a.SalesMonth DESC)
FROM    SalesTarget a

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40228265
I would also use row_number() OR dense_rank() for this. If every row (within a "partition") must be a unique number then use row_number() otherwise if the ranking is allowed to repeat (within a "partition") then use dense_rank()

From the sample these were the results
| SALESID | SALESMONTH | SALESVALUE | SITENAME | SALESTYPE | GIVEN_RANKING | ROW_RANKING | DENSE_RANKING |
|---------|------------|------------|----------|-----------|---------------|-------------|---------------|
|      G2 | 2014-04-01 |          4 |    Test1 |  Forecast |             1 |           1 |             1 |
|      G2 | 2014-05-01 |          6 |    Test1 |  Forecast |             2 |           2 |             2 |
|      G2 | 2014-06-01 |          3 |    Test1 |  Forecast |             3 |           3 |             3 |
|      G2 | 2014-07-01 |          5 |    Test1 |  Forecast |             4 |           4 |             4 |
|      G2 | 2014-08-01 |          8 |    Test1 |  Forecast |             5 |           5 |             5 |
|      G2 | 2014-09-01 |          8 |    Test1 |  Forecast |             6 |           6 |             6 |
|      G2 | 2014-10-01 |          8 |    Test1 |  Forecast |             7 |           7 |             7 |
|      G2 | 2014-11-01 |          8 |    Test1 |  Forecast |             8 |           8 |             8 |
|      G2 | 2014-12-01 |          8 |    Test1 |  Forecast |             9 |           9 |             9 |
|      G2 | 2015-01-01 |          8 |    Test1 |  Forecast |            10 |           1 |             1 |
|      G2 | 2015-02-01 |          8 |    Test1 |  Forecast |            11 |           2 |             2 |
|      G2 | 2015-03-01 |          8 |    Test1 |  Forecast |            12 |           3 |             3 |
|      G2 | 2015-04-01 |          8 |    Test1 |  Forecast |            13 |           4 |             4 |
|      G2 | 2015-05-01 |          9 |    Test1 |  Forecast |            14 |           5 |             5 |
|      G2 | 2015-06-01 |          6 |    Test1 |  Forecast |            15 |           6 |             6 |
|      G2 | 2015-07-01 |          9 |    Test1 |  Forecast |            16 |           7 |             7 |
|      G2 | 2015-08-01 |          7 |    Test1 |  Forecast |            17 |           8 |             8 |
|      G2 | 2015-09-01 |          8 |    Test1 |  Forecast |            18 |           9 |             9 |
|      G2 | 2015-10-01 |         10 |    Test1 |  Forecast |            19 |          10 |            10 |
|      G2 | 2015-11-01 |          9 |    Test1 |  Forecast |            20 |          11 |            11 |
|      G2 | 2015-12-01 |          8 |    Test1 |  Forecast |            21 |          12 |            12 |
|      G2 | 2016-01-01 |          7 |    Test1 |  Forecast |            22 |           1 |             1 |
|      G2 | 2016-02-01 |          4 |    Test1 |  Forecast |            23 |           2 |             2 |
|      G2 | 2016-03-01 |          5 |    Test1 |  Forecast |            24 |           3 |             3 |
|      G2 | 2014-04-01 |          4 |    Test2 |  Forecast |             1 |           1 |             1 |
|      G2 | 2014-05-01 |          6 |    Test2 |  Forecast |             2 |           2 |             2 |
|      G2 | 2014-06-01 |          3 |    Test2 |  Forecast |             3 |           3 |             3 |
|      G2 | 2014-07-01 |          5 |    Test2 |  Forecast |             4 |           4 |             4 |
|      G2 | 2014-08-01 |          8 |    Test2 |  Forecast |             5 |           5 |             5 |
|      G2 | 2014-09-01 |          8 |    Test2 |  Forecast |             6 |           6 |             6 |
|      G2 | 2014-10-01 |          8 |    Test2 |  Forecast |             7 |           7 |             7 |
|      G2 | 2014-11-01 |          8 |    Test2 |  Forecast |             8 |           8 |             8 |
|      G2 | 2014-12-01 |          8 |    Test2 |  Forecast |             9 |           9 |             9 |
|      G2 | 2015-01-01 |          8 |    Test2 |  Forecast |            10 |           1 |             1 |
|      G2 | 2015-02-01 |          1 |    Test2 |  Forecast |            11 |           2 |             2 |
|      G2 | 2015-03-01 |          8 |    Test2 |  Forecast |            12 |           3 |             3 |

Open in new window

SELECT
      a.ID                                    AS SalesID
    , CONVERT(varchar(10), a.SalesMonth, 121) AS SalesMonth
    , a.SalesValue                            AS SalesValue
    , a.sitename
    , a.SalesType
    , a.ranking                               AS given_ranking
    , row_ranking = ROW_NUMBER() OVER (PARTITION BY a.SalesType, a.ID, a.sitename, YEAR(SalesMonth)
                                       ORDER BY a.SalesMonth ASC)
    , dense_ranking = DENSE_RANK() OVER (PARTITION BY a.SalesType, a.ID, a.sitename, YEAR(SalesMonth)
                                         ORDER BY a.SalesMonth ASC)
FROM SalesTarget a
ORDER BY
      ID
      , sitename
      , YEAR(SalesMonth) ASC
      , MONTH(SalesMonth) ASC
;

Open in new window

Also note it isn't exactly known if you need SalesType or ID in the "partition" or if it's SalesType then ID or vice versa
0
 
LVL 10

Author Comment

by:ukerandi
ID: 40228561
Please find attached Excel sheet, please see the expected results column, that is  a way I expecting
Results-Expected.xlsx
0
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

 
LVL 8

Expert Comment

by:Surrano
ID: 40228600
If your problem is only with the G2-Test1-Forecast ranks being greater than 12 then the problem is in input data:
ID	SalesMonth	SalesValue	sitename	SalesType	ranking
G2	2015.04.01	8	Test1	Forecast	13
G2	2015.05.01	9	Test1	Forecast	14
G2	2015.06.01	6	Test1	Forecast	15
G2	2015.07.01	9	Test1	Forecast	16
G2	2015.08.01	7	Test1	Forecast	17
G2	2015.09.01	8	Test1	Forecast	18
G2	2015.10.01	10	Test1	Forecast	19
G2	2015.11.01	9	Test1	Forecast	20
G2	2015.12.01	8	Test1	Forecast	21
G2	2016.01.01	7	Test1	Forecast	22
G2	2016.02.01	4	Test1	Forecast	23
G2	2016.03.01	5	Test1	Forecast	24
G2	2014.04.01	4	Test1	Forecast	1
G2	2014.05.01	6	Test1	Forecast	2
G2	2014.06.01	3	Test1	Forecast	3
G2	2014.07.01	5	Test1	Forecast	4
G2	2014.08.01	8	Test1	Forecast	5
G2	2014.09.01	8	Test1	Forecast	6
G2	2014.10.01	8	Test1	Forecast	7
G2	2014.11.01	8	Test1	Forecast	8
G2	2014.12.01	8	Test1	Forecast	9
G2	2015.01.01	8	Test1	Forecast	10
G2	2015.02.01	8	Test1	Forecast	11
G2	2015.03.01	8	Test1	Forecast	12

Open in new window


(Sorry for date format, it's YYYY-MM-DD in my regional settings)
Without the definition of the fiscal year, how can you tell when the fiscal year starts, i.e. what's number 1?
Also, this approach is fairly dangerous. What if a record is missing? Say, no entry for 2015.01.01 then it'd look like:

G2      2014.11.01      8      Test1      Forecast      8
G2      2014.12.01      8      Test1      Forecast      9
G2      2015.02.01      8      Test1      Forecast      10
G2      2015.03.01      8      Test1      Forecast      11

which is probably not what you want.

If you have a strong grip on the fiscal year always starting on 1st of April then why not extract the date and translate it into a rank?
mod(datepart(month, a.SalesMonth)+8,12)+1

Open in new window

This translates the date as follows: April = 1, May = 2, ... December = 9, January = 10, ...March = 12.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40228619
I see, FISCAL year. Try this:
| SALESID | SALESMONTH | SALESVALUE | SITENAME | SALESTYPE | RANKING |
|---------|------------|------------|----------|-----------|---------|
|      G2 | 2014-04-01 |          4 |    Test1 |  Forecast |       1 |
|      G2 | 2014-05-01 |          6 |    Test1 |  Forecast |       2 |
|      G2 | 2014-06-01 |          3 |    Test1 |  Forecast |       3 |
|      G2 | 2014-07-01 |          5 |    Test1 |  Forecast |       4 |
|      G2 | 2014-08-01 |          8 |    Test1 |  Forecast |       5 |
|      G2 | 2014-09-01 |          8 |    Test1 |  Forecast |       6 |
|      G2 | 2014-10-01 |          8 |    Test1 |  Forecast |       7 |
|      G2 | 2014-11-01 |          8 |    Test1 |  Forecast |       8 |
|      G2 | 2014-12-01 |          8 |    Test1 |  Forecast |       9 |
|      G2 | 2015-01-01 |          8 |    Test1 |  Forecast |      10 |
|      G2 | 2015-02-01 |          8 |    Test1 |  Forecast |      11 |
|      G2 | 2015-03-01 |          8 |    Test1 |  Forecast |      12 |
|      G2 | 2015-04-01 |          8 |    Test1 |  Forecast |       1 |
|      G2 | 2015-05-01 |          9 |    Test1 |  Forecast |       2 |
|      G2 | 2015-06-01 |          6 |    Test1 |  Forecast |       3 |
|      G2 | 2015-07-01 |          9 |    Test1 |  Forecast |       4 |
|      G2 | 2015-08-01 |          7 |    Test1 |  Forecast |       5 |
|      G2 | 2015-09-01 |          8 |    Test1 |  Forecast |       6 |
|      G2 | 2015-10-01 |         10 |    Test1 |  Forecast |       7 |
|      G2 | 2015-11-01 |          9 |    Test1 |  Forecast |       8 |
|      G2 | 2015-12-01 |          8 |    Test1 |  Forecast |       9 |
|      G2 | 2016-01-01 |          7 |    Test1 |  Forecast |      10 |
|      G2 | 2016-02-01 |          4 |    Test1 |  Forecast |      11 |
|      G2 | 2016-03-01 |          5 |    Test1 |  Forecast |      12 |
|      G2 | 2014-04-01 |          4 |    Test2 |  Forecast |       1 |
|      G2 | 2014-05-01 |          6 |    Test2 |  Forecast |       2 |
|      G2 | 2014-06-01 |          3 |    Test2 |  Forecast |       3 |
|      G2 | 2014-07-01 |          5 |    Test2 |  Forecast |       4 |
|      G2 | 2014-08-01 |          8 |    Test2 |  Forecast |       5 |
|      G2 | 2014-09-01 |          8 |    Test2 |  Forecast |       6 |
|      G2 | 2014-10-01 |          8 |    Test2 |  Forecast |       7 |
|      G2 | 2014-11-01 |          8 |    Test2 |  Forecast |       8 |
|      G2 | 2014-12-01 |          8 |    Test2 |  Forecast |       9 |
|      G2 | 2015-01-01 |          8 |    Test2 |  Forecast |      10 |
|      G2 | 2015-02-01 |          1 |    Test2 |  Forecast |      11 |
|      G2 | 2015-03-01 |          8 |    Test2 |  Forecast |      12 |
		

Open in new window

SELECT
      a.ID                                    AS SalesID
    , CONVERT(varchar(10), a.SalesMonth, 121) AS SalesMonth
    , a.SalesValue                            AS SalesValue
    , a.sitename
    , a.SalesType
    , ranking = ROW_NUMBER() OVER (PARTITION BY a.SalesType, a.ID, a.sitename, YEAR(dateadd(month,-3,salesmonth))
                                       ORDER BY a.SalesMonth ASC)
FROM SalesTarget a
ORDER BY
      ID
      , sitename
      , YEAR(SalesMonth) ASC
      , MONTH(SalesMonth) ASC
;

http://sqlfiddle.com/#!3/844bd/4

Open in new window

0
 
LVL 10

Author Closing Comment

by:ukerandi
ID: 40228732
Great,very logical
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40230695
You are welcome.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

792 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