sql server - Group by does not work - SQL 2008 R2 -
i have 3 rows need outputted 1 row, script below should do, can't seem have expected result, shows 3 rows, there i'm missing? should sum fields since in "group" clause?
btw, working before made changes following result fields;
tsl_day_a tsl_net_a tsl_tx_sal tsl_chg these fields assigned 0.00 default values.
i have eyes tired staring @ it,...
select br_code ,n_check ,clas_c ,clas_trd_c ,stor_no ,sum(tsl_new_a) tsl_new_a ,sum(tsl_old_a) tsl_old_a ,salestype ,sum(tsl_day_a) tsl_day_a ,sum(tsl_dis_a) tsl_dis_a ,tsl_dis_b ,sum(tsl_dis_c) tsl_dis_c ,tsl_dis_d ,sum(tsl_dis_e) tsl_dis_e ,tsl_dis_f ,tsl_dis_g ,tsl_dis_h ,tsl_dis_i ,tsl_dis_j ,tsl_dis_k ,tsl_dis_l ,sum(tsl_tax_a) tsl_tax_a ,tsl_tax_b ,tsl_adj_a ,tsl_adj_pos ,ts_adj_neg ,tsl_adj_nt_pos ,tsl_adj_nt_neg ,sum(tsl_net_a) tsl_net_a ,sum(tsl_void) tsl_void ,sum(tsl_rfnd) tsl_rfnd ,sum(tsl_tx_sal) tsl_tx_sal ,sum(tsl_nx_sal) tsl_nx_sal ,tsl_chg ,sum(tsl_csh) tsl_csh ,sum(tsl_gc) tsl_gc ,tsl_eps ,tsl_tnd ,tsl_mcrd ,tsl_visa ,tsl_amex ,tsl_diners ,tsl_jbc ,tsl_otcrd ,tsl_sv_chg ,tsl_ot_chg ,sum(tsl_ft) tsl_ft ,sum(tsl_lt) tsl_lt ,sum(tsl_nt) tsl_nt ,sum(tsl_beg_inv) tsl_beg_inv ,sum(tsl_end_inv) tsl_end_inv ,sum(tsl_tc_cash) tsl_tc_cash ,sum(tsl_tc_gc) tsl_tc_gc ,tsl_tc_eps ,tsl_tc_tnd ,tsl_tc_mcd ,tsl_tc_vis ,tsl_tc_amx ,tsl_tc_din ,tsl_tc_jbc ,tsl_tc_oc ,tsl_mch ,tsl_srl ,sum(tsl_zcnt) tsl_zcnt ,tsl_time ,tsl_dte from( select br_code ,n_check ,clas_c ,clas_trd_c ,stor_no ,sum(tsl_new_a) tsl_new_a ,sum(tsl_old_a) tsl_old_a ,salestype ,sum(tsl_day_a) tsl_day_a ,sum(tsl_dis_a) tsl_dis_a ,tsl_dis_b ,sum(tsl_dis_c) tsl_dis_c ,tsl_dis_d ,sum(tsl_dis_e) tsl_dis_e ,tsl_dis_f ,tsl_dis_g ,tsl_dis_h ,tsl_dis_i ,tsl_dis_j ,tsl_dis_k ,tsl_dis_l ,sum(tsl_tax_a) tsl_tax_a ,tsl_tax_b ,tsl_adj_a ,tsl_adj_pos ,ts_adj_neg ,tsl_adj_nt_pos ,tsl_adj_nt_neg ,sum(tsl_net_a) tsl_net_a ,sum(tsl_void) tsl_void ,sum(tsl_rfnd) tsl_rfnd ,sum(tsl_tx_sal) tsl_tx_sal ,sum(tsl_nx_sal) tsl_nx_sal ,tsl_chg ,sum(tsl_csh) tsl_csh ,sum(tsl_gc) tsl_gc ,tsl_eps ,tsl_tnd ,tsl_mcrd ,tsl_visa ,tsl_amex ,tsl_diners ,tsl_jbc ,tsl_otcrd ,tsl_sv_chg ,tsl_ot_chg ,sum(tsl_ft) tsl_ft ,sum(tsl_lt) tsl_lt ,sum(tsl_nt) tsl_nt ,sum(tsl_beg_inv) tsl_beg_inv ,sum(tsl_end_inv) tsl_end_inv ,sum(tsl_tc_cash) tsl_tc_cash ,sum(tsl_tc_gc) tsl_tc_gc ,tsl_tc_eps ,tsl_tc_tnd ,tsl_tc_mcd ,tsl_tc_vis ,tsl_tc_amx ,tsl_tc_din ,tsl_tc_jbc ,tsl_tc_oc ,tsl_mch ,tsl_srl ,sum(tsl_zcnt) tsl_zcnt ,tsl_time ,tsl_dte (select @branchcode br_code ,@ncheck n_check ,@classc clas_c ,@classtrd clas_trd_c ,@storeno stor_no ,sum(cast(ngtotal decimal(16,2)) + cast(ogtotal decimal(16,2))) tsl_new_a ,sum(cast(ogtotal decimal(16,2))) tsl_old_a ,@salestype salestype ,sum(((cast(salesregular decimal(16,2)) - cast(salesvatexempt decimal(16,2))) + cast(salescd20 decimal(16,2)) + cast(salescd5 decimal(16,2)) + cast(salepwd decimal(16,2)) + cast(salestgovt decimal(16,2)) + cast(saleszerorated decimal(16,2)) + cast(discregularamt decimal(16,2)) + cast(discscd20amt decimal(16,2)) + cast(discscd5amt decimal(16,2)) + cast(discpwd20amt decimal(16,2)) + cast(discpwd5amt decimal(16,2)) + cast(voidtranamt decimal(16,2)) + cast(itemvoidamt decimal(16,2)) + cast(ticketretamt decimal(16,2)) + cast(itemretamt decimal(16,2)))/1.12) + cast(salesvatexempt decimal(16,2)) tsl_day_a --sum(cast(round((cast(tsales decimal)/1.12),2) decimal(16,2))) tsl_day_a --help ,sum(abs(cast(discregularamt decimal(16,2)))) tsl_dis_a ,0.00 tsl_dis_b ,sum(cast(discscd20amt decimal(16,2)) + cast(discscd5amt decimal(16,2))) tsl_dis_c ,0.00 tsl_dis_d ,sum(cast(discscd20amt decimal(16,2)) + cast(discscd5amt decimal(16,2))) tsl_dis_e ,0.00 tsl_dis_f ,0.00 tsl_dis_g ,0.00 tsl_dis_h ,0.00 tsl_dis_i ,0.00 tsl_dis_j ,0.00 tsl_dis_k ,0.00 tsl_dis_l ,sum(cast(vatsaleamt decimal(16,2))) tsl_tax_a ,0.00 tsl_tax_b ,0.00 tsl_adj_a ,0.00 tsl_adj_pos ,0.00 ts_adj_neg ,0.00 tsl_adj_nt_pos ,0.00 tsl_adj_nt_neg ,sum(cast(dgrosssales decimal(16,2)) - cast(totaltaxvalue decimal(16,2))) tsl_net_a --help ,sum(cast(voidtranamt decimal(16,2))) tsl_void ,sum(abs(cast(itemretamt decimal(16,2)))) tsl_rfnd ,sum(((cast(salesregular decimal(16,2)) + cast(salesvatexempt decimal(16,2))) + cast(salescd20 decimal(16,2)) + cast(salescd5 decimal(16,2)) + cast(salepwd decimal(16,2)) + cast(salestgovt decimal(16,2)) + cast(saleszerorated decimal(16,2)) + cast(discregularamt decimal(16,2)) + cast(discscd20amt decimal(16,2)) + cast(discscd5amt decimal(16,2)) + cast(discpwd20amt decimal(16,2)) + cast(discpwd5amt decimal(16,2)) + cast(voidtranamt decimal(16,2)) + cast(itemvoidamt decimal(16,2)) + cast(ticketretamt decimal(16,2)) + cast(itemretamt decimal(16,2)))) tsl_tx_sal --help ,(cast(salesvatexempt decimal(16,2)) + (cast(zeroratedsaleamt decimal(16,2)) - cast(zeroratedsaletaxvalue decimal(16,2)))) tsl_nx_sal ,sum(cast(ccardatmchrgonlineamt decimal(16,2))) tsl_chg --help ,sum(cast(tendercashamt decimal(16,2))) tsl_csh ,sum(cast(othtendersmgcamt decimal(16,2)) + cast(othtendersodexoamt decimal(16,2))) tsl_gc --+ cast(tenderchkamt decimal) tsl_gc ,0.00 tsl_eps ,0.00 tsl_tnd ,0.00 tsl_mcrd ,0.00 tsl_visa ,0.00 tsl_amex ,0.00 tsl_diners ,0.00 tsl_jbc ,0.00 tsl_otcrd ,0.00 tsl_sv_chg ,0.00 tsl_ot_chg ,sum(cast(abs(begsi) int)) tsl_ft ,sum(cast(abs(lassi) int)) tsl_lt ,sum(cast((cast(qtyregular decimal(18,2)) + cast(qtyscd20 decimal(18,2)) + cast(qtyscd5 decimal(18,2)) + cast(qtypwd decimal(18,2)) + cast(qtytgovt decimal(18,2)) + cast(qtyzerorated decimal(18,2)) + cast(qtyvatexempt decimal(18,2)) + cast(discregularqty decimal(18,2)) + cast(discscd20qty decimal(18,2)) + cast(discscd5qty decimal(18,2)) + cast(discpwd20qty decimal(18,2)) + cast(discpwd5qty decimal(18,2)) + cast(voidtranqty decimal(18,2)) + cast(itemvoidqty decimal(18,2))*-1 + cast(ticketretqty decimal(18,2))*-1 + cast(itemretqty decimal(18,2))*-1 + cast(addloantotqty decimal(18,2))) int)) tsl_nt ,sum(cast(abs(begsi) int)) tsl_beg_inv ,sum(cast(abs(lassi) int)) tsl_end_inv ,sum(cast(qtyregular int)) tsl_tc_cash ,sum(cast(othtendersmgcqty int) + cast(othtendersodexoqty int)) tsl_tc_gc --+ cast(tenderchkqty int) tsl_tc_gc ,0 tsl_tc_eps ,0 tsl_tc_tnd ,0 tsl_tc_mcd ,0 tsl_tc_vis ,0 tsl_tc_amx ,0 tsl_tc_din ,0 tsl_tc_jbc ,0 tsl_tc_oc ,@machineno tsl_mch ,0 tsl_srl ,sum(cast(abs(zcounter) int)) tsl_zcnt --,left(convert(time,getdate()),8) tsl_time ,right('00'+rtrim(cast(datepart(hh,getdate())as varchar(5))),2)+right('00'+rtrim(cast(datepart(mi,getdate()) varchar(5))),2)+right('00'+rtrim(cast(datepart(ss,getdate()) varchar(5))),2) tsl_time --,substring(convert(varchar,workdate,111),6,2)+'/'+right(convert(varchar,workdate,111),2)+'/'+left(convert(varchar,workdate,111),4) tsl_dte ,substring(convert(varchar,workdate,111),6,2)+right(convert(varchar,workdate,111),2)+left(convert(varchar,workdate,111),4) tsl_dte dbo.termztran t left join (select slessee, stenantnumber tenantcode, sstorenumber mlandlordstore) tinfo on cast(t.store int) = cast(tinfo.sstorenumber int) cast(t.store int) = cast(@branchcode int) --and cast(t.pos int) = cast(@posno int) , tinfo.slessee = @lesseecode , convert(nvarchar(10),t.workdate,101) = @trandate --and convert(nvarchar(10),t.workdate,101) = '04/16/2013' --@trandate group salesvatexempt ,zeroratedsaleamt ,zeroratedsaletaxvalue ,workdate ) tfinal --where cast(br_code int) = cast('0040' int) group br_code ,n_check ,clas_c ,clas_trd_c ,stor_no ,tsl_new_a ,tsl_old_a ,salestype ,tsl_day_a ,tsl_dis_a ,tsl_dis_b ,tsl_dis_c ,tsl_dis_d ,tsl_dis_e ,tsl_dis_f ,tsl_dis_g ,tsl_dis_h ,tsl_dis_i ,tsl_dis_j ,tsl_dis_k ,tsl_dis_l ,tsl_tax_a ,tsl_tax_b ,tsl_adj_a ,tsl_adj_pos ,ts_adj_neg ,tsl_adj_nt_pos ,tsl_adj_nt_neg ,tsl_net_a ,tsl_void ,tsl_rfnd ,tsl_tx_sal ,tsl_nx_sal ,tsl_chg ,tsl_csh ,tsl_gc ,tsl_eps ,tsl_tnd ,tsl_mcrd ,tsl_visa ,tsl_amex ,tsl_diners ,tsl_jbc ,tsl_otcrd ,tsl_sv_chg ,tsl_ot_chg ,tsl_ft ,tsl_lt ,tsl_nt ,tsl_beg_inv ,tsl_end_inv ,tsl_tc_cash ,tsl_tc_gc ,tsl_tc_eps ,tsl_tc_tnd ,tsl_tc_mcd ,tsl_tc_vis ,tsl_tc_amx ,tsl_tc_din ,tsl_tc_jbc ,tsl_tc_oc ,tsl_mch ,tsl_srl ,tsl_zcnt ,tsl_time ,tsl_dte) dt group dt.br_code ,dt.clas_c ,dt.clas_trd_c ,dt.n_check ,dt.stor_no ,dt.salestype ,tsl_day_a ,tsl_dis_a ,tsl_dis_b ,tsl_dis_c ,tsl_dis_d ,tsl_dis_e ,tsl_dis_f ,tsl_dis_g ,tsl_dis_h ,tsl_dis_i ,tsl_dis_j ,tsl_dis_k ,tsl_dis_l ,tsl_tax_a ,tsl_tax_b ,tsl_adj_a ,tsl_adj_pos ,ts_adj_neg ,tsl_adj_nt_pos ,tsl_adj_nt_neg ,tsl_net_a ,tsl_void ,tsl_rfnd ,tsl_tx_sal ,tsl_nx_sal ,tsl_chg ,tsl_csh ,tsl_gc ,tsl_eps ,tsl_tnd ,tsl_mcrd ,tsl_visa ,tsl_amex ,tsl_diners ,tsl_jbc ,tsl_otcrd ,tsl_sv_chg ,tsl_ot_chg ,tsl_ft ,tsl_lt ,tsl_nt ,tsl_beg_inv ,tsl_end_inv ,tsl_tc_cash ,tsl_tc_gc ,tsl_tc_eps ,tsl_tc_tnd ,tsl_tc_mcd ,tsl_tc_vis ,tsl_tc_amx ,tsl_tc_din ,tsl_tc_jbc ,tsl_tc_oc ,tsl_mch ,tsl_srl ,tsl_zcnt ,tsl_time ,tsl_dte here's script create sample tables , data
use [landlord] go /****** object: table [dbo].[mlandlordstore] script date: 04/25/2013 20:21:42 ******/ set ansi_nulls on go set quoted_identifier on go set ansi_padding on go create table [dbo].[mlandlordstore]( [idnumber] [int] identity(1,1) not null, [sstorenumber] [nvarchar](50) not null, [slessee] [nvarchar](50) not null, [slesseenumber] [int] not null, [shogenerated] [varchar](1) not null, [lasttranno] [int] null, [sspace] [nvarchar](50) null, [smallcode] [nvarchar](20) null, [stenantnumber] [nvarchar](20) null, [sclasscode] [nvarchar](20) null, [stradecode] [nvarchar](20) null, [soutletnumber] [nvarchar](20) null, [nsaletype] [int] null, [batchcount] [int] null ) on [primary] go set ansi_padding off go set identity_insert [dbo].[mlandlordstore] on insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (1, n'40', n'smcoin', 11002558, n'y', 0, n'01', n'72', n'011002559', n'01', n'sap', n'03', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (3, n'1033', n'smcoin', 103312345, n'y', 0, n'01', n'73', n'7312345', n'01', n'sap', n'04', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (4, n'1133', n'aci', 113312345, n'y', 0, n'01', n'74', n'7412345', n'01', n'sap', n'05', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (5, n'1233', n'ali', 123312345, n'y', 0, n'01', n'75', n'7512345', n'01', n'sap', n'06', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (6, n'1333', n'mwc', 133312345, n'y', 0, n'01', n'76', n'7612345', n'01', n'sap', n'07', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (7, n'1433', n'oclp', 143312345, n'y', 0, n'01', n'77', n'7712345', n'01', n'sap', n'08', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (8, n'1533', n'rlc', 153312345, n'y', 0, n'01', n'78', n'7812345', n'01', n'sap', n'09', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (9, n'1633', n'sle', 163312345, n'y', 0, n'01', n'79', n'7912345', n'01', n'sap', n'10', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (10, n'1733', n'star', 173312345, n'y', 0, n'01', n'80', n'8012345', n'01', n'sap', n'11', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (11, n'1833', n'wm', 183312345, n'y', 0, n'01', n'81', n'8112345', n'01', n'sap', n'12', 1, 0) insert [dbo].[mlandlordstore] ([idnumber], [sstorenumber], [slessee], [slesseenumber], [shogenerated], [lasttranno], [sspace], [smallcode], [stenantnumber], [sclasscode], [stradecode], [soutletnumber], [nsaletype], [batchcount]) values (12, n'1933', n'fsi', 193312345, n'y', 0, n'01', n'82', n'8212345', n'01', n'sap', n'13', 1, 0) set identity_insert [dbo].[mlandlordstore] off /****** object: default [df_mlandlordstore_lasttranno] script date: 04/25/2013 20:21:42 ******/ alter table [dbo].[mlandlordstore] add constraint [df_mlandlordstore_lasttranno] default ((1)) [lasttranno] go use [landlord] go /****** object: table [dbo].[termztran] script date: 04/25/2013 20:19:54 ******/ set ansi_nulls on go set quoted_identifier on go create table [dbo].[termztran]( [headerid] [uniqueidentifier] not null, [workdate] [smalldatetime] not null, [processdate] [smalldatetime] not null, [processmode] [nvarchar](10) not null, [poscount] [int] null, [salepharma] [money] null, [salenonpharma] [money] null, [voidamount] [money] null, [voidcount] [int] null, [zcounter] [nvarchar](4000) null, [store] [nvarchar](4000) null, [pos] [nvarchar](4000) null, [date] [nvarchar](300) null, [time] [nvarchar](300) null, [ogtotal] [nvarchar](300) null, [ngtotal] [nvarchar](300) null, [begsi] [nvarchar](300) null, [lassi] [nvarchar](300) null, [tsales] [nvarchar](300) null, [qtyregular] [nvarchar](300) null, [salesregular] [nvarchar](300) null, [qtyscd20] [nvarchar](300) null, [salescd20] [nvarchar](300) null, [qtyscd5] [nvarchar](300) null, [salescd5] [nvarchar](300) null, [qtypwd] [nvarchar](300) null, [salepwd] [nvarchar](300) null, [qtytgovt] [nvarchar](300) null, [salestgovt] [nvarchar](300) null, [qtyzerorated] [nvarchar](300) null, [saleszerorated] [nvarchar](300) null, [qtyvatexempt] [nvarchar](300) null, [salesvatexempt] [nvarchar](300) null, [tdiscount] [nvarchar](300) null, [discregularqty] [nvarchar](300) null, [discregularamt] [nvarchar](300) null, [discscd20qty] [nvarchar](300) null, [discscd20amt] [nvarchar](300) null, [discscd5qty] [nvarchar](300) null, [discscd5amt] [nvarchar](300) null, [discpwd20qty] [nvarchar](300) null, [discpwd20amt] [nvarchar](300) null, [discpwd5qty] [nvarchar](300) null, [discpwd5amt] [nvarchar](300) null, [dnetsales] [nvarchar](300) null, [dgrosssales] [nvarchar](300) null, [salesaccountability] [nvarchar](300) null, [voidtranqty] [nvarchar](300) null, [voidtranamt] [nvarchar](300) null, [itemvoidqty] [nvarchar](300) null, [itemvoidamt] [nvarchar](300) null, [ticketretqty] [nvarchar](300) null, [ticketretamt] [nvarchar](300) null, [itemretqty] [nvarchar](300) null, [itemretamt] [nvarchar](300) null, [addloantotqty] [nvarchar](300) null, [addloantotamt] [nvarchar](300) null, [tendercashqty] [nvarchar](300) null, [tendercashamt] [nvarchar](300) null, [tenderatmofflineqty] [nvarchar](300) null, [tenderatmofflineamt] [nvarchar](300) null, [tenderchrgofflineqty] [nvarchar](300) null, [tenderchrgofflineamt] [nvarchar](300) null, [tenderchkqty] [nvarchar](300) null, [tenderchkamt] [nvarchar](300) null, [tenderbdorebatesqty] [nvarchar](300) null, [tenderbdorebatesamt] [nvarchar](300) null, [overtenderqty] [nvarchar](300) null, [overtenderamt] [nvarchar](300) null, [othertenderqty] [nvarchar](300) null, [othertenderamt] [nvarchar](300) null, [tenderccmqty] [nvarchar](300) null, [tenderccmamt] [nvarchar](300) null, [tenderarcodqty] [nvarchar](300) null, [tenderarcodamt] [nvarchar](300) null, [tenderadbqty] [nvarchar](300) null, [tenderadbamt] [nvarchar](300) null, [tenderfwv5qty] [nvarchar](300) null, [tenderfwv5amt] [nvarchar](300) null, [tenderewt1qty] [nvarchar](300) null, [tenderewt1amt] [nvarchar](300) null, [tendertop20kqty] [nvarchar](300) null, [tendertop20kamt] [nvarchar](300) null, [ccardatmchrgonlineqty] [nvarchar](300) null, [ccardatmchrgonlineamt] [nvarchar](300) null, [othtendersmgcqty] [nvarchar](300) null, [othtendersmgcamt] [nvarchar](300) null, [othtendersodexoqty] [nvarchar](300) null, [othtendersodexoamt] [nvarchar](300) null, [othtendercorpaccqty] [nvarchar](300) null, [othtendercorpaccamt] [nvarchar](300) null, [othtenderdifferedqty] [nvarchar](300) null, [othtenderdifferedamt] [nvarchar](300) null, [tendertotal] [nvarchar](300) null, [vatsaleamt] [nvarchar](300) null, [vatsaletaxvalue] [nvarchar](300) null, [vatexsaleamt] [nvarchar](300) null, [vatexsaletaxvalue] [nvarchar](300) null, [zeroratedsaleamt] [nvarchar](300) null, [zeroratedsaletaxvalue] [nvarchar](300) null, [totalamt] [nvarchar](300) null, [totaltaxvalue] [nvarchar](300) null ) on [primary] go insert [dbo].[termztran] ([headerid], [workdate], [processdate], [processmode], [poscount], [salepharma], [salenonpharma], [voidamount], [voidcount], [zcounter], [store], [pos], [date], [time], [ogtotal], [ngtotal], [begsi], [lassi], [tsales], [qtyregular], [salesregular], [qtyscd20], [salescd20], [qtyscd5], [salescd5], [qtypwd], [salepwd], [qtytgovt], [salestgovt], [qtyzerorated], [saleszerorated], [qtyvatexempt], [salesvatexempt], [tdiscount], [discregularqty], [discregularamt], [discscd20qty], [discscd20amt], [discscd5qty], [discscd5amt], [discpwd20qty], [discpwd20amt], [discpwd5qty], [discpwd5amt], [dnetsales], [dgrosssales], [salesaccountability], [voidtranqty], [voidtranamt], [itemvoidqty], [itemvoidamt], [ticketretqty], [ticketretamt], [itemretqty], [itemretamt], [addloantotqty], [addloantotamt], [tendercashqty], [tendercashamt], [tenderatmofflineqty], [tenderatmofflineamt], [tenderchrgofflineqty], [tenderchrgofflineamt], [tenderchkqty], [tenderchkamt], [tenderbdorebatesqty], [tenderbdorebatesamt], [overtenderqty], [overtenderamt], [othertenderqty], [othertenderamt], [tenderccmqty], [tenderccmamt], [tenderarcodqty], [tenderarcodamt], [tenderadbqty], [tenderadbamt], [tenderfwv5qty], [tenderfwv5amt], [tenderewt1qty], [tenderewt1amt], [tendertop20kqty], [tendertop20kamt], [ccardatmchrgonlineqty], [ccardatmchrgonlineamt], [othtendersmgcqty], [othtendersmgcamt], [othtendersodexoqty], [othtendersodexoamt], [othtendercorpaccqty], [othtendercorpaccamt], [othtenderdifferedqty], [othtenderdifferedamt], [tendertotal], [vatsaleamt], [vatsaletaxvalue], [vatexsaleamt], [vatexsaletaxvalue], [zeroratedsaleamt], [zeroratedsaletaxvalue], [totalamt], [totaltaxvalue]) values (n'35a9637c-4342-4cb4-8f10-1c1d52b09cd4', cast(0xa1a20000 smalldatetime), cast(0xa1a80478 smalldatetime), n'sqljob', 1, 0.0000, 0.0000, 0.0000, 0, n'0001', n'0040', n'001', n'16/04/2013', n'19:06', n'0.00', n'73420.22', n'0000000001', n'0000000078', n'69827.81', n'75', n'48543.85', n'7', n'15683.96', n'4', n'940.00', n'6', n'4520.00', n'0', n'0.00', n'0', n'0.00', n'1', n'140.00', n'8283.98', n'30', n'4361.04', n'7', n'3136.79', n'4', n'41.96', n'3', n'723.21', n'3', n'20.98', n'56502.49', n'73420.22', n'61760.30', n'3', n'5815.95', n'-33', n'-1282.50', n'-14', n'-5886.00', n'-151', n'-3923.18', n'2', n'6000.00', n'68', n'46045.65', n'1', n'67.50', n'3', n'751.75', n'1', n'3200.00', n'2', n'310.82', n'2', n'-3.00', n'3', n'0.03', n'0', n'0.00', n'0', n'0.00', n'0', n'0', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'11', n'9096.12', n'5', n'800.00', n'5', n'450.00', n'1', n'1041.43', n'0', n'0.00', n'61760.30', n'43815.32', n'5257.81', n'15823.96', n'0.00', n'0.00', n'0.00', n'59639.28', n'5257.81') insert [dbo].[termztran] ([headerid], [workdate], [processdate], [processmode], [poscount], [salepharma], [salenonpharma], [voidamount], [voidcount], [zcounter], [store], [pos], [date], [time], [ogtotal], [ngtotal], [begsi], [lassi], [tsales], [qtyregular], [salesregular], [qtyscd20], [salescd20], [qtyscd5], [salescd5], [qtypwd], [salepwd], [qtytgovt], [salestgovt], [qtyzerorated], [saleszerorated], [qtyvatexempt], [salesvatexempt], [tdiscount], [discregularqty], [discregularamt], [discscd20qty], [discscd20amt], [discscd5qty], [discscd5amt], [discpwd20qty], [discpwd20amt], [discpwd5qty], [discpwd5amt], [dnetsales], [dgrosssales], [salesaccountability], [voidtranqty], [voidtranamt], [itemvoidqty], [itemvoidamt], [ticketretqty], [ticketretamt], [itemretqty], [itemretamt], [addloantotqty], [addloantotamt], [tendercashqty], [tendercashamt], [tenderatmofflineqty], [tenderatmofflineamt], [tenderchrgofflineqty], [tenderchrgofflineamt], [tenderchkqty], [tenderchkamt], [tenderbdorebatesqty], [tenderbdorebatesamt], [overtenderqty], [overtenderamt], [othertenderqty], [othertenderamt], [tenderccmqty], [tenderccmamt], [tenderarcodqty], [tenderarcodamt], [tenderadbqty], [tenderadbamt], [tenderfwv5qty], [tenderfwv5amt], [tenderewt1qty], [tenderewt1amt], [tendertop20kqty], [tendertop20kamt], [ccardatmchrgonlineqty], [ccardatmchrgonlineamt], [othtendersmgcqty], [othtendersmgcamt], [othtendersodexoqty], [othtendersodexoamt], [othtendercorpaccqty], [othtendercorpaccamt], [othtenderdifferedqty], [othtenderdifferedamt], [tendertotal], [vatsaleamt], [vatsaletaxvalue], [vatexsaleamt], [vatexsaletaxvalue], [zeroratedsaleamt], [zeroratedsaletaxvalue], [totalamt], [totaltaxvalue]) values (n'35a9637c-4342-4cb4-8f10-1c1d52b09cd4', cast(0xa1a20000 smalldatetime), cast(0xa1a80478 smalldatetime), n'sqljob', 2, 0.0000, 0.0000, 0.0000, 0, n'0001', n'0040', n'002', n'16/04/2013', n'19:06', n'0.00', n'60296.14', n'0000000001', n'0000000076', n'58336.24', n'61', n'44770.01', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'7', n'4597.00', n'15', n'8269.23', n'4', n'700.00', n'3890.95', n'17', n'3890.95', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'49572.91', n'60296.14', n'54445.29', n'2', n'186.00', n'-133', n'-3567.50', n'-7', n'-6661.50', n'-90', n'-2085.50', n'2', n'6000.00', n'66', n'39251.01', n'1', n'67.50', n'1', n'149.00', n'0', n'0.00', n'0', n'0.00', n'3', n'-22.68', n'10', n'3989.88', n'0', n'0.00', n'0', n'0.00', n'0', n'0', n'11', n'205.23', n'11', n'41.05', n'13', n'41.05', n'8', n'4323.75', n'9', n'2500.00', n'1', n'100.00', n'2', n'3799.50', n'0', n'0.00', n'54445.29', n'40603.68', n'4872.38', n'700.00', n'0.00', n'8269.23', n'0.00', n'49572.91', n'4872.38') insert [dbo].[termztran] ([headerid], [workdate], [processdate], [processmode], [poscount], [salepharma], [salenonpharma], [voidamount], [voidcount], [zcounter], [store], [pos], [date], [time], [ogtotal], [ngtotal], [begsi], [lassi], [tsales], [qtyregular], [salesregular], [qtyscd20], [salescd20], [qtyscd5], [salescd5], [qtypwd], [salepwd], [qtytgovt], [salestgovt], [qtyzerorated], [saleszerorated], [qtyvatexempt], [salesvatexempt], [tdiscount], [discregularqty], [discregularamt], [discscd20qty], [discscd20amt], [discscd5qty], [discscd5amt], [discpwd20qty], [discpwd20amt], [discpwd5qty], [discpwd5amt], [dnetsales], [dgrosssales], [salesaccountability], [voidtranqty], [voidtranamt], [itemvoidqty], [itemvoidamt], [ticketretqty], [ticketretamt], [itemretqty], [itemretamt], [addloantotqty], [addloantotamt], [tendercashqty], [tendercashamt], [tenderatmofflineqty], [tenderatmofflineamt], [tenderchrgofflineqty], [tenderchrgofflineamt], [tenderchkqty], [tenderchkamt], [tenderbdorebatesqty], [tenderbdorebatesamt], [overtenderqty], [overtenderamt], [othertenderqty], [othertenderamt], [tenderccmqty], [tenderccmamt], [tenderarcodqty], [tenderarcodamt], [tenderadbqty], [tenderadbamt], [tenderfwv5qty], [tenderfwv5amt], [tenderewt1qty], [tenderewt1amt], [tendertop20kqty], [tendertop20kamt], [ccardatmchrgonlineqty], [ccardatmchrgonlineamt], [othtendersmgcqty], [othtendersmgcamt], [othtendersodexoqty], [othtendersodexoamt], [othtendercorpaccqty], [othtendercorpaccamt], [othtenderdifferedqty], [othtenderdifferedamt], [tendertotal], [vatsaleamt], [vatsaletaxvalue], [vatexsaleamt], [vatexsaletaxvalue], [zeroratedsaleamt], [zeroratedsaletaxvalue], [totalamt], [totaltaxvalue]) values (n'35a9637c-4342-4cb4-8f10-1c1d52b09cd4', cast(0xa1a20000 smalldatetime), cast(0xa1a80478 smalldatetime), n'sqljob', 3, 0.0000, 0.0000, 0.0000, 0, n'0001', n'0040', n'003', n'16/04/2013', n'19:06', n'0.00', n'0.00', n'0000000000', n'0000000000', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0.00', n'0.00', n'0.00', n'1', n'0.01', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0', n'0.00', n'0.00', n'0.00', n'0.00', n'0.00', n'0.00', n'0.00', n'0.00', n'0.00', n'0.00') please help...
you have quite weird data schema. did try store numeric values decimal rather nvarchar. possible may should -
select br_code ,n_check ,clas_c ,clas_trd_c ,stor_no ,sum(tsl_new_a) tsl_new_a ,sum(tsl_old_a) tsl_old_a ,salestype ,sum(tsl_day_a) tsl_day_a ,sum(tsl_dis_a) tsl_dis_a ,tsl_dis_b = 0 ,sum(tsl_dis_c) tsl_dis_c ,tsl_dis_d = 0 ,sum(tsl_dis_e) tsl_dis_e ,tsl_dis_f = 0 ,tsl_dis_g = 0 ,tsl_dis_h = 0 ,tsl_dis_i = 0 ,tsl_dis_j = 0 ,tsl_dis_k = 0 ,tsl_dis_l = 0 ,sum(tsl_tax_a) tsl_tax_a ,tsl_tax_b = 0 ,tsl_adj_a = 0 ,tsl_adj_pos = 0 ,ts_adj_neg = 0 ,tsl_adj_nt_pos = 0 ,tsl_adj_nt_neg = 0 ,sum(tsl_net_a) tsl_net_a ,sum(tsl_void) tsl_void ,sum(tsl_rfnd) tsl_rfnd ,sum(tsl_tx_sal) tsl_tx_sal ,sum(tsl_nx_sal) tsl_nx_sal ,tsl_chg ,sum(tsl_csh) tsl_csh ,sum(tsl_gc) tsl_gc ,tsl_eps = 0 ,tsl_tnd = 0 ,tsl_mcrd = 0 ,tsl_visa = 0 ,tsl_amex = 0 ,tsl_diners = 0 ,tsl_jbc = 0 ,tsl_otcrd = 0 ,tsl_sv_chg = 0 ,tsl_ot_chg = 0 ,sum(tsl_ft) tsl_ft ,sum(tsl_lt) tsl_lt ,sum(tsl_nt) tsl_nt ,sum(tsl_beg_inv) tsl_beg_inv ,sum(tsl_end_inv) tsl_end_inv ,sum(tsl_tc_cash) tsl_tc_cash ,sum(tsl_tc_gc) tsl_tc_gc ,tsl_tc_eps = 0 ,tsl_tc_tnd = 0 ,tsl_tc_mcd = 0 ,tsl_tc_vis = 0 ,tsl_tc_amx = 0 ,tsl_tc_din = 0 ,tsl_tc_jbc = 0 ,tsl_tc_oc = 0 ,tsl_mch = 0 ,tsl_srl = 0 ,sum(tsl_zcnt) tsl_zcnt ,tsl_time ,tsl_dte from( select br_code , n_check , clas_c , clas_trd_c , stor_no , sum(tsl_new_a) tsl_new_a , sum(tsl_old_a) tsl_old_a , salestype , sum(tsl_day_a) tsl_day_a , sum(tsl_dis_a) tsl_dis_a , sum(tsl_dis_c) tsl_dis_c , sum(tsl_dis_e) tsl_dis_e , sum(tsl_tax_a) tsl_tax_a , sum(tsl_net_a) tsl_net_a , sum(tsl_void) tsl_void , sum(tsl_rfnd) tsl_rfnd , sum(tsl_tx_sal) tsl_tx_sal , sum(tsl_nx_sal) tsl_nx_sal , tsl_chg , sum(tsl_csh) tsl_csh , sum(tsl_gc) tsl_gc , sum(tsl_ft) tsl_ft , sum(tsl_lt) tsl_lt , sum(tsl_nt) tsl_nt , sum(tsl_beg_inv) tsl_beg_inv , sum(tsl_end_inv) tsl_end_inv , sum(tsl_tc_cash) tsl_tc_cash , sum(tsl_tc_gc) tsl_tc_gc , sum(tsl_zcnt) tsl_zcnt , tsl_time , tsl_dte ( select br_code = 0 , n_check = 0 , clas_c = 0 , clas_trd_c = 0 , stor_no = 0 , tsl_new_a = sum(cast(ngtotal decimal(16,2)) + cast(ogtotal decimal(16,2))) , tsl_old_a = sum(cast(ogtotal decimal(16,2))) , salestype = 0 , sum(((cast(salesregular decimal(16,2)) - cast(salesvatexempt decimal(16,2))) + cast(salescd20 decimal(16,2)) + cast(salescd5 decimal(16,2)) + cast(salepwd decimal(16,2)) + cast(salestgovt decimal(16,2)) + cast(saleszerorated decimal(16,2)) + cast(discregularamt decimal(16,2)) + cast(discscd20amt decimal(16,2)) + cast(discscd5amt decimal(16,2)) + cast(discpwd20amt decimal(16,2)) + cast(discpwd5amt decimal(16,2)) + cast(voidtranamt decimal(16,2)) + cast(itemvoidamt decimal(16,2)) + cast(ticketretamt decimal(16,2)) + cast(itemretamt decimal(16,2)))/1.12) + cast(salesvatexempt decimal(16,2)) tsl_day_a --sum(cast(round((cast(tsales decimal)/1.12),2) decimal(16,2))) tsl_day_a --help ,sum(abs(cast(discregularamt decimal(16,2)))) tsl_dis_a ,sum(cast(discscd20amt decimal(16,2)) + cast(discscd5amt decimal(16,2))) tsl_dis_c ,sum(cast(discscd20amt decimal(16,2)) + cast(discscd5amt decimal(16,2))) tsl_dis_e ,sum(cast(vatsaleamt decimal(16,2))) tsl_tax_a ,sum(cast(dgrosssales decimal(16,2)) - cast(totaltaxvalue decimal(16,2))) tsl_net_a --help ,sum(cast(voidtranamt decimal(16,2))) tsl_void ,sum(abs(cast(itemretamt decimal(16,2)))) tsl_rfnd ,sum(((cast(salesregular decimal(16,2)) + cast(salesvatexempt decimal(16,2))) + cast(salescd20 decimal(16,2)) + cast(salescd5 decimal(16,2)) + cast(salepwd decimal(16,2)) + cast(salestgovt decimal(16,2)) + cast(saleszerorated decimal(16,2)) + cast(discregularamt decimal(16,2)) + cast(discscd20amt decimal(16,2)) + cast(discscd5amt decimal(16,2)) + cast(discpwd20amt decimal(16,2)) + cast(discpwd5amt decimal(16,2)) + cast(voidtranamt decimal(16,2)) + cast(itemvoidamt decimal(16,2)) + cast(ticketretamt decimal(16,2)) + cast(itemretamt decimal(16,2)))) tsl_tx_sal --help ,(cast(salesvatexempt decimal(16,2)) + (cast(zeroratedsaleamt decimal(16,2)) - cast(zeroratedsaletaxvalue decimal(16,2)))) tsl_nx_sal ,sum(cast(ccardatmchrgonlineamt decimal(16,2))) tsl_chg --help ,sum(cast(tendercashamt decimal(16,2))) tsl_csh ,sum(cast(othtendersmgcamt decimal(16,2)) + cast(othtendersodexoamt decimal(16,2))) tsl_gc --+ cast(tenderchkamt decimal) tsl_gc ,sum(cast(abs(begsi) int)) tsl_ft ,sum(cast(abs(lassi) int)) tsl_lt ,sum(cast((cast(qtyregular decimal(18,2)) + cast(qtyscd20 decimal(18,2)) + cast(qtyscd5 decimal(18,2)) + cast(qtypwd decimal(18,2)) + cast(qtytgovt decimal(18,2)) + cast(qtyzerorated decimal(18,2)) + cast(qtyvatexempt decimal(18,2)) + cast(discregularqty decimal(18,2)) + cast(discscd20qty decimal(18,2)) + cast(discscd5qty decimal(18,2)) + cast(discpwd20qty decimal(18,2)) + cast(discpwd5qty decimal(18,2)) + cast(voidtranqty decimal(18,2)) + cast(itemvoidqty decimal(18,2))*-1 + cast(ticketretqty decimal(18,2))*-1 + cast(itemretqty decimal(18,2))*-1 + cast(addloantotqty decimal(18,2))) int)) tsl_nt ,sum(cast(abs(begsi) int)) tsl_beg_inv ,sum(cast(abs(lassi) int)) tsl_end_inv ,sum(cast(qtyregular int)) tsl_tc_cash ,sum(cast(othtendersmgcqty int) + cast(othtendersodexoqty int)) tsl_tc_gc ,sum(cast(abs(zcounter) int)) tsl_zcnt ,right('00'+rtrim(cast(datepart(hh,getdate())as varchar(5))),2)+right('00'+rtrim(cast(datepart(mi,getdate()) varchar(5))),2)+right('00'+rtrim(cast(datepart(ss,getdate()) varchar(5))),2) tsl_time ,substring(convert(varchar,workdate,111),6,2)+right(convert(varchar,workdate,111),2)+left(convert(varchar,workdate,111),4) tsl_dte dbo.termztran t left join ( select slessee, stenantnumber tenantcode, sstorenumber mlandlordstore ) tinfo on t.store = tinfo.sstorenumber cast(t.store int) = cast(@branchcode int) , tinfo.slessee = @lesseecode , convert(nvarchar(10),t.workdate,101) = @trandate , convert(nvarchar(10),t.workdate,101) = '04/16/2013' , cast(br_code int) = cast('0040' int) group salesvatexempt , zeroratedsaleamt , zeroratedsaletaxvalue , workdate ) tfinal group br_code ,n_check ,clas_c ,clas_trd_c ,stor_no ,tsl_new_a ,tsl_old_a ,salestype ,tsl_day_a ,tsl_dis_a ,tsl_dis_c ,tsl_dis_e ,tsl_tax_a ,tsl_net_a ,tsl_void ,tsl_rfnd ,tsl_tx_sal ,tsl_nx_sal ,tsl_chg ,tsl_csh ,tsl_gc ,tsl_ft ,tsl_lt ,tsl_nt ,tsl_beg_inv ,tsl_end_inv ,tsl_tc_cash ,tsl_tc_gc ,tsl_zcnt ,tsl_time ,tsl_dte ) dt group dt.br_code ,dt.clas_c ,dt.clas_trd_c ,dt.n_check ,dt.stor_no ,dt.salestype ,tsl_day_a ,tsl_dis_a ,tsl_dis_c ,tsl_dis_e ,tsl_tax_a ,tsl_net_a ,tsl_void ,tsl_rfnd ,tsl_tx_sal ,tsl_nx_sal ,tsl_chg ,tsl_csh ,tsl_gc ,tsl_ft ,tsl_lt ,tsl_nt ,tsl_beg_inv ,tsl_end_inv ,tsl_tc_cash ,tsl_tc_gc ,tsl_zcnt ,tsl_time ,tsl_dte 
