Use date and time in sql statement for join, but not required in the output
-
Afternoon,
I'm using the date / time from a view to join on the date / time from another part of the view, but do not require the date and time to be included in the output.
Can this be done, even though they are used to join the recordsDECLARE @begindate varchar(30) = '2014-04-11 18:00:00'
DECLARE @enddate varchar(30) = '2014-05-11 18:00:00'
select * from
(select FORMAT( date, 'MM/dd/yyyy', 'en-US') as [Date], FORMAT( date, 'H:00', 'en-US') as [Time], [data qualifier] as [Hardware Status]
from [xxx].[xxx].[xxxxx]
where date >= @BeginDate and date < @EndDate and [Series Name] = 'xxxxx') PRECIPACC
JOIN (select FORMAT( date, 'MM/dd/yyyy', 'en-US') as [Date], FORMAT( date, 'H:00', 'en-US') as [Time], coalesce(lead(value,1) over (order by date),value) - Value as [Intensity (mm/Hr)]
from [xxx].[xxx.[xxxxx]
where date >= @BeginDate AND date < @EndDate and [Series Name] = 'INTENSITY') INTENSITY
on PRECIPACC.[Date] = Intensity.[Date] and PRECIPACC.[time] = Intensity.[time]
Walter
- walter.dziuba - Friday, March 3, 2017 10:50 AMAfternoon,
I'm using the date / time from a view to join on the date / time from another part of the view, but do not require the date and time to be included in the output.
Can this be done, even though they are used to join the recordsDECLARE @begindate varchar(30) = '2014-04-11 18:00:00'
DECLARE @enddate varchar(30) = '2014-05-11 18:00:00'
select * from
(select FORMAT( date, 'MM/dd/yyyy', 'en-US') as [Date], FORMAT( date, 'H:00', 'en-US') as [Time], [data qualifier] as [Hardware Status]
from [xxx].[xxx].[xxxxx]
where date >= @BeginDate and date < @EndDate and [Series Name] = 'xxxxx') PRECIPACC
JOIN (select FORMAT( date, 'MM/dd/yyyy', 'en-US') as [Date], FORMAT( date, 'H:00', 'en-US') as [Time], coalesce(lead(value,1) over (order by date),value) - Value as [Intensity (mm/Hr)]
from [xxx].[xxx.[xxxxx]
where date >= @BeginDate AND date < @EndDate and [Series Name] = 'INTENSITY') INTENSITY
on PRECIPACC.[Date] = Intensity.[Date] and PRECIPACC.[time] = Intensity.[time]Walter
Specify the columns you want to retrieve rather than using the "*" wildcard. Remember to specify one or the other of your derived table aliases (e.g. INTENRITY.[Intensity (mm/Hr)]).
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer -
Change the * to include the columns you need separated by commas.
Avoid using FORMAT as it is very slow and use CONVERT instead. -
I`m attempting to remove the ``*`` from the statement and trying to only select on the view`s date and value column, but the error is stating that the
``Ambiguous column name ``Date``
Since that field name already exists in the remaining statement, how do I select those two fields
Walter
- walter.dziuba - Friday, March 3, 2017 12:15 PM
Fully qualify the columns in your column list with the alias of your subqueries/derived tables. If you want an example on how to do it, just see what you did on your JOIN clause.
-
As Luis pointed out, you'll want to fully qualify the column names. Essentially, the [date] column is used by more than one table and SQL Server does not know which one to use.
SELECT PRECIPACC.[Date]
, PRECIPACC.[Time]
, PRECIPACC.[Hardware Status]
, INTENSITY.[Intensity (mm/Hr)]
FROM
(
SELECT FORMAT ( x1.date, 'MM/dd/yyyy', 'en-US' ) AS [Date]
, FORMAT ( x1.date, 'H:00', 'en-US' ) AS [Time]
, [data qualifier] AS [Hardware Status]
FROM [xxx].[xxx].[xxxxx] AS x1
WHERE x1.date >= @begindate
AND x1.date < @enddate
AND [Series Name] = 'xxxxx'
) AS PRECIPACC
JOIN
(
SELECT FORMAT ( x2.date, 'MM/dd/yyyy', 'en-US' ) AS [Date]
, FORMAT ( x2.date, 'H:00', 'en-US' ) AS [TIME]
, COALESCE(lead ( value, 1 ) OVER (ORDER BY x2.date), value) - Value AS [Intensity (mm/Hr)]
FROM [xxx].[xxx.[xxxxx] AS x2
WHERE x2.date >= @BeginDate
AND x2.date < @EndDate
AND [Series Name] = 'INTENSITY'
) AS INTENSITY
ON PRECIPACC.[Date] = Intensity.[Date]
AND PRECIPACC.[time] = Intensity.[time]
;Also, some tricks with the date/time columns that can be used:
SELECT CONVERT(varchar(10), @begindate, 101)
, CONVERT(varchar(2), CAST(@enddate AS time)) + ':00';
https://msdn.microsoft.com/en-us/library/ms187928.aspx - Luis Cazares - Friday, March 3, 2017 11:07 AM
+1000 to that! 44 times slower, in most cases.
Heh... and stop joining on formatted data no matter how you format it. It makes index seeks followed by a range scan impossible.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
>>I am using the date / time from a view to join on the date / time from another part of the view, but do not require the date and time to beincluded in the output. <<
Where is the base table DDL for this? I don’t see it anywhere. What I do see is a bunch of string handling as if this was COBOL in 1950’sand not SQL at all. You don’t even seem to know the differencebetween a row and a record exclamation. let’s try and fix this total mess, even though you did not meet minimum forum requirementsfor netiquette.First of all, you don’t understand that we have temporal datatypes in SQL. Instead you use a 1950’s COBOL style string and you even make it too long for COBOL! Here’s what you should have written
DECLARE@in_begin_date DATETIME2(0) = '2014-04-11 18:00:00;
DECLARE@in_end_date DATETIME2(0)= '2014-05-11 18:00:00';the rest of your code is total garbage.. Why are you formatting and reformatting converting temporal data into strings? Over and over, redundantly, needlessly.
Ever since about 1975 the model for programming has been tiered architecture. The database layer produces data in standardized formats and result sets. This layer then throws the data “over the wall” to presentation layers which turn it into some kind of local dialect, either for the end-user or for the equipment that has to do pass it along. In short, competent SQL programmers never use the Format(); it is only for incompetent programmers who were using SQL to write 1950’s COBOL. Please look at my credentials and what I've done with SQL, so you’ll know that I probably said this with some authority.“DATE”is a data type and can never be a valid column name. If you read the ISO 11179 standards, you’ll see you can have a “<something>_date”because the postfix is what is called an attribute property. You need a course in basic data modeling.
Since you're trying to join on the timestamp, I will guess that the DDL you totally fail to post should include check constraints that would force the timestamps in both tables to fall into timeslots, rather than fractional second values. But if you will follow forum rules., it is really hard to help you. Also please be polite enough not to post screen shots that we have to blow up,, print out and transcribe into SQL for you.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Monday, March 6, 2017 12:34 PM
So how do you code a column with a date that's supposed to represent a whole month?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, March 6, 2017 6:51 PM
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Monday, March 6, 2017 8:00 PM
Yeah... based on the following article, especially the section on the "Report Period Tables", I thought you'd say that.
https://www.simple-talk.com/sql/t-sql-programming/on-handling-dates-in-sql/The concept of a formatted date in a report table means that you're mixing the presentation layer with the data layer, which you just got done blasting the OP for.
The concept of having a YYYY-00 format violates the ISO 8601 format, which clearly states that the month segment must contain only the values of '01' through '12'. It also states that the YYYY-MM-DD format must use only the values of '01' thru '31' for the "DD" part.
Since dashes are present in all of those, they must be strings, just like in Cobol.
In your article, you've taught that it's ok to mix the presentation layer with the data layer, that it's ok to store dates as strings as was done in COBOL instead of using a temporal data type, and that it's ok to violate ISO 8601 date format standards, and then you chastise OP after OP for doing exactly what you've done in one of your own articles.
You need to back off on the tough guy act until you have your own act together.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, March 6, 2017 8:46 PM
>>Theconcept of a formatted date in a report table means that you'remixing the presentation layer with the data layer, which you just gotdone blasting the OP for. << No,the report is in the presentation layer. That’s where things shouldbe displayed. . But that’s not the only place obviously in the userinterface (a presentation layer of its own kind) will have to showvalues in some kind of string.
Canyou actually imagine the user interface where you transcribed yourqueries in pure abstract thought?? no, as per Dr. Codd’s originalarticle, the row relational database must be represented in linearlanguage strings.
>>it’sTheconcept of having a YYYY-00 format violates the ISO 8601 format,which clearly states that the month segment must contain only thevalues of '01' through '12'. It also states that the YYYY-MM-DDformat must use only the values of '01' thru '31' for the "DD"part. <<
yes,and this is why I like them MySQL convention of “yyyy-mm-00†formonths and “yyyy-00-00†for a year. Again, these have been usedby MySQL, the sort with the current ISO 8601 standards, and they arelanguage independent. I do not know who will be adapted. I’m notactive in ANSI/ISO standards anymore.
>>Sincedashes are present in all of those, they must be strings, just likein COBOL.<<
Thereare two errors here. COBOL is based on the article records, so therewould be a separate field with the year, month and date. They can besplit out from a string. This concept is not exist in SQL; separatecolumns are separate apt attributes and putting together makes nosense.
Butperhaps more fundamental than this is a concept of display (numerals)versus the actual data (numbers). A few hundred years ago weunderstood that’s Chinese numerals, Aztec numerals, etc. were notthe same as a numeric value represented. We knew that havingpunctuation marks are a problem (representation) versus the actualvalue (and abstraction)..
Whenwe were working on the ANSI/ISO standards for SQL data types, wetried to separate presentation (display) formats from internalformats.
Whyare confusing the idea the can storing something like the string ‘2017-03-10’ as a DATE, but not as an INTEGER? I have stressedfor decades (not years, decades), that we need check constraints toassure that data takes a correct format on its way into the databaselayer of a well formatted schema.
Isimply hope that the MySQL convention for years and months, as whatthe ANSI ISO standard calls INTERVAL data types will become theirpresentation format. Until then, the MySQL L community has a hugepresence in the trade, and that usually means they’re going to gettheir way. I think it’s a good thing.
Slightlyoff-topic from this post, the ANSI/ISO standards have an INTERVALdata type. This is based on the on picking two points in the spectrum(year, month, day, hour, minute, second). This is what lets us dostart and end dates for an event. Currently it’s not possible inMicrosoft.. My lookup tables are keyed on what will be eventually atemporal value; currently I cannot express it in the product, so Ihave to use strings and put a hell of a lot of check constraints onthem to make sure that they are never violated. This comes fromworking with a product that is not up to standards and doing akludge. But as a responsible a programmer. I need to make sure thatwhen Microsoft catches up, my stuff will be there and ready. I’vealready had to do this with outer joins, bit datatypes and otherthings that were substandard in the old days.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Friday, March 10, 2017 2:26 PM
I'm going to frame this one, Joe. It's a real shame that they've hidden all of your posts for your previous blackballed logins so that I could show you how many times you've broken the antlers off an OP for doing the exact same things you've just preached.
I agree that reporting tables and views and functions along with whatever the local date/time dialect is when SQL Server is necessarily the only "presentation layer" that you have. This is especially true when creating files for output that must be transmitted in a required format even if those formats aren't ISO 8601.
On that note, send someone not using MySQL a file with month information using your highly proprietary, non-standard, and generally unsupported YYYY-MM-00 format and see if they actually appreciate what you've sent them. 😉 For that matter, send them the ISO 8601 format of YYYY-MM and see how happy you make them. You frequently rant about portability and then forget about data portability.
As for the confusion you mention between dates and integers, there is no confusion on my part. I didn't bring up integers because we're talking about dates. You're the one that brought up integers. I will say that if anyone ever sent me an integer as an equation like 2017-03-22 in a file instead of just 1992, there would be an immediate road trip and a pork chop launcher involved. Anyone that stores a date of 20170322 (for example) as in integer in a table (even a reporting table) is out of their mind (what the hell were they thinking when they made the job/step history tables in MSDB? :sick:)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply