sql help

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
LVL 10
ukerandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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
PortletPaulfreelancerCommented:
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
ukerandiAuthor Commented:
Please find attached Excel sheet, please see the expected results column, that is  a way I expecting
Results-Expected.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SurranoSystem EngineerCommented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ukerandiAuthor Commented:
Great,very logical
0
Anthony PerkinsCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.