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!
Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Joining multiple result sets together
Hi All,
I've been trying this for over a week now, and I simply cannot get this to work. Basically, what I am trying to do is get a result set that has the 4 columns, PID, LAST, SMOKER, HYPER for every person in the table (every person has a unique PID). My problem is that these columns are from different tables, so I have been trying to do 2 left joins to make the result set. This is what I came up with, but it has syntax problems from what I can gather.
SELECT E.PID, E.LAST, M.SMOKER, C.HYPER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)
After looking at the above query, I got the feeling that the C.HYPER might have been the problem as it is getting its data from the first left join. I tried to fix that with the statement below. It's probably terribly wrong too, but my SQL experience is limited, and i've never actually had to do a join from 3 tables before.
SELECT TT.PID, TT.LAST, TT.SMOKER_PAST, C.HYPER FROM (SELECT E.PID, E.LAST, M.SMOKER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT) LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)
What I was aiming for here was to make the second select result set act like a table of its own (TT). This didn't work too well either :)
Any help at all would be greatly appreciated.
Thanks,
Michael
BTW this forum is great. I only just found it, but I think I'll be coming here a lot more now. The amount of knowledge in here is unbelievable.try this --SELECT E.PID, E.LAST, M.SMOKER, C.HYPER
FROM ENROLLMENT E
LEFT
JOIN MEDICALHISTORY M
ON E.PID = M.PID
LEFT
JOIN CARDIACHISTORY C
ON E.PID = C.PIDrudy
http://r937.com/|||Thanks for the reply. I don't have access to the database right now, but I'll get back to you on Monday to let you know how it worked.|||Hi Rudy,
Thanks for the help. I just tried it out on the database, and whilst it didn't work for me, it got me thinking and I managed to get something that was workable. I'd post up what I ended up using, but it's a mess and hardly the most efficient way of doing it (it had to be generated by code on the fly, so the actual solution I ended up with is really redundant, but is easy to build in the code when certain buttons are pressed).
Thanks for the help, mate.
Cheers,
Michael
I've been trying this for over a week now, and I simply cannot get this to work. Basically, what I am trying to do is get a result set that has the 4 columns, PID, LAST, SMOKER, HYPER for every person in the table (every person has a unique PID). My problem is that these columns are from different tables, so I have been trying to do 2 left joins to make the result set. This is what I came up with, but it has syntax problems from what I can gather.
SELECT E.PID, E.LAST, M.SMOKER, C.HYPER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)
After looking at the above query, I got the feeling that the C.HYPER might have been the problem as it is getting its data from the first left join. I tried to fix that with the statement below. It's probably terribly wrong too, but my SQL experience is limited, and i've never actually had to do a join from 3 tables before.
SELECT TT.PID, TT.LAST, TT.SMOKER_PAST, C.HYPER FROM (SELECT E.PID, E.LAST, M.SMOKER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT) LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)
What I was aiming for here was to make the second select result set act like a table of its own (TT). This didn't work too well either :)
Any help at all would be greatly appreciated.
Thanks,
Michael
BTW this forum is great. I only just found it, but I think I'll be coming here a lot more now. The amount of knowledge in here is unbelievable.try this --SELECT E.PID, E.LAST, M.SMOKER, C.HYPER
FROM ENROLLMENT E
LEFT
JOIN MEDICALHISTORY M
ON E.PID = M.PID
LEFT
JOIN CARDIACHISTORY C
ON E.PID = C.PIDrudy
http://r937.com/|||Thanks for the reply. I don't have access to the database right now, but I'll get back to you on Monday to let you know how it worked.|||Hi Rudy,
Thanks for the help. I just tried it out on the database, and whilst it didn't work for me, it got me thinking and I managed to get something that was workable. I'd post up what I ended up using, but it's a mess and hardly the most efficient way of doing it (it had to be generated by code on the fly, so the actual solution I ended up with is really redundant, but is easy to build in the code when certain buttons are pressed).
Thanks for the help, mate.
Cheers,
Michael
Monday, February 20, 2012
Join problem
Hi - I have a problem with an update (with a join) that I'm attempting to run on a table with 53 million records in.
Basically I set this query off for the first time and it chugged away for 96 hours before I killed it - something was awry.
I've restored the database to another server (just in case we have an issue with disks, compress, memory etc) and run some tests on samples of the 53 million and for low samples, the update runs in a reasonable time which increases in time directly in line with the increase in sample size:
Top # sample from database tests:
1,000,000 - 36 seconds
2,000,000 - 71 seconds
3,000,000 - 122 seconds
but once I try 4,000,000 it runs and runs - got up to 36 minutes before I cancelled it.
Can anyone see any reason for this? I don't think the query size is going up exponentially - because if you graph the sample size & time from 1-3 million, the line is linear.
I'm currently adding a record ID to the table so I can select the bottom 4,000,000 so I can be sure that there's not some weird data somewhere between 3-4mill that is making the query go whoopsie.
Here are the tables & query I am attemping to run:
UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM ListCategoryHierarchy2
WHERE MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]
Each table is clustered on SourceCode ID/Sourcecode
(varchars are COLLATE Latin1_General_CI_AS)
CREATE TABLE [ListCategoryHierarchy2] (
[Campaign ID] [varchar] (255) NULL,
[Media ID] [varchar] (255) NULL,
[Media Description] [varchar] (255) NULL ,
[Media Selections] [varchar] (255) NULL ,
[SourceCode ID] [varchar] (7) NULL ,
[List ID] [varchar] (255) NULL ,
[Mailing Date] [smalldatetime] NULL ,
[List Category] [varchar] (255) NULL ,
[Source Code Offer] [varchar] (255) NULL ,
[ERI Value] [float] NULL
) ON [PRIMARY]
(9,923 records)
CREATE TABLE [MailingHistory_sample] (
[MatchKey] [binary] (20) NULL ,
[SourceCode] [varchar] (6) NULL ,
[ListID] [varchar] (7) NULL ,
[StationeryCode] [varchar] (5) NULL ,
[PDYear] [varchar] (2) NULL ,
[NaadID] [varchar] (10) NULL ,
[OrderNumber] [char] (9) NOT NULL ,
[CustomerNumber] [binary] (8) NULL ,
[CampaignCode] [varchar] (4) NULL ,
[ProductCode] [varchar] (4) NULL ,
[ResponseType] [varchar] (1) NULL ,
[HouseholdNumber] [bigint] NULL ,
[IndividualNumber] [bigint] NULL ,
[DataType] [varchar] (1) NULL ,
[AddressNumber] [bigint] NULL ,
[DateStamp] [char] (8) NULL ,
[CountColumn] [int] NULL ,
[MailingInstance] [int] NULL ,
[PCPrizm] [char] (5) NULL ,
[Postcode] [char] (7) NULL ,
[PostalArea] [varchar] (2) NULL ,
[TVRegion] [varchar] (3) NULL ,
[ERIRange] [int] NULL ,
[ERIValue] [float] NULL ,
[MediaID] [varchar] (10) NULL
) ON [PRIMARY]
SQL 2000
Any thoughts? Could there be some critical mass of temp table size etc that I am hitting?
thx
wAh, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?|||Well, I'm not too fond of your syntax. You should link your tables in a join rather than the WHERE clause, though I don't know that this would be impacting your execution time. It might.
UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM MailingHistory_Sample
inner join ListCategoryHierarchy2 on MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]
Also, drop any indexes on MailingHistory_Sample except one on [SourceCode]. This column should be indexed in both tables.|||Ah, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?
Well that's one problem...
Where are the Indexes for these tables?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.
We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.
Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.
Any ideas what this might be?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.
We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.
Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.
Any ideas what this might be?
If your database is running in Full Recovery mode, a complete before and after image of the update must be stored in the transaction log. If the log file is situated on the same physical drive as the primary data store, has too small an auto-grow value, or is badly physically fragmented, the overall update time can become very, very long.
Options include: issuing an ALTER DATABASE command and backup to take the database into Simple Recovery before the update; relocate the transaction log to a dedicated physical drive; set the transaction log to very, very big and do not truncate its space to filing system when backing up.|||Ah thanks - that makes sense. I only learnt this morning about putting data files and transaction logs on different physical disks. That is the case for this database so we are going to slap in a new drive and split the two.
thanks
w
Basically I set this query off for the first time and it chugged away for 96 hours before I killed it - something was awry.
I've restored the database to another server (just in case we have an issue with disks, compress, memory etc) and run some tests on samples of the 53 million and for low samples, the update runs in a reasonable time which increases in time directly in line with the increase in sample size:
Top # sample from database tests:
1,000,000 - 36 seconds
2,000,000 - 71 seconds
3,000,000 - 122 seconds
but once I try 4,000,000 it runs and runs - got up to 36 minutes before I cancelled it.
Can anyone see any reason for this? I don't think the query size is going up exponentially - because if you graph the sample size & time from 1-3 million, the line is linear.
I'm currently adding a record ID to the table so I can select the bottom 4,000,000 so I can be sure that there's not some weird data somewhere between 3-4mill that is making the query go whoopsie.
Here are the tables & query I am attemping to run:
UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM ListCategoryHierarchy2
WHERE MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]
Each table is clustered on SourceCode ID/Sourcecode
(varchars are COLLATE Latin1_General_CI_AS)
CREATE TABLE [ListCategoryHierarchy2] (
[Campaign ID] [varchar] (255) NULL,
[Media ID] [varchar] (255) NULL,
[Media Description] [varchar] (255) NULL ,
[Media Selections] [varchar] (255) NULL ,
[SourceCode ID] [varchar] (7) NULL ,
[List ID] [varchar] (255) NULL ,
[Mailing Date] [smalldatetime] NULL ,
[List Category] [varchar] (255) NULL ,
[Source Code Offer] [varchar] (255) NULL ,
[ERI Value] [float] NULL
) ON [PRIMARY]
(9,923 records)
CREATE TABLE [MailingHistory_sample] (
[MatchKey] [binary] (20) NULL ,
[SourceCode] [varchar] (6) NULL ,
[ListID] [varchar] (7) NULL ,
[StationeryCode] [varchar] (5) NULL ,
[PDYear] [varchar] (2) NULL ,
[NaadID] [varchar] (10) NULL ,
[OrderNumber] [char] (9) NOT NULL ,
[CustomerNumber] [binary] (8) NULL ,
[CampaignCode] [varchar] (4) NULL ,
[ProductCode] [varchar] (4) NULL ,
[ResponseType] [varchar] (1) NULL ,
[HouseholdNumber] [bigint] NULL ,
[IndividualNumber] [bigint] NULL ,
[DataType] [varchar] (1) NULL ,
[AddressNumber] [bigint] NULL ,
[DateStamp] [char] (8) NULL ,
[CountColumn] [int] NULL ,
[MailingInstance] [int] NULL ,
[PCPrizm] [char] (5) NULL ,
[Postcode] [char] (7) NULL ,
[PostalArea] [varchar] (2) NULL ,
[TVRegion] [varchar] (3) NULL ,
[ERIRange] [int] NULL ,
[ERIValue] [float] NULL ,
[MediaID] [varchar] (10) NULL
) ON [PRIMARY]
SQL 2000
Any thoughts? Could there be some critical mass of temp table size etc that I am hitting?
thx
wAh, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?|||Well, I'm not too fond of your syntax. You should link your tables in a join rather than the WHERE clause, though I don't know that this would be impacting your execution time. It might.
UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM MailingHistory_Sample
inner join ListCategoryHierarchy2 on MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]
Also, drop any indexes on MailingHistory_Sample except one on [SourceCode]. This column should be indexed in both tables.|||Ah, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?
Well that's one problem...
Where are the Indexes for these tables?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.
We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.
Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.
Any ideas what this might be?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.
We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.
Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.
Any ideas what this might be?
If your database is running in Full Recovery mode, a complete before and after image of the update must be stored in the transaction log. If the log file is situated on the same physical drive as the primary data store, has too small an auto-grow value, or is badly physically fragmented, the overall update time can become very, very long.
Options include: issuing an ALTER DATABASE command and backup to take the database into Simple Recovery before the update; relocate the transaction log to a dedicated physical drive; set the transaction log to very, very big and do not truncate its space to filing system when backing up.|||Ah thanks - that makes sense. I only learnt this morning about putting data files and transaction logs on different physical disks. That is the case for this database so we are going to slap in a new drive and split the two.
thanks
w
Subscribe to:
Posts (Atom)