i not world's greatest sql author, until thought @ least decent @ this. trying write query sums count of column based on input parameters , find date falls between start date , end date.
however, cannot make reference date column, though column exists in 1 of tables i'm querying. error sql server studio says "invalid column name". note column name unique between 2 tables , i'm using qualified name, chances of naming conflict low in opinion. below sql i'm using:
use [db_rpe_config] go set ansi_nulls on go set quoted_identifier on go declare @startdate datetime declare @enddate datetime declare @doctype int -- leave unset select form type -- set @doctype = 700000 set @startdate = convert(datetime, '2016-06-08 00:00:00') set @enddate = convert(datetime, '2016-06-10 23:59:59') select r_sdf_definition.code, sum("forms filed payment") "forms filed payment", sum("forms filed without payment") "forms filed without payment" (select sdf_definition_key, count(*) "forms filed payment", 0 "forms filed without payment" its_extnl_staging_document sdf_definition_key = isnull(@doctype, sdf_definition_key) , remittance_amount > 0 group sdf_definition_key union select sdf_definition_key, 0 "forms filed payment", count(*) "forms filed without payment" its_extnl_staging_document sdf_definition_key = isnull(@doctype, sdf_definition_key) , remittance_amount = 0 group sdf_definition_key) its_extnl_staging_document left join r_sdf_definition on its_extnl_staging_document.sdf_definition_key = r_sdf_definition.sdf_definition_key its_extnl_staging_document.submitted_dttm between @startdate , @enddate group r_sdf_definition.code when attempt use query without clause, results come fine. totals various form types, whether specify form type or let default form types present.
can make suggestions or point me in right direction?
edit --------------------
by taking suggested course of adding specification inner selects, following. outer select still errors out same way:
select r_sdf_definition.code, sum("forms filed payment") "forms filed payment", sum("forms filed without payment") "forms filed without payment" (select sdf_definition_key, count(*) "forms filed payment", 0 "forms filed without payment", "submitted_dttm" submitted_dttm its_extnl_staging_document sdf_definition_key = isnull(@doctype, sdf_definition_key) , remittance_amount > 0 , (its_extnl_staging_document.submitted_dttm between @startdate , @enddate) group sdf_definition_key union select sdf_definition_key, 0 "forms filed payment", count(*) "forms filed without payment", "submitted_dttm" submitted_dttm its_extnl_staging_document sdf_definition_key = isnull(@doctype, sdf_definition_key) , remittance_amount = 0 , (its_extnl_staging_document.submitted_dttm between @startdate , @enddate) group sdf_definition_key ) its_extnl_staging_document left join r_sdf_definition on its_extnl_staging_document.sdf_definition_key = r_sdf_definition.sdf_definition_key its_extnl_staging_document.submitted_dttm between @startdate , @enddate group r_sdf_definition.code
if you're talking final clause, not present.
where its_extnl_staging_document.submitted_dttm between @startdate , @enddate the issue there no column named "submitted_dttm" in 2 select clauses have joined union.
you need either include in each inner select or put clause in each select. can leave outer select alone.
another option submitted_dttm column on other table have left joined.
update
this little different approach, might easier understand:
select sdf_definition_key, remittancetype, count(*) its_extnl_staging_document left join r_sdf_definition on its_extnl_staging_document.sdf_definition_key = r_sdf_definition.sdf_definition_key sdf_definition_key = isnull(@doctype, sdf_definition_key) group r_sdf_definition.code, case when (remittance_amount = 0) 1 else 2 end remittancetype this should give 2 types of rows:
key remittancetype count you wrap in select then.
Comments
Post a Comment