February 9, 2010 at 3:51 am
Hi
I have the following tables
CREATE TABLE [dbo].[Master1] (
[TERM_ID] [char] (8) NOT NULL ,
[TERM_OWNER_NAME] [char] (22) NULL ,
[RETAILER_ID] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tran1] (
[DATTIM] [datetime] NULL ,
[TERM_ID] [char] (16) NULL ,
[AMT1] [decimal](18, 0) NULL ,
[SRVMODE] [char] (3) NULL
) ON [PRIMARY]
GO
INSERT MASTER1
SELECT '12600002','12600002 ','310558010 ' UNION ALL
SELECT '12600004','12600004 ','213876001 ' UNION ALL
SELECT '12600005','12600005 ','210059022 ' UNION ALL
SELECT '05000002','05000002 ','217243001 ' UNION ALL
SELECT '05000003','05000003 ','217244001 ' UNION ALL
SELECT '05000005','05000005 ','914222006 ' UNION ALL
SELECT '04002366','04002366 ','919960001 ' UNION ALL
SELECT '04002938','04002938 ','220325001 ' UNION ALL
SELECT '04002951','04002951 ','112639002 '
INSERT TRAN1
SELECT 'Feb 7 2010 6:45PM','12600002 ','336000','021' UNION ALL
SELECT 'Feb 8 2010 11:54AM','12600002 ','373000','021' UNION ALL
SELECT 'Feb 8 2010 11:58AM','12600002 ','36000','051' UNION ALL
SELECT 'Feb 6 2010 12:06PM','12600004 ','78750','021' UNION ALL
SELECT 'Feb 6 2010 5:46PM','12600004 ','10000','021' UNION ALL
SELECT 'Feb 6 2010 6:31PM','12600004 ','43250','021' UNION ALL
SELECT 'Feb 6 2010 1:22PM','05000002 ','9000','051' UNION ALL
SELECT 'Feb 6 2010 6:58PM','05000002 ','9500','051' UNION ALL
SELECT 'Feb 7 2010 11:54AM','05000002 ','31000','051' UNION ALL
SELECT 'Feb 6 2010 6:24PM','05000003 ','14000','801' UNION ALL
SELECT 'Feb 6 2010 8:52AM','04002366 ','350000','021' UNION ALL
SELECT 'Feb 6 2010 9:04AM','04002366 ','600000','021' UNION ALL
SELECT 'Feb 6 2010 9:45AM','04002366 ','488000','021' UNION ALL
SELECT 'Feb 6 2010 9:27AM','04002951 ','8500','051' UNION ALL
SELECT 'Feb 6 2010 9:42AM','04002951 ','70000','021' UNION ALL
SELECT 'Feb 6 2010 11:43AM','04002951 ','15500','021'
This SQL help me to identify with the type of the terminal and this is fine.
SELECT TYPE = CASE
WHEN TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN' END, SUBSTRING(TERM_ID,1,3) AS TYP,
COUNT(TERM_ID) TOTALS
FROM MASTER1
GROUP BY
CASE
WHEN TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN' END, SUBSTRING(TERM_ID,1,3)
In Tran1 table, based SRVMODE column we identify the terminal whether is old or new. If it is new, SRVMODE = '051' else its old.
I want to group all terminals from master1 joining the tran1 table along with New terminals / old terminals. Incase, there is no transaction
that should reflect as 'no trx' column
The expected output:
TYPE SERVICE TOTALS NEW_TERMS OLD_TERMS NO_TRX
------- --- ------- --------- --------- ------
A GROUP040 3 111
B GROUP050 3 201
C GROUP126 3 111
Can anyone enlighten me? Thanks in advance
Moorthy
February 9, 2010 at 4:28 am
I have 2 questions for you.
1. How does NEW_TERMS value is 2 for B Group? It should be 1.
2. What would be the output if there exists an entry for TERM_ID = 12600005 and SRVMODE = 021 in TRAN1 table?
--Ramesh
February 9, 2010 at 4:58 am
Thanks Ramesh.
1. I forgot to add 801 in new type. 051 / 801 is new type
2. If there is a transaction with 021, it should be there in old terms and should be removed from NO_TRX
Hope, i clear your questions 🙂
February 9, 2010 at 5:08 am
Let's see if this query helps.
SELECT( CASE
WHEN M.TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN M.TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN M.TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN'
END ) AS [TYPE], SUBSTRING( M.TERM_ID, 1, 3 ) AS TYP,
COUNT( DISTINCT M.TERM_ID ) AS TOTALS,
COUNT( DISTINCT ( CASE WHEN T.SRVMODE IN( '051', '801' ) THEN T.SRVMODE ELSE NULL END ) ) AS NEW_TERMS,
COUNT( DISTINCT ( CASE WHEN T.SRVMODE NOT IN( '051', '801' ) THEN T.SRVMODE ELSE NULL END ) ) AS OLD_TERMS,
COUNT( DISTINCT ( CASE WHEN T.TERM_ID IS NULL THEN M.TERM_ID ELSE NULL END ) ) AS NO_TRX
FROMMASTER1 M
LEFT JOIN
(
SELECTDISTINCT TERM_ID, SRVMODE
FROMTRAN1
) T ON M.TERM_ID = T.TERM_ID
GROUP BY ( CASE
WHEN M.TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN M.TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN M.TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN'
END ), SUBSTRING( M.TERM_ID, 1, 3 )
ORDER BY [TYPE]
--Ramesh
February 9, 2010 at 10:29 pm
Thanks Ramesh. The query looks fine. I just tweaked the data and the results are not correct. 🙁 Please check with this data.
SELECT 'Feb 6 2010 12:06PM','12600004 ','78750','021' UNION ALL
SELECT 'Feb 6 2010 5:46PM','12600004 ','10000','021' UNION ALL
SELECT 'Feb 6 2010 6:31PM','12600004 ','43250','021' UNION ALL
SELECT 'Feb 6 2010 1:22PM','05000002 ','9000','051' UNION ALL
SELECT 'Feb 6 2010 6:58PM','05000002 ','9500','051' UNION ALL
SELECT 'Feb 7 2010 11:54AM','05000002 ','31000','051' UNION ALL
SELECT 'Feb 6 2010 6:24PM','05000003 ','14000','021' UNION ALL
SELECT 'Feb 6 2010 8:52AM','04002366 ','350000','021' UNION ALL
SELECT 'Feb 6 2010 9:04AM','04002366 ','600000','021' UNION ALL
SELECT 'Feb 6 2010 9:45AM','04002366 ','488000','021' UNION ALL
SELECT 'Feb 6 2010 9:27AM','04002951 ','8500','021' UNION ALL
SELECT 'Feb 6 2010 9:42AM','04002951 ','70000','021' UNION ALL
SELECT 'Feb 6 2010 11:43AM','04002951 ','15500','021'
February 10, 2010 at 12:55 am
Moorthy,
Can you try this query?
SELECT TYPE = CASE WHEN TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN'
END
,SUBSTRING(TERM_ID, 1, 3) AS TYP
,COUNT(TERM_ID) TOTALS
,SUM(CASE WHEN [x].[SRVMODE] IN ('051', '801') THEN 1
ELSE 0
END) AS NEW_TERMS
,SUM(CASE WHEN [x].[SRVMODE] NOT IN ('051', '801') THEN 1
ELSE 0
END) AS OLD_TERMS
,SUM(CASE WHEN [x].[SRVMODE] IS NULL THEN 1
ELSE 0
END) AS NO_TRX
FROM (SELECT DISTINCT
[m].[TERM_ID]
,[t].[SRVMODE]
FROM [dbo].[Master1] AS m
LEFT JOIN [dbo].[Tran1] AS t
ON [m].[TERM_ID] = [t].[TERM_ID]) x
GROUP BY CASE WHEN TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN'
END
,SUBSTRING(TERM_ID, 1, 3)
February 10, 2010 at 1:25 am
Thanks Willem for the query.
The problem really comes, if any terminal does both '051' and '021' transaction, it will reflect in both old and new terminals.
But if any transactions with SRVMODE = '051' should be added only in new terminal even if any transactions with '021' done on the terminal.
The problem here is getting distinct value of terminals/srvmode.
Thanks again.
February 10, 2010 at 1:52 am
OK - I didn't get that from your earlier specifications.
Try this
SELECT TYPE = CASE WHEN TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN'
END
,SUBSTRING(TERM_ID, 1, 3) AS TYP
,COUNT(TERM_ID) TOTALS
,SUM(CASE WHEN [x].[IS_NEW] = 1 THEN 1
ELSE 0
END) AS NEW_TERMS
,SUM(CASE WHEN [x].[IS_NEW] = 0 THEN 1
ELSE 0
END) AS OLD_TERMS
,SUM(CASE WHEN [x].[IS_NEW] IS NULL THEN 1
ELSE 0
END) AS NO_TRX
FROM (SELECT [m].[TERM_ID]
,MAX(CASE WHEN [t].[SRVMODE] IN ('051', '801') THEN 1
WHEN [t].[SRVMODE] NOT IN ('051', '801') THEN 0
ELSE NULL
END) AS IS_NEW
FROM [dbo].[Master1] AS m
LEFT JOIN [dbo].[Tran1] AS t
ON [m].[TERM_ID] = [t].[TERM_ID]
GROUP BY [m].[TERM_ID]) x
GROUP BY CASE WHEN TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN'
END
,SUBSTRING(TERM_ID, 1, 3)
February 10, 2010 at 1:57 am
Ok, now I get what you wanted to do.
SELECT ( CASE
WHEN M.TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN M.TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN M.TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN'
END ) AS [TYPE], SUBSTRING( M.TERM_ID, 1, 3 ) AS TYP,
COUNT( DISTINCT M.TERM_ID ) AS TOTALS,
SUM( T.NEW_TERMS ) AS NEW_TERMS,
SUM( ( CASE WHEN T.NEW_TERMS > 0 THEN 0 ELSE T.OLD_TERMS END ) ) AS OLD_TERMS,
COUNT( DISTINCT ( CASE WHEN T.TERM_ID IS NULL THEN M.TERM_ID ELSE NULL END ) ) AS NO_TRX
FROM MASTER1 M
LEFT JOIN
(
SELECT TERM_ID,
COUNT( DISTINCT ( CASE WHEN SRVMODE IN( '051', '801' ) THEN SRVMODE ELSE NULL END ) ) AS NEW_TERMS,
COUNT( DISTINCT ( CASE WHEN SRVMODE NOT IN( '051', '801' ) THEN SRVMODE ELSE NULL END ) ) AS OLD_TERMS
FROM TRAN1
GROUP BY TERM_ID
) T ON M.TERM_ID = T.TERM_ID
GROUP BY ( CASE
WHEN M.TERM_ID LIKE '040%' THEN 'A GROUP'
WHEN M.TERM_ID LIKE '050%' THEN 'B GROUP'
WHEN M.TERM_ID LIKE '126%' THEN 'C GROUP'
ELSE 'UNKNOWN'
END ), SUBSTRING( M.TERM_ID, 1, 3 )
ORDER BY [TYPE]
--Ramesh
February 10, 2010 at 10:38 am
Thanks Ramesh and Willem. I will update you tomorrow after running this query.
February 15, 2010 at 11:59 pm
Thanks again Ramesh and William. Sorry, for the delayed update 🙂
Ramesh, your query fit correctly after tweaking the NEW_TERMS and OLD_TERMS conditions.
William, NEW_TERMS and OLD_TERMS is fine but no_trx condition didnot produce the expected results. Thanks again guys for your time.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply