[Solved] SQL Server Error: Invalid column name

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Aug 16, 2023 12:05 AM
Forum: SQL Server
Answer: 1
Views: 839

I have a following Data

tblCalving

ID Tag C_Date

1 101 2023-08-10

2 105 2023-08-15

3 107 2023-08-21

tblProtocol

ID Days Pro_Name

1 0 PU + BU

2 5 I.U Check

3 10 Utrus Examination

Required output:

In tblCalving data is added on 2023-08-10 and 2023-08-15. Now we want to fectch data based on current date. So, this added data will be mapped with tblProtocol for adding days. In tblProtocol Days value is 0 and 5 and 10. Today is 2023-08-15, Hence 0 days will be added in 2023-08-15 and 5 days will be added in 2023-08-10. Now output will be like that following table.

ID Tag C_Date Days New Date

1 101 2023-08-10 5 2023-08-15

2 105 2023-08-15 0 2023-08-15

I wtote the following query.

SELECT CalvID,C_Date,DATEADD(DAY,(SELECT D_After FROM tblProtocol where PID='2'),C_Date) as [Date] FROM tblCalving 
where [NewDate]=CONVERT(varchar(10), GETDATE(), 23)

It is showing me following error

Msg 207, Level 16, State 1, Line 2 Invalid column name 'NewDate'.

how to get solution plz?

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 5

Reply
ANSWER
User: dharmendr
Replied: on Aug 17, 2023 01:03 AM
Report

Hi smile,

Refer below sample query.

SQL

DECLARE @tblCalving AS TABLE(ID INT,Tag INT,C_Date DATE)
INSERT INTO @tblCalving VALUES (1, 101, '2023-08-10')
INSERT INTO @tblCalving VALUES (2, 105, '2023-08-15')
INSERT INTO @tblCalving VALUES (3, 107, '2023-08-21')


DECLARE @tblProtocol AS TABLE(ID INT,Days INT,Pro_Name VARCHAR(50))
INSERT INTO @tblProtocol VALUES (1, 0, 'PU + BU')
INSERT INTO @tblProtocol VALUES (2, 2, 'I.U Check')
INSERT INTO @tblProtocol VALUES (3, 10, 'Utrus Examination')

SELECT * FROM
(
SELECT ID,Tag,C_Date,
	(SELECT Days FROM @tblProtocol p where p.Id = t.Id) [Days],
	DATEADD(DAY,(SELECT Days FROM @tblProtocol p where p.Id = t.Id),C_Date) as [NewDate] 
FROM @tblCalving t
)z
WHERE [NewDate] = CAST(GETDATE() AS DATE)

Output

ID Tag C_Date Days NewDate

2 105 2023-08-15 2 2023-08-17

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

[Solved] SQL Server Error: Invalid column name

Company

Explore

Follow us

Contact Us