March 16, 2017 at 9:23 am
Hi
Im using a view [dbo].[CustomerBrokerView_PMR2] , in orderto do a join and select the top 1 Primary Key
value our of this view
My code is as follows:
FROM [dbo].[SalesReportView] srv
INNER JOIN dbo.CustomerView cv ON srv.CustomerId = cv.customerid
innerJOIN [dbo].[CustomerBrokerView_PMR2] cb ON cb.customerid = cv.CustomerID AND cb.[AccountOffice] = cv.[OfficeLocation]
AND
cb.[customerBrokerid] =
(
SELECT TOP 1[customerBrokerid]
FROM
[dbo].[CustomerBrokerView_PMR2]CBV
WHERE
CBV.[customerid]= cv.CustomerID
AND
cbv.[AccountOffice] =cv.[OfficeLocation]
AND
cbv.[CreatedAt] <= srv.CreatedAt
ORDER BYcbv.createdat desc
My data in [dbo].[CustomerBrokerView_PMR2] is as follows:
| customerBrokerid
| customerid
| CustomerName
| CreatedAt
| AccountOffice
|
| C9565947-AA73-49B3-9CF7-C2218E93B2E3
| 8E0CB473-F889-42EC-9646-99E4A6FB0EF3
| SWFC ( Sheffield Wednesday FC )
| 26/08/2015 17:36
| London
|
| 1A03E7EB-0DEF-4CE4-B5A9-0187F86E6E58
| 8E0CB473-F889-42EC-9646-99E4A6FB0EF3
| SWFC ( Sheffield Wednesday FC )
| 22/02/2017 14:01
| London
|
A sorting [dbo].[CustomerBrokerView_PMR2] in date descending order , will yield
the value 22/07/2017 14:01
I know that the value for srv.CreatedAt will return '016-01-21 00:00:00.000'and therefore
I expect no value to be returned
however a value is being returned because the inner Top 1 query forSelect Top 1
should give me the value for the 2nd row shown above in Red and white
what is going on here and why does sorting a view with a unique identifier giveme the wrong result?
is there something I need to do in the casting in my datatypes in the joins orfields in my view??
March 16, 2017 at 9:35 am
With only a portion of the code being run, no DDL for the tables/views involved and no sample data that is representative of the problem domain and the problem (sense we also don't have access to your servers and databases to see for ourselves) we can't really give you a clear answer.
As this appears to be a correlated sub-query, you may also want to look at the data in the table outside the query providing date information.
March 16, 2017 at 9:40 am
The screenshot doesn't show where the data is coming from, so all we can do is guess.
If you supply DDL and consumable sample data that shows the problem, it would be much easier.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 16, 2017 at 9:58 am
Hi there
OK here goes
This is my complete query
SELECT
srv.salesreportid,
cv.CustomerName,
cv.officelocation,
cb.[AccountOffice],
cv.CustomerId,
cv.Division AS DepartmentType,
srv.SalesReportDate,
SRV.CreatedAt,
cb.BrokerCode,
cb.[CreatedAt] AS Cbv_effectiveDate,
cb.customerBrokerid,
srv.[CreatedAt] AS Updatedat,
CASE
WHEN srv.ContactType IN ('TELEPHONE')
THEN 1
ELSE 0
END AS Calls
FROM [dbo].[SalesReportView] srv
INNER JOIN dbo.CustomerView cv ON srv.CustomerId = cv.customerid
inner JOIN [dbo].[CustomerBrokerView_PMR2] cb ON cb.customerid = cv.CustomerIDAND cb.[AccountOffice] = cv.[OfficeLocation]
AND
cb.[customerBrokerid] =
(
SELECT TOP 1 [customerBrokerid]
FROM
[dbo].[CustomerBrokerView_PMR2] CBV
WHERE
CBV.[customerid] = cv.CustomerID
AND
cbv.[AccountOffice]= cv.[OfficeLocation]
AND
cbv.[CreatedAt] <= srv.CreatedAt
ORDER BY cbv.createdat desc
)
WHERE
YEAR(srv.CreatedAt) = 2016
AND
MONTH(srv.CreatedAt) = 1
AND
srv.ContactType = 'TELEPHONE'
AND
srv.BrokerCode = 'LONCOMPP'
ORDER BY cb.CustomerName
-----------------------
DDL for views
1) dbo.SalesReportView
| Column_name | Type | Computed | Length | Prec | Scale | Nullable | Collation |
| ID | uniqueidentifier | no | 16 | no | NULL | ||
| CustomerId | uniqueidentifier | no | 16 | no | NULL | ||
| salesReportID | int | no | 4 | 10 | 0 | yes | NULL |
| SalesReportDate | datetime | no | 8 | no | NULL | ||
| BrokerCode | nvarchar | no | 400 | yes | Latin1_General_CI_AS | ||
| CustomerName | nvarchar | no | 510 | no | Latin1_General_CI_AS | ||
| ContactForename | nvarchar | no | 510 | yes | Latin1_General_CI_AS | ||
| ContactSurname | nvarchar | no | 510 | yes | Latin1_General_CI_AS | ||
| ContactType | nvarchar | no | 400 | yes | Latin1_General_CI_AS | ||
| ReportType | nvarchar | no | -1 | no | Latin1_General_CI_AS | ||
| CreatedAt | datetime | no | 8 | no | NULL | ||
| CreatedBy | nvarchar | no | 400 | yes | Latin1_General_CI_AS | ||
| EstimatedChartersPerAnnum | numeric | no | 13 | 27 | 2 | yes | NULL |
| ContactMood | nvarchar | no | 400 | yes | Latin1_General_CI_AS | ||
| CityVisited | nvarchar | no | 510 | yes | Latin1_General_CI_AS | ||
| CountryVisited | nvarchar | no | 400 | yes | Latin1_General_CI_AS |
2) dbo.CustomerView
| Column_name | Type | Length | Nullable | Collation |
| CustomerID | nvarchar | 200 | yes | Latin1_General_CI_AS |
| CustomerGUID | uniqueidentifier | 16 | no | NULL |
| CustomerName | nvarchar | 510 | no | Latin1_General_CI_AS |
| IsSameAsACSName | bit | 1 | no | NULL |
| PostalName | nvarchar | 510 | yes | Latin1_General_CI_AS |
| CustomerSource | nvarchar | 400 | yes | Latin1_General_CI_AS |
| Division | nvarchar | 400 | no | Latin1_General_CI_AS |
| Broker | uniqueidentifier | 16 | yes | NULL |
| PrimaryBrokerContact | uniqueidentifier | 16 | yes | NULL |
| BrokerCode | nvarchar | 400 | yes | Latin1_General_CI_AS |
| AddressLines | nvarchar | -1 | yes | Latin1_General_CI_AS |
| Address | uniqueidentifier | 16 | yes | NULL |
| Town | nvarchar | 510 | yes | Latin1_General_CI_AS |
| County | nvarchar | 510 | yes | Latin1_General_CI_AS |
| State | nvarchar | 400 | yes | Latin1_General_CI_AS |
| Postcode | nvarchar | 400 | yes | Latin1_General_CI_AS |
| Country | nvarchar | 400 | yes | Latin1_General_CI_AS |
| Website | nvarchar | 510 | yes | Latin1_General_CI_AS |
| DefaultInvoicingContact | bit | 1 | no | NULL |
| BusinessType | nvarchar | 400 | yes | Latin1_General_CI_AS |
| EstimatedChartersPerYear | numeric | 13 | yes | NULL |
| IsVATRegistered | bit | 1 | no | NULL |
| VATNumber | nvarchar | 510 | yes | Latin1_General_CI_AS |
| IBOS_NT_UserName | nvarchar | 400 | yes | Latin1_General_CI_AS |
| OfficeLocation | nvarchar | 400 | no | Latin1_General_CI_AS |
| CreatedAt | datetime | 8 | yes | NULL |
3) dbo.CustomerBrokerView_PMR2
| Column_name | Type | Computed | Length | Collation |
| customerBrokerid | uniqueidentifier | no | 16 | NULL |
| customerid | uniqueidentifier | no | 16 | NULL |
| CustomerName | nvarchar | no | 510 | Latin1_General_CI_AS |
| CreatedAt | datetime | no | 8 | NULL |
| BrokerCode | nvarchar | no | 400 | Latin1_General_CI_AS |
| AccountOffice | nvarchar | no | 400 | Latin1_General_CI_AS |
SAMPLE DATA FROM VIEWS
| [dbo].[SalesReportView] | |||||||||||||||
| ID | CustomerId | salesReportID | SalesReportDate | BrokerCode | CustomerName | ContactForename | ContactSurname | ContactType | ReportType | CreatedAt | CreatedBy | EstimatedChartersPerAnnum | ContactMood | CityVisited | CountryVisited |
| 33E32998-8102-4AA2-8A35-5AEB74DDB1A9 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | 512879 | 22/02/2017 00:00 | LONCOMPP | SWFC ( Sheffield Wednesday FC ) | NULL | NULL | Telephone | Couldn't get through receoption. Tried twice now. Always in meetings. Tried bullshitting when asked the reason for the calls. Just said we'd been working on some things with her. PP | 22/02/2017 14:17 | pphilpott | 3 | Friendly | NULL | NULL |
| A4AD5922-9EE4-4205-A5C0-4B3A6BCF12AA | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | 512889 | 22/02/2017 00:00 | LONCOMPP | SWFC ( Sheffield Wednesday FC ) | Lindsey | Taylor | Telephone | Followed up with brochures in the post and email. PP | 22/02/2017 14:50 | pphilpott | 3 | Friendly | NULL | NULL |
| 7754C0B8-5B3D-4C0A-80C9-3AFAF2853C58 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | 362459 | 21/01/2016 00:00 | LONCOMPP | SWFC ( Sheffield Wednesday FC ) | Lindsey | Taylor | Telephone | Lindsey is the contact. Wasn't able to get through but was told to send her an email (don't think they like to put people directly through to their staff if they aren't returning a call. PP | 21/01/2016 15:16 | pphilpott | NULL | Friendly | NULL | NULL |
| 1E0A7FD7-CB21-4DEC-A682-D4F73E5AC933 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | 312590 | 26/08/2015 00:00 | LONCOMPP | SWFC ( Sheffield Wednesday FC ) | Andy | Daykin | Telephone | Sent Andy a brief note on the Soccerex networking tool to let him know we'll be there. Will also drop him a call and email early next week if I don't get a response. PP | 26/08/2015 17:39 | pphilpott | NULL | Friendly | NULL | NULL |
| [dbo].[CustomerView] | |||||||||||||||||||||||||
| CustomerID | CustomerGUID | CustomerName | IsSameAsACSName | PostalName | CustomerSource | Division | Broker | PrimaryBrokerContact | BrokerCode | AddressLines | Address | Town | County | State | Postcode | Country | Website | DefaultInvoicingContact | BusinessType | EstimatedChartersPerYear | IsVATRegistered | VATNumber | IBOS_NT_UserName | OfficeLocation | CreatedAt |
| 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | SWFC ( Sheffield Wednesday FC ) | 0 | NULL | Other | COMMJ | 4672B0B3-C4B4-4B05-9298-8CB4B6D586B8 | 5D66B0E7-8F36-429E-92A8-70E5E4F47FE1 | LONCOMPP | Hillsborough Stadium Leppings Lane | AF516A57-465E-4021-B59A-DC292CB1526F | Sheffield | NULL | NULL | S6 1SW | United Kingdom | http://www.swfc.co.uk | 0 | Sport | 3 | 0 | NULL | NULL | London | 47:43.0 |
| [dbo].[CustomerBrokerView_PMR2] | |||||
| customerBrokerid | customerid | CustomerName | CreatedAt | BrokerCode | AccountOffice |
| 47002475-B3DE-4715-B152-13D89CE1B934 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | SWFC ( Sheffield Wednesday FC ) | 16/05/2016 12:27 | LONCOMWS | London |
| 9BAD3DE0-5124-4852-BD19-993322F663F2 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | SWFC ( Sheffield Wednesday FC ) | 17/04/2012 18:47 | LONCOMSH | London |
| C9565947-AA73-49B3-9CF7-C2218E93B2E3 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | SWFC ( Sheffield Wednesday FC ) | 26/08/2015 17:36 | LONCOMPP | London |
| 1A03E7EB-0DEF-4CE4-B5A9-0187F86E6E58 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | SWFC ( Sheffield Wednesday FC ) | 22/02/2017 14:01 | LONCOMPP | London |
| 977EF938-1014-4ABD-8FB8-D9C145AC0A31 | 8E0CB473-F889-42EC-9646-99E4A6FB0EF3 | SWFC ( Sheffield Wednesday FC ) | 03/10/2012 16:59 | CZ | London |
March 16, 2017 at 10:05 am
Also, does the sample data demonstrate the problem you are experiencing?
If so, what should it return if working correctly?
March 16, 2017 at 10:24 am
chris.asaipillai-624309 - Thursday, March 16, 2017 9:23 AMHi
Im using a view [dbo].[CustomerBrokerView_PMR2] , in orderto do a join and select the top 1 Primary Key
value our of this viewMy code is as follows:
FROM [dbo].[SalesReportView] srv
INNER JOIN dbo.CustomerView cv ON srv.CustomerId = cv.customerid
innerJOIN [dbo].[CustomerBrokerView_PMR2] cb ON cb.customerid = cv.CustomerID AND cb.[AccountOffice] = cv.[OfficeLocation]
AND
cb.[customerBrokerid] =
(
SELECT TOP 1[customerBrokerid]
FROM
[dbo].[CustomerBrokerView_PMR2]CBV
WHERE
CBV.[customerid]= cv.CustomerID
AND
cbv.[AccountOffice] =cv.[OfficeLocation]
AND
cbv.[CreatedAt] <= srv.CreatedAt
ORDER BYcbv.createdat desc
My data in [dbo].[CustomerBrokerView_PMR2] is as follows:
customerBrokerid
customerid
CustomerName
CreatedAt
AccountOffice
C9565947-AA73-49B3-9CF7-C2218E93B2E3
8E0CB473-F889-42EC-9646-99E4A6FB0EF3
SWFC ( Sheffield Wednesday FC )
26/08/2015 17:36
London
1A03E7EB-0DEF-4CE4-B5A9-0187F86E6E58
8E0CB473-F889-42EC-9646-99E4A6FB0EF3
SWFC ( Sheffield Wednesday FC )
22/02/2017 14:01
London
A sorting [dbo].[CustomerBrokerView_PMR2] in date descending order , will yield
the value 22/07/2017 14:01
I know that the value for srv.CreatedAt will return '016-01-21 00:00:00.000'and therefore
I expect no value to be returnedhowever a value is being returned because the inner Top 1 query forSelect Top 1
should give me the value for the 2nd row shown above in Red and whitewhat is going on here and why does sorting a view with a unique identifier giveme the wrong result?
is there something I need to do in the casting in my datatypes in the joins orfields in my view??
No, it won't. The srv.CreatedAt date of 21/01/2016 15:16 will not include the date 22/02/2107 14:01 as it is not less than or equal to 21/01/2016 15:16.
March 16, 2017 at 10:29 am
Lynn Pettis - Thursday, March 16, 2017 10:05 AMAlso, does the sample data demonstrate the problem you are experiencing?If so, what should it return if working correctly?
Hi Lynn
The inner query should return a value for [CreatedAt] from [dbo.CustomerBrokerView_PMR2] of '2017-02-22 14:01:16.783' with ORDER BY cbv.createdAt desc
Now the part of the query
where cbv.CreatedAt = '2017-02-22 14:01:16.783' and srv.CreatedAt = 2016-01-21 00:00:00.000
Means that
cbv.[CreatedAt] <= srv.Created At Returns no records (The Result I want)
However,
ORDER
BY cbv.createdat desc returns a value of '2015-08-26 17:36:55.243' for cbv.CreatedAt
Therefore when cbv.[CreatedAt] <= srv.Created is run
'2015-08-26 17:36:55.243' <= 2016-01-21 00:00:00.000 (Result I don't want)
why does sorting dbo.CustomerBrokerView_PMR2 in my inner query not give me the value for 2017-02-22 14:01:16.783
for the CreatedAt column?
March 16, 2017 at 10:37 am
I think what you're after here is a CROSS APPLY, so you can limit your result that way.
This is untested, however, instead of your INNER JOIN to [CustomerBrokerView] does this work?CROSS APPLY (SELECT TOP 1 *
FROM [dbo].[CustomerBrokerView_PMR2] ca
WHERE ca.customerid = cv.CustomerID
AND ca.[AccountOffice] = cv.[OfficeLocation]
AND ca.[CreatedAt] <= srv.CreatedAt
ORDER BY ca.Createdat DESC) cb
If not, does it get you close, and are you able to amend this to get what you need?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 16, 2017 at 10:50 am
chris.asaipillai-624309 - Thursday, March 16, 2017 10:29 AMLynn Pettis - Thursday, March 16, 2017 10:05 AMAlso, does the sample data demonstrate the problem you are experiencing?If so, what should it return if working correctly?
Hi Lynn
The inner query should return a value for [CreatedAt] from [dbo.CustomerBrokerView_PMR2] of '2017-02-22 14:01:16.783' with ORDER BY cbv.createdAt desc
Now the part of the query
where cbv.CreatedAt = '2017-02-22 14:01:16.783' and srv.CreatedAt = 2016-01-21 00:00:00.000
Means thatcbv.[CreatedAt] <= srv.Created At Returns no records (The Result I want)
However,
ORDER
BY cbv.createdat desc returns a value of '2015-08-26 17:36:55.243' for cbv.CreatedAt
Therefore when cbv.[CreatedAt] <= srv.Created is run
'2015-08-26 17:36:55.243' <= 2016-01-21 00:00:00.000 (Result I don't want)why does sorting dbo.CustomerBrokerView_PMR2 in my inner query not give me the value for 2017-02-22 14:01:16.783
for the CreatedAt column?
This sub-query:
SELECT TOP 1 [customerBrokerid]
FROM
[dbo].[CustomerBrokerView_PMR2] CBV
WHERE
CBV.[customerid] = cv.CustomerID
AND
cbv.[AccountOffice] = cv.[OfficeLocation]
AND
cbv.[CreatedAt] <= srv.CreatedAt
ORDER BY
cbv.createdat desc
Filters the data down to all the data that has a CreatedAt date that is less than or equal to 21/01/2016 15:16 which is the CreatedAt date from the outer query. Sorting this data by CreatedAt in descending order
puts the row of data with the date 26/08/2015 17:36 at the top which is what is returned by the TOP 1.
March 16, 2017 at 10:55 am
Lynn Pettis - Thursday, March 16, 2017 10:50 AMchris.asaipillai-624309 - Thursday, March 16, 2017 10:29 AMLynn Pettis - Thursday, March 16, 2017 10:05 AMAlso, does the sample data demonstrate the problem you are experiencing?If so, what should it return if working correctly?
Hi Lynn
The inner query should return a value for [CreatedAt] from [dbo.CustomerBrokerView_PMR2] of '2017-02-22 14:01:16.783' with ORDER BY cbv.createdAt desc
Now the part of the query
where cbv.CreatedAt = '2017-02-22 14:01:16.783' and srv.CreatedAt = 2016-01-21 00:00:00.000
Means thatcbv.[CreatedAt] <= srv.Created At Returns no records (The Result I want)
However,
ORDER
BY cbv.createdat desc returns a value of '2015-08-26 17:36:55.243' for cbv.CreatedAt
Therefore when cbv.[CreatedAt] <= srv.Created is run
'2015-08-26 17:36:55.243' <= 2016-01-21 00:00:00.000 (Result I don't want)why does sorting dbo.CustomerBrokerView_PMR2 in my inner query not give me the value for 2017-02-22 14:01:16.783
for the CreatedAt column?This sub-query:
SELECT TOP 1 [customerBrokerid]
FROM
[dbo].[CustomerBrokerView_PMR2] CBV
WHERE
CBV.[customerid] = cv.CustomerID
AND
cbv.[AccountOffice] = cv.[OfficeLocation]
AND
cbv.[CreatedAt] <= srv.CreatedAt
ORDER BY
cbv.createdat desc
Filters the data down to all the data that has a CreatedAt date that is less than or equal to 21/01/2016 15:16 which is the CreatedAt date from the outer query. Sorting this data by CreatedAt in descending order
puts the row of data with the date 26/08/2015 17:36 at the top which is what is returned by the TOP 1.
Hi Lynn
Ok is there a way that I can force the value of 22/02/2017 14:01 to the top?
March 16, 2017 at 11:00 am
Thom A - Thursday, March 16, 2017 10:37 AMI think what you're after here is a CROSS APPLY, so you can limit your result that way.This is untested, however, instead of your INNER JOIN to [CustomerBrokerView] does this work?
CROSS APPLY (SELECT TOP 1 *
FROM [dbo].[CustomerBrokerView_PMR2] ca
WHERE ca.customerid = cv.CustomerID
AND ca.[AccountOffice] = cv.[OfficeLocation]
AND ca.[CreatedAt] <= srv.CreatedAt
ORDER BY ca.Createdat DESC) cb
If not, does it get you close, and are you able to amend this to get what you need?
Hi
That didn't work for me im afraid. I got multiple records back
March 16, 2017 at 11:12 am
chris.asaipillai-624309 - Thursday, March 16, 2017 10:55 AMLynn Pettis - Thursday, March 16, 2017 10:50 AMchris.asaipillai-624309 - Thursday, March 16, 2017 10:29 AMLynn Pettis - Thursday, March 16, 2017 10:05 AMAlso, does the sample data demonstrate the problem you are experiencing?If so, what should it return if working correctly?
Hi Lynn
The inner query should return a value for [CreatedAt] from [dbo.CustomerBrokerView_PMR2] of '2017-02-22 14:01:16.783' with ORDER BY cbv.createdAt desc
Now the part of the query
where cbv.CreatedAt = '2017-02-22 14:01:16.783' and srv.CreatedAt = 2016-01-21 00:00:00.000
Means thatcbv.[CreatedAt] <= srv.Created At Returns no records (The Result I want)
However,
ORDER
BY cbv.createdat desc returns a value of '2015-08-26 17:36:55.243' for cbv.CreatedAt
Therefore when cbv.[CreatedAt] <= srv.Created is run
'2015-08-26 17:36:55.243' <= 2016-01-21 00:00:00.000 (Result I don't want)why does sorting dbo.CustomerBrokerView_PMR2 in my inner query not give me the value for 2017-02-22 14:01:16.783
for the CreatedAt column?This sub-query:
SELECT TOP 1 [customerBrokerid]
FROM
[dbo].[CustomerBrokerView_PMR2] CBV
WHERE
CBV.[customerid] = cv.CustomerID
AND
cbv.[AccountOffice] = cv.[OfficeLocation]
AND
cbv.[CreatedAt] <= srv.CreatedAt
ORDER BY
cbv.createdat desc
Filters the data down to all the data that has a CreatedAt date that is less than or equal to 21/01/2016 15:16 which is the CreatedAt date from the outer query. Sorting this data by CreatedAt in descending order
puts the row of data with the date 26/08/2015 17:36 at the top which is what is returned by the TOP 1.Hi Lynn
Ok is there a way that I can force the value of 22/02/2017 14:01 to the top?
Change or remove this part of the sub-query, AND cbv.[CreatedAt] <= srv.CreatedAt, but it is obviously there for a reason.
Or, change the criteria for the main query to change the date range being queried which based on your post is srv.CreatedAt >= '2016-01-01' and srv.CreatedAt < '2016-02-01'.
Your query actually uses YEAR(srv.CreatedAt) = 2016 and MONTH(srv.CreatedAt) = 1, all of which is non-sargable and won't use an index if available.
March 16, 2017 at 11:27 am
Lynn Pettis - Thursday, March 16, 2017 11:12 AMchris.asaipillai-624309 - Thursday, March 16, 2017 10:55 AMLynn Pettis - Thursday, March 16, 2017 10:50 AMchris.asaipillai-624309 - Thursday, March 16, 2017 10:29 AMLynn Pettis - Thursday, March 16, 2017 10:05 AMAlso, does the sample data demonstrate the problem you are experiencing?If so, what should it return if working correctly?
Hi Lynn
The inner query should return a value for [CreatedAt] from [dbo.CustomerBrokerView_PMR2] of '2017-02-22 14:01:16.783' with ORDER BY cbv.createdAt desc
Now the part of the query
where cbv.CreatedAt = '2017-02-22 14:01:16.783' and srv.CreatedAt = 2016-01-21 00:00:00.000
Means thatcbv.[CreatedAt] <= srv.Created At Returns no records (The Result I want)
However,
ORDER
BY cbv.createdat desc returns a value of '2015-08-26 17:36:55.243' for cbv.CreatedAt
Therefore when cbv.[CreatedAt] <= srv.Created is run
'2015-08-26 17:36:55.243' <= 2016-01-21 00:00:00.000 (Result I don't want)why does sorting dbo.CustomerBrokerView_PMR2 in my inner query not give me the value for 2017-02-22 14:01:16.783
for the CreatedAt column?This sub-query:
SELECT TOP 1 [customerBrokerid]
FROM
[dbo].[CustomerBrokerView_PMR2] CBV
WHERE
CBV.[customerid] = cv.CustomerID
AND
cbv.[AccountOffice] = cv.[OfficeLocation]
AND
cbv.[CreatedAt] <= srv.CreatedAt
ORDER BY
cbv.createdat desc
Filters the data down to all the data that has a CreatedAt date that is less than or equal to 21/01/2016 15:16 which is the CreatedAt date from the outer query. Sorting this data by CreatedAt in descending order
puts the row of data with the date 26/08/2015 17:36 at the top which is what is returned by the TOP 1.Hi Lynn
Ok is there a way that I can force the value of 22/02/2017 14:01 to the top?
Change or remove this part of the sub-query, AND cbv.[CreatedAt] <= srv.CreatedAt, but it is obviously there for a reason.
Or, change the criteria for the main query to change the date range being queried which based on your post is srv.CreatedAt >= '2016-01-01' and srv.CreatedAt < '2016-02-01'.
Your query actually uses YEAR(srv.CreatedAt) = 2016 and MONTH(srv.CreatedAt) = 1, all of which is non-sargable ant won't use an index if available.
Hi Lynn
cbv.CreatedAt <= srv.CreatedAt in the query .
I also changed the criteria for the query for
srv.CreatedAt >= '2016-01-01' and srv.CreatedAt < '2016-02-01'
But still get cbv_effectiveDate = 2015-08-26 17:36:55.243 and salesReportDate = 2016-01-21 00:00:00.000
March 16, 2017 at 11:40 am
chris.asaipillai-624309 - Thursday, March 16, 2017 11:27 AMLynn Pettis - Thursday, March 16, 2017 11:12 AMchris.asaipillai-624309 - Thursday, March 16, 2017 10:55 AMLynn Pettis - Thursday, March 16, 2017 10:50 AMchris.asaipillai-624309 - Thursday, March 16, 2017 10:29 AMLynn Pettis - Thursday, March 16, 2017 10:05 AMAlso, does the sample data demonstrate the problem you are experiencing?If so, what should it return if working correctly?
Hi Lynn
The inner query should return a value for [CreatedAt] from [dbo.CustomerBrokerView_PMR2] of '2017-02-22 14:01:16.783' with ORDER BY cbv.createdAt desc
Now the part of the query
where cbv.CreatedAt = '2017-02-22 14:01:16.783' and srv.CreatedAt = 2016-01-21 00:00:00.000
Means thatcbv.[CreatedAt] <= srv.Created At Returns no records (The Result I want)
However,
ORDER
BY cbv.createdat desc returns a value of '2015-08-26 17:36:55.243' for cbv.CreatedAt
Therefore when cbv.[CreatedAt] <= srv.Created is run
'2015-08-26 17:36:55.243' <= 2016-01-21 00:00:00.000 (Result I don't want)why does sorting dbo.CustomerBrokerView_PMR2 in my inner query not give me the value for 2017-02-22 14:01:16.783
for the CreatedAt column?This sub-query:
SELECT TOP 1 [customerBrokerid]
FROM
[dbo].[CustomerBrokerView_PMR2] CBV
WHERE
CBV.[customerid] = cv.CustomerID
AND
cbv.[AccountOffice] = cv.[OfficeLocation]
AND
cbv.[CreatedAt] <= srv.CreatedAt
ORDER BY
cbv.createdat desc
Filters the data down to all the data that has a CreatedAt date that is less than or equal to 21/01/2016 15:16 which is the CreatedAt date from the outer query. Sorting this data by CreatedAt in descending order
puts the row of data with the date 26/08/2015 17:36 at the top which is what is returned by the TOP 1.Hi Lynn
Ok is there a way that I can force the value of 22/02/2017 14:01 to the top?
Change or remove this part of the sub-query, AND cbv.[CreatedAt] <= srv.CreatedAt, but it is obviously there for a reason.
Or, change the criteria for the main query to change the date range being queried which based on your post is srv.CreatedAt >= '2016-01-01' and srv.CreatedAt < '2016-02-01'.
Your query actually uses YEAR(srv.CreatedAt) = 2016 and MONTH(srv.CreatedAt) = 1, all of which is non-sargable ant won't use an index if available.Hi Lynn
cbv.CreatedAt <= srv.CreatedAt in the query .
I also changed the criteria for the query forsrv.CreatedAt >= '2016-01-01' and srv.CreatedAt < '2016-02-01'
But still get cbv_effectiveDate = 2015-08-26 17:36:55.243 and salesReportDate = 2016-01-21 00:00:00.000
So your query is still going to return the data for the row with this ID: 7754C0B8-5B3D-4C0A-80C9-3AFAF2853C58, which will still return this date/time: 21/01/2016 15:16, which will still limit the data returned by the
sub-query to all dates less than or equal to that date which will exclude the date you want: 22/02/2017 14:50.
I don't know what to tell you to get what you want.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply