Using FoodMart, how can i add time to this:
WITH MEMBER [Measures].[Rank] AS 'Rank ( Product.CurrentMember,Order( {Product.CurrentMember.Parent.Children} ,[Profit], DESC) ) '
SELECT
{[Profit] , [Rank]} ON COLUMNS,
Drink.Children ON ROWS
FROM Sales
Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Thanks in advance.
Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
Code:
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Thanks in advance.
I am trying to get a total for times, but I am having trouble gettinganything to work. Basically I created a query to pull up severalpeople's name that performed certain details and how long each detailwas. I now want to add up all the hours to a total for each person. Cananyone point me in the right direction?*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedI have three different columns, hour(s), min(s), sec(s)
I can add it up, but will like to convert it into. hrs, mins and sec.
this is how, i am adding it up into seconds.
SELECT ((TotalTimeSpentHrs*60*60)+(TotalTimeSpentMin*60)+(TotalTimeSpentSec))AS totaltime
FROM EST1
How can I convert the total seconds, so that i can input the result in a new column.
I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi all,I'm writing a website with Cold Fusion and when a user submits arequest and it's stored in the MS SQL database, I want the unique ID(Identity field in table) to be given to the user on screen plusemailed to user.Now can I store data to the database (where the ID is created) andreturn this as a variable in the same statement? I've seen this doneon many websites, but I have no idea how to do it in one step.Thanks,Alex.
View 1 Replies View RelatedI am trying to add integer value to the datetime to get the configurable "date".But it is not working out. Here is the code for it.
declare @Id int;
exec @Id = up_GetConfigurableDayInterval
print getdate() + @Id
The sp "up_GetConfigurableDayInterval", will return number of days to add the the current date time. But print getdate() + @Id , is not producing the updated result.But when ever i have replaced the variable "@Id" with a value, Say "5", it is producing the expected result.I have also tested the above code by the following:-
create table Temp(value datetime);
insert into Temp values(getdate() + @Id);
select * from Temp
But it is not having the new added datetime.
I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.
Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.
For background on why I would want to add the same column to a model more than once please read this post.
Thanks.
I have data that looks like the following.
ID Date Time Length Interval_Num
1 10/11/2014 9:00 420 14
Basically, length represents the # of minutes a person is scheduled for. We have 30 minute intervals. Interval_Num = (Length/30). I need the data to show each interval. For the above example it should only be 14 intervals.For the above example the solution should look like....
ID Date Time Length
1 10/11/2014 9:00 30
1 10/11/2014 9:30 30
1 10/11/2014 10:00 30
1 10/11/2014 10:30 30
for 14 intervals up until 16:00
Hello all. I was just wondering how I could add the current date and time to the name of the .bak file. I perform a full backup daily and would like to keep a weeks worth of backups, but everytime I run the job, it just overites the previous one.
The command in the wizzard is:
BACKUP DATABASE [snl] TO DISK = N'C:Backups_for_FTPSNL_Full.bak' WITH NOINIT , NOUNLOAD , NAME = N'snl backup', SKIP , STATS = 10, DESCRIPTION = N'SNL Backup', NOFORMAT
Thanks,
Parallon
I am trying to add the hours between each time block stored in a database.
In this database a user enters the begin time and the end time. For example the course MATH0001 would start at 8am and end at 10am. Therefore the user would enter 0810 in the start field and 1000 in the end field. The course MATH0001 doesn't run the entire semester it may only run from 8th Jan - 15th March and the course is scheduled in a room called GR4. Now because a course can be scheduled modularly, one room could have several courses scheduled in this manner.
The problem: I need to find out how many hours GR4 is used but it contains the following courses
Code:
Schedule for room called GR4
Course StartDate EndDate BeginTime EndTime HoursPerClass
MATH0001 06-FEB-13 19-FEB-13 0810 1700 9
MATH0002 20-FEB-13 04-Mar-13 0810 1700 9
MATH0003 10-JAN-13 05-Feb-13 0810 1700 9
MATH0004 22-APR-13 17-May-13 0810 1700 9
MATH0005 08-MAR-13 21-APR-13 0810 1700 9
MATH0006 07-JAN-13 09-JAN-13 0910 1300 4
MATH0007 20-JAN-13 17-MAY-13 1710 2000 3
MATH0008 08-JAN-13 18-JAN-13 1710 2000 3
A day only has 13 hours. Therefore the total hours spent in GR4 should be 12 hours. This is calculated by adding the hours between 8am and 5pm = 9 hours and 5pm an 8pm = 3 hours. I would not include 9am to 1pm because it is a subset of the 8am to 5pm slot.
Now, how to accomplish this but below is the code that i have thus far:
I forgot to mention that this code was to just test my 'final code' results and it outputs the table shown above. Anyway for testing purposes I have limited the search to the room GR4 and the day Tuesdays.
Code:
select DISTINCT ssrmeet_room_code,
ssrmeet_start_date,
ssrmeet_end_date,
ssrmeet_crn,
ssrmeet_begin_time,
ssrmeet_end_time,
(((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 As HoursPerClass,
[code]....
I have a report which takes around 5 seconds when run in BIDS but takes around 20 seconds when deployed on report server.Execution log says TimeDataRetrieval is around 3-4 seconds and  rendering time is around 15-17 secs.From this report I am passing 8 parameters to a  drill through report and there are 36 text boxes where I have defined these parameters for drill through action.All these parameters are populated in main dataset.When I deployed the same report without any drill through action and parameter, it takes 5 seconds.So I am suspecting that because of drill through parameters report is taking more rendering time on server.I am using 2008R2 and IE11.
Is it expected behavior that due to so many parameters for drill through action, report will take more rendering time?If yes, then why is it not taking same time in BIDS?
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
[Code] ....
Hi,
Why adidas is better-ranked than nike??
Table "indice":
idcCod fabCod mcaCod catCod fabNom mcaNom catNom
1111NikeNikeRopa
2221AdidasAdidasRopa
3113NikeNikeMedias
4118NikeNikeLargas
SELECT[RANK] ,[KEY],idc.*
FROMFREETEXTTABL (dbo.indice,mcaNombre,fabNombre,catNombre),
'ropa adidas nike',
LANGUAGE 3082, 5) res left join
dbo.indice idc with (nolock) on idc.idcCodigo=res.[key]
ORDER BY [RANK] DESC;
Result:
RANK KEY
182
03
04
01
Why idcCods's 1 and 2 have different rank??
because idcCod's 3 and 4 affects ?? or it's just thinks of language??
Thank's in advance
Ok. Im not able to understand this logic please help. As you can see we have 2 columns of ranks, 1)normal 2)corrective. what is the logic behind this and how do u write a query for this? these ranks are for the Salary Column.
Imran,
"You truly do not know someone untill you fight them."-THE MATRIX.
EmpID Empname EmpSalary RankNormal RankCorrective
1 A 150001 1
2 B 100002 4
3 C 150001 1
4 D 40003 5
5 E 150001 1
6 F 15004 6
7 G 15004 6
8 H 5005 8
I have table :
Result1 Rank
5
6
78
4
27
3
How to rank in above table ?
Thank you very much !
Hi All,
Please let me know the equivalent of RANK() over ( order by...)
in SQL server 2000.
I thought this was supported in SQL server 2000.
Please let me know if there exists a user defined function.
Thanks in Advance.
Thanks
I would like to write a query that gives me the values of a set ofobservations, and their rank.CREATE TABLE #Values(val int)INSERT #Values SELECT 1INSERT #Values SELECT 5INSERT #Values SELECT 10I would like to select this:1 10 -- rank 1, value 102 53 1I can put them into a temp table with an identity column, ordered bythe column I'm interested in, and then retrieve in order by theidentity column. I'm wondering if there's a way to do that with asubquery.Thanks,Jim
View 3 Replies View RelatedI've written a bunch of code using contains for fts. Then as I was trying to run the sorting, I realized that I have to use containstable in order to sort by rank. Is that correct? When I was using contains, I just used it as a where clause so I would have something like...
WHERE ((PostedUntil >= '5/22/2006') AND (SiteId=199)) AND (CONTAINS (PositionTitle, '"sales"') OR CONTAINS (Description, '"sales"'))
From the examples I've seen, in order to use containstable, I have to join a new dynamic table to the freetext enabled table and it only uses 1 containstable phrase for the new table. In my case, I may have multiple containstable phrases, so would they all fall in a () set like..
FROM Categories AS FT_TBL INNER JOIN
(CONTAINSTABLE (table, col1, searchphrase) OR (CONTAINSTABLE(table, col2, searchphrase2) OR CONTAINSTABLE (table, col3, searchphrase3)) AS KEY_TBL
... or would each containstable have to be a new join? I don't want to spend anymore time going back and rewriting code just to test it since it's about a days worth of recoding. Thanks.
I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq
View 2 Replies View Relatedive created a photo sharing site, and im trying to show the statisics for certain users, so far i can COUNT all the photos the user has upload, and show them in descending order, but i need to be able to see the ranking of the user, like :
RANK USERID No. Of Photos
1 10 100
2 8 70
3 9 85
is there anyway of doing this? thanks Si!
My data:
Code:
CUST NO | StoreName| Rank
Cust1 0781 1
Cust1 0246 2
Cust1 0481 3
Cust2 2101 1
Cust2 8876 2
Cust2 5445 3
Cust3 3243 1
Cust3 4545 2
Cust3 3223 3
Im trying to find a way to rank as shown in the third column.
This is the code im using:
Code:
SELECT top 100 (CustomerNo)as CustomerNo, StoreName,
(SELECT COUNT(DISTINCT CustomerNo)
FROM dbo.Top3CustomerNoStores AS O2
WHERE O2.CustomerNo < O1.CustomerNo) + 1 AS drnk
FROM Top3CustomerNoStores as O1
... and getting this result
Code:
CUST NO | StoreName| drnk
Cust1 0781 1
Cust1 0246 1
Cust1 0481 1
Cust2 2101 2
Cust2 8876 2
Cust2 5445 2
Cust3 3243 3
Cust3 4545 3
Cust3 3223 3
... and using this code
Code:
SELECT top 100 (CustomerNo)as CustomerNo, StoreName,
(SELECT COUNT(*)
FROM dbo.Orders AS O2
WHERE O2.qty < O1.qty) + 1 AS rnk
.... which gives me ...
Code:
CUST NO | StoreName| rnk
Cust1 0781 1
Cust1 0246 1
Cust1 0481 1
Cust2 2101 4
Cust2 8876 4
Cust2 5445 4
Cust3 3243 7
Cust3 4545 7
Cust3 3223 7
I have noticed in SQL 2005, this is accomplished much easier with built in functions.
any help appreciated
Hi all,
until recently ive been using a rank equation to calculate rank,
essentially doing a select statement and selecting this as the rank field, where query 2 is the same as query 1:
((select count(*) from (query1) where (query1).value < (query2).value) +1)) as Rank
problem is that this is now running like a dog (takes 10 secs) and i'd like to try and do this another way- 2005 has a rank function, how can i do this in 2000?
here is the full statement :
SELECT StudentId, GCSE_Score, LTRIM(STR
((SELECT COUNT(*)
FROM dbo.[Score2004-05]
WHERE GCSE_score < s.GCSE_Score) + 1)) AS GCSE_Rank, SetId
FROM dbo.[Score2004-05] S
WHERE (SetId = '2004/2005')
greg
Is there a way to use the Rank function like in 2005 for sql 2000
View 6 Replies View RelatedI need help in understanding how the rank is calculated in FREETEXTABLE. I have a following query
select ft_tbl.saon
,ft_tbl.paon
,ft_tbl.street
,ft_tbl.postcode
,key_tbl.rank
from temp as ft_tbl
INNER JOIN freetextTABLE(temp, (saon, paon, street), '80 ridge avenue', 15) as key_tbl
ON FT_tbl.ID = key_tbl.[key]
First, the resulting rows does have one record which has an address 80 ridge avenue but it appears at 15th place. Ideally it should appear on 1st. All the ranks of the results are same.
Second, the results are also showing two rows which does not contain the specified search string at all. They not only appears above in the resulting table but also have the same rank as the result in question (80 ridge avenue). For example result shows €œLong Ridges, Flat 21, Fortis Green€? at fifth place which is no way near the search string. And actual record shows up at 15th Place
Is there any way we can influence the rank to show exact match first. Just to clarify I have removed the digits from noise file as we need to search house numbers. Digits appears in noise file as default.
Hi ,
I have a report created and within the report is columns that perform additions on the fields from the database.
I want to create a rank column to show the rank of the row compare with the rest.
Col1 Col2 Total Ranks
1 2 3 3
2 3 5 1
2 2 4 2
On the above Col 1 and 2 would come form my database, Total is calculated by the report. How can I get ranking on this total? I cannot sort by the total as the report should only showing rankings but not be ordered by the rank.
I am using SQL Server 2005 Reporting Services...anny help is much appreciated.
Thanks
This query:
SELECT xx.JumpHeight, xx.HeightRank, xx.NoEvents, xx.MinRunDate, xx.DogIdent,
Dogs.CallName, DogOwner.LastName, DogOwner.FirstName
FROM
(SELECT JumpHeight, DogIdent, MIN(RunDate) as "MinRunDate", COUNT(Event_ID) AS "NoEvents",
RANK() OVER (PARTITION BY JumpHeight ORDER BY COUNT(Event_ID) DESC, MIN(RunDate)) AS "HeightRank"
FROM EventData
WHERE Event=@Event
GROUP BY JumpHeight, DogIdent) AS xx, Dogs, DogOwner
WHERE (Dogs.Breed = @Breed AND xx.DogIdent = Dogs.DogIdent and Dogs.Owner_ID = DogOwner.Owner_ID) AND
(xx.HeightRank <= 10)
ORDER BY xx.JumpHeight, xx.HeightRank
produces this output:
Jump Ht.
Rank
# of Events
First Event
Owner
Call Name
08
3
1
2/19/2006
Some Owner
Otto
08
4
1
3/12/2006
Some Owner
Schotzie
I want it to produce this output:
Jump Ht.
Rank
# of Events
First Event
Owner
Call Name
08
1
1
2/19/2006
Some Owner
Otto
08
2
1
3/12/2006
Some Owner
Schotzie
I have tried several things and cannot correct the problem. Obviously, RANK is being evaluated in the wrong place, but placing it elsewhere has failed to produce the above results.
I appreciate any help I can get. Thanks!
Hi,All,
I have one table like this
UserID,Name,GameScore
1 A 25
2 B 23
3 C 22
4 D 25
5 E 23
6 F 26
Now i want the query which return like this
Name Score Rank
F 26 1
A 25 2
D 25 2
B 23 3
E 23 3
C 22 4
Can anyone give me the sql 2000 query for this
Hi all:
In the Sql below, sample from William Pearson, the amount Spend is in descending order and the Rank number is in ascending order. Like this:
Spend Rank
24 1
12 2
10 3
9 4
What I wish to accomplish is:
Spend Rank (descending)
24 4
12 3
10 2
9 1
Please let me know what I need to accomplish it.
Thanks for the help
Victor
SELECT
CompanyName, Spend,
(SELECT COUNT(*)
FROM
ACC0704 AS CoSpendTotal
WHERE
ACC0704.Spend <= CoSpendTotal.Spend)
AS Rank
FROM
ACC0704
ORDER BY
Spend DESC
Hi,
i want to create a report so that a list of the top 30 records are returned to the report user. In the report i want to have the records position in the list shown (ie the first row should have 1. and the second should be 2. right on down to the 30th having 30.)
how do i achieve this please?
many thanks
FatherJack
Hi all,
I don't know how to explain what I wanna do in english so here's a concrete example:
I have this data
Item - Qty
IT1 - 2
IT2 - 2
IT1 - 4
IT1 - 5
IT2 - 2
And I wanna do something like this
Item - Rank - Qty
IT1 - 1 - 2
IT1 - 2 - 4
IT1 - 3 - 5
IT2 - 1 - 2
IT2 - 2 - 2
So basically I want to assing a rank (on the fly) in a SELECT statement
Thanks in advance
Or Tho
i am trying to create a rank formula.i just need to return the highest MCC count for the mcc code and return the seller code...So eg row 3 and 4, there are two MMC CODE called 4772, i need the formula to return the seller code r10, this is because r10 has 9 counts against that mcc code, and r03 has 9,
View 2 Replies View Related