Author |
Topic |
mahesh.sanka
Starting Member
18 Posts |
Posted - 2013-03-25 : 08:06:15
|
Hi ,I need to pull out the data based on the year select.This is my tablePOLICY_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 QualtouchA47148161 Z00000 005826165 2012-10-01 00:00:00.000 E QUAEND QualtouchA47152911 Z00000 005840653 2012-10-01 00:00:00.000 E QUAEND QualtouchA47387908 Z00000 005858520 2012-11-20 00:00:00.000 L QUAELG Qualtouch A46852284 QUAEND1 007 2012-10-01 00:00:00.000 L QUAELG QualtouchA46852287 QUAEND1 I want the out put like this when user entered 2012Vender Name Month-Year Count of E Count of LQualtouch October-2012 1 4Qualtouch November-2012 0 1Below is the my sql QuerySelect 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 1Qualtouch October-2012 E 4Qualtouch October-2012 L 1Can any one help me where i am doing wrongThanks & Regards,Mahesh Kumar Sanka |
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-03-25 : 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_DETAILSgroup by vendor_name,convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) |
|
|
mahesh.sanka
Starting Member
18 Posts |
Posted - 2013-03-25 : 08:53:18
|
Hi ahmeds08 , Thanks for reply. But i am getting error while executing your query. Thanks & Regards,Mahesh Kumar Sanka |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-25 : 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
18 Posts |
Posted - 2013-03-25 : 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
18 Posts |
Posted - 2013-03-25 : 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_DETAILSgroup by vendor_name,convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))
Hi Ahmed,Thanks for Reply.[CODE]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_DETAILSgroup by VENDER_NAME,CALL_TYPE ,convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime))[/CODE]But i am getting wrong out put. i.e duplication of data is not comingThanks & Regards,Mahesh Kumar Sanka |
|
|
mahesh.sanka
Starting Member
18 Posts |
Posted - 2013-03-25 : 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--ChanduError rectified but out put is not correct
Thanks & Regards,Mahesh Kumar Sanka |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-25 : 09:22:30
|
group by VENDER_NAME,CALL_TYPE ,convert(varchar(4),MONTH(calldatetime))+'-'+convert(varchar(4),YEAR(calldatetime)) |
|
|
mahesh.sanka
Starting Member
18 Posts |
Posted - 2013-03-26 : 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 4Qualtouch 10-2012 5 5GanpatiEnterprises 11-2012 1 1Qualtouch 11-2012 1 1Thanks & Regards,Mahesh Kumar Sanka |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-26 : 00:38:29
|
If you want E_count and L_Count based on only Month-YYYY, then use the following codeSELECT DISTINCT icd.VENDER_NAME, call_date, E_Count, L_CountFROM INSR_CALL_DETAILS icdJOIN ( 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) ) tempON temp.call_date = datename(month, icd.CALLDATETIME ) + '-' + datename(year, icd.CALLDATETIME) |
|
|
mahesh.sanka
Starting Member
18 Posts |
Posted - 2013-03-26 : 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 codeSELECT DISTINCT icd.VENDER_NAME, call_date, E_Count, L_CountFROM INSR_CALL_DETAILS icdJOIN ( 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) ) tempON 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 2GanpatiEnterprises October-2012 8 1Qualtouch November-2012 0 2Qualtouch October-2012 8 1Thanks & Regards,Mahesh Kumar Sanka |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-26 : 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
18 Posts |
Posted - 2013-03-26 : 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 StructureAND 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]GOSET ANSI_PADDING OFFGOScripts for InsertionINSERT [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 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-26 : 01:36:51
|
Expected output?Count based on [month-YYYY] or ([month-YYYY] and Vendername)? |
|
|
mahesh.sanka
Starting Member
18 Posts |
Posted - 2013-03-26 : 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 solvedSELECT 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 onlyThanks & Regards,Mahesh Kumar Sanka |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-26 : 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.... |
|
|
|
|
|