|
|
How is the @Sql1 variable defined ?
Ex: Declare @Sql1 Varchar(200)
|
|
|
|
|
After thinking about this a bit more than required;
Member 11533892 wrote: But I didn't want to divide the execution of the update into 2 variables!!!
You mean you don't want to execute two update-statements for the same record. Two variables cost nothing nowadays, so that doesn't make any sense. Two updates must be atomic, or you get concurrency issues. Could be simply solved by using a transaction.
You may find the error to be the design of the table, not the update-statement. I'm going to guess a lot of those fields can be "NULL" or revert to some preset default value.
..but, let me give you a solution from a VB6 programmer view; you can rename all columns into numbers. So if column one is named [Name], you replace that with [1]. You can keep a list in Excel to look up the column names. That way, the update-statement is shorted a lot, and a huge lots of them would fit in a VARCHAR(MAX) command.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I Have 3 Tables Orders, OrderItems, OrderPymts. For this query, I only need records from the Orders and OrderPymts Tables.
The Order Table Contains Fields: OrderId, ClientId, SubTotal, Tax, OrderTotal
The OrderPymts Table Contains Fields: PaymentId, OrderId, PaymentDate, Terms, PrevBal, Payment, Balance.
I'm trying to query records only on the Last PaymentDate for each Order (To show the Outstanding Balance of Each Order).
I tried This:
SELECT Orders.OrderId, Orders.ClientId, Orders.OrderTotal, OrderPymts.Terms, (SELECT MAX(OrderPymts.PaymentDate)) AS PaymentDate, OrderPymts.PrevBal, OrderPymts.Payment, OrderPymts.Balance
FROM OrderPymts
JOIN Orders ON OrderPymts.OrderId = Orders.OrderId
WHERE (Balance >= 0.01)
ORDER BY Orders.OrderId, PaymentDate
To just get the last payment but it still returns all the payments.
Example Result of My query:
OrderId |ClientId |OrderTotal|Terms |PaymentDate|PrevBal|Payment|Balance|
1001 | 1 | 500 |Credit| 1/1/2023 | 500 | 100 | 400 |
1002 | 2 | 800 |Cash | 1/9/2023 | 800 | 200 | 600 |
1002 | 2 | 800 |Cash | 1/11/2023 | 600 | 100 | 500 |
Above is an example of the data my query is showing. I only want to get the bolded records in the query. But my code above is pulling all the records with a balance greater than 0.01. As you can see, I tried getting the Max date but that didn't work. I also tried Group By but I get the same results as above. I need someone smarter than me, to help me with this.
Thanks for your help.
modified 20-Jan-23 11:34am.
|
|
|
|
|
Assuming Microsoft SQL Server, ROW_NUMBER is your friend. 
WITH ctePayments As
(
SELECT
OrderId,
PaymentDate,
Terms,
PrevBal,
Payment,
Balance,
ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY PaymentDate DESC) As RN
FROM
OrderPymts
)
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
INNER JOIN ctePayments As P
ON P.OrderId = O.OrderId And P.RN = 1
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
; ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[^]
Alternatively, you could use a correlated sub-query:
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
CROSS JOIN
(
SELECT TOP 1 Terms, PaymentDate, PrevBal, Payment, Balance
FROM OrderPymts
WHERE OrderPymts.OrderId = O.OrderId
ORDER BY PaymentDate DESC
) As P
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank You Richard. I'm coming From VB.Net learning C#, and SQL, Didn't think about a cross Join.
modified 20-Jan-23 18:43pm.
|
|
|
|
|
On a different note - it is probably not a good idea to link payments to an order, or at least make it a secondary connection. A client should have an account, orders place a debit on the account and payments credit the account. You may then want to link 1 or more payments to an order or 1 or more orders to a payment.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I needed it to show the last payment made on an invoice. But thanks for your advice.
|
|
|
|
|
I have an entity table which is begin queried into an entity:
UserEntity? user = dc.Users
.Where(x => x.UserName.CompareTo(entity.UserName) == 0)
.FirstOrDefault();
Now, if I add a property to the entity that does NOT exist in the underlying table, it throws an exception:
Microsoft.Data.SqlClient.SqlException: 'Invalid column name 'FullName'.'
FullName if not a table field. How can I prevent this exception for properties that don't exist in the data?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
If I remember correctly, you add the [NotMapped] attribute to the property in your model class.
public class User
{
public string FirstName { get; set; }
public string LastName { get; set; }
[NotMapped]
public string FullName { get; set; }
}
You could easily find this yourself just by Googling for "Entity Framework ignore property".
|
|
|
|
|
Thanks.
I spent time on Google looking around, but I wasn't really sure what I was looking for.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
How to backup multiple database to Azure blob storage using native SQL Server Backup to URL option.
I have used the Ola Backup scripts with success but will like another option.
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@BackupType = 'FULL',
@Compress='Y',
@copyonly ='Y',
@Url='https://development.blob.core.windows.net/backups',
@BlockSize=65536,
@MaxTransferSize=4194304
--script to modify for multiple database
DECLARE @name VARCHAR(100) -- database name
Declare @url as Varchar(max) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory e.g. 'https://development.blob.core.windows.net/backups'
SET @url = 'https://development.blob.core.windows.net/backups'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO URL = @URL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Any suggestions that does not include having to execute the backup command for each database will be helpful
|
|
|
|
|
Myself I would first look for a commercial solution. Rolling your own requires dealing with all of the possible error scenarios and notifications that must occur when an error happens.
But other than that there are two problems not one. So there should be two solutions.
1. Do the back up
2. Move the back up to the remote location.
|
|
|
|
|
Using EF Core 6 Code First.
I'm trying to create a Notes field.
entity.Property(e => e.Notes)
.HasColumnName("Notes")
.HasColumnType("varchar");
This becomes varchar(1) in the DB. How do I create a column with varchar(max)?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
|
Your post did not answer the question
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
When I look at the posted link it certainly looks to me like it provides an answer to the question asked.
So perhaps you might want to explain how that does not answer your question.
|
|
|
|
|
I need to create a subquery that pulls detail records for the header records in the main query.
But I'd like to display values from the detail records in the form of a comma separated list, like so, "value1, value2, value3". And this list should appear as an extra column in the result set.
Is there any way to achieve this in SQL Server?
SOLUTION: Use the STRING_AGG function! Cool.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
STRING_AGG is the best approach for SQL Server 2017 or later.
If you need to support earlier versions, you'll need an alternative:
Concatenating Row Values in Transact-SQL - Simple Talk[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Well today was a crappy day, thinking I can hit and run this small web page and clean it up. I spent hours modeling this in SQL Manager finally getting it to work, and then used it my PHP code, it worked but fell apart. Maybe I have it backwards, and I should call "Project" first, and then join "commission_Summary"
The first example works, but when I join the "project" table, it cancels out my "commission_summary.startup_check_date" filter. I get that or think that my JOIN is wrong, or that my JOIN is calling up all records and overriding my date filter. I tried different types of JOINs, but with no success.
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate
FROM commission_summary
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
ORDER BY commission_summary.startup_check_date DESC
I tried adding one join at a time, and it worked or produced my desired results using just JOIN. I don't understand why this returns all the records, well I know it's JOIN related, just not sure why. My theory, is that the "Project" JOIN may have a duplicate column name. I wonder if I should of used a wildcard for testing, like SELECT * FROM. This returns all the records
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate,
project.Status,
project.Sales_no,
project.swan_job,
employee.Employee_ID,
employee.FName,
employee.LName,
customer.Customer_no, customer.LName,
customer.FName,
customer.City
FROM commission_summary
JOIN project on commission_summary.project_no = project.project_no
JOIN employee on commission_summary.employee_id = employee.Employee_Id
JOIN customer on customer.Customer_No = project.Customer_no
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
AND project.status = 'construction' OR project.status = 'finished'
ORDER BY commission_summary.startup_check_date DESC
I'm confused by how this works, because it produces my desired results, of about 12 records in 2022.
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10),
commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate,
project.Status,
project.Sales_no,
project.swan_job,
employee.Employee_ID,
employee.FName,
employee.LName,
customer.Customer_no,
customer.LName,
customer.FName,
customer.City
FROM commission_summary
JOIN project on commission_summary.project_no = project.project_no
JOIN employee on commission_summary.employee_id = employee.Employee_Id
JOIN customer on customer.Customer_No = project.Customer_no
WHERE (CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME))
AND project.status = 'construction' OR project.status = 'finished'
AND commission_summary.employee_id = '91'
ORDER BY commission_summary.startup_check_date DESC
Any point in the right direction would be appreciated, or even a simple explanation.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
You have an OR in there without any parentheses, that might be the problem. Try this:
AND (project.status = 'construction' OR project.status = 'finished')
Both sides of the OR are using the same table column, so you could use IN instead
AND project.status IN ('construction', 'finished')
|
|
|
|
|
That seems to be it. Wow!
I didn't catch that yesterday.
Thanks
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I always use parens. Removes the question for me an future maintenance programmers as to what I thought the precedence was.
|
|
|
|
|
That was hard to describe.
Because of some bad business logic, or dealing with say vendors that retire or go out of business after 20 years, the question of what to do with them remains.
I'm thinking it's best to ask for the vendor anyways, and join any task if it exists. If it doesn't exist, then at least give me the vendor info, and the rest can be blank. So if I can get the VendorID, CompanyName and Vendor DeleteFlag even if the JOIN can't JOIN or the JOIN request doesn't exists, that would solve some issues for me.
I don't know what this is called, and the keywords I'm using don't bring up much except for NOT EXIST. I'm just looking for a point in the right direction or no it can't be done.
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044'
If it ain't broke don't fix it
Discover my world at jkirkerx.com
modified 21-Nov-22 15:36pm.
|
|
|
|
|
But it would interesting to be able to do it.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
