mattmontalto
IS-IT--Management
I am trying to select the count of multiple columns from 2 different tables that are relational.... Best way for me to give you the whole picture is to show you the script I am using to select the data that I need a count of.... however this script selects the actual data from the columns I need from both tables.... I am trying to select this exact query except that instead of getting the values for the fields, I just want to get a count of them.... - The are all bit fields that I am trying to get a count of.... essentially a count of the number of values that are "1"
Can someone help me solve this... thank you
Here is the script:
SELECT
dbo.INTAKE_Tbl.fileno,
dbo.INTAKE_Tbl.[date] AS [Intake Date],
dbo.CVB_Tbl.[date] AS [Service Date],
dbo.INTAKE_Tbl.firstname,
dbo.INTAKE_Tbl.lastname,
dbo.INTAKE_Tbl.dob,
dbo.CVB_Tbl.newintakecase,
dbo.INTAKE_Tbl.race_amerindian,
dbo.INTAKE_Tbl.race_asian,
dbo.INTAKE_Tbl.race_blkaframer,
dbo.INTAKE_Tbl.race_hisplatino,
dbo.INTAKE_Tbl.race_ntchawaiinpacislnd,
dbo.INTAKE_Tbl.race_whtnonlatinocauc,
dbo.INTAKE_Tbl.race_someotherrace,
dbo.INTAKE_Tbl.race_multi,
dbo.INTAKE_Tbl.race_notdisclosed,
dbo.INTAKE_Tbl.gndr_male,
dbo.INTAKE_Tbl.gndr_female,
dbo.INTAKE_Tbl.gndr_other_expl,
dbo.INTAKE_Tbl.gndr_notdisclosed,
dbo.INTAKE_Tbl.agp_0_12,
dbo.INTAKE_Tbl.agp_13_17,
dbo.INTAKE_Tbl.agp_18_24,
dbo.INTAKE_Tbl.agp_25_59,
dbo.INTAKE_Tbl.agp_60_oldr,
dbo.INTAKE_Tbl.agp_notdiclosed,
dbo.INTAKE_Tbl.assault,
dbo.INTAKE_Tbl.adultsexualabuse,
dbo.INTAKE_Tbl.vt_adltsexabuseaschild,
dbo.INTAKE_Tbl.vt_arson,
dbo.INTAKE_Tbl.vt_bullying_cyverphy,
dbo.INTAKE_Tbl.burgulary,
dbo.INTAKE_Tbl.childphysicalabue,
dbo.INTAKE_Tbl.vt_childporn,
dbo.INTAKE_Tbl.childsexualabuse,
dbo.INTAKE_Tbl.domesticviolence,
dbo.INTAKE_Tbl.dwidui,
dbo.INTAKE_Tbl.elderabuse,
dbo.INTAKE_Tbl.vt_hatecrime,
dbo.INTAKE_Tbl.vt_humtrfk_lbr,
dbo.INTAKE_Tbl.vt_humtrfk_sex,
dbo.INTAKE_Tbl.vt_idtheft_fraud,
dbo.INTAKE_Tbl.vt_kidnap_noncust,
dbo.INTAKE_Tbl.vt_kidnap_cust,
dbo.INTAKE_Tbl.vt_massviolence,
dbo.INTAKE_Tbl.vt_vehicular_h_n_r,
dbo.INTAKE_Tbl.robbery,
dbo.INTAKE_Tbl.vt_stalk_harrass,
dbo.INTAKE_Tbl.familyofhomicide,
dbo.INTAKE_Tbl.vt_teendatevic,
dbo.INTAKE_Tbl.vt_terrorism,
dbo.INTAKE_Tbl.vt_viol_ct_ordr,
dbo.INTAKE_Tbl.vt_other,
dbo.INTAKE_Tbl.sclass_deaf,
dbo.INTAKE_Tbl.sclass_homeless,
dbo.INTAKE_Tbl.sclass_immigrant_rfg_asy,
dbo.INTAKE_Tbl.sclass_lgntq,
dbo.INTAKE_Tbl.sclass_veteran,
dbo.INTAKE_Tbl.sclass_victim_cog_phys_mntl_disable,
dbo.INTAKE_Tbl.sclass_victim_limtd_eng,
dbo.INTAKE_Tbl.sclass_other,
dbo.CVB_Tbl.cvbinfoandassess,
dbo.CVB_Tbl.inforeferral,
dbo.CVB_Tbl.a1_info_cjp,
dbo.CVB_Tbl.a2_vicrts,
dbo.CVB_Tbl.a3_referothpgrm,
dbo.CVB_Tbl.a4_referothsvcs,
dbo.CVB_Tbl.personaladvocacy,
dbo.CVB_Tbl.b1_acc_emer_medcare,
dbo.CVB_Tbl.b2_acc_medforexam,
dbo.CVB_Tbl.b3_acc_int_lawadv,
dbo.CVB_Tbl.b4_indabvoc,
dbo.CVB_Tbl.b5_perf_medexam,
dbo.CVB_Tbl.b6_immagr_asst,
dbo.CVB_Tbl.b7_interv_empl_ll_sch,
dbo.CVB_Tbl.b8_cld_dep_careasst,
dbo.CVB_Tbl.b9_agncy_trnspt,
dbo.CVB_Tbl.b10_interpret_svcs,
dbo.CVB_Tbl.counseling,
dbo.CVB_Tbl.c1_crisis_interv,
dbo.CVB_Tbl.c2_hotline_crisis,
dbo.CVB_Tbl.c3_onscene_crisis_resp,
dbo.CVB_Tbl.c4_indiv_council,
dbo.CVB_Tbl.c5_supp_grps,
dbo.CVB_Tbl.c6_therapy,
dbo.CVB_Tbl.c7_emer_finan_asst,
dbo.CVB_Tbl.emergencyassist,
dbo.CVB_Tbl.d_shelter_house_svcs,
dbo.CVB_Tbl.d1_emer_safehse,
dbo.CVB_Tbl.d2_tradtl_house,
dbo.CVB_Tbl.d3_reloacte_asst,
dbo.CVB_Tbl.crimejustsupport,
dbo.CVB_Tbl.e1_notif_of_crim,
dbo.CVB_Tbl.e2_vic_impt_stmt,
dbo.CVB_Tbl.e3_restitut_asst,
dbo.CVB_Tbl.e4_atty_prot_restr_ordr,
dbo.CVB_Tbl.e5_atty_asst_famlaw,
dbo.CVB_Tbl.e6_oth_emer_justice_rel,
dbo.CVB_Tbl.e7_atty_immigr_asst,
dbo.CVB_Tbl.e8_prosec_intv_accomp,
dbo.CVB_Tbl.e9_law_enf_int_accomp,
dbo.CVB_Tbl.e10_crim_advoc_accomp,
dbo.CVB_Tbl.e11_oth_leg_consul
FROM dbo.INTAKE_Tbl
LEFT OUTER JOIN dbo.CVB_Tbl ON dbo.INTAKE_Tbl.fileno = dbo.CVB_Tbl.intake_fileno
where dbo.INTAKE_Tbl.date between '2016-05-1 00:00:00:000' and '2016-05-31 00:00:00:000'
and dbo.CVB_Tbl.date between '2016-05-1 00:00:00:000' and '2016-05-31 00:00:00:000'
ORDER BY dbo.INTAKE_Tbl.fileno DESC
Can someone help me solve this... thank you
Here is the script:
SELECT
dbo.INTAKE_Tbl.fileno,
dbo.INTAKE_Tbl.[date] AS [Intake Date],
dbo.CVB_Tbl.[date] AS [Service Date],
dbo.INTAKE_Tbl.firstname,
dbo.INTAKE_Tbl.lastname,
dbo.INTAKE_Tbl.dob,
dbo.CVB_Tbl.newintakecase,
dbo.INTAKE_Tbl.race_amerindian,
dbo.INTAKE_Tbl.race_asian,
dbo.INTAKE_Tbl.race_blkaframer,
dbo.INTAKE_Tbl.race_hisplatino,
dbo.INTAKE_Tbl.race_ntchawaiinpacislnd,
dbo.INTAKE_Tbl.race_whtnonlatinocauc,
dbo.INTAKE_Tbl.race_someotherrace,
dbo.INTAKE_Tbl.race_multi,
dbo.INTAKE_Tbl.race_notdisclosed,
dbo.INTAKE_Tbl.gndr_male,
dbo.INTAKE_Tbl.gndr_female,
dbo.INTAKE_Tbl.gndr_other_expl,
dbo.INTAKE_Tbl.gndr_notdisclosed,
dbo.INTAKE_Tbl.agp_0_12,
dbo.INTAKE_Tbl.agp_13_17,
dbo.INTAKE_Tbl.agp_18_24,
dbo.INTAKE_Tbl.agp_25_59,
dbo.INTAKE_Tbl.agp_60_oldr,
dbo.INTAKE_Tbl.agp_notdiclosed,
dbo.INTAKE_Tbl.assault,
dbo.INTAKE_Tbl.adultsexualabuse,
dbo.INTAKE_Tbl.vt_adltsexabuseaschild,
dbo.INTAKE_Tbl.vt_arson,
dbo.INTAKE_Tbl.vt_bullying_cyverphy,
dbo.INTAKE_Tbl.burgulary,
dbo.INTAKE_Tbl.childphysicalabue,
dbo.INTAKE_Tbl.vt_childporn,
dbo.INTAKE_Tbl.childsexualabuse,
dbo.INTAKE_Tbl.domesticviolence,
dbo.INTAKE_Tbl.dwidui,
dbo.INTAKE_Tbl.elderabuse,
dbo.INTAKE_Tbl.vt_hatecrime,
dbo.INTAKE_Tbl.vt_humtrfk_lbr,
dbo.INTAKE_Tbl.vt_humtrfk_sex,
dbo.INTAKE_Tbl.vt_idtheft_fraud,
dbo.INTAKE_Tbl.vt_kidnap_noncust,
dbo.INTAKE_Tbl.vt_kidnap_cust,
dbo.INTAKE_Tbl.vt_massviolence,
dbo.INTAKE_Tbl.vt_vehicular_h_n_r,
dbo.INTAKE_Tbl.robbery,
dbo.INTAKE_Tbl.vt_stalk_harrass,
dbo.INTAKE_Tbl.familyofhomicide,
dbo.INTAKE_Tbl.vt_teendatevic,
dbo.INTAKE_Tbl.vt_terrorism,
dbo.INTAKE_Tbl.vt_viol_ct_ordr,
dbo.INTAKE_Tbl.vt_other,
dbo.INTAKE_Tbl.sclass_deaf,
dbo.INTAKE_Tbl.sclass_homeless,
dbo.INTAKE_Tbl.sclass_immigrant_rfg_asy,
dbo.INTAKE_Tbl.sclass_lgntq,
dbo.INTAKE_Tbl.sclass_veteran,
dbo.INTAKE_Tbl.sclass_victim_cog_phys_mntl_disable,
dbo.INTAKE_Tbl.sclass_victim_limtd_eng,
dbo.INTAKE_Tbl.sclass_other,
dbo.CVB_Tbl.cvbinfoandassess,
dbo.CVB_Tbl.inforeferral,
dbo.CVB_Tbl.a1_info_cjp,
dbo.CVB_Tbl.a2_vicrts,
dbo.CVB_Tbl.a3_referothpgrm,
dbo.CVB_Tbl.a4_referothsvcs,
dbo.CVB_Tbl.personaladvocacy,
dbo.CVB_Tbl.b1_acc_emer_medcare,
dbo.CVB_Tbl.b2_acc_medforexam,
dbo.CVB_Tbl.b3_acc_int_lawadv,
dbo.CVB_Tbl.b4_indabvoc,
dbo.CVB_Tbl.b5_perf_medexam,
dbo.CVB_Tbl.b6_immagr_asst,
dbo.CVB_Tbl.b7_interv_empl_ll_sch,
dbo.CVB_Tbl.b8_cld_dep_careasst,
dbo.CVB_Tbl.b9_agncy_trnspt,
dbo.CVB_Tbl.b10_interpret_svcs,
dbo.CVB_Tbl.counseling,
dbo.CVB_Tbl.c1_crisis_interv,
dbo.CVB_Tbl.c2_hotline_crisis,
dbo.CVB_Tbl.c3_onscene_crisis_resp,
dbo.CVB_Tbl.c4_indiv_council,
dbo.CVB_Tbl.c5_supp_grps,
dbo.CVB_Tbl.c6_therapy,
dbo.CVB_Tbl.c7_emer_finan_asst,
dbo.CVB_Tbl.emergencyassist,
dbo.CVB_Tbl.d_shelter_house_svcs,
dbo.CVB_Tbl.d1_emer_safehse,
dbo.CVB_Tbl.d2_tradtl_house,
dbo.CVB_Tbl.d3_reloacte_asst,
dbo.CVB_Tbl.crimejustsupport,
dbo.CVB_Tbl.e1_notif_of_crim,
dbo.CVB_Tbl.e2_vic_impt_stmt,
dbo.CVB_Tbl.e3_restitut_asst,
dbo.CVB_Tbl.e4_atty_prot_restr_ordr,
dbo.CVB_Tbl.e5_atty_asst_famlaw,
dbo.CVB_Tbl.e6_oth_emer_justice_rel,
dbo.CVB_Tbl.e7_atty_immigr_asst,
dbo.CVB_Tbl.e8_prosec_intv_accomp,
dbo.CVB_Tbl.e9_law_enf_int_accomp,
dbo.CVB_Tbl.e10_crim_advoc_accomp,
dbo.CVB_Tbl.e11_oth_leg_consul
FROM dbo.INTAKE_Tbl
LEFT OUTER JOIN dbo.CVB_Tbl ON dbo.INTAKE_Tbl.fileno = dbo.CVB_Tbl.intake_fileno
where dbo.INTAKE_Tbl.date between '2016-05-1 00:00:00:000' and '2016-05-31 00:00:00:000'
and dbo.CVB_Tbl.date between '2016-05-1 00:00:00:000' and '2016-05-31 00:00:00:000'
ORDER BY dbo.INTAKE_Tbl.fileno DESC