Company Calender

Question

Post reply

Company Calender

b_boy

SSCertifiable

Points: 5145
More actions
September 3, 2008 at 5:56 am

#190090

Hello
Does anyone know how to add a company calender to a database? I have imported a company calender with all the financial weekd ends and week starts.
When i run a query which is based on a date range and reference the newly created compnay calender table it either gives me no results or a cartisian (cross join) result, thereby making my result set inaccurate.
can anyone help?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi there,

Please could you give us the table structure and data in the table as well as your query.

Please refer to this link for tips how to get the best out of your posts.

http://www.sqlservercentral.com/articles/Best+Practices/61537/">

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

b_boy SSCertifiable Points: 5145 More actions · Answer 2

Hello

I have attached sample data in a csv formats and the three table structures as a text file, i hope this works.

this is the query i used:

SELECT Returns.ReturnDate, SUM(returnlines.QuantityReturned) AS qty_returned, SUM(Returns.ReturnTotal) AS [Returned Value], ReturnTypes.ReturnType

FROM ReturnTypes INNER JOIN

Returns ON ReturnTypes.ReturnTypeID = Returns.ReturnTypeID RIGHT OUTER JOIN

returnlines. ON Returns.ReturnID = returnlines.ReturnId CROSS JOIN

CompanyCalendar AS C

WHERE (Returns.ReturnDate BETWEEN C.PERIOD_START and C.PERIOD_END)

GROUP BY Returns.ReturnDate,ReturnTypes.ReturnType

parackson Mr or Mrs. 500 Points: 532 More actions · Answer 3

Your data provided does not match the table specifications.... Plus the query you provided continually failed due to bad syntax...

I have played with your query to at least get it to run, but since I cannot get your data in properly I cannot fully test to see if it works... Rather intersting query.. Inner, Right and Cross Joins. (Is all that necessary)

SELECT

T_Returns.ReturnDate,

,SUM(T_returnlines.QuantityReturned) AS qty_returned

,SUM(T_Returns.ReturnTotal) AS [Returned Value]

,ReturnTypes.ReturnType

FROM

ReturnTypes

INNER JOIN

T_Returns

ON

ReturnTypes.ReturnTypeID = T_Returns.ReturnTypeID

RIGHT OUTER JOIN

T_returnlines

ON

T_Returns.ReturnID = T_returnlines.ReturnId

CROSS JOIN

CompanyCalendar AS C

WHERE

T_Returns.ReturnDate BETWEEN C.PERIOD_START and C.PERIOD_END

GROUP BY

T_Returns.ReturnDate

,ReturnTypes.ReturnType

b_boy SSCertifiable Points: 5145 More actions · Answer 4

Using the cross join gives me a cartisian product which is not what i want