I struggle with where clause and dates hoping for some help
-
this is my from where SQL statement
FROM [HYP_FDMEE].[dbo].[TDATASEG] with (NoLock) LEFT JOIN [blackline].[dbo].[cimentity] ON [HYP_FDMEE].[dbo].[tdataseg].[ENTITYX] = [blackline].[dbo].[cimentity].[HFMEntityID]
WHERE PARTITIONKEY=23
--AND LEFT(PeriodKey,10) = '2018-02-28'
this is a picture of the TDATASEG table
So this will be run each month, around the 7th of the month for the PRIOR month. so on April 7th, 2018 I need March 2018 data. May 7, 2018 I need April 2018 data. and yes the date is always as it shows in the picture.
I have no idea what the statement should be. any help is appreciated.
- randyetheridge - Friday, March 16, 2018 3:43 PM
this is my from where SQL statement
FROM [HYP_FDMEE].[dbo].[TDATASEG] with (NoLock) LEFT JOIN [blackline].[dbo].[cimentity] ON [HYP_FDMEE].[dbo].[tdataseg].[ENTITYX] = [blackline].[dbo].[cimentity].[HFMEntityID]
WHERE PARTITIONKEY=23
--AND LEFT(PeriodKey,10) = '2018-02-28'
this is a picture of the TDATASEG table
So this will be run each month, around the 7th of the month for the PRIOR month. so on April 7th, 2018 I need March 2018 data. May 7, 2018 I need April 2018 data. and yes the date is always as it shows in the picture.
I have no idea what the statement should be. any help is appreciated.
Well, I can't get the image to expand so I have no idea what is shown. I have taken the time to slightly rewrite your FROM and WHERE clauses:
FROM
[HYP_FDMEE].[dbo].[TDATASEG] AS [tds] --WITH (NoLock) drop the nolock hint
LEFT JOIN [blackline].[dbo].[cimentity] AS [ce]
ON [tds].[ENTITYX] = [ce].[HFMEntityID]
WHERE
[?].[PARTITIONKEY] = 23 -- What table does this column belong to?
AND [?].[PeriodKey] >= DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(DAY,-6,GETDATE())) - 1,0) -- what table does the PeriodKey belong?
AND [?].[PeriodKey] < DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(DAY,-6,GETDATE())),0); - randyetheridge - Friday, March 16, 2018 3:43 PM
this is my from where SQL statement
FROM [HYP_FDMEE].[dbo].[TDATASEG] with (NoLock) LEFT JOIN [blackline].[dbo].[cimentity] ON [HYP_FDMEE].[dbo].[tdataseg].[ENTITYX] = [blackline].[dbo].[cimentity].[HFMEntityID]
WHERE PARTITIONKEY=23
--AND LEFT(PeriodKey,10) = '2018-02-28'
this is a picture of the TDATASEG table
So this will be run each month, around the 7th of the month for the PRIOR month. so on April 7th, 2018 I need March 2018 data. May 7, 2018 I need April 2018 data. and yes the date is always as it shows in the picture.
I have no idea what the statement should be. any help is appreciated.
What is the datatype of the PeriodKey column? DATETIME or ???
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
partitionkey is a field in tdataseg
picture below of table tdataseg - randyetheridge - Friday, March 16, 2018 4:11 PMpartitionkey is a field in tdataseg
picture below of table tdatasegOkay, so the [?] in my code becomes [tds] in my code.
- randyetheridge - Friday, March 16, 2018 4:11 PMpartitionkey is a field in tdataseg
picture below of table tdatasegI was looking for the PeriodKey datatype. Not the PartitionKey datatype. Fortunately, PeriodKey was included in your graphic above and it IS a DATETIME datatype.
Based on that, it looks like Lynn's code will do the trick for you (although I've not done a deep dive on it).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thanks for all the help. and yes the code above worked perfectly. I really struggle with date formatting so this was a big help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply