Solved

sql help

Posted on 2014-07-29
7
361 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
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.
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

757 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

24 Experts available now in Live!

Get 1:1 Help Now