Julian Date

A table that I am querying from has the date field in julian date format "04194". I am only wanting to pull the information from this table that is => than todays date. Maybe some of you have done this before and can give a little help in finding the best way to do this?You could take today's date and subtract it from the first day of the year (DATEDIFF ( datepart , startdate , enddate ) ) to give you an int value for the day of the year (actually day of year -1 since 20040101 - 20040101 = 0). Then convert to a 3 character value and concatinate the two digit year to the front and ... tadaaa ... the julian date.|||? I am not following you?|||You can get the Julian date using:SELECT 1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate())If you need it in character format, you can use:SELECT Replace(Str(
1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate()), 5), ' ', '0')After you've got that, the rest should just be a simple compare.

-PatP|||Or you could go the other way so you can utilize all of SQL Server's functions

DECLARE @.julian char(5), @.gregorian datetime
SELECT @.julian = '04194'
SELECT @.gregorian = DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3)),CON VERT(datetime,'20'+SUBSTRING(@.julian,1,2)+'/01/01'))
SELECT DATEDIFF(dd,GetDate(),@.gregorian)|||Perfect!...Thanks for your help. I was going about it the wrong way. I was trying to convert the julian date and then compare.|||Thanks also Brett That was a road I was eventually going to have to cross.|||udf's...Makes a perfect house warming gift

CREATE FUNCTION udf_JulianToGregorian(@.julian char(5))
RETURNS datetime
AS
BEGIN
DECLARE @.gregorian datetime
SELECT @.gregorian =
DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3))
,CONVERT(datetime
,CASE WHEN SUBSTRING(@.julian,1,2) BETWEEN '00' AND '50'
THEN '20'
ELSE '19'
END
+SUBSTRING(@.julian,1,2)+'/01/01'))
RETURN @.gregorian
END
GO

DECLARE @.julian char(5)
SELECT @.julian = '04194'
SELECT dbo.udf_JulianToGregorian(@.julian)
GO

Joining summary data with detail data

I know similar questions have been asked but I wanted to try my luck that my issue is somehow different.

I am querying a database which has detail information (sales transactions) and is grouped by customer. I also have a table with one record for each customer with some historical sales information (summary information). The requirements for the report are to have the sums of the sales for each customer along with the historical data for that customer in the same row in the table. I haven't found a way to do this using one dataset and from what I've read, the current version doesn't support joining multiple datasets over a grouping field (customer).. or at all.

Any one have ideas?

In an ideal world, you would think that you could use SSRS to create the summary information from the detail information. That being said, I have a similar situation where the summary data is not exactly the same information. My summary data is counts by month of eligible accounts. The detail is counts of various rewards by month. I end up having information that looks like

Jan Feb Mar Apr May Jun
Elig 10 12 10 11 8 15
Reward 1 2 1 3 4 1 0
Reward 2 3 3 2 3 2 3
Reward 3 4 1 2 1 0 4

The way I did this was to use a UNION query with constants into a matrix report.

SELECT 0 'Level', 'Elig' Reward, Month, count(*) amt from SummaryTable Group By 1,2,3
UNION
SELECT 1 'Level', Reward, Month, count(*) amt from DetailTable Group By 1,2,3
Order by 1,2,3;

This way the Eligible data (SUMMARY) was at the top and the detail followed.

R