April 14, 2016 at 8:17 am
Hi All,
Me again.
Right, I am determined to do this right this time.
So this is the data that I have in my table:
DROP TABLE mytable;
CREATE TABLE mytable(
No_of_Applicants INTEGER NOT NULL PRIMARY KEY
,No_of_Applications INTEGER NOT NULL
,Month INTEGER NOT NULL
,Year INTEGER NOT NULL
,Application_Bracket_Total VARCHAR(6) NOT NULL
);
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (78343,78343,4,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (25280,50560,4,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (12386,37158,4,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (7225,28900,4,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (4768,23840,4,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (8999,64459,4,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (174,30215,4,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5758,75925,4,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (2349,68455,4,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (430,28821,4,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (125108,125108,5,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (42602,85204,5,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (21756,65268,5,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (13095,52380,5,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (8560,42800,5,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (16729,119701,5,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (413,81426,5,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (11181,148033,5,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (4782,137328,5,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (938,62436,5,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (133118,133118,6,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (44888,89776,6,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (22759,68277,6,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (13614,54456,6,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (8999,44995,6,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (17562,126098,6,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (379,76139,6,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (11803,156223,6,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5114,148804,6,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (1026,68356,6,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (138116,138116,7,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (46136,92272,7,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (23430,70290,7,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (13853,55412,7,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (9237,46185,7,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (17952,128572,7,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (380,73489,7,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (12126,160003,7,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5173,150772,7,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (1031,68689,7,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (131139,131139,8,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (44515,89030,8,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (22418,67254,8,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (13453,53812,8,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (8840,44200,8,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (17218,123170,8,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (363,73641,8,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (11297,149504,8,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (4925,142938,8,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (941,62194,8,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (144881,144881,9,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (49386,98772,9,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (24738,74214,9,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (14772,59088,9,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (9609,48045,9,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (18788,134439,9,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (392,78899,9,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (12173,160369,9,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5264,152308,9,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (989,65838,9,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (143553,143553,10,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (49040,98080,10,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (24696,74088,10,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (14778,59112,10,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (9671,48355,10,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (18370,131479,10,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (399,80952,10,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (12146,160067,10,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5268,152178,10,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (970,64503,10,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (131462,131462,11,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (44832,89664,11,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (22503,67509,11,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (13254,53016,11,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (8763,43815,11,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (16306,116457,11,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (347,66658,11,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (10534,138843,11,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (4442,128182,11,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (872,57742,11,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (103392,103392,12,2015,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (34748,69496,12,2015,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (17601,52803,12,2015,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (10417,41668,12,2015,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (6956,34780,12,2015,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (12754,91109,12,2015,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (232,42558,12,2015,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (8274,109186,12,2015,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (3425,98377,12,2015,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (602,40337,12,2015,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (159749,159749,1,2016,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (55875,111750,1,2016,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (28577,85731,1,2016,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (17084,68336,1,2016,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (11337,56685,1,2016,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (21528,153912,1,2016,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (418,82570,1,2016,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (14109,185948,1,2016,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5883,170411,1,2016,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (1160,77163,1,2016,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (148496,148496,2,2016,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (52299,104598,2,2016,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (26402,79206,2,2016,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (15842,63368,2,2016,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (10556,52780,2,2016,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (20288,145033,2,2016,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (377,68721,2,2016,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (13294,176249,2,2016,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5423,157141,2,2016,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (1116,74540,2,2016,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (147464,147464,3,2016,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (51740,103480,3,2016,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (26738,80214,3,2016,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (15766,63064,3,2016,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (10404,52020,3,2016,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (20205,144715,3,2016,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (455,81359,3,2016,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (13062,172418,3,2016,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5567,160993,3,2016,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (1156,77491,3,2016,'50-100');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (82185,82185,4,2016,'01');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (25752,51504,4,2016,'02');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (12351,37053,4,2016,'03');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (7136,28544,4,2016,'04');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (4626,23130,4,2016,'05');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (8496,60526,4,2016,'06-10');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (114,19134,4,2016,'100+');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (5183,67974,4,2016,'10-20');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (1948,55633,4,2016,'20-50');
INSERT INTO mytable(No_of_Applicants,No_of_Applications,Month,Year,Application_Bracket_Total) VALUES (350,23204,4,2016,'50-100');
My query that I am running is:
with cte as (select a.applicant_id as [Applicant ID],
--count (distinct a.vac_ref) as [No Of Jobs Applied For]
count (distinct a.APPLICATION_ID) as [No Of Applications],
month(a.CREATE_DATE) as [Month],
year(a.create_date) as [Year]
from application as a
where a.CREATE_DATE >= DATEADD(year,-1,current_timestamp)
--and a.AGENCY_CC = 'uk'
and a.APPLICATION_SITE_CC = 'uk'
and a.IS_JOBGATE = 0
and a.IS_LINK_FINANCE = 0
group by a.APPLICANT_ID,month(a.CREATE_DATE) ,
year(a.create_date)
)
select count(distinct c.[Applicant ID]) as [No of Applicants], sum(c.[No Of Applications]) as [No of Applications], c.Month, c.Year,
case when [No Of Applications] = '1' then '01'
when [No Of Applications] = '2' then '02'
when [No Of Applications] = '3' then '03'
when [No Of Applications] = '4' then '04'
when [No Of Applications] = '5' then '05'
when [No Of Applications] >= '6' and [No Of Applications] < '10' then '06-10'
when [No Of Applications] >= '10' and [No Of Applications] < '20' then '10-20'
when [No Of Applications] >= '20' and [No Of Applications] < '50' then '20-50'
when [No Of Applications] >= '50' and [No Of Applications] < '100' then '50-100'
else '100+' end as [Application Bracket Total]
from cte c
group by c.Month, c.Year,
case when [No Of Applications] = '1' then '01'
when [No Of Applications] = '2' then '02'
when [No Of Applications] = '3' then '03'
when [No Of Applications] = '4' then '04'
when [No Of Applications] = '5' then '05'
when [No Of Applications] >= '6' and [No Of Applications] < '10' then '06-10'
when [No Of Applications] >= '10' and [No Of Applications] < '20' then '10-20'
when [No Of Applications] >= '20' and [No Of Applications] < '50' then '20-50'
when [No Of Applications] >= '50' and [No Of Applications] < '100' then '50-100'
else '100+' end
order by
c.Year, c.Month,
5
So what I want to do is order by the order of the case statement. I have tried adding leading zeroes to the single numbers in the Application bracket field but this has not worked. I also tried row_number but it didn't like it (as I thought if I could row number it then I could order it all by that.
Any help is appreciated
April 14, 2016 at 8:24 am
You can put a case statement in the order by, so something like for the order by.
ORDER BY
case when [No Of Applications] = '1' then 1
when [No Of Applications] = '2' then 2
when [No Of Applications] = '3' then 3
when [No Of Applications] = '4' then 4
when [No Of Applications] = '5' then 5
when [No Of Applications] >= '6' and [No Of Applications] < '10' then 6
when [No Of Applications] >= '10' and [No Of Applications] < '20' then 10
when [No Of Applications] >= '20' and [No Of Applications] < '50' then 20
when [No Of Applications] >= '50' and [No Of Applications] < '100' then 50
else 100 end
April 14, 2016 at 8:26 am
Also note that the [No of Applications] column is an integer. In the WHEN expressions, it should not be in single quotes. That's a string and the implicit conversion may or may not do what you expect. Something like WHEN 1 THEN '1' will work fine.
April 14, 2016 at 8:29 am
Why not just order by Number of Applications? Should have the same net effect.
Note that your range names are misleading and overlapping. '06-10' should be '06-09', '10-20' should be '10-19' etc
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 14, 2016 at 8:31 am
ZZartin (4/14/2016)
You can put a case statement in the order by, so something like for the order by.
ORDER BYcase when [No Of Applications] = '1' then 1
when [No Of Applications] = '2' then 2
when [No Of Applications] = '3' then 3
when [No Of Applications] = '4' then 4
when [No Of Applications] = '5' then 5
when [No Of Applications] >= '6' and [No Of Applications] < '10' then 6
when [No Of Applications] >= '10' and [No Of Applications] < '20' then 10
when [No Of Applications] >= '20' and [No Of Applications] < '50' then 20
when [No Of Applications] >= '50' and [No Of Applications] < '100' then 50
else 100 end
This is back to front, because [No Of Applications] is an integer.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 14, 2016 at 8:42 am
Phil, those were the groupings they wanted but I am going to change them as I do not disagree with you it's misleading :).
So I did change it to this:
with cte as (select a.applicant_id as [Applicant ID],
--count (distinct a.vac_ref) as [No Of Jobs Applied For]
count (distinct a.APPLICATION_ID) as [No Of Applications],
month(a.CREATE_DATE) as [Month],
year(a.create_date) as [Year]
from application as a
where a.CREATE_DATE >= DATEADD(year,-1,current_timestamp)
--and a.AGENCY_CC = 'uk'
and a.APPLICATION_SITE_CC = 'uk'
and a.IS_JOBGATE = 0
and a.IS_LINK_FINANCE = 0
group by a.APPLICANT_ID,month(a.CREATE_DATE) ,
year(a.create_date)
)
select count(distinct c.[Applicant ID]) as [No of Applicants], sum(c.[No Of Applications]) as [No of Applications], c.Month, c.Year,
case when [No Of Applications] = 1 then '1'
when [No Of Applications] = 2 then '2'
when [No Of Applications] = 3 then '3'
when [No Of Applications] = 4 then '4'
when [No Of Applications] = 5 then '5'
when [No Of Applications] >= 6 and [No Of Applications] < 10 then '6 to 10'
when [No Of Applications] >= 10 and [No Of Applications] < 20 then '10 to 20'
when [No Of Applications] >= 20 and [No Of Applications] < 50 then '20 to 50'
when [No Of Applications] >= 50 and [No Of Applications] < 100 then '50 to 100'
else '100 plus' end as [Application Bracket Total]
from cte c
group by c.Month, c.Year,
case when [No Of Applications] = 1 then '1'
when [No Of Applications] = 2 then '2'
when [No Of Applications] = 3 then '3'
when [No Of Applications] = 4 then '4'
when [No Of Applications] = 5 then '5'
when [No Of Applications] >= 6 and [No Of Applications] < 10 then '6 to 10'
when [No Of Applications] >= 10 and [No Of Applications] < 20 then '10 to 20'
when [No Of Applications] >= 20 and [No Of Applications] < 50 then '20 to 50'
when [No Of Applications] >= 50 and [No Of Applications] < 100 then '50 to 100'
else '100 plus' end
order by
c.Year, c.Month,
sum(c.[No Of Applications]) desc
Still not in the case statement order. Now this order
1
10 to 20
20 to 50
6 to 10
2
3
100 plus
4
50 to 100
5
I feel like I am missing something fundamental.
April 14, 2016 at 9:02 am
Had a brainwave as this will be an SSRS report I could do conditional sorting on it in there versus in the query itself.
Although I am a dog with a bone at the mo, I think this can be done hehe.
April 14, 2016 at 9:15 am
Kazmerelda (4/14/2016)
Had a brainwave as this will be an SSRS report I could do conditional sorting on it in there versus in the query itself.Although I am a dog with a bone at the mo, I think this can be done hehe.
The problem I have in helping you with this is that your CREATE TABLE and INSERT statements seem to create a table which has a different structure from the CTE which you have posted.
So trying to change your query to provide the results you desire is ... challenging.
Can you post some DDL/INSERTs for the 'application' table, perhaps?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 14, 2016 at 9:28 am
Your order by isn't working because you are trying to sort by a alphanumeric field that doesn't sort in the order you want. Did you try this,
ORDER BY
case when [No Of Applications] = '1' then 1
when [No Of Applications] = '2' then 2
when [No Of Applications] = '3' then 3
when [No Of Applications] = '4' then 4
when [No Of Applications] = '5' then 5
when [No Of Applications] >= '6' and [No Of Applications] < '10' then 6
when [No Of Applications] >= '10' and [No Of Applications] < '20' then 10
when [No Of Applications] >= '20' and [No Of Applications] < '50' then 20
when [No Of Applications] >= '50' and [No Of Applications] < '100' then 50
else 100 end
And it doesn't help that your posted table and cte code don't work.
April 14, 2016 at 9:43 am
ZZartin (4/14/2016)
Your order by isn't working because you are trying to sort by a alphanumeric field that doesn't sort in the order you want. Did you try this,
ORDER BYcase when [No Of Applications] = '1' then 1
when [No Of Applications] = '2' then 2
when [No Of Applications] = '3' then 3
when [No Of Applications] = '4' then 4
when [No Of Applications] = '5' then 5
when [No Of Applications] >= '6' and [No Of Applications] < '10' then 6
when [No Of Applications] >= '10' and [No Of Applications] < '20' then 10
when [No Of Applications] >= '20' and [No Of Applications] < '50' then 20
when [No Of Applications] >= '50' and [No Of Applications] < '100' then 50
else 100 end
Why are you continuing with the implicit conversion from int to string? It returns the wrong results (because, for example, '9' is 'greater than' '1000').
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 14, 2016 at 10:00 am
Phil Parkin (4/14/2016)
ZZartin (4/14/2016)
Your order by isn't working because you are trying to sort by a alphanumeric field that doesn't sort in the order you want. Did you try this,
ORDER BYcase when [No Of Applications] = '1' then 1
when [No Of Applications] = '2' then 2
when [No Of Applications] = '3' then 3
when [No Of Applications] = '4' then 4
when [No Of Applications] = '5' then 5
when [No Of Applications] >= '6' and [No Of Applications] < '10' then 6
when [No Of Applications] >= '10' and [No Of Applications] < '20' then 10
when [No Of Applications] >= '20' and [No Of Applications] < '50' then 20
when [No Of Applications] >= '50' and [No Of Applications] < '100' then 50
else 100 end
Why are you continuing with the implicit conversion from int to string? It returns the wrong results (because, for example, '9' is 'greater than' '1000').
The CTE doesn't work as posted so I didn't try to trouble shoot anything beyond asking why he's not using a case statement in his order by. But yes to get the comparisons to work right if [No Of Applications] is string the values would need to be tweaked a little, '0009' instead of '9', either way the point being to convert the strings he's trying to sort to ints which will sort predictably.
April 14, 2016 at 10:15 am
I thought I was doing so well this time with posting the code this time rarrghhhh.
I have for now run it and sorted in excel but want to sort it for automation.
I have to read and run for now as something else calls 🙂 but I will come back to this!
Thanks for your help so far.
April 14, 2016 at 10:19 am
Phil Parkin (4/14/2016)
Why are you continuing with the implicit conversion from int to string?
Actually, int has a higher precedence than string, so the implicit conversion should be from string to int. And probably handled when parsing/optimizing the query, not in execution.
The code is sloppy and probably not performing optimal, but I don't think incorrect results will occur.
April 14, 2016 at 10:37 am
I had a chuckle at my code being sloppy, I am getting there 😛 😉 (I am not offended I still have SO much to learn).
Am going to hit the books tonight to look at case, and will be rewriting this. Will keep you posted.
April 14, 2016 at 11:10 am
Hugo Kornelis (4/14/2016)
Phil Parkin (4/14/2016)
Why are you continuing with the implicit conversion from int to string?Actually, int has a higher precedence than string, so the implicit conversion should be from string to int. And probably handled when parsing/optimizing the query, not in execution.
The code is sloppy and probably not performing optimal, but I don't think incorrect results will occur.
Correct! Thanks, Hugo.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply