How to make daily report - 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

How to make daily report

Author

Topic

boboalex
Starting Member

2 Posts

Posted - 2012-06-04 : 03:00:13

I have a table with 3 column (date, company, actual) like this.
20120101 A 100.00
20120101 B 100.00
20120101 C 1000.00
20120102 B 200.00
20120102 A 200.00

I want to make report with all company eventhough there's no transaction in that day with result like this
20120101 A 100.00
20120101 B 100.00
20120101 C 1000.00
20120102 A 200.00
20120102 B 200.00
20120102 C 0
How to make this report?

regards

Alex

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-04 : 06:46:50

I think you will also have a "company-table".
Then you need a calendar table with all needed dates without gaps (search for calendar table here or in google).
Then you can join the company-table, the calendar-table and the above given table.

No, you're never too old to Yak'n'Roll if you're too young to die.

boboalex
Starting Member

2 Posts

Posted - 2012-06-04 : 06:53:13

hi webfred,
i only have that table
is that possible with 1 table?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-04 : 06:57:52

You can generate a derived table with distinct company from your existing table as a company-table.
As I told you, you can search for calendar table to get examples on how to generate this.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 10:10:49

see

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

use it like


SELECT m.[Date],m.company,COALESCE(n.actual,0.00) AS actual
FROM
(
SELECT f.[Date],t.company
FROM dbo.CalendarTable(@startdate,@enddate,0,0) f
cross join (select distinct company from yourtable)t
)m
LEFT JOIN YourTable n
ON n.company = m.company
AND n.[Date] = m.[Date]

@startdate and @enddate represent date range between which you want transactions to be listed. it can be parameterised to take values as input from user
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources