|
Instead of removing them, you can just group them by their ID. 
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
I am surprised as to why you didn't consider using GroupBy function? That would be useful in grouping the objects by a property, so that only distinct ones are returned, since they will be grouped by their property in a similar manner, to what you want to expect from Distinct function call here. Something like this,
from oh in context.Order_History
where oh.OrderDate ...
orderby oh.OrderNumber desc
group oh by oh.RequiredProperty in ohGrouped
...
select ...
Something like this will be useful, as it will group the records by the properties which you specify. Please have a look here, [c# - Group by in LINQ - Stack Overflow](https://stackoverflow.com/questions/7325278/group-by-in-linq)
There is a library, which contains a lot of added features to LINQ, you should also look into it as it might also help you out with a lot of good function calls, such as this DistinctBy (DistinctBy takes the properties by which you want to select the records), get it here, GitHub - morelinq/MoreLINQ: Extensions to LINQ to Objects
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
How do I set this in a Sql query?
If code >= 1 or <= 9 then place two zeros if front...001, 002....009
if code >= 10 or <= 99 then place one zero if front....010, 050...,099
Thanks
**** NEVER MIND, I GOT IT:*********
when [EMPLOYEE].JOB_CODE >=1 AND [EMPLOYEE].JOB_CODE <= 9 then '00' + [EMPLOYEE].JOB_CODE
when [EMPLOYEE].JOB_CODE >=10 AND [EMPLOYEE].JOB_CODE <= 99 then '0' + [EMPLOYEE].JOB_CODE
modified 20-Jul-17 8:43am.
|
|
|
|
|
CASE
If current SQL Server, look at FORMAT.
modified 19-Jul-17 20:53pm.
|
|
|
|
|
Don't you mean ...
If code >= 1 AND <= 9 then place two zeros if front...001, 002....009
if code >= 10 AND <= 99 then place one zero if front....010, 050...,099
... ?
|
|
|
|
|
Correct - was in a rush to leave the office when I typed it.
I want to concatenate 00 in front of the value.
modified 20-Jul-17 8:31am.
|
|
|
|
|
For Microsoft SQL 2012 or higher, use FORMAT[^]:
FORMAT(code, 'D3')
For older versions, use CASE[^]:
CASE
WHEN code Between 1 And 9 THEN '00'
WHEN code Between 10 And 99 THEN '0'
ELSE ''
END + CAST(code As varchar(10))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
you can do it without any case statement like this:
SELECT Right('00' + Convert(Varchar(10),[code]),2) as [Code]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Firstly, the second argument to Right should be 3, not 2.
And secondly, if the input number is 1000 or higher, that will strip the leading digits:
WITH cte (code) As
(
SELECT 1
UNION ALL SELECT 10
UNION ALL SELECT 100
UNION ALL SELECT 1000
UNION ALL SELECT 10000
)
SELECT
code,
RIGHT('00' + CONVERT(varchar(10), code), 3)
FROM
cte
; Output:
1 001
10 010
100 100
1000 000
10000 000
It's not clear whether that's what the OP wants, but I doubt it. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You are correct to point out my mistake, but it's easily fixed.
There is, of course another way:
SELECT Format([Code],'000') as [Code]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Chris Quinn wrote: There is, of course another way:
Which was the first thing I suggested above[^]!
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello,
Use REPLICATE() and RIGHT Functions,
SELECT RIGHT(REPLICATE(0,2)+CAST([EMPLOYEE].JOB_CODE AS NVARCHAR(200)),3) FROM YourTable
Regards,
Pradeep M
|
|
|
|
|
How do I set value to zero in a Pivot? I would like to set the NULL values to zero from attached code
Select * From
(select Partner, Facility, PAY_PERIOD_BEGIN_DATE, PAY_PERIOD_END_DATE, Job_Code, Pay_ClassHrs, Pay_ClassAmount, Pay_Hours, Pay_Amount from #PremierFinal) as sourcetable
PIVOT
(
sum(Pay_Hours)
FOR Pay_ClassHrs in (PBS_Hrs, POT_Hrs, PED_Hrs, POR_Hrs, PJB_Hrs, PTO_Hrs, PWH_Hrs, PHR_Hrs, PNH_Hrs)
)as pvt
Quote: PBS_Hrs
NULL
NULL
NULL
NULL
792
278
NULL
|
|
|
|
|
Try using an ISNULL
e.g. ISNULL(sum(Pay_hours),0)
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
It is not the SUM that I want to zero but the column values in
FOR Pay_ClassHrs in (PBS_Hrs, POT_Hrs, PED_Hrs, POR_Hrs, PJB_Hrs, PTO_Hrs, PWH_Hrs, PHR_Hrs, PNH_Hrs)
example:
case when PBS_Hrs IS NULL then 0 end and so on...
Thanks
|
|
|
|
|
I tried it but it comes back with Syntax error, my environment is MS SQL SERVER Management Studio 2016:
FOR Pay_ClassHrs in (ISNULL(PBS_Hrs, 0), POT_Hrs, PED_Hrs, POR_Hrs, PJB_Hrs, PTO_Hrs, PWH_Hrs, PHR_Hrs, PNH_Hrs)
Any ideas...!
|
|
|
|
|
This isn't a question about how to detect and prune unused tables in a live database. I'm more interested in the reasons why it should be done at all.
Here's some context. In our development environment we maintain a production database from which a schema and upgrade script is generated. When a database is upgraded it will create or alter existing tables on a live system, but not remove any tables.
After a debate about how it could be done and whether it should be done, I was struck by my colleague's statement of 'a lot of effort and potential pain, for not much gain'. There is of course the obvious risk of removing a table that really is used and causing a live system to fail.
There didn't seem to be much debate on whether defunct tables should be removed from a live database on the internet.
Apart from those of us who are hopelessly OCD about these sort of things, are there any good reasons for running the risk of removing defunct tables from a live system?
|
|
|
|
|
mbb01 wrote: Apart from those of us who are hopelessly OCD about these sort of things
Sorry I fall into this category so no further justification is required.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
We use procedure that move tables into a recyclebin schema based on naming and date criteria (e.g. no live tables will contain digits in their names, but tables created/copied in response to support tickets will have the ticket number appended to the table name).
After a specified period (two weeks in our case) tables containing digits that were created prior to thespecified date are moved to the recycle bin schema. If someone screams that their table has gone, we move it back in to the original schema.
After another specified period (again two weeks), any table that has been in the recyclebin schema longer than that will be dropped.
This is all done automatically using an Agent job which calls a standard stored procedure we created.
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Thanks Chris, I'll certainly put forward your good ideas to our DBAs.
But ... you answered how, not why? Implicit in your answer is the acceptance that the live database should reflect what it 'should' be from Dev.
What are the reasons (technical or business) why a live system should reflect exactly what is in Dev, when those superfluous tables are not really harming the operation of the live system?
Any insights would be appreciated.
|
|
|
|
|
Our procedures specify that is we are doing data fixes, or adding new functionality, we back up any data affected and any procedures etc to allow us to revert quickly in cases where something has gone wrong, or the client has asked for the wrong thing etc. All backups contain the ticket number - this allows other team members to find them quickly if the person who originally worked on the ticket is not available for any reason
We clear them after a set period to free space and keep the production systems as tidy as possible.
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
mbb01 wrote: I'm more interested in the reasons why it should be done at all.
Pruning does not prove the table is not in use or required. Imagine all your clients going down due to a simple logging-table that is only used once in the entire application, in a not-often used function.
mbb01 wrote: After a debate about how it could be done and whether it should be done, I was struck by my colleague's statement of 'a lot of effort and potential pain, for not much gain'. There is of course the obvious risk of removing a table that really is used and causing a live system to fail.
..and what is the gain? That some empty unused table is no longer present? How much space did you reclaim with that action, and how much does space cost these days? Now compare the potential gain to the potential risc.
I would strongly recommend creating some documentation; that would slow the development-proces, but it would also result in a stronger grip on your datastore. Someone wrote a SQL/Linq/EF-statement? Please update the docs and jot down which tables are impacted and when.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
What percent, by size, of the database is unused tables? If the percent is significant enough, then the savings may be using less disk space, offline backups, etc.
Is there a legal requirement on how long the data must be maintained, and, by extension, if the data is still available, can it be used in a legal sense against the company?
Are there valid reasons to prune? Yes, but they are dictated by the business needs.
|
|
|
|
|
mbb01 wrote: are there any good reasons for running the risk of removing defunct tables from a live system?
Sure. If there is a table with 100 million rows then removing it saves that space in a number of contexts. Obviously not as much an issue with 3 rows.
Additionally there is a maintenance issue. If there is an old table that Bob knows is no longer in use and then Bob dies and they hire Sharon to replace him the only way she can figure out that the table is not in use is to go through the entire code base. Not so much a problem if the is 100 lines of code but a real problem with 100 million lines of code (and poorly organized code at that.)
Same if Bob is on vacation and a new table replaced the old one but the data still exists in the old one. Sharon needs to make an emergency fix and ends up looking at the old one, and the data sort of looks correct, so that is what gets fixed.
Obviously that latter problem won't be a problem in there is sufficient testing in place to adequately test the enterprise. But if that is true then removing old cruft isn't a problem either because that same testing will demonstrate problems with that as well.
mbb01 wrote:
Apart from those of us who are hopelessly OCD about these sort of things
Me I am in the camp that there better be enough testing in place, both automated and manual, that changes have a minimal risk because changes will be needed even if old information is not removed. And I can't see that insuring functionality with additions and modifications would not cover deletions as well.
Or there is not sufficient testing in place and that is a problem.
|
|
|
|
|
Everyone seem to think this about space and performance, it should be about support. Someone looking at the schema in the future should not have to chase down the usage of redundant tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
