Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:
> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>sql
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Friday, March 30, 2012
Need Help Coalescing
Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,Dave
Hi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:
> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,Dave
Hi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:
> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>
Need Help Coalescing
Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:
> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:
> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>
Monday, March 26, 2012
Need for Distributor server in existing Replication Setup
We have a setup with one Publisher Server and 15 Subscribers with merge replication configured for all subscribers. The current Database size is approximately 3 GB expected to grow to 10 GB in next 1 year. We wanted to know what benefits would we incur if we add an additional seperate distributor server (hardware box). Also, what is the approx. size of a database where a seperate distributor server (hardware box) is recommended.
Thanks in advanceRead up on replication in SQL Books Online (provided with SQL Server). It covers a lot of the trade-offs in detail. Without knowing a LOT more about your configuration, plans, etc. I don't know how to give you a simple answer.
Typically, when you have one publisher with only 15 subscribers, the load isn't too heavy unless the publisher is stressed. The primary advantage comes from improved security, better network performance, and better managability. As your system grows (more data and more subscribers), then the performance benefits will start to come into play.
Again, without a lot more understanding of what you are doing now and what you plan to do in the near future, I really can't give you a straightforward answer.
-PatP|||Sometimes you make me crack up, I swear. ..."only 15 subscribers"... You'll see an immediate benefit even if you have just 1 subscriber...man, how many of those have you set up?|||Nice to know that you find me good for something!
I've set up about six production replication systems, a few hundred training systems, and I'm not really sure how many test systems. As I said in my first post, without knowing a lot more about what the poster wants and what they've actually got, any observation I can offer is only in very general terms.
Based on my experience, if you set up at least one NIC for replication on a relatively beefy box, it can handle a reasonable user load along with the replication... If you try to skimp on bandwidth, RAM, or processing power, you can certainly swamp any box.
It seems obvious to me that having a separate distributor is better than having one box do both, so on that point we certainly agree. I guess the question is where do you see the performance "knee" due to the additional load imposed by distribution. Without knowing a lot more, I don't see how you can offer much insight there.
-PatP|||Hey, I just felt that having 15 subscribers is enough to predict that a dedicated distributor is well due, that's all ;)
Thanks in advanceRead up on replication in SQL Books Online (provided with SQL Server). It covers a lot of the trade-offs in detail. Without knowing a LOT more about your configuration, plans, etc. I don't know how to give you a simple answer.
Typically, when you have one publisher with only 15 subscribers, the load isn't too heavy unless the publisher is stressed. The primary advantage comes from improved security, better network performance, and better managability. As your system grows (more data and more subscribers), then the performance benefits will start to come into play.
Again, without a lot more understanding of what you are doing now and what you plan to do in the near future, I really can't give you a straightforward answer.
-PatP|||Sometimes you make me crack up, I swear. ..."only 15 subscribers"... You'll see an immediate benefit even if you have just 1 subscriber...man, how many of those have you set up?|||Nice to know that you find me good for something!
I've set up about six production replication systems, a few hundred training systems, and I'm not really sure how many test systems. As I said in my first post, without knowing a lot more about what the poster wants and what they've actually got, any observation I can offer is only in very general terms.
Based on my experience, if you set up at least one NIC for replication on a relatively beefy box, it can handle a reasonable user load along with the replication... If you try to skimp on bandwidth, RAM, or processing power, you can certainly swamp any box.
It seems obvious to me that having a separate distributor is better than having one box do both, so on that point we certainly agree. I guess the question is where do you see the performance "knee" due to the additional load imposed by distribution. Without knowing a lot more, I don't see how you can offer much insight there.
-PatP|||Hey, I just felt that having 15 subscribers is enough to predict that a dedicated distributor is well due, that's all ;)
Need Expression Help with DateTime Variable
Hello,
I have a DateTime variable called CurrentDate that needs to reflect the current date, but the date portion only. I checked several functions in the Expression Builder to use with GETDATE() so that I could just get the date portion, but I didn't see anything that really fit. In a SQL query I would normally use CONVERT to do this.
Any ideas?
Thank you for your help!
cdun2
Why do you care if it has a time portion on it?How are you populating the variable? It'll have to be a string type in order to eliminate the time component.|||
Sorry for the confusion. Actually, the date does need to be cast to a string. I need to append the date to another package variable to complete the 'name' of a flat file. The idea is that when the package runs, the flat file destination file name will contain the current date.
I know how to set up an exression for the 'Name' of the flat file, but what would be the best way to take the result of GETDATE(), cast it to a string, and parse out just the date portion? Since I don't want the non alpha numeric characters in the string, maybe I should just put a date string together using YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE()).
If I go this route, how do I used type casting with these date functions?
Thanks again.
|||
Here is my first idea. This would be the expression for the Name property of the flat file Connection Manager;
@.[User::FilePath] + @.[User::FilePrefix] + (DT_STR, 4, 1252) YEAR(GETDATE()) + (DT_STR,2, 1252)MONTH(GETDATE()) + (DT_STR, 2,1252)DAY(GETDATE())
|||
cdun2 wrote:
Here is my first idea. This would be the expression for the Name property of the flat file Connection Manager;
@.[User::FilePath] + @.[User::FilePrefix] + (DT_STR, 4, 1252) YEAR(GETDATE()) + (DT_STR,2, 1252)MONTH(GETDATE()) + (DT_STR, 2,1252)DAY(GETDATE())
I like this idea.
Wednesday, March 21, 2012
Need current date in ssrs expression
I have a report which has a date like 01/05/2007 for a bill date. I need to create and expression which will take the current date and subtract the number of days from todays date and give a total number of days. How do I get thew current date in the expression.
the billed date expression is =FormatDateTime(Today()) - FormatDateTime(Fields!BILLDATE.value,2) I can not get this to work
Any help would be appreciated
You can execute code within a Report. You may need to do this although I think the reporting expressions should support what you are trying to accomplish. This is a good resource for you anyway though:http://support.microsoft.com/kb/920769
Hope this helps!
Matt
|||Thanks for the link but my expression isnt working in the report can you tell me what is wrong with it.
|||HI,cpowers:
You can try this expression:
= DateDiff("d",Fields!ShippedDate.Value ,Today() )
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
|||Rex Lin,
That works great thanks.. What I am tring to accomplish is a 1-30 31-60 61-90 days aging
I tried to di the following expression-
=DateDiff("d",Fields!ShippedDate.Value,Today()) < 30 for the 1-30 day
=DateDiff('d".Fields!ShippedDate.Value, Today()) >30<61 for the 31-60 day I am getting a true or false instead of the days is there away to modify the expression to give only the number of days that fall within the range
|||
HI,cpowers:
Hope this will meet your requirement:
=iif( DateDiff("d",Fields!ShippedDate.Value,Today())<30,DateDiff("d",Fields!ShippedDate.Value,Today()),"out of range")
|||I am tring to get a total for the amount owed that is over 30days but under 60days. My expression just gives me the word True does not give me the total MASRCVAMOUNT $$ in that date range. Can come one explain or help with expression...=FormatCurrency(Sum(Fields!MASRCVAMOUNT.Value))=DateDiff("d",Fields!Expr1.Value ,Today())>31<60
I also need to no how to get total MASRCVAMOUNT over 120 days
=iif( DateDiff("d",Fields!ShippedDate.Value,Today())<30,DateDiff("d",Fields!ShippedDate.Value,Today()),"out of range") How would I sum the MASCVAMOUNT in this date range and how can I select a between 61-91days these are my two real issues. I have the dates working.
Thanks for all the help I do appreciate it
|||HI，cpowers：
It seems that the goal you need to achieve is not easily for expression. How about deal with it in the sql statement?
|||Thanks for the help Rex Lin,
Ok I added the daging date to my sql script. Every thing saves ok and in my parameters there is now an new entry filter. Before I only had a StartDate, Enddate and Company. Now I have the Startdate , Enddate Company and dAging. I have tried to enter a date in to the dAging but it keeps giving me and error.This script was added in the creation of the ssrs report in the ssrs query builder. Can you explain to me what I may be missing or need to do to get my 1-30 , 31 60, 61-90 and 91 and over amounts.... The field I am collecting the amounts from is MASRECEIVL.MASRCVAMOUNT I am really stuck with this and need any help availible. Thanks
SELECT ' Language Detail by Company' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,PAYER.PAY_GROUPNAME,PAYER.PAY_COMPANY,PAYER.PAY_CITY,PAYER.PAY_STATE,PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,JOB.PATIENTID,JOB.JOBOUTCOMEID,JOB.SERVICEOUTCOME,INVOICE_AR.INVOICE_NO,INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,INVOICE_AR.CLAIMNUMBER,PATIENT.LASTNAME,PATIENT.FIRSTNAME,PATIENT.EMPLOYERNAME,JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,PAT_SERVICES_HISTORY.LANG_TYPE,MASRECEIVL.MASRVDATE,MASRECEIVL.MASRCVAMOUNT,REFERRAL_SOURCE.REF_LASTNAME,
REFERRAL_SOURCE.REF_FIRSTNAME,REFERRAL_SOURCE.REF_PHONE,REFERRAL_SOURCE.REF_PHONE_EXT,REFERRAL_SOURCE.REF_FAX,REFERRAL_SOURCE.REF_EMAIL,JOB.INJURYDATE,JOB.APPT_DATE,
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') THEN 1 ELSE 0 END) AS 'CompletedWithcomplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') THEN 1 ELSE 0 END) AS 'CompletedSuccessfullyItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') THEN 1 ELSE 0 END) AS 'CompletedwithNoChargeItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') THEN 1 ELSE 0 END) AS 'CompletedwithNoShowItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') THEN 1 ELSE 0 END) AS 'CompletedWithSituationItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Not Completed') THEN 1 ELSE 0 END) AS 'NotCompletedItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') THEN 1 ELSE 0 END) AS 'CancelledPriortoserviceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') THEN 1 ELSE 0 END) AS 'CancelledDuringServiceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Awaiting for completion') THEN 1 ELSE 0 END) AS 'AwaitingforcpmpletionItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Pending for review') THEN 1 ELSE 0 END) AS 'PendingforreviewItems',
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-30 and @.dAgingDate THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age30,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-60 and @.dAgingDate-31 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age60,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-90 and @.dAgingDate-61 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age90,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-120 and @.dAgingDate-91 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age120
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN MASRECEIVL ON MASRECEIVL.INVOICE_NO = INVOICE_AR.INVOICE_NO
LEFT OUTER JOIN REFERRAL_SOURCE ON REFERRAL_SOURCE.REFERRAL_ID = JOB.ADJUSTERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE
(INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_Company like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%L') AND (MASRECEIVL.MASRCVAMOUNT > 0)
Group By
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.LANG_TYPE,
MASRECEIVL.MASRVDATE,
MASRECEIVL.MASRCVAMOUNT,
REFERRAL_SOURCE.REF_LASTNAME,
REFERRAL_SOURCE.REF_FIRSTNAME,
REFERRAL_SOURCE.REF_PHONE,
REFERRAL_SOURCE.REF_PHONE_EXT,
REFERRAL_SOURCE.REF_FAX,
REFERRAL_SOURCE.REF_EMAIL,
JOB.INJURYDATE,
JOB.APPT_DATE
Order By 'QTR' asc
|||HI,cpowers:
Your sql statement is quite complicated than i expected. I have created a simple one by using Orders table in the Northwind. It brings out the sum value of 30-60 and 60-90 date intervals
select
sum(case when timespan between 0 and 29 then shipvia end )as zeroToThirty,
sum(case when timespan between 30 and 59 then shipvia end )as ThirtyToSixty
from
(select (datediff(dd,getdate(),orderdate)+4000)as timespan,*
from dbo.orders) as newtable
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
|||Rex Lin,
I am having similar problems, except all I want is to substract current date from the promised date (date that is was suppose to ship) and be able to view only the negetive data. I tried =GETDATE()-Fields!PROMISE.Value
but it would give me an error, then I tried the format you gave cpowers
=DateDiff("d",fields!Promise.Value, Today()) but all it did was give me one number. Am I doing it wrong? What do you recomend? Please advice
Thanks
Monday, March 19, 2012
Need alernative to cursor
Hey guys and gals - here's my current code
DECLARE @.uID varchar(12)
DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
FROM gvAbsence
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.uID
WHILE @.@.FETCH_STATUS = 0 BEGIN
UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6) ELSE Floor(Rand() * 5) END
WHERE unique_identifier = @.uID
FETCH NEXT FROM MyCursor
INTO @.uID
END
CLOSE MyCursor
DEALLOCATE MyCursor
SELECT *
FROM gvAbsence
Which as you can see, sucks.
It cursors through nearly 4K records assigning a new random number (0-5) to an employees holiday_brought_forward. Naturally, this is an absolute ballache... I just can't think of a better solution at current so any advice you can give would be greatly appreciated :cool:
To put it simply, I want to assign a random number between 0 and 5 (inclusive) to each record in the table gvAbsence
-GeorgeWhich bit you stuck with? Version of SS?
ss5k method of getting a random number between 0 and 5. Not sure if it is the best way of course. With a suitable bit of SQLery I think you can get a random number per person.
select *
from
(select number
, rn = ROW_NUMBER() OVER (ORDER BY newid())
from dbo.numbers
where number between 1 and 5) AS der_t
where rn = 1
Did you try googling?|||SS 2K.
And the above code works how I want it to, but I was wondering if there was a better alternative to using a cursor?
--Return random number between 0 and 5 inc
SELECT Floor(Rand() * 6)
(Can Rand() return 1?)
I've just progressed on with my problem with the cursor and all is well, apart from it taking a full minute to cursor through and apply other updates etc.|||Define random for me:
Does the code need to generate a different number for a particular member of staf each time it is run? Does it need to generate an even distribution for the 4k records? Basically - do you need a truly random number?|||I am creating a test environment and to replicate the scenario I needed to assign all sorts of different values to certain fields (in this case holiday_bought_forward) to that I could test a bunch of different permeatations of the data.
It does not need to do it every time the code is run, except when I need to reset the data (and during testing the data will need reset a good number of times).
No, does not need to have an even distribution across the 4K records, truly random will do just fine ;)
It is not a high priority issue as it's only for the test environment but speeding things up a bit would be nice :p
I don't like cursors all that much (you guys have always told me not to use them!) so I was looking into what alternatives exist
Thanks Poots|||Does gvAbsence have a primary key? If so, extract them into a 2 column table, and when you extract them use the RAND function to generate your random value.
Since the RAND function returns a random float value between 0 and one, you will have to multiply by 5 and take the integer value (lookup FLOOR in BOL)
Hopefully this will get you started.
EDIT: Once you have this table, you can update gvAbsence with the random value from the table for a quick reset that will restore identical values each and every time you need a reset.|||My understanding was that rand won't work in a set based query.|||If I was to keep my cursor, I could use something like a counter instead of a random number for consistancy.
Something like
DECLARE @.Counter int
SET @.Counter = 0
UPDATE gvAbsence
SET holiday_brought_forward = @.Counter % 5
WHERE unique_identifier = @.uID
SET @. Counter = Counter + 1
I dunno, I still don't like cursoring...|||Aha! So nothing like random at all!
This is much easier now - we are deteriministic. Your people got a surrogate key (I know you like em ;))? Why not perform mod on that instead of an increment? Easy & set based.|||One issue I do have now is that the holiday_bought_forward has some constraints; after 5 years of service you're entitled to an extra days holiday -this means you can only bring over maximum of 4 days. (basically a total of 30).
I've actually finished my script now, but I'm simply not happy with the cursor. Like I said - this is only test data that I'm creating to replicate live stuff - so it doesn't have to be the same every time - it simply has to contain permeatations of every possibility (which it does).
If anyone's interested in seeing the redicuous script I had to write for this, just let me know :p|||Let's create some test data
select 'unique_identifier'=id, 'years_of_service'=id, 'holiday_brought_forward'=0
into #gvAbsence from sysobjects where id<20
Instead of using the the rand() function that does not work as expected use a numbers table e.g.
select top 6 seqno=identity(int) into #numbers from syscolumns
And instead of hard coding data into your code lets use a holiday_constraints table.
Now if the conditions chance you don't have to update code, just the data in your table.
create table #holiday_constraints (maxdays int, yrlow int, yrhigh int)
insert into #holiday_constraints select
6,0,5 union all select
5,6,999999999
Then the update.
Note: An unnecessary where clause in the sub select to force execution for every row.
update #gvAbsence
SET holiday_brought_forward =
(select top 1 seqno from #numbers c where seqno<=b.maxdays and a.unique_identifier=a.unique_identifier order by newid())
from #gvAbsence a, #holiday_constraints b
where a.years_of_service between b.yrlow and b.yrhigh
select * from #gvAbsence|||Unfortunately I'm using SQL Server 2000; TOP won't work.|||Also, there are more rules involved and the added problem of generating a random number between -5 and a conditional top value
I'm thinking that in this case a cursor might be my only solution.
I apologise, I don't think I made my problem/question very clear from the beginning but thanks everyone for your help! My final mark up of the cursor is below - the really clever line is the SET ;) Lovely formula right thar!
DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
,holiday_entitlement
,holiday_brought_forward
FROM gvAbsence
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf
SET @.Min = -5
WHILE @.@.FETCH_STATUS = 0 BEGIN
SET @.Max = CASE @.Ent + @.Bf
WHEN 30 THEN 0
WHEN 29 THEN 1
WHEN 28 THEN 2
WHEN 27 THEN 3
WHEN 26 THEN 4
WHEN 25 THEN 5 END
UPDATE gvAbsence
SET holiday_bought = Floor(Rand() * (@.Max - @.Min + 1)) + @.Min
WHERE unique_identifier = @.uID
FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf
END
CLOSE MyCursor
DEALLOCATE MyCursor|||Unfortunately I'm using SQL Server 2000; TOP won't work.Howdya figure that?|||I didn't think TOP was implemented in 2000?
Any time I try run a TOP statement in QA I get
SELECT TOP 5 surname
FROM employee
ORDER BY surname ASC
--
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.
Errors!|||From SQL 2K BoL:
Limiting Result Sets Using TOP and PERCENT
The TOP clause limits the number of rows returned in the result set.
TOP n [PERCENT]
n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return:
TOP 120 /*Return the top 120 rows of the result set. */TOP 15 PERCENT /* Return the top 15% of the result set. */.If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.
The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:
The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.The top keyword changed in 2005 only in so far as you can use a variable.|||Then how come I can't execute SELECT TOP statements? :confused:|||Have you checked the compatability level of the database?|||Works for me
USE Northwind
GO
SELECT TOP 5 LastName
FROM employees
ORDER BY LastName ASC
GO|||Ummm...assuming your "algorithym" is correct, why couldn't you just do
USE Northwind
GO
CREATE TABLE myTable99(unique_identifier int, years_of_service int, holiday_brought_forward int)
GO
INSERT INTO myTable99(unique_identifier, years_of_service)
SELECT 1, 1 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 20 UNION ALL
SELECT 4, 30
GO
UPDATE g
SET holiday_brought_forward =
CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6)
ELSE Floor(Rand() * 5) END
FROM myTable99 g
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO|||... because rand() will return the same value for all rows.:)|||Not necessarily...
select case floor (rand()*5)
when 1 then 1
when 2 then 2
when 3 then 3
when 4 then 4
else 5 end
from [table of your choice]
This came up in a very nice SQL puzzle posted here some time ago. For those of you who are interested...
select case floor (rand()*5)
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]
EDIT: Due to the nature of the "oddity", it may not be a truly random sampling. Sounds like it may be random enough for you, though.|||Well pummel my piles with a paddle. What is that about then? The case statement causes the rand to be re-evaluated for each row rather than once for the set? And what's the deal with 'WTF'? I mean - wtf? You got a link?|||Actually, rand() is invoked on each row of the output. This is why inline user defined functions can get you in to piles of problems that will pummel your performance paddle(s) past pertinent perturbation.
Sadly, I do not have a link to the original puzzle. It may have been lost in the troubles that DBForums was going through some time back when posts just up and disappeared. Besides, it will likely be a good exercise for you to figure out what is going on in there.
DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.|||Oh noblet - I forgot about the 0 :rolleyes:
But anyway:
select case 1
when 1 then floor (rand()*5) end
from sys.databases
select case 1
when 2 then NULL else floor (rand()*5) end
fromsys.databases
select case floor (rand()*5)
when -1 then NULL else floor (rand()*5) end
from sys.databases
Coooooooooooooooo.|||DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.Ah jolly good! I was wondering what subtle distinction you were making :)|||tsk tsk tsk. Perhaps I should have taken the 0 into consideration, as well...
select case floor (rand()*5)
when 0 then '0'
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]
Extend as necessary to convince yourself this is not so easy as all that.|||neat trick! this seems to work as well without the need for laying out all the possibilities in the case:
select case when name is not null then rand() else NULL end from sys.objects
all you need is something to check in the when clause that will always be true, but that the optimizer won't optimize away. 1=1 won't work. That is, this is broken:
select case when 1=1 then rand() else NULL end from sys.objects|||UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN
CASE WHEN 1=1 THEN Floor(Rand() * 6) ELSE NULL END
ELSE
CASE WHEN 1=1 THEN Floor(Rand() * 5) ELSE NULL END
END
Does not give the desired result - (same problem as before)
UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*5)
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END
Same goes for this *sigh*
I can understand the use of CASE statements to make sure every row is evaluated individualy - but that doesn't appear to be the case when running the above...
this might sound silly but in other languages you need to use
Randomize
Would that apply here? *shrugs*
thanks for the input so far :)|||Can you create a derived table and update from that rather than directly with the case statement?|||Do you mean something like this?
IF EXISTS (SELECT NULL FROM sysobjects WHERE type='U' AND name='gvVals') BEGIN
DROP TABLE gvVals
END
CREATE TABLE gvVals
(
bought_forward int
)
INSERT INTO gvVals(bought_forward) VALUES(1)
INSERT INTO gvVals(bought_forward) VALUES(2)
INSERT INTO gvVals(bought_forward) VALUES(3)
INSERT INTO gvVals(bought_forward) VALUES(4)
INSERT INTO gvVals(bought_forward) VALUES(5)
INSERT INTO gvVals(bought_forward) VALUES(6)
UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END
FROM gvVals
Holiday Brought Forward
--
5.0
5.0
5.0
5.0
5.0
etc|||Text file containing the cursored SQL uploaded for your amusement / berrating ;)
EDIT: and I guess comments, too|||I could not get different values for rand() using the case statement on SS2K
Generating a random value from newid() seems to work
UPDATE gvAbsence
SET holiday_brought_forward =
right(convert(char(2),(convert(int, (convert(varbinary(1),right(newid(),1))))) ),1)/10.*5+1|||pdreyer - wow, that appears to work (and quickly!)
But I'm afraid I have absolutely no idea what it is doing ;)
/10.*5+1 ?? I removed the point and it didn't work - what's that doing :)
Seems like an aweful lot of converts in there and I can't for the life of me understand the char convert!
Hehe, thanks for that - I'd love to implement it, but only if I can understand it first :D
EDIT: I have removed the +1 as I want to include zero values :) I love the fact it even does halves! I hadn't even considered that before :)
RE-EDIT: Not quite returning the values I need... I need 0-5 inclusive and 0.5's would be lovely (but not essential).
/10.*5 returns 0 - 4.5 inc
/10.*5+1 returns 1 - 5.5 inc|||Do you mean something like this?Nope :)
But it sounds like rand() is working differently in 2k anyway. I don't have any 2k boxes to play with and test so I'll drop out at this point.|||Awww but Pooooooooots!
Thanks for all your prior help bud - it is appreciated :)
and if that's not what you meant... what is! :p|||Did we not cover derived tables in your last thread (or thread but one)? Genuinly can't remember for sure...|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?
And did you see what I did before?
so I'll drop out at this point
I don't know why but I'm finding it harder and harder to ask questions here - easy enough to answer (some) but when I ask them I go into "newb" mode and yeah - it sucks :(|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?
No. But you might be able to do it as a sub select.
Derived table example:
UPDATE gvAbsence
SET holiday_brought_forward = derived_table.random_number
FROM dbo.gvVals
INNER JOIN
(SELECT MyPK
, CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END AS random_number
FROM dbo.gvVals) AS derived_table
ON derived_table.MyPK = gvVals.MyPk
And did you see what I did before?Dunno - what did you do?|||I have absolutely no idea what it is doing
it uses the ASCII code of a character from newid() as a random number (caveat: limited range but OK for your needs)
can't understand the char convertNot needed, should be removed.
what's the point doingIt ensure decimal arithmetic else the integer devide will result in zero
it even does halvesThat was not the intended result. I assumed the target field was an integer, So to get values 0-5 multiply by 6 and convert to int at the end. e.g.
select convert(int,right(ascii(newid()),1)/10.*6) from sysobjects
DECLARE @.uID varchar(12)
DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
FROM gvAbsence
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.uID
WHILE @.@.FETCH_STATUS = 0 BEGIN
UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6) ELSE Floor(Rand() * 5) END
WHERE unique_identifier = @.uID
FETCH NEXT FROM MyCursor
INTO @.uID
END
CLOSE MyCursor
DEALLOCATE MyCursor
SELECT *
FROM gvAbsence
Which as you can see, sucks.
It cursors through nearly 4K records assigning a new random number (0-5) to an employees holiday_brought_forward. Naturally, this is an absolute ballache... I just can't think of a better solution at current so any advice you can give would be greatly appreciated :cool:
To put it simply, I want to assign a random number between 0 and 5 (inclusive) to each record in the table gvAbsence
-GeorgeWhich bit you stuck with? Version of SS?
ss5k method of getting a random number between 0 and 5. Not sure if it is the best way of course. With a suitable bit of SQLery I think you can get a random number per person.
select *
from
(select number
, rn = ROW_NUMBER() OVER (ORDER BY newid())
from dbo.numbers
where number between 1 and 5) AS der_t
where rn = 1
Did you try googling?|||SS 2K.
And the above code works how I want it to, but I was wondering if there was a better alternative to using a cursor?
--Return random number between 0 and 5 inc
SELECT Floor(Rand() * 6)
(Can Rand() return 1?)
I've just progressed on with my problem with the cursor and all is well, apart from it taking a full minute to cursor through and apply other updates etc.|||Define random for me:
Does the code need to generate a different number for a particular member of staf each time it is run? Does it need to generate an even distribution for the 4k records? Basically - do you need a truly random number?|||I am creating a test environment and to replicate the scenario I needed to assign all sorts of different values to certain fields (in this case holiday_bought_forward) to that I could test a bunch of different permeatations of the data.
It does not need to do it every time the code is run, except when I need to reset the data (and during testing the data will need reset a good number of times).
No, does not need to have an even distribution across the 4K records, truly random will do just fine ;)
It is not a high priority issue as it's only for the test environment but speeding things up a bit would be nice :p
I don't like cursors all that much (you guys have always told me not to use them!) so I was looking into what alternatives exist
Thanks Poots|||Does gvAbsence have a primary key? If so, extract them into a 2 column table, and when you extract them use the RAND function to generate your random value.
Since the RAND function returns a random float value between 0 and one, you will have to multiply by 5 and take the integer value (lookup FLOOR in BOL)
Hopefully this will get you started.
EDIT: Once you have this table, you can update gvAbsence with the random value from the table for a quick reset that will restore identical values each and every time you need a reset.|||My understanding was that rand won't work in a set based query.|||If I was to keep my cursor, I could use something like a counter instead of a random number for consistancy.
Something like
DECLARE @.Counter int
SET @.Counter = 0
UPDATE gvAbsence
SET holiday_brought_forward = @.Counter % 5
WHERE unique_identifier = @.uID
SET @. Counter = Counter + 1
I dunno, I still don't like cursoring...|||Aha! So nothing like random at all!
This is much easier now - we are deteriministic. Your people got a surrogate key (I know you like em ;))? Why not perform mod on that instead of an increment? Easy & set based.|||One issue I do have now is that the holiday_bought_forward has some constraints; after 5 years of service you're entitled to an extra days holiday -this means you can only bring over maximum of 4 days. (basically a total of 30).
I've actually finished my script now, but I'm simply not happy with the cursor. Like I said - this is only test data that I'm creating to replicate live stuff - so it doesn't have to be the same every time - it simply has to contain permeatations of every possibility (which it does).
If anyone's interested in seeing the redicuous script I had to write for this, just let me know :p|||Let's create some test data
select 'unique_identifier'=id, 'years_of_service'=id, 'holiday_brought_forward'=0
into #gvAbsence from sysobjects where id<20
Instead of using the the rand() function that does not work as expected use a numbers table e.g.
select top 6 seqno=identity(int) into #numbers from syscolumns
And instead of hard coding data into your code lets use a holiday_constraints table.
Now if the conditions chance you don't have to update code, just the data in your table.
create table #holiday_constraints (maxdays int, yrlow int, yrhigh int)
insert into #holiday_constraints select
6,0,5 union all select
5,6,999999999
Then the update.
Note: An unnecessary where clause in the sub select to force execution for every row.
update #gvAbsence
SET holiday_brought_forward =
(select top 1 seqno from #numbers c where seqno<=b.maxdays and a.unique_identifier=a.unique_identifier order by newid())
from #gvAbsence a, #holiday_constraints b
where a.years_of_service between b.yrlow and b.yrhigh
select * from #gvAbsence|||Unfortunately I'm using SQL Server 2000; TOP won't work.|||Also, there are more rules involved and the added problem of generating a random number between -5 and a conditional top value
I'm thinking that in this case a cursor might be my only solution.
I apologise, I don't think I made my problem/question very clear from the beginning but thanks everyone for your help! My final mark up of the cursor is below - the really clever line is the SET ;) Lovely formula right thar!
DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
,holiday_entitlement
,holiday_brought_forward
FROM gvAbsence
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf
SET @.Min = -5
WHILE @.@.FETCH_STATUS = 0 BEGIN
SET @.Max = CASE @.Ent + @.Bf
WHEN 30 THEN 0
WHEN 29 THEN 1
WHEN 28 THEN 2
WHEN 27 THEN 3
WHEN 26 THEN 4
WHEN 25 THEN 5 END
UPDATE gvAbsence
SET holiday_bought = Floor(Rand() * (@.Max - @.Min + 1)) + @.Min
WHERE unique_identifier = @.uID
FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf
END
CLOSE MyCursor
DEALLOCATE MyCursor|||Unfortunately I'm using SQL Server 2000; TOP won't work.Howdya figure that?|||I didn't think TOP was implemented in 2000?
Any time I try run a TOP statement in QA I get
SELECT TOP 5 surname
FROM employee
ORDER BY surname ASC
--
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.
Errors!|||From SQL 2K BoL:
Limiting Result Sets Using TOP and PERCENT
The TOP clause limits the number of rows returned in the result set.
TOP n [PERCENT]
n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return:
TOP 120 /*Return the top 120 rows of the result set. */TOP 15 PERCENT /* Return the top 15% of the result set. */.If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.
The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:
The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.The top keyword changed in 2005 only in so far as you can use a variable.|||Then how come I can't execute SELECT TOP statements? :confused:|||Have you checked the compatability level of the database?|||Works for me
USE Northwind
GO
SELECT TOP 5 LastName
FROM employees
ORDER BY LastName ASC
GO|||Ummm...assuming your "algorithym" is correct, why couldn't you just do
USE Northwind
GO
CREATE TABLE myTable99(unique_identifier int, years_of_service int, holiday_brought_forward int)
GO
INSERT INTO myTable99(unique_identifier, years_of_service)
SELECT 1, 1 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 20 UNION ALL
SELECT 4, 30
GO
UPDATE g
SET holiday_brought_forward =
CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6)
ELSE Floor(Rand() * 5) END
FROM myTable99 g
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO|||... because rand() will return the same value for all rows.:)|||Not necessarily...
select case floor (rand()*5)
when 1 then 1
when 2 then 2
when 3 then 3
when 4 then 4
else 5 end
from [table of your choice]
This came up in a very nice SQL puzzle posted here some time ago. For those of you who are interested...
select case floor (rand()*5)
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]
EDIT: Due to the nature of the "oddity", it may not be a truly random sampling. Sounds like it may be random enough for you, though.|||Well pummel my piles with a paddle. What is that about then? The case statement causes the rand to be re-evaluated for each row rather than once for the set? And what's the deal with 'WTF'? I mean - wtf? You got a link?|||Actually, rand() is invoked on each row of the output. This is why inline user defined functions can get you in to piles of problems that will pummel your performance paddle(s) past pertinent perturbation.
Sadly, I do not have a link to the original puzzle. It may have been lost in the troubles that DBForums was going through some time back when posts just up and disappeared. Besides, it will likely be a good exercise for you to figure out what is going on in there.
DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.|||Oh noblet - I forgot about the 0 :rolleyes:
But anyway:
select case 1
when 1 then floor (rand()*5) end
from sys.databases
select case 1
when 2 then NULL else floor (rand()*5) end
fromsys.databases
select case floor (rand()*5)
when -1 then NULL else floor (rand()*5) end
from sys.databases
Coooooooooooooooo.|||DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.Ah jolly good! I was wondering what subtle distinction you were making :)|||tsk tsk tsk. Perhaps I should have taken the 0 into consideration, as well...
select case floor (rand()*5)
when 0 then '0'
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]
Extend as necessary to convince yourself this is not so easy as all that.|||neat trick! this seems to work as well without the need for laying out all the possibilities in the case:
select case when name is not null then rand() else NULL end from sys.objects
all you need is something to check in the when clause that will always be true, but that the optimizer won't optimize away. 1=1 won't work. That is, this is broken:
select case when 1=1 then rand() else NULL end from sys.objects|||UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN
CASE WHEN 1=1 THEN Floor(Rand() * 6) ELSE NULL END
ELSE
CASE WHEN 1=1 THEN Floor(Rand() * 5) ELSE NULL END
END
Does not give the desired result - (same problem as before)
UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*5)
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END
Same goes for this *sigh*
I can understand the use of CASE statements to make sure every row is evaluated individualy - but that doesn't appear to be the case when running the above...
this might sound silly but in other languages you need to use
Randomize
Would that apply here? *shrugs*
thanks for the input so far :)|||Can you create a derived table and update from that rather than directly with the case statement?|||Do you mean something like this?
IF EXISTS (SELECT NULL FROM sysobjects WHERE type='U' AND name='gvVals') BEGIN
DROP TABLE gvVals
END
CREATE TABLE gvVals
(
bought_forward int
)
INSERT INTO gvVals(bought_forward) VALUES(1)
INSERT INTO gvVals(bought_forward) VALUES(2)
INSERT INTO gvVals(bought_forward) VALUES(3)
INSERT INTO gvVals(bought_forward) VALUES(4)
INSERT INTO gvVals(bought_forward) VALUES(5)
INSERT INTO gvVals(bought_forward) VALUES(6)
UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END
FROM gvVals
Holiday Brought Forward
--
5.0
5.0
5.0
5.0
5.0
etc|||Text file containing the cursored SQL uploaded for your amusement / berrating ;)
EDIT: and I guess comments, too|||I could not get different values for rand() using the case statement on SS2K
Generating a random value from newid() seems to work
UPDATE gvAbsence
SET holiday_brought_forward =
right(convert(char(2),(convert(int, (convert(varbinary(1),right(newid(),1))))) ),1)/10.*5+1|||pdreyer - wow, that appears to work (and quickly!)
But I'm afraid I have absolutely no idea what it is doing ;)
/10.*5+1 ?? I removed the point and it didn't work - what's that doing :)
Seems like an aweful lot of converts in there and I can't for the life of me understand the char convert!
Hehe, thanks for that - I'd love to implement it, but only if I can understand it first :D
EDIT: I have removed the +1 as I want to include zero values :) I love the fact it even does halves! I hadn't even considered that before :)
RE-EDIT: Not quite returning the values I need... I need 0-5 inclusive and 0.5's would be lovely (but not essential).
/10.*5 returns 0 - 4.5 inc
/10.*5+1 returns 1 - 5.5 inc|||Do you mean something like this?Nope :)
But it sounds like rand() is working differently in 2k anyway. I don't have any 2k boxes to play with and test so I'll drop out at this point.|||Awww but Pooooooooots!
Thanks for all your prior help bud - it is appreciated :)
and if that's not what you meant... what is! :p|||Did we not cover derived tables in your last thread (or thread but one)? Genuinly can't remember for sure...|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?
And did you see what I did before?
so I'll drop out at this point
I don't know why but I'm finding it harder and harder to ask questions here - easy enough to answer (some) but when I ask them I go into "newb" mode and yeah - it sucks :(|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?
No. But you might be able to do it as a sub select.
Derived table example:
UPDATE gvAbsence
SET holiday_brought_forward = derived_table.random_number
FROM dbo.gvVals
INNER JOIN
(SELECT MyPK
, CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END AS random_number
FROM dbo.gvVals) AS derived_table
ON derived_table.MyPK = gvVals.MyPk
And did you see what I did before?Dunno - what did you do?|||I have absolutely no idea what it is doing
it uses the ASCII code of a character from newid() as a random number (caveat: limited range but OK for your needs)
can't understand the char convertNot needed, should be removed.
what's the point doingIt ensure decimal arithmetic else the integer devide will result in zero
it even does halvesThat was not the intended result. I assumed the target field was an integer, So to get values 0-5 multiply by 6 and convert to int at the end. e.g.
select convert(int,right(ascii(newid()),1)/10.*6) from sysobjects
Subscribe to:
Posts (Atom)