?
Solved

sql help

Posted on 2014-07-29
7
Medium Priority
?
377 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
[X]
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
  • 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 49

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 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