|
hi
I don't know whats happened sorry...I tried so hard to edit the previous post, and it would not let me, so I signed out , went to log in it said denied, asked for reset I think it has issues if you login with facebook as uses same email. please help
|
|
|
|
|
If you have problems with your account then you should use the Bugs and Suggestions[^] forum to get assistance from the administrators. I suggest you do so now from your original account, and delete this one.
|
|
|
|
|
Not only is this a repost of the question below, you haven't responded to any of the questions asked in response to that post.
2018-10-31 is less than 2018-11-01; therefore, if there are no records with SaleDateFrom <= '20181101', there will be no records with SaleDateFrom <= '20181031'.
You need to explain what you're actually trying to achieve.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm doing my best to explain ,
here is my where clause
where (SaleDateFrom<= '20181101'
and (SaleDateTo '20010101' or
SaleDateTo is null))
I want to replace the date in bold to 20181031 if the highest date entered into the data base is not the 20181101. this is to make sure the data for the start of the month has been entered
please help
please see response to Richard - about account big issue, which I be contacting code project about
|
|
|
|
|
And once again: If there are no records where the SaleDateFrom is on or before 1st November, then there will be no records where SaleDateFrom is on or before 31st October.
If there is a record for 31st October, then that will already be included in your current query.
If there are no records on or before 1st November, then changing your query to look for records with an earlier date isn't going to make any difference.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
that makes sense now...fab , I totally get it. thank you team.. I do appreciate the help 
|
|
|
|
|
Why don't you use a sub-select for the date?
where (SaleDateFrom <= ( select max(your date column) from your table with date value ) and (SaleDateTo '20010101' or SaleDateTo is null ))
|
|
|
|
|
Hi
I wish to only begin a select statement if the top value date is found. It's my way of knowing data has been added into a system at the beginning of the month e.g max date is 01/01/2018. If not take the last day of the month 31/10/2018. Table is called sales. Column is called SaleDateFrom.
I want to pull e.g data from SaleDateFrom <=01/01/2018...if not pull data
SaleDateFrom <=31/12/2018
Any ideas team?
|
|
|
|
|
That does not make a lot of sense. If you ask for records whose date is less than or equal to 01/01/2018, then all records whose date is less than 31/12/2017 (I presume you mean 2017 here) will also fulfil the criteria.
|
|
|
|
|
You want to pull the "most recent months" data.
Yes that it possible. Didn't look but I am certain that google will display many answers for that. Add your specific database in do get a more relevant answer.
|
|
|
|
|
I have 2 date time entries that represent time in and time out. I want to graph this data representing how many people or on the clock per 15 minute intervals.
Should I create a temp table that holds the qty of people that are clocked in for every 15 minute interval or what?
|
|
|
|
|
I usually use a view to service this type of requirement. Craft the view to supply only the required data eg date, starttime in 15 minute groupings and the number of people falling into the slots who are clocked on. End time only represents a person not clocked on and is irrelevant to the graph.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I am trying to document a system that I have taken over
it is a single website with 3 database connections. The access to the database is via a single SQL Server login and it has the minimal access that is required for the site.
My question is; each database has the same logon should they have different logon names for the database?
Thanks
Simon
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
modified 9-Nov-18 4:33am.
|
|
|
|
|
My door key fits in the front door and in the back door; it may not be as safe as two keys, but it is convenient. OTOH, there's little of value here, so the risc of something happening is low.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Why would two keys be safer?
You only need to break into one door to access the house.
|
|
|
|
|
That's because my example is not perfect; if it were two houses it would be more in line with the multiple databases.
I do not buy locks based on best practices, but rather on the risc versus the damage.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
|
Thank Eddy your analogy made sense 
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
You're welcome 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
|
Wasn't sure if it was a problem so thought I'd ask a question.
Looks like from yours and Eddys response that there is not really risk with it so I am not going to change it.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
If you plan to split the databases in the future there might be a point to it.
Or if you want to give different people different access, but there are easier ways to achieve that.
|
|
|
|
|
No the databases are already split into 3
Users
Data
Log
I was asked if there was merit to different logons for the website to the databases (via services) but couldn't find a suitable answer so thought I'd ask her as someone may know of a good practise or had experience.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Hi all,
I have a joins select statement, what I want is, if I am having same tables in two left joins and with exclusive Data then in another table when we are joining Data in ON clause I want to be able to Join one or the other table to get the Data, maybe like a case statement but joining one table in condition over the other.
Here is the example:
<pre>DECLARE @AdminAddressTypeId int
SET @AdminAddressTypeId = (select top 1 AddType.PKAddressTypeLKPId from AddressTypeLKP AddType where AddType.Description = 'Administrative')
SELECT LE.LegalEntityName
,LE.PKLegalEntityId
,LE.FederalTaxId
,LE.LegalEntityNbr
-- ,LE.LegalEntityBgnDt
-- ,LE.LegalEntityEndDt
-- ,LE.CreatedDate
-- ,LE.CreatedBy
-- ,LE.ModifiedDate
-- ,LE.ModifiedBy
-- ,OW.Description as OwnershipType
--,OW.Code +'-'+ OW.Description as OwnershipType
--,LE.FKOwnershipTypeLKPId
--,MGMT.Description as ManagementType
--,LE.FKManagementTypeLKPId
,PRG.Description as ProgramType
,LE.FKProgramTypeLKPId
--,C.County_Name as CountyName
,C.PK_Geographic_Location_Code + '-' + C.County_Name as CountyName
,LE.FKCountyLKPId
,AA.StreetAddress1
,AA.City
,Admin_S.State_Name
,AA.Zip1
-- ,LE.IsCompleted
--,LE.Comments
--,LET.Description as LegalEntityType
FROM [dbo].[LegalEntity] LE
-- left outer join [dbo].[OwnershipTypeLKP] OW ON LE.FKOwnershipTypeLKPId = OW.PKOwnershipTypeLKPId
-- left outer join [dbo].[ManagementTypeLKP] MGMT ON LE.FKManagementTypeLKPId = MGMT.PKManagementTypeLKPId
inner join [dbo].[ProgramTypeLKP] PRG on LE.FKProgramTypeLKPId = PRG.PKProgramTypeLKPId and PRG.Description = 'Mental Health'
left outer Join [dbo].County C on LE.FKCountyLKPId = C.PKCountyId
--left outer join [dbo].[LegalEntityTypeLKP] LET on LE.FKLegalEntityTypeLKPId = LET.PKLegalEntityTypeLKPId
Left outer join LegalEntityAddress LEA_Admin on LE.PKLegalEntityId = LEA_Admin.FKLegalEntityId and LEA_Admin.FKAddressTypeLKPId = @AdminAddressTypeId and LEA_Admin.IsValid = 1 and ( LEA_Admin.EffectiveDateTo is null or LEA_Admin.EffectiveDateTo > getdate())
--(case when @AAEffectiveDateTo ='' then 0 else @AAEffectiveDateTo end)
left Outer JOIN Address AA ON LEA_Admin.FKAddressId = AA.PKAddressId
Left outer JOIN County Admin_CY on Admin_CY.PKCountyId = AA.FK_County
Left outer join State Admin_S on Admin_S.PKStateId = AA.FK_State
where LE.IsCompleted = 1
What I want is I am checking for only LegalEntityAddress that are Admins, but if Admin Address is not available for that Legal Entity I want to display any address that has the latest closing Date, if it doesn't find any data for these two conditions then I want any first date that's available or the one with highest PK value.
If I can achieve this by having one time join of the table that will be nice if I can't get it by using one same table join or need same table to be joined multiple times, please help me how can I achieve it. If I need to have LegalEntityAddress multiple joins with different conditions then how can I handle that situation in further down tables which are using this LegalEntityAddress reference table to join.
Any help would be very helpful, thanks in advance.
|
|
|
|
|
I think you are going to need multiple joins to the same table and use nested ISNULL conditions.
However your requirement to pick the latest valid address due to different conditions is going to make this a nightmare in a single pass. I would split that logic out to a view that encapsulates the rules and supplies a single address for each entity. Then simply inner join to the view. Make the view generic and you have it available for any procedure 
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
