Home » SQL & PL/SQL » SQL & PL/SQL » Please kindly help in decode count
Please kindly help in decode count [message #224321] Tue, 13 March 2007 20:51 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I have the sql like this

SELECT distinct
DECODEa.LIC_NO "LIC NO",a.svmd_code "MODEL",TO_CHAR(B.reg_date_org,'DD-MON-YY') "REG DATE",
MAX(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2000','')) "00",
MAX(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2001','')) "01",
MAX(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2002','')) "02",
MAX(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2003','')) "03",
MAX(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2004','')) "04",
MAX(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2005','')) "05",
MAX(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2006','')) "06"
FROM MRH a, MVCR b
WHERE a.ROST_CODE <> 'PDI' and a.ro_dtime between '01-JAN-2000' AND '01-JAN-2007'
AND a.CARD_ID=b.ID AND a.sts IN ('I') AND A.MAKE_CODE='T7'
and a.lic_no LIKE '%QX%'
GROUP BY a.LIC_NO,a.CHS_NO,A.SVMD_CODE,b.reg_date_org,a.ro_dtime ORDER BY 2;

and my current output like this

LIC NO MODEL REG DATE 00 01 02 03 04 05 06
QX2389M TROOPER 16-Apr-90
QX4446T TROOPER 27-Oct-99
QX4447R TROOPER 27-Oct-99
QX4448M TROOPER 27-Oct-99
QX4449K TROOPER 27-Oct-99

I want to use count below each year to show how many times that person come in during that year.

I want the output like this

LIC NO MODEL REG DATE 00 01 02 03 04 05 06
QX2389M TROOPER 16-Apr-90 4 2 3 1 2 2 1
QX4446T TROOPER 27-Oct-99 5 3 4 1 0 1 0
QX4447R TROOPER 27-Oct-99 2 2 3 4 1 0 0
QX4448M TROOPER 27-Oct-99 1 1 1 1 1 1 1
QX4449K TROOPER 27-Oct-99 2 3 4 1 2 3 0

Can anyone help me in this regard please
SOLUTION FOR THE QUERY [message #224340 is a reply to message #224321] Wed, 14 March 2007 00:00 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
SELECT distinct
LIC_NO "LIC NO",svmd_code "SVC MODEL",
MAX(DECODE(RO_dtime,'2000',cnt,'')) "00",
MAX(DECODE(RO_dtime,'2001',cnt,'')) "01",
MAX(DECODE(RO_dtime,'2002',cnt,'')) "02",
MAX(DECODE(RO_dtime,'2003',cnt,'')) "03",
MAX(DECODE(RO_dtime,'2004',cnt,'')) "04",
MAX(DECODE(RO_dtime,'2005',cnt,'')) "05",
MAX(DECODE(RO_dtime,'2006',cnt,'')) "06"
FROM
(select
a.LIC_NO,a.svmd_code,TO_CHAR(a.ro_dtime,'YYYY') RO_DTIME,count(DISTINCT A.RO_NO) cnt
from MSS_RO_HDR a, Mss_V_car_records b
WHERE a.ROST_CODE <> 'PDI' and a.ro_dtime between '01-JAN-2000' AND '01-JAN-2007'
AND a.CARD_ID=b.ID AND a.sts IN ('I') AND A.MAKE_CODE='T7'
and a.lic_no is not null
GROUP BY
a.LIC_NO,A.SVMD_CODE,TO_CHAR(A.RO_DTIME,'YYYY'))
group by lic_no,svmd_code

[Updated on: Wed, 14 March 2007 00:02]

Report message to a moderator

Re: SOLUTION FOR THE QUERY [message #224512 is a reply to message #224340] Wed, 14 March 2007 08:38 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
kumarvk wrote on Wed, 14 March 2007 01:00

WHERE a.ROST_CODE <> 'PDI' and a.ro_dtime between '01-JAN-2000' AND '01-JAN-2007'



These are sloppy and invalid.
Re: SOLUTION FOR THE QUERY [message #227429 is a reply to message #224512] Wed, 28 March 2007 04:02 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
SELECT distinct
a.LIC_NO "LIC NO",a.svmd_code "MODEL",TO_CHAR(B.reg_date_org,'DD-MON-YY') "REG DATE",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2000',1,0)) "00",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2001',1,0)) "01",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2002',1,0)) "02",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2003',1,0)) "03",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2004',1,0)) "04",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2005',1,0)) "05",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2006',1,0)) "06"
FROM MRH a, MVCR b
WHERE a.ROST_CODE <> 'PDI' and a.ro_dtime between '01-JAN-2000' AND '01-JAN-2007'
AND a.CARD_ID=b.ID AND a.sts IN ('I') AND A.MAKE_CODE='T7'
and a.lic_no LIKE '%QX%'
GROUP BY a.LIC_NO,a.CHS_NO,A.SVMD_CODE,b.reg_date_org,a.ro_dtime ORDER BY 2;
Re: SOLUTION FOR THE QUERY [message #227520 is a reply to message #227429] Wed, 28 March 2007 08:13 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
muzahidul islam wrote on Wed, 28 March 2007 05:02
SELECT distinct
a.LIC_NO "LIC NO",a.svmd_code "MODEL",TO_CHAR(B.reg_date_org,'DD-MON-YY') "REG DATE",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2000',1,0)) "00",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2001',1,0)) "01",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2002',1,0)) "02",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2003',1,0)) "03",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2004',1,0)) "04",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2005',1,0)) "05",
SUM(DECODE(TO_CHAR(A.RO_dtime,'YYYY'),'2006',1,0)) "06"
FROM MRH a, MVCR b
WHERE a.ROST_CODE <> 'PDI' and a.ro_dtime between '01-JAN-2000' AND '01-JAN-2007'
AND a.CARD_ID=b.ID AND a.sts IN ('I') AND A.MAKE_CODE='T7'
and a.lic_no LIKE '%QX%'
GROUP BY a.LIC_NO,a.CHS_NO,A.SVMD_CODE,b.reg_date_org,a.ro_dtime ORDER BY 2;


Would you please stop giving the answers without directing the poster how to get an answer. Who learns when a complete answer is given.
And, you are just a sloppy in your answer by comparing DATE columns to strings. This is an expert forum and this is a sloppy newbie response.
Previous Topic: Get latest Access record for each User
Next Topic: How to call another sql script within the sql script
Goto Forum:
  


Current Time: Thu Dec 08 23:59:04 CST 2016

Total time taken to generate the page: 0.09816 seconds