September 26, 2018 at 9:02 am
Ok, now i need to pull a specific number of entries from a table based on the following code. i can't figure out how to get the count from #Temp1 into the "X" on row one.
Thoughts?
SELECT TOP X
Entry_Num
FROM
ADHOC.ATS_ESH
WHERE
Entry_Summary_Date >= '8/1/2018'
AND Entry_Summary_Date <= '9/1/2018'
AND ADHOC.ATS_ESH.Importer = 'XXX'
AND Entered_Value > 70000
SELECT * into #TEMP1
FROM
(
SELECT
COUNT(*) * .0025 AS Entry_Count -----This is my X for row 1
FROM
ADHOC.ATS_ESH
WHERE
Entry_Summary_Date >= '8/1/2018'
AND Entry_Summary_Date <= '9/1/2018'
AND ADHOC.ATS_ESH.Importer = 'XXX'
) d
September 26, 2018 at 9:07 am
jeffshelix - Wednesday, September 26, 2018 9:02 AMOk, now i need to pull a specific number of entries from a table based on the following code. i can't figure out how to get the count from #Temp1 into the "X" on row one.
Thoughts?
SELECT TOP X
Entry_Num
FROM
ADHOC.ATS_ESH
WHERE
Entry_Summary_Date >= '8/1/2018'
AND Entry_Summary_Date <= '9/1/2018'
AND ADHOC.ATS_ESH.Importer = 'XXX'
AND Entered_Value > 70000
SELECT * into #TEMP1
FROM
(
SELECT
COUNT(*) * .0025 AS Entry_Count -----This is my X for row 1
FROM
ADHOC.ATS_ESH
WHERE
Entry_Summary_Date >= '8/1/2018'
AND Entry_Summary_Date <= '9/1/2018'
AND ADHOC.ATS_ESH.Importer = 'XXX'
) d
I am confused. How many rows of data are you trying to pull?
September 26, 2018 at 9:40 am
jeffshelix - Wednesday, September 26, 2018 9:02 AMOk, now i need to pull a specific number of entries from a table based on the following code. i can't figure out how to get the count from #Temp1 into the "X" on row one.
Thoughts?
SELECT TOP X
Entry_Num
FROM
ADHOC.ATS_ESH
WHERE
Entry_Summary_Date >= '8/1/2018'
AND Entry_Summary_Date <= '9/1/2018'
AND ADHOC.ATS_ESH.Importer = 'XXX'
AND Entered_Value > 70000
SELECT * into #TEMP1
FROM
(
SELECT
COUNT(*) * .0025 AS Entry_Count -----This is my X for row 1
FROM
ADHOC.ATS_ESH
WHERE
Entry_Summary_Date >= '8/1/2018'
AND Entry_Summary_Date <= '9/1/2018'
AND ADHOC.ATS_ESH.Importer = 'XXX'
) d
First, you can't use the results of something that you haven't calculated, yet. You are trying to use the results of your second select statement in your first select statement.
Second, it's a bad idea to use TOP without an ORDER BY clause.
Finally, there is no reason to read the same table twice here. The following will give you what I think you are looking for. (It would be simpler if you were using the same criteria in both sets of your data). I've also set it up so that it will allow an unpredictable sampling of your data.
WITH CTE AS
(
SELECT TOP 0.25 PERCENT
Entry_Num
FROM ADHOC.ATS_ESH
WHERE Entry_Summary_Date >= '8/1/2018'
AND Entry_Summary_Date <= '9/1/2018'
AND ADHOC.ATS_ESH.Importer = 'XXX'
ORDER BY CASE WHEN Entered_Value > 70000 THEN 1 ELSE 2 END
)
SELECT *
FROM CTE
WHERE Entered_Value > 70000
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply