Hi All,
I have the following table:
Name Age
A 15
B 18
C 22
D 27
E 33
F 42
I need to have the following output:
Range Count
0-10 0
11-20 2
21-30 2
31-40 1
41-50 1
Could anyone let me know how to achieve this in SQL Server 2005?
Thanks,
Varun
Hello all,
i do have two TABLE's namely AS "Bro1" AND "Tra1"
Bro1 OUTPUT
ID Group_Code Group_Change_Date
212229 10/04/2006
2122 26 01/03/2008
Tra1 OUTPUT
TId Modify_Date
2122 11/06/2007
2122 18/06/2007
2122 03/12/2007
2122 14/12/2007
2122 04/01/2008
2122 24/01/2008
2122 10/04/2008
2122 14/04/2008
2122 24/04/2008
Mine Requirement IS....
TId Modify_Date Group_Code
2122 11/06/2007 29
2122 18/06/2007 29
2122 03/12/2007 29
2122 14/12/2007 29
2122 04/01/2008 26
2122 24/01/2008 26
2122 10/04/2008 26
2122 14/04/2008 26
2122 24/04/2008 26
it means IF Modify_Date IS BETWEEN '10/04/2006 ' AND '01/03/2008'
THEN i need TO show it's Group_Code [29] in my output
Same way the Modify_Date >= '01/03/2008' then i need to show it's Group_Code [26] in my OUTPUT
I hope i am clear WITH my question.
Please help me in writing query....
Thanks
Prashant Hirani
I want to display the following output where integer value is column data.
{[Advertiser].[AdvertiserKey].&[4000],
[Advertiser].[AdvertiserKey].&[4001],
[Advertiser].[AdvertiserKey].&[4002],
[Advertiser].[AdvertiserKey].&[4003],
[Advertiser].[AdvertiserKey].&[3661],
[Advertiser].[AdvertiserKey].&[3662]
}
I have a table Test123 having three column EmpID,AttribName,AttribValue.
run the below query to generate table structure and data.
Create Table Test123(EmpID int,AttribName varchar(50),AttribValue varchar(50))
insert into Test123 values(1,'Name','X')
insert into Test123 values(1,'Age',50)
insert into Test123 values(1,'Salary',1000)
insert into Test123 values(2,'Name','Y')
insert into Test123 values(2,'Age',30)
insert into Test123 values(2,'Salary',2000)
insert into Test123 values(3,'Name','Z')
insert into Test123 values(3,'Age',35)
insert into Test123 values(3,'Salary','One Hundred')
And I want output in below format.
AttributeValueType
=================
AGE | NUMERIC
NAME | ALPHABET
SALARY | ALPHANUMERIC
==================
Hi all. I have a stored procedure on my sql server that returns asimple informtation about that tim a database was backed up. I owuldlike to create an HTA that operator types can look at to make surethat the backups finished, can someone help me do this?The stored procedure in called sp_lastback and the output looks likethis:database days since backup timestamp of backupdbase1 0 2004-10-14 00:41:21.000dbase2 0 2004-10-14 00:08:36.000dbase3 1 2004-10-13 22:46:57.000Can this be done? If someone could help me with this simple problem, Icould probably reuse the same method a 100 useful ways.Thanks in advance!
View 3 Replies View RelatedI tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO
If I run the query in Query Analyzer directly, I can display @UserName:
DECLARE @UserName as nvarchar(100)
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
Select @UserName
But how can I display @UserName if I call stored procedure:
exec UserLogin 'email', 'password', ''
I believed it return int, right?
Thanks in advance for help.
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
View 2 Replies View RelatedHow can i format my query so that each piece of data appears on a new separate line? Is there a command for a new line feed? does not work.
thanks.
For example:
a: data
b: data
c: data
a: data
b: data
c: data
I would like to display a portion of report where there is data or no data
There is data subreport display
Product Name Latex Gloves
Product ID
xxxx5678
There NO data in the subReport
Product Name
Product ID
Im trying to get a table where the columns are a range of dollars spent...
Code:
$1 - $24 $25 - $49 $50 - $74
01-12 Month 5,000 6,000 6,200
13-24 Month 7,000 8,800 9,120
-------------------------
This is what i have so far. It gives me one value. Of course I can run it a bunch of times changing the values, but im just curious if there is a way to do it all at once.
---------------------
Code:
SELECT COUNT(transaction_header.transaction_id) AS CustID
FROM transaction_header INNER JOIN
Northwind.dbo.Ttype ON
transaction_header.transaction_type =
Northwind.dbo.Ttype.transaction_type
WHERE (Northwind.dbo.Ttype.Cat2 = 'CATALOG' OR
Northwind.dbo.Ttype.Cat2 = 'ECOM') AND (DATEDIFF(mm, transaction_header.transaction_date, { fn NOW() }) < 13)
AND
(transaction_header.total_net_retail BETWEEN 1 AND 24)
Thanks,
Dynasty
Table:
SomeFKID <pk>
StartDateTime <pk>
EndDateTime
SomeValue
Example Scenario:
Data is stored hourly. So there would be 24 records for today for each SomeFKID. I need to be able to pass a TimeSpan (in minutes), a StartDateTime, and an EndDateTime to a stored procedure and return totals in the date range grouped by the TimeSpan. So if I want all records today grouped by 2 hour intervals I would need to pass:
7/1/2004 00:00:00, 7/1/2004 23:59:59, 120 --> and return 12 records one for hours 0-2, one for hours 2-4, etc.
Any advice would be greatly appreciated!
Thanks in advance,
Wheatster
after i create a paratiotion, i want to see the ranges.
when i tried to do :
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
i got the paratition data but not the ranges themselfs.
how can i do this?
thnaks in advance
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
I'm trying to write a statement that will return all records within a range. The only problem is that the records can have modifiers on them.For example, a sample range I would want would be all records between 1008 and 1120. I can't use a between statement though because these records can end in modifiers, like 1009X or 1117B. How can I search for records within a range when they have modifiers such as above?
View 1 Replies View RelatedHello -
Building a small app for a school nurse......
Have a SP that takes 2 parameters (@login, @logout) both in the form of "convert(varchar,@Login,108)" or 08:00 AM, etc
What I need to do is then check to see:
1. What period did they login in?
2. What period did they logout in?
3. How much time in a period did they actually miss?
The ranges look like:
Set @Period1Start='08:20 AM'
Set @Period1End='09:10 AM'
Set @Period2Start='09:15 AM'
Set @Period2End='10:00 AM'
...
...
...
So.....
if @login = 08:30 AM and @logout = 08:45 then they missed 15 minutes of period 1
If @login = 08:30 AM and @logout = 09:45 then they missed 40 minutes of period 1 AND 30 minutes of period 2
Not knowing all of the time functions in SQl, I am looking for some ideas on how to accomplish this.
Thanks!
Dan B
I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.
View 1 Replies View RelatedCan someone please help me with this?
I need a query that will pull clients that made payments last year but not this year.
I need the query to use date parameters so I can select any date range for the past year and the current year.
I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables.
Date range is based on the tblPaymentReceipts.PaymentDate
tblClients, ClientID
tblPayment, PmtID, ClientID, Paystartdate, Paygroup
tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount
Hi All,
I have a startdate (01/11/2007) and a enddate (01/11/2008). I need to add dates into a table for everyday between these dates. Can anyone help?
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.
Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.
:confused: Dont know if this will be tough for the rest of you but for someone who is fairly new to SQL...I cannot figure it out...
I have a table:
Rownumber starttime endtime
1 l 30 l 240
2 l 40 l 120
3 l 50 l 260
4 l 1300 l 1400
Rows 1, 2, and 3 over lap with one another and I am trying to obtain the starttime and endtime values which can cover them all.
I would like to find the overlapping (starttime - endtime) ranges and accept the lowest starttime value and the highest endtime value.
Row 1: 30--------------------240
Row 2: 40--------------120
Row 3: 50----------------------260
Row 4: ...1300---------1440
I would like to include starttime-endtime ranges that do not overlap with any other integer range.
which in this case would be:
Rownumber starttime endtime
1 l 30 l 260
2 l 1330 l 1400
I was thinking of using a cursor and comparing each row to all of the other rows in the table and then setting a boolean in that row if it overlaps with another row in the table...is there a better way of doing this?
Thank you for the help!
hello, i have quite a challenge on my hands here and would appreciate any help. :confused:
I have a table variable that stores integer ranges representing times of the day:
select * from @reservations
room date | starttime | endtime
1 2004-12-11 0 1440 (represents an entire day in minutes)
2 2004-12-12 420 1020
3 2004-12-14 200 600
4 2004-12-15 0 200
4 2004-12-15 500 1000
I need to be able to return the minutes that are open for each room. The @reservations table shows me the times that are blocked.
I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.
Based on the @reservations table above, I'd like to write something that returns:
room date starttime endtime
2 2004-12-12 0 420
2 2004-12-12 1020 1440
3 2004-12-14 0 200
3 2004-12-14 600 1440
4 2004-12-15 200 500
4 2004-12-15 1000 1440
This result represents the times in minutes that are available.
I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @reservations table.
I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time! :)
I've gone cold here. Dunno if I've had too little coffee - as I'm currently drinking some seriously wicked green tea - or whether my brain has locked down from yesterdays "bad eggs for lunch" experience.
Anyway... I have database with a customer, for each customer is a related history table with assigned consultant.
The assigned consultant table has information on consultant id, name, the start date of his assignment and the end date.
I need to find all customers that currently have (or have had) two or more consultants actively assigned. In other words, I need to see if the start/end times overlap.
At my current state, I'm just done.. i can't maintain the perspective... how do I do this?
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
I'll keep the analogy as basic as possible, but lets say we sell vouchers. The vouchers are individually numbered.
We sell the vouchers in batches, and are stored in the DB as one record per sale with a range of the vouchers sold (so they scan the first and last voucher number to get the range)
E.g.
Table1
SalesID Description From To
SalesID0001 Batch of paper 100001 100015
SalesID0002 More paper 100016 100150
The vouchers come back in individually however, so we might get voucher 100011 back and is stored in another table as an individual item.
I'm trying to write a report which shows which vouchers have not come back yet and I'm struggling since the data is stored in ranges not on an individual basis...?
My thinking is leading me to maybe create a temp table where I can convert those ranges into real numbers....which I'll be able to more easily compare - but how I might do that.
(Example output from above example
TempTable
SalesID Description Voucher
SalesID0001 Batch of paper 100001
SalesID0001 Batch of paper 100002
SalesID0001 Batch of paper 100003
SalesID0001 Batch of paper 100004
SalesID0001 Batch of paper 100005
etc, etc)
I've got a linked server setup to DB2, and some of the
date fields in the DB contain 1/1/0001 values.
I've got views created in SQL2000 against the DB2 linked server.
When I run a query against a particular table that contains multiple field of datetime type.
I get the below error
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
This only happens when I include in the select the field that contains 1/1/0001 values.
I assume since valid dates ranges in SQL are from
January 1, 1753 through December 31, 9999, this would be
what's causing this.
I tried to covert in the select but that failed as well. The only thing that I've been able to do, is to use a DTS to pull the data from the DB2 to a local SQL2000 table, with that fields type set as varchar. This works.
Using a DTS to pull the data to a local table in production isn't a viable workaround, since this table contains 1.8 million rows.
How is DTS converting this field, when convert fails in the select?
How do I get around this?
Thanks
Hey,
I woudl like to take an age range (Say 22-27) and determine the min and max year, and then take those two DateTime Year values and do a SELECT from a column titled "Birthday Year", which is a an int value like '1984'. Could someone give me a hand with this?
/* Value 1: 1978
Value 2: 1988
*/
SELECT(???) FROM User WHERE ['BirthdayYear'] <> (?Age Function?)
GO
Thanks.
I have a set of MS SQL reports, that need to always run on a certain day of the month. Generally the 20th. If the report was to run few days before the 20th, say on the 10th, I wish to retrieve those days between the 20th from the previous month, till the current date.
e.g: '2015-4-10' should only return 20 days worth of data.
I have tried the following query:
SELECT
DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) As EndDate,
MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp),DATEPART(MONTH,
(SELECT CASE WHEN DATEDIFF(DAY,DATEPART(DAY, GETDATE()),28) <0 THEN (SELECT DATEPART(MONTH, GETDATE()))
ELSE (SELECT DATEPART(MONTH, GETDATE()) -1) END AS Date)),28)AS DATE)) AS StartOfMonth
FROM
tbLogTimeValues
WHERE
DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, -1, GETDATE()))
Which parses ok and managed to test all individual queries, however, as a whole, I get the following error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
Hello,I am importing data that lists rates for particular coverages for aparticular period of time. Unfortunately, the data source isn't veryclean. I've come up with some rules that I think will work to clean thedata, but I'm having trouble putting those rules into efficient SQL.The table that I'm dealing with has just under 9M rows and I may needto use similar logic on an even larger table, so I'd like somethingthat can be made efficient to some degree using indexes if necessary.Here is some sample (simplified) code:CREATE TABLE Coverage_Rates (rate_id INT IDENTITY NOT NULL,coverage_id INT NOT NULL,start_date SMALLDATETIME NOT NULL,end_date SMALLDATETIME NOT NULL,rate MONEY NOT NULL )GOINSERT INTO Coverage_Rates VALUES (1, '2004-01-01', '2004-06-01',40.00)INSERT INTO Coverage_Rates VALUES (1, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (1, '2004-06-01', '2004-08-01',30.00)INSERT INTO Coverage_Rates VALUES (2, '2004-01-01', '9999-12-31',90.00)INSERT INTO Coverage_Rates VALUES (2, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (2, '2004-08-01', '2004-08-01',30.00)GOThe rule is basically this... for any given period of time, for aparticular coverage, always use the coverage with the highest rate. So,given the rows above, I would want the results to be:coverage_id start_dt end_dt rate----------- ---------- ---------- --------1 2004-01-01 2004-06-01 40.001 2004-06-01 2004-08-01 30.002 2004-01-01 9999-12-31 90.00There can be any combination of start and end dates in the source, butin my final results I would like to be able to have only one distinctrow for any given time and coverage ID. So, given any date @my_date,SELECT coverage_id, COUNT(*)FROM <results>WHERE @my_date >= start_dtAND @my_date < end_dtGROUP BY coverage_idHAVING COUNT(*) > 1the above query should return 0 rows.Thanks for any help!-Tom.
View 9 Replies View Related
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
Joshi
I'm currently using Reporting Services for SQL Server 2005. I have been able to setup, and configure the Report Manager interface, as well as generate reports via the Report Builder. What I have been unable to do is allow the user to dynamically set the date range that my SQL query will use. Can someone suggest / is it even possible wihtout using a custom web interface?
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention