Hi friend,
As i want to create web application for report generating.
In that report creating i need, sql data values import to excel sheets its working.
This is my Coding: Payment details of every month report:
WITH
allDays (calc_date)
AS
(SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - DAY(GETDATE()-1), 0)
UNION ALL
SELECT DATEADD(DAY, 1, calc_date)
FROM allDays
WHERE DATEADD(DAY, 1, calc_date) < DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))),DATEADD(mm,1,getdate())))
select
convert(varchar,calc_date,4) AS 'Date_',
convert(numeric(10,0),SUM(case when Course_Name = 'A+ ' then Amount_Paid else 0
end )) as 'ap',
convert(numeric(10,0),SUM(case when Course_Name = 'CCNA' then Amount_Paid else
0 end)) as 'ccna',
convert(numeric(10,0),SUM(case when Course_Name = 'Combo' then Amount_Paid else
0 end)) as 'combo',
convert(numeric(10,0),SUM(case
when Course_Name = 'A+ ' then Amount_Paid
when Course_Name = 'CCNA' then Amount_Paid
when Course_Name = 'Combo' then Amount_Paid
else 0 end)) as
'total'
into #temp_Coimbatore_col from tbl_receipt right outer join allDays on
Tbl_Receipt.Branch_Name='Coimbatore'
and
Tbl_Receipt.Payment_Date >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),101)
and
convert(varchar,tbl_receipt.Payment_Date,4)=convert(varchar,allDays.calc_date,4)
group by
convert(varchar,tbl_receipt.Payment_Date,4),
convert(varchar,allDays.calc_date,4)
Update after current date to convert null values:
update
#temp_Coimbatore_col set
ap=null,
ccna=null,
combo=null,
total=null
where
convert(varchar,Date_,4) > convert(varchar,getdate(),4)
Calculate the total Amount of that month:SELECT
date_ 'Date',
sum(ap) 'A+',
sum(ccna) 'CCNA',
sum(combo) 'Combo',
SUM(total) 'Total'
into #temp2_Coimbatore_col FROM #temp_Coimbatore_col
GROUP BY date_
WITH ROLLUP Import sql datavalues to excel:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\temp_auto\dr_rep\Coimbatore_Daily_Report.xls;',
'select * FROM [Collection$]')
select * from #temp2_Coimbatore_col
As i need, to display comma value of sql data values to excel and also display comment in excel format. How to write the sql query ?
Please any one help immediately solution for me..
I need immediately solution for this questions. Advance thanks for friends