Author |
Topic  |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/25/2013 : 08:06:15
|
Hi ,
I need to pull out the data based on the year select.
This is my table
POLICY_NO CALLDATETIME CALLTYPE AGENT_ID VENDER_NAME APPLICATION_NUMBER UNIQUE_ID
005820319 2012-10-01 00:00:00.000 E QUAEND Qualtouch A47148143 Z00000
005813521 2012-10-01 00:00:00.000 E QUAEND Qualtouch A47148161 Z00000
005826165 2012-10-01 00:00:00.000 E QUAEND Qualtouch A47152911 Z00000
005840653 2012-10-01 00:00:00.000 E QUAEND Qualtouch A47387908 Z00000
005858520 2012-11-20 00:00:00.000 L QUAELG Qualtouch A46852284 QUAEND1
007 2012-10-01 00:00:00.000 L QUAELG Qualtouch A46852287 QUAEND1
I want the out put like this when user entered 2012
Vender Name Month-Year Count of E Count of L Qualtouch October-2012 1 4 Qualtouch November-2012 0 1
Below is the my sql Query
Select VENDER_NAME,datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME) 'call_date',CALL_TYPE,COUNT(*) 'cnt'
FROM INSR_CALL_DETAILS
WHERE year(CALLDATETIME) = '2012'
GROUP BY datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME),VENDER_NAME,CALL_TYPE
But i am getting the o/p like this.
Qualtouch November-2012 L 1 Qualtouch October-2012 E 4 Qualtouch October-2012 L 1
Can any one help me where i am doing wrong
Thanks & Regards, Mahesh Kumar Sanka |
|
ahmeds08
Aged Yak Warrior
India
737 Posts |
Posted - 03/25/2013 : 08:42:30
|
Not sure but,try this:
select vendor_name, convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) as month_year, count(case when calltype='E' then 1 else 0)end as count_of_E, count(case when calltype='L' then 1 else 0) end as count_of_L from INSR_CALL_DETAILS group by vendor_name, convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) |
 |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/25/2013 : 08:53:18
|
Hi ahmeds08 ,
Thanks for reply. But i am getting error while executing your query.
Thanks & Regards, Mahesh Kumar Sanka |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/25/2013 : 09:06:20
|
quote: Originally posted by mahesh.sanka
Hi ahmeds08 , Thanks for reply. But i am getting error while executing your query. Thanks & Regards, Mahesh Kumar Sanka
Which error you got? Post that error
-- Chandu |
 |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/25/2013 : 09:10:25
|
quote: Originally posted by bandi
quote: Originally posted by mahesh.sanka
Hi ahmeds08 , Thanks for reply. But i am getting error while executing your query. Thanks & Regards, Mahesh Kumar Sanka
Which error you got? Post that error
-- Chandu
Hi Chandu ,
I am getting the following error.
Incorrect Syntax near )
Thanks & Regards, Mahesh Kumar Sanka |
 |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/25/2013 : 09:20:30
|
quote: Originally posted by ahmeds08
Not sure but,try this:
select vendor_name, convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) as month_year, count(case when calltype='E' then 1 else 0)end as count_of_E, count(case when calltype='L' then 1 else 0) end as count_of_L from INSR_CALL_DETAILS group by vendor_name, convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))
Hi Ahmed,
Thanks for Reply.
select VENDER_NAME,
convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) as month_year,
count(case CALL_TYPE when 'E' then 1 else 0 end )as count_of_E,
count(case CALL_TYPE when 'L' then 1 else 0 end) as count_of_L from INSR_CALL_DETAILS
group by VENDER_NAME,CALL_TYPE ,
convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))
But i am getting wrong out put. i.e duplication of data is not coming
Thanks & Regards, Mahesh Kumar Sanka |
 |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/25/2013 : 09:21:12
|
quote: Originally posted by bandi
quote: Originally posted by mahesh.sanka
Hi ahmeds08 , Thanks for reply. But i am getting error while executing your query. Thanks & Regards, Mahesh Kumar Sanka
Which error you got? Post that error
-- Chandu
Error rectified but out put is not correct
Thanks & Regards, Mahesh Kumar Sanka |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/25/2013 : 09:22:30
|
group by VENDER_NAME,CALL_TYPE ,
convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))
|
 |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/26/2013 : 00:12:59
|
quote: Originally posted by bandi
group by VENDER_NAME,CALL_TYPE ,
convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))
Thanks for your reply. Even though i am getting wrong out put.
GanpatiEnterprises 10-2012 4 4 Qualtouch 10-2012 5 5 GanpatiEnterprises 11-2012 1 1 Qualtouch 11-2012 1 1
Thanks & Regards, Mahesh Kumar Sanka |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/26/2013 : 00:38:29
|
If you want E_count and L_Count based on only Month-YYYY, then use the following code
SELECT DISTINCT icd.VENDER_NAME, call_date, E_Count, L_Count
FROM INSR_CALL_DETAILS icd
JOIN ( SELECT datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME) 'call_date',
COUNT(CASE WHEN CALLTYPE='E' THEN 1 END) 'E_Count',
COUNT(CASE WHEN CALLTYPE='L' THEN 1 END) 'L_Count'
FROM INSR_CALL_DETAILS
WHERE year(CALLDATETIME) = '2012'
GROUP BY datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME)
) temp
ON temp.call_date = datename(month, icd.CALLDATETIME ) + '-' + datename(year, icd.CALLDATETIME)
|
 |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/26/2013 : 00:50:59
|
quote: Originally posted by bandi
If you want E_count and L_Count based on only Month-YYYY, then use the following code
SELECT DISTINCT icd.VENDER_NAME, call_date, E_Count, L_Count
FROM INSR_CALL_DETAILS icd
JOIN ( SELECT datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME) 'call_date',
COUNT(CASE WHEN CALLTYPE='E' THEN 1 END) 'E_Count',
COUNT(CASE WHEN CALLTYPE='L' THEN 1 END) 'L_Count'
FROM INSR_CALL_DETAILS
WHERE year(CALLDATETIME) = '2012'
GROUP BY datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME)
) temp
ON temp.call_date = datename(month, icd.CALLDATETIME ) + '-' + datename(year, icd.CALLDATETIME)
Thanks for your reply. But now also i am getting wrong out put.
The above query combining the values of every vendor.
GanpatiEnterprises November-2012 0 2 GanpatiEnterprises October-2012 8 1 Qualtouch November-2012 0 2 Qualtouch October-2012 8 1
Thanks & Regards, Mahesh Kumar Sanka |
Edited by - mahesh.sanka on 03/26/2013 00:53:58 |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/26/2013 : 01:09:11
|
Can you show us the sample data with different VenderNames and also how will you get to know above is incorrect result? Tell me the logic behind it... Only you need E, L count based on Year or Month-Year? or any thing else..........
Do you need other columns(except E_COUNT, L_COUNT, Call_date) in the SELECT ? |
 |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/26/2013 : 01:19:06
|
quote: Originally posted by bandi
Can you show us the sample data with different VenderNames and also how will you get to know above is incorrect result? Tell me the logic behind it... Only you need E, L count based on Year or Month-Year? or any thing else..........
Do you need other columns(except E_COUNT, L_COUNT, Call_date) in the SELECT ?
This is my table Structure
AND COLUMN IS CALL_TYPE where i am storing either 'E' or 'L'
CREATE TABLE [dbo].[INSR_CALL_DETAILS]( [CUSTOMER_ID] [varchar](40) NULL, [POLICY_NO] [varchar](40) NULL, [POLICY_DESC] [varchar](240) NULL, [CALLDATETIME] [datetime] NULL, [CALLTYPE] [varchar](1) NULL, [AGENT_ID] [varchar](240) NOT NULL, [VENDER_NAME] [varchar](240) NOT NULL, [EXTENSION_DETAILS] [varchar](50) NULL, [MOBILE_NO] [varchar](75) NULL, [MEDIA_PATH] [varchar](1000) NULL, [CMP_CODE] [varchar](10) NULL, [CREATED_BY] [varchar](10) NULL, [CREATED_TIME] [datetime] NULL, [MODIFIED_BY] [varchar](10) NULL, [MODIFIED_TIME] [datetime] NULL, [APPLICATION_NUMBER] [varchar](30) NULL, [UNIQUE_ID] [varchar](300) NULL, [PRODUCT_NAME] [varchar](3000) NULL, [CALL_TYPE] [char](1) NULL, [CLI_INDV_SUR_NM] [nvarchar](max) NULL, [CLI_INDV_MID_NM] [nvarchar](max) NULL, [CLI_INDV_GIV_NM] [nvarchar](max) NULL, [CVG_MAT_XPRY_DT] [datetime] NULL, [BSLI_APP_BR_RECV_DT] [datetime] NULL, [status] [varchar](240) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Scripts for Insertion
INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'BODHRAJTARGOTRA', N'005820319 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'Qualtouch', NULL, N'9848421860', N'//Qualtouch//End To End Process//2012//10//01//A47148143.wav', N'C008', N'Admin', CAST(0x0000A16A00FB5FE0 AS DateTime), N'Admin', CAST(0x0000A17200B5905A AS DateTime), N'A47148143', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'BODH', N'RAJ', N'TARGOTRA', CAST(0x0000A73D00000000 AS DateTime), CAST(0x0000A0FB00000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'DINESHKUMAR', N'005813521 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'Qualtouch', NULL, N'9848022338', N'//Qualtouch//End To End Process//2012//10//01//A47148161.wav', N'C008', N'Admin', CAST(0x0000A16A00FB611A AS DateTime), N'Admin', CAST(0x0000A178011CE0BF AS DateTime), N'A47148161', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'DINESH', N'KUMAR', NULL, CAST(0x00010ED700000000 AS DateTime), CAST(0x0000A0F700000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'SATYAJITDAS', N'005826165 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'Qualtouch', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47152911.wav', N'C008', N'Admin', CAST(0x0000A16A00FB6238 AS DateTime), NULL, NULL, N'A47152911', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'SATYAJIT', NULL, N'DAS', CAST(0x0000FC5100000000 AS DateTime), CAST(0x0000A10000000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'ANILKUMARSINGH', N'005840653 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'Qualtouch', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47387908.wav', N'C008', N'Admin', CAST(0x0000A16A00FB6358 AS DateTime), NULL, NULL, N'A47387908', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'ANIL', N'KUMAR', N'SINGH', CAST(0x0000FF3800000000 AS DateTime), CAST(0x0000A10C00000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'PRONOTIASHISHLAHIRI', N'005858520 ', NULL, CAST(0x0000A10F00000000 AS DateTime), N'O', N'QUAELG', N'Qualtouch', NULL, N'', N'//Qualtouch//Lead Generation//2012//11//20//QUAEND1_11202012_Sachin.wav', N'C008', N'Admin', CAST(0x0000A16A00FF5F1F AS DateTime), NULL, NULL, N'A46852284 ', N'QUAEND1', N'BSLI Protector Plan - Term 14 Pay 14 ', N'L', N'PRONOTI', N'ASHISH', N'LAHIRI', CAST(0x0000A11A00000000 AS DateTime), CAST(0x0000A11900000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'Prasad', N'007', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAELG', N'Qualtouch', NULL, N'9820696039', N'fdfd', N'C008', N'Admin', CAST(0x0000A15800000000 AS DateTime), NULL, NULL, N'DNSKLDN', N'FDLN', N'FNDL', N'L', N'FD', N'L', N'L', CAST(0x00009FCB00000000 AS DateTime), CAST(0x00009FCB00000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'BODHRAJTARGOTRA', N'005820319 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'GANEND', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47148143.wav', N'C008', N'Admin', CAST(0x0000A16A00FB5FE0 AS DateTime), NULL, NULL, N'A47148143', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'BODH', N'RAJ', N'TARGOTRA', CAST(0x0001104900000000 AS DateTime), CAST(0x0000A0FB00000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'DINESHKUMAR', N'005813521 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'GANEND', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47148161.wav', N'C008', N'Admin', CAST(0x0000A16A00FB611A AS DateTime), NULL, NULL, N'A47148161', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'DINESH', N'KUMAR', NULL, CAST(0x00010ED700000000 AS DateTime), CAST(0x0000A0F700000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'SATYAJITDAS', N'005826165 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'GANEND', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47152911.wav', N'C008', N'Admin', CAST(0x0000A16A00FB6238 AS DateTime), NULL, NULL, N'A47152911', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'SATYAJIT', NULL, N'DAS', CAST(0x0000FC5100000000 AS DateTime), CAST(0x0000A10000000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'ANILKUMARSINGH', N'005840653 ', NULL, CAST(0x0000A0DD00000000 AS DateTime), N'O', N'QUAEND', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//End To End Process//2012//10//01//A47387908.wav', N'C008', N'Admin', CAST(0x0000A16A00FB6358 AS DateTime), NULL, NULL, N'A47387908', N'Z00000 ', N'BSLI Vision Plan - GSB Pay 20 ', N'E', N'ANIL', N'KUMAR', N'SINGH', CAST(0x0000FF3800000000 AS DateTime), CAST(0x0000A10C00000000 AS DateTime), NULL) INSERT [dbo].[INSR_CALL_DETAILS] ([CUSTOMER_ID], [POLICY_NO], [POLICY_DESC], [CALLDATETIME], [CALLTYPE], [AGENT_ID], [VENDER_NAME], [EXTENSION_DETAILS], [MOBILE_NO], [MEDIA_PATH], [CMP_CODE], [CREATED_BY], [CREATED_TIME], [MODIFIED_BY], [MODIFIED_TIME], [APPLICATION_NUMBER], [UNIQUE_ID], [PRODUCT_NAME], [CALL_TYPE], [CLI_INDV_SUR_NM], [CLI_INDV_MID_NM], [CLI_INDV_GIV_NM], [CVG_MAT_XPRY_DT], [BSLI_APP_BR_RECV_DT], [status]) VALUES (N'PRONOTIASHISHLAHIRI', N'005858520 ', NULL, CAST(0x0000A10F00000000 AS DateTime), N'O', N'GANELG', N'GanpatiEnterprises', NULL, N'', N'//Qualtouch//Lead Generation//2012//11//20//QUAEND1_11202012_Sachin.wav', N'C008', N'Admin', CAST(0x0000A16A00FF5F1F AS DateTime), NULL, NULL, N'A46852284 ', N'QUAEND1', N'BSLI Protector Plan - Term 14 Pay 14 ', N'L', N'PRONOTI', N'ASHISH', N'LAHIRI', CAST(0x0000A11A00000000 AS DateTime), CAST(0x0000A11900000000 AS DateTime), NULL)
Thanks & Regards, Mahesh Kumar Sanka |
Edited by - mahesh.sanka on 03/26/2013 01:31:18 |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/26/2013 : 01:36:51
|
Expected output? Count based on [month-YYYY] or ([month-YYYY] and Vendername)? |
 |
|
mahesh.sanka
Starting Member
India
18 Posts |
Posted - 03/26/2013 : 02:23:59
|
quote: Originally posted by bandi
Expected output? Count based on [month-YYYY] or ([month-YYYY] and Vendername)?
I want count based on ([month-YYYY] and Vendername)
The problem is solved
SELECT VENDER_NAME,datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME) 'call_date',
COUNT(CASE CALL_TYPE WHEN 'E' THEN 1 END ) 'E_Count',
COUNT(CASE CALL_TYPE when 'L' THEN 1 END) 'L_Count'
FROM INSR_CALL_DETAILS
WHERE year(CALLDATETIME) = '2012'
GROUP BY VENDER_NAME,datename(month, CALLDATETIME ) + '-' + datename(year, CALLDATETIME)
Thanks all of you guys. I have modified your original query only
Thanks & Regards, Mahesh Kumar Sanka |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/26/2013 : 02:33:50
|
Already we have suggested above query at ( bandi Posted - 03/25/2013 : 09:22:30)....
Be specific in the explanation whenever you want query so that you can save lot of time....
|
 |
|
|
Topic  |
|
|
|