problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-01 : 17:36:12

declare @table3 table(phone2 int) --
insert into @table3 values(111111)

--
declare @table2 table(phone1 int,debt money)
insert into @table2 values(111111,1.23)

declare @table1 table(phone int,old_debt money)
insert into @table1 values(111111,6.3)

declare @t242 table(ph int,qebzbr money)
insert into @t242 values(111111,6.3)

declare @t1 table(phone int,sadbr money)
insert into @t1 values(111111,-1.10)

-- ????????????? ??????? ??????
declare @absher table(telefon varchar(10),odenis varchar(10))
insert into @absher values('00111111','000000089')
insert into @absher values('00111111','000000056')

----------
declare @avans table(tlf varchar(10),Test varchar(10))
insert into @avans values('00111111','000000009')
insert into @avans values('00111111','000000523')
insert into @avans values('00111111','000000026')

--------------------------------------------------------------
select t3.phone2,
Sum(ISNULL(tt1.sadbr,0.0)-ISNULL(t242.qebzbr,0.0)) as f2
,SUM(ISNULL(t2.debt,0.0)-isnull(t1.old_debt,0.0)
) as f1
,sum(isnull(p.mny,0)) as abser_ode
,sum(isnull (d.av_sad,0)) as avansid_ode
from @table3 as t3
left join @t242 as t242 ON t242.ph= t3.phone2
left join @t1 as tt1 ON tt1.phone= t3.phone2
left join @table2 as t2 ON t2.phone1 = t3.phone2
left join @table1 as t1 ON t1.phone = t3.phone2
left join (select convert(int,telefon) as tf,convert(money,odenis)/100.0 as mny
from @absher) p ON p.tf = t3.phone2
left join (select convert(int,tlf) as tlf ,convert(money,Test)/100.0 as av_sad from @avans
)as d on d.tlf=t3.phone2
group by t3.phone2

if the script is run we get a result
-------------------------------------------------
Phone f2 f1 abser_ode avansid_ode
111111 -44,40 -30,42 4.350000000 11.160000000

how make exporting resulting table in excell with a title

just about
so
--------------------------------------------
hhhhhhhhhhh iiiii pppppp
kkkkkk oooooooo

---------------------------------------------------------------------
phone2 f2 f1 abser_ode avansid_ode
111111 -7,40 -5,07 1.450000000 5.580000000

kmkmmm

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-01 : 19:41:42

????

kmkmmm

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-01 : 19:44:45

one can not solve?/

kmkmmm

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-01 : 19:56:38

quote:
Originally posted by pascal_jimi

declare @table3 table(phone2 int) --
insert into @table3 values(111111)

--
declare @table2 table(phone1 int,debt money)
insert into @table2 values(111111,1.23)

declare @table1 table(phone int,old_debt money)
insert into @table1 values(111111,6.3)

declare @t242 table(ph int,qebzbr money)
insert into @t242 values(111111,6.3)

declare @t1 table(phone int,sadbr money)
insert into @t1 values(111111,-1.10)

-- ????????????? ??????? ??????
declare @absher table(telefon varchar(10),odenis varchar(10))
insert into @absher values('00111111','000000089')
insert into @absher values('00111111','000000056')

----------
declare @avans table(tlf varchar(10),Test varchar(10))
insert into @avans values('00111111','000000009')
insert into @avans values('00111111','000000523')
insert into @avans values('00111111','000000026')

--------------------------------------------------------------
select t3.phone2,
Sum(ISNULL(tt1.sadbr,0.0)-ISNULL(t242.qebzbr,0.0)) as f2
,SUM(ISNULL(t2.debt,0.0)-isnull(t1.old_debt,0.0)
) as f1
,sum(isnull(p.mny,0)) as abser_ode
,sum(isnull (d.av_sad,0)) as avansid_ode
from @table3 as t3
left join @t242 as t242 ON t242.ph= t3.phone2
left join @t1 as tt1 ON tt1.phone= t3.phone2
left join @table2 as t2 ON t2.phone1 = t3.phone2
left join @table1 as t1 ON t1.phone = t3.phone2
left join (select convert(int,telefon) as tf,convert(money,odenis)/100.0 as mny
from @absher) p ON p.tf = t3.phone2
left join (select convert(int,tlf) as tlf ,convert(money,Test)/100.0 as av_sad from @avans
)as d on d.tlf=t3.phone2
group by t3.phone2

if the script is run we get a result
-------------------------------------------------
Phone f2 f1 abser_ode avansid_ode
111111 -44,40 -30,42 4.350000000 11.160000000

how make exporting resulting table in excell with a title

just about
so
--------------------------------------------
hhhhhhhhhhh iiiii pppppp
kkkkkk oooooooo

---------------------------------------------------------------------
phone2 f2 f1 abser_ode avansid_ode
111111 -7,40 -5,07 1.450000000 5.580000000

kmkmmm

How did you arrive at the second set of results from the first set of results? In other words, what is the rule to be used to get -7,40 from -44,40? Also, what does the "hhhhhhhhhhh iiiii pppppp kkkkkk oooooooo" represent? Is that an additional title row?

If you simply want to export to Excel, you can use SSIS or Import/Export Wizard. To use Import/Export Wizard, right-click on the database name in SSMS object explorer, select Tasks -> Export data and follow through the wizard.

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-01 : 20:06:28

this is my result table

phone2 f2 f1 abser_ode avansid_ode
111111 -7,40 -5,07 1.450000000 5.580000000

but I want to export to Excell with headers
for example

jjjj jjjj jjj
kkk kkk

phone2 f2 f1 abser_ode avansid_ode
111111 -7,40 -5,07 1.450000000 5.580000000

---------------

kmkmmm

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-01 : 20:12:26

"hhhhhhhhhhh ????? PPPPPP KKKKKK OOOOOOOO" this is headers result table in excell

kmkmmm

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-01 : 20:15:26

quote:
Originally posted by pascal_jimi

this is my result table

phone2 f2 f1 abser_ode avansid_ode
111111 -7,40 -5,07 1.450000000 5.580000000

but I want to export to Excell with headers
for example

jjjj jjjj jjj
kkk kkk

phone2 f2 f1 abser_ode avansid_ode
111111 -7,40 -5,07 1.450000000 5.580000000

---------------

kmkmmm

I am afraid I am not following you. If you run the query you posted, you get ONE data row (the one that starts with 111111). Then there is the header row, which starts with phone2.

If exporting that one data row and including the header row is your goal, then use SSIS or Import/Export Wizard as I had described earlier.

If that is not the case, i.e., you have a title row for the Excel sheet, where does the title come from? Is that the result of a query? Or is that pre-defined? You can export data to an existing excel file without overwriting the existing data, if you are trying to do the former.

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-01 : 20:25:56

I want to receive the exported data in excell with a header Table

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 20:31:05

where does header table data come from? is it another table or do you just need column names of result to be populated?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources