T-SQL Common Table Expression to get Last Value on Max Date, Has a Alpha Key -- 5
Budget: $10 – $30 USD
T-SQL to get the Max Earnings value for each ID_NUM. My attempt is to use the last date SAL_RWV_DTE. When I try to join on [ORG_POS] I get the error listed below. It might need a window over partician to determine row numbers. The test script and data are attached. Reply with when you can complete the project. Project needed in 12 hours. Sql Server 2016 SP1
Conversion failed when converting date and/or time from character string.
Warning: Null value is eliminated by an aggregate or other SET operation.
Table
[ID_NUM] [int] NOT NULL, key
[ORG_POS] [varchar](10) NOT NULL, key
[POS_SEQ] [int] NOT NULL,key
[SAL_RVW_SEQ] [int] NOT NULL,
[SAL_RWV_DTE] [datetime] NULL,
[EARNINGS] [numeric](10, 2) NOT NULL
Please reply with:
The time of day that you are available. I am available 7am to 10pm UTC-06:00 Central Time US & Canada. Will you require contact by voice and do you speak English? When you can complete the project.
Confidentiality.
a) No Use. Recipient agrees not to use the Confidential Information in any way, or to manufacture or test any product embodying Confidential Information, except for the purpose set forth above.
b) No Disclosure. Recipient agrees to use its best efforts to prevent and protect the Confidential Information, or any part thereof, from disclosure to any person other than Recipient's employees having a need for disclosure in connection with Recipient's authorized use of the Confidential Information.
c) Protection of Secrecy. Recipient agrees to take all steps reasonably necessary to protect the secrecy of the Confidential Information, and to prevent the Confidential Information from falling into the public domain or into the possession of unauthorized persons.
d) Scope. The scope of Confidentiality is deemed to be in all contracts present past and future with the Parties to this request
Conversion failed when converting date and/or time from character string.
Warning: Null value is eliminated by an aggregate or other SET operation.
Table
[ID_NUM] [int] NOT NULL, key
[ORG_POS] [varchar](10) NOT NULL, key
[POS_SEQ] [int] NOT NULL,key
[SAL_RVW_SEQ] [int] NOT NULL,
[SAL_RWV_DTE] [datetime] NULL,
[EARNINGS] [numeric](10, 2) NOT NULL
Please reply with:
The time of day that you are available. I am available 7am to 10pm UTC-06:00 Central Time US & Canada. Will you require contact by voice and do you speak English? When you can complete the project.
Confidentiality.
a) No Use. Recipient agrees not to use the Confidential Information in any way, or to manufacture or test any product embodying Confidential Information, except for the purpose set forth above.
b) No Disclosure. Recipient agrees to use its best efforts to prevent and protect the Confidential Information, or any part thereof, from disclosure to any person other than Recipient's employees having a need for disclosure in connection with Recipient's authorized use of the Confidential Information.
c) Protection of Secrecy. Recipient agrees to take all steps reasonably necessary to protect the secrecy of the Confidential Information, and to prevent the Confidential Information from falling into the public domain or into the possession of unauthorized persons.
d) Scope. The scope of Confidentiality is deemed to be in all contracts present past and future with the Parties to this request