Are there SQL functions to convert back and forth between these two?
TIA for the help.
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenetmyob@.beatles.com wrote:
> Are there SQL functions to convert back and forth between these two?
> TIA for the help.
> -- Posted with NewsLeecher v3.0 Beta 6
> -- http://www.newsleecher.com/?usenet
Not in SQL Server. This is a hard problem because no single,
universally valid conversion between Julian and Gregorian is possible.
The answer depends on what time, place and proleptic calendar you take
as your reference point. I suggest you create your own Calendar table
to do the conversion.
The last word on all the messy details can be found here:
http://www.tondering.dk/claus/calendar.html
David Portas
SQL Server MVP
--|||David Portas wrote:
> Not in SQL Server. This is a hard problem because no single,
> universally valid conversion between Julian and Gregorian is possible.
Clarification: What I mean is that any conversion you choose to apply
may or may not be factually, historically or legally accurate dependent
on geography and the date in question. You can of course choose to use
a single conversion but its validity may be only theoretical.
David Portas
SQL Server MVP
--
Showing posts with label convert. Show all posts
Showing posts with label convert. Show all posts
Monday, March 26, 2012
Julian Dates
Is there a function or quick way to convert a calendar date to a julian
date. For example 1 September 2004 to 4245.
Rus BaileyIs Julian date days from a particular date? If so, you could just use
dateadd()/datediff().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rus Bailey" <Rus.Bailey@.dla.mil> wrote in message
news:OSn9uCFkEHA.3476@.tk2msftngp13.phx.gbl...
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>|||I used a Date Time Picker and a label and it worked for me here is the code I
used
lblJulian.Text = DateTimePicker1.Value.DayOfYear
Hope this helps
"Rus Bailey" wrote:
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>
>
date. For example 1 September 2004 to 4245.
Rus BaileyIs Julian date days from a particular date? If so, you could just use
dateadd()/datediff().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rus Bailey" <Rus.Bailey@.dla.mil> wrote in message
news:OSn9uCFkEHA.3476@.tk2msftngp13.phx.gbl...
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>|||I used a Date Time Picker and a label and it worked for me here is the code I
used
lblJulian.Text = DateTimePicker1.Value.DayOfYear
Hope this helps
"Rus Bailey" wrote:
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>
>
Julian Datehelp :(
I have a julian date(04159) and I need to convert this to any normal date format yyyymmdd or mmddyy or basically anything that I can work with! I am just about fed up with trying to convert this. It would be simple enough if there want leap years. Does anyone have a function or something out there that can convert this for me? Code that I found on the web turned out to be bogus. Your help will be greatly appreciated. If you have something in VB that would work also. SQLServer preferably.
ThanksIt may be off by a day or so, but it's a start:
select dateadd(day, cast(reverse(cast(reverse('04159') as char(3))) as int), '01/01/' + cast('04159' as char(2)))|||Yeah...Thats sorta where I left off in my efforts. Thanks for your input. I will eventually get it...Hopefully someone in here will see this and have something.|||This is ancient old code, from when SQL 2000 was still in beta, but I think it works. Give this a try:CREATE FUNCTION dbo.f_jDate(@.piJdate INT) RETURNS DATETIME AS
BEGIN
RETURN DateAdd(day, (@.piJdate % 1000) - 1, Convert(DATETIME
, CASE WHEN @.piJdate < 50000 THEN '20' ELSE '19' END
+ Replace(Str(@.piJdate / 1000, 2), ' ', '0') + '-01-01'))
END-PatP|||Thanks Pat....That worked perfectly! Your my hero for the day!
ThanksIt may be off by a day or so, but it's a start:
select dateadd(day, cast(reverse(cast(reverse('04159') as char(3))) as int), '01/01/' + cast('04159' as char(2)))|||Yeah...Thats sorta where I left off in my efforts. Thanks for your input. I will eventually get it...Hopefully someone in here will see this and have something.|||This is ancient old code, from when SQL 2000 was still in beta, but I think it works. Give this a try:CREATE FUNCTION dbo.f_jDate(@.piJdate INT) RETURNS DATETIME AS
BEGIN
RETURN DateAdd(day, (@.piJdate % 1000) - 1, Convert(DATETIME
, CASE WHEN @.piJdate < 50000 THEN '20' ELSE '19' END
+ Replace(Str(@.piJdate / 1000, 2), ' ', '0') + '-01-01'))
END-PatP|||Thanks Pat....That worked perfectly! Your my hero for the day!
Julian date time Conversion
Can anyone tell me how to convert julian date time to DateTime and Vice Versa?
the function which I have only convers the date to Julian and julian to date but the time is not appended.
How can i get the time into Julian format and from julian format?
Any help would be appreciated.
thanks.If you poke around the source forthis page, you'll find the JavaScript that they use to do it.
|||
the function which I have only convers the date to Julian and julian to date but the time is not appended.
How can i get the time into Julian format and from julian format?
Any help would be appreciated.
thanks.If you poke around the source forthis page, you'll find the JavaScript that they use to do it.
|||
Try the links below for UDF Julian to DateTime conversion code. Hope this helps.
http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-3-udf_DT_FromJulian.htm
http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-2-udf_DT_ToJulian.htm
Friday, March 23, 2012
Joins
I want to convert the following statement:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_s pec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_s pec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
Message posted via http://www.sqlmonster.com
Sorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD_10_calc_s pec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
Message posted via http://www.sqlmonster.com
sql
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_s pec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_s pec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
Message posted via http://www.sqlmonster.com
Sorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD_10_calc_s pec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
Message posted via http://www.sqlmonster.com
sql
Joins
I want to convert the following statement:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
--
Message posted via http://www.sqlmonster.comSorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
--
Message posted via http://www.sqlmonster.com
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
--
Message posted via http://www.sqlmonster.comSorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
--
Message posted via http://www.sqlmonster.com
Joins
I want to convert the following statement:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD
_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD
_10_calc_spec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
Message posted via http://www.droptable.comSorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD
_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
Message posted via http://www.droptable.com
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD
_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD
_10_calc_spec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
Message posted via http://www.droptable.comSorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD
_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
Message posted via http://www.droptable.com
Subscribe to:
Posts (Atom)