Empty date values - 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

Empty date values

Author

Topic

Myclos81
Starting Member

5 Posts

Posted - 2012-02-03 : 08:24:46

Hi,

Code:
SELECT facturen.facturatie_datum,
facturen.factuurnr,
facturen.factuurbedrag,
left(f_gettranslation ('DYNAMIC_DDDW_VALUES',month(facturen.facturatie_datum),
(select dddw_value from dynamic_dddw_values where dddw_value_id = month(facturen.facturatie_datum))) ,3) as maand_naam

FROM facturen,
project

WHERE (project.projectid = facturen.project_id ) and
(( project.invoice_group_id = 277 ) AND
(facturen.facturatie_datum >= '2006-01-01' ) AND
(facturen.facturatie_datum <= '2012-02-01') )

With this query I get next result:

2012-01-26 ZERO 0.00 Jan
2011-12-22 00000001 2383.94 Dec
2011-12-22 00000002 1402.00 Dec
2011-12-22 00000004 1897.13 Dec
2007-12-22 00000003 1897.13 Dec
2008-12-22 00000007 1069.48 Dec
2012-01-04 00000005 799.43 Jan
2012-01-04 00000006 200.57 Jan

I have a crosstab which shows above the years and on the left the months. With this query, he only shows januari and december. How can I complete my SQL so that he shows the other months too (with zero values)?

Don't know if it's possible. I just want him to show always 12 months (the empty with 0) and not only those who contains values in the database. I would like to generate the missing data for the months that don't have values (already tried with outer join but no result ?).

Table dynamic_dddw_values contains names for the months and takes the first three letters.

Thx,
M81

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-03 : 08:34:18

join the results to a calendar table before the crosstab.

You'll probably want to LEFT JOIN from the calendar table to your results.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 09:19:23

see a typical function you can use to generate calendar table

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Myclos81
Starting Member

5 Posts

Posted - 2012-02-06 : 03:54:17

Ok, I joined the calendar table (DYNAMIC_DDDW_VALUES) to my results.
This is my query:

SELECT dynamic_dddw_values.dddw_value,
facturen.facturatie_datum,
facturen.factuurnr,
facturen.factuurbedrag,
left(f_gettranslation ('DYNAMIC_DDDW_VALUES',month(facturen.facturatie_datum),
(select dddw_value from dynamic_dddw_values where dddw_value_id = month(facturen.facturatie_datum))) ,3) as maand_naam

FROM dynamic_dddw_values,
facturen,
project

LEFT OUTER JOIN DYNAMIC_DDDW_VALUES ON month(facturen.facturatie_datum) = DYNAMIC_DDDW_VALUES.DDDW_VALUE_ID

WHERE (project.projectid = facturen.project_id ) and
(( project.invoice_group_id = 277 ) AND
(facturen.facturatie_datum >= '2006-01-01' ) AND
(facturen.facturatie_datum <= '2012-02-01')

But now I get the error: "Illegal reference to correlation name 'facturen' ??

I googled it but there they say that it is because it is a join to itself which it isn't ??

Thx for reply,
M81

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:28:00

looks like you're using sybase.
this is ms sql server forum, so solutions given here are mostly sql server specific
Suggest you try in some sybase forums like one at www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources