Testing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Testing

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-03-29 : 11:02:26

During testing, across many DBs, I want to avoid playing with parameters to get reasonable spreads of activity, where a DB could have action over a week in 1950, or nothing until yesterday, or some bits & bobs here & there...

Using the core of the FROM clause, and getting just a date and count from that, and not wanting to manually set a having count > 60, I creamed the top row, and will set the focus from date_from and date_to parameters around that date, by about a month, or more, and I'll eventually apply this to each stored procedure individually. What's the best way to use this outcome, be this a date, or sales volume? I'm thinking of putting this into #peak_orderdate

How do I do that within an existing stored procedure? Temp table, or set the @Date_From and @Date_To, ignoring input from outside..?

It's simple, but I could use advice...

select top 1 orderdate from
(select
orderdate
,count(*) as count

FROM PatientsWithDatesRpt PV
INNER JOIN
Orders O
ON O.patient = PV.patuId
INNER JOIN
OrdersWithDatesRpt OV
ON OV.orduId = O.uId
INNER JOIN
LimbTypes LT
ON LT.uId = O.limbType
INNER JOIN
LimbTypeCodes LTC
ON LTC.uId = LT.limbTypeCode
LEFT OUTER JOIN
Amputations A
ON A.uId = O.amputation
INNER JOIN
AmpTypes ATY
ON ATY.uId = A.ampType
INNER JOIN
AmpLevels AL
ON AL.uId = ATY.ampLevel

GROUP BY orderdate) t
ORDER BY count desc

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-29 : 19:12:20

I'm not clear on what you are trying to accomplish. Are you trying to set the stored procedure's parameter's test values from within the stored procedure?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-03-30 : 04:19:00

Tooo simple to dictate the limits manually. Results could be empty. I want to group on order_date and see where the peak is, as I said - the majority per month - this month, or 10 years ago... Then set the date range around that month. This is for testing - to ensure we see a relevant date with the most results. Once the report is meaningful, I'd then leave the parameters for the person running the report to choose

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-30 : 18:31:18

GROUP BY
DateAdd(month, DateDiff(month, 0, orderdate), 0)
ORDER BY
count DESC

???

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources