Hi, I´ve searching for an reuseable SQL-Statement for follwoing
situation:
Having e.g. a table tblAdress with some fields like persons full name
(as Name), City, Birthdate, Street and so on.
Now I will find e.g. all oldest persons for every City. No problem
with:
select name, city, max(birthdate) group by name, city
But if I also want to have the streetname for the oldest person, I
have a problem cause I can´t include the street into the select
statement like this:
select name, streetname, country, max(birthdate)
group by name, country
This statement is not correct cause streetname is not included in
group by. If I include the street, I´ve got the oldest persons per
city and street. This is not what I want to have. Remember I want the
oldest person in City and after that therefore the street ... and this
in a single sql statement. It´s only an example, but I think it´s a
standard sql statement to retrieve information like this one for
included aggregate functions. I spend hours on internet searching and
many self made test ... without any solution. Does anyone has a
standard solution for this situation ?
Hi, I'm trying to write an aggregat in c#.It's no problem for sum or multiplication aggregates. But when I try to use an array to stor the values in it, to compare them with each other, I get the Message:
Fehler 1 Type "AggregatClr.AggregatClr" is marked for native serialization, but field "Werte" of type "AggregatClr.AggregatClr" is of type "mscorlib.System.Array" which is a non-value type. Native serialization types can only have fields of blittable types. If you wish to have a field of any other type, consider using different kind of serialization format, such as User Defined Serialization. AggregatClr
And when I try it with User Defined instead of Native:
Fehler 1 Type "AggregatClr.AggregatClr" is marked for user-defined serialization, but does not implement the "System.Data.Microsoft.SqlServer.Server.IBinarySerialize" interface. AggregatClr
What does that means? How can I do that?? Is it possible to use an Array in a User Defined Aggregat?? Thanks
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
hi experts, i'm working in a web page for some statistics and i have a calendar where the customer can choose a day a week or a month and according to the date he select i need to query the database according to the date selected. what i want to know is how can i store the date for a day in a variable so i can call it from a stored procedure, the day actually is easy what i want is how can i store the whole week in a variable so i can give it to the stored procedure and query the data in the database according to the whole week may be with startday and endday also the same problem for the whole month, any idea how can i implement that in C#?!!! thanks
I am running a query using the MS Query Analyzer. I am inserting a large number of fields into a table, one of the fields is a date field where I enter the date (Hard Coded) that the query is run. I was wondering if it was possible to have the script get the date while it is running via the Date() function. I have tried the following:
Declare @DateVar SmallDateTime Set @DateVar = Date()
I am getting an error on the second line, so I guess the Date() function is not native to SQL. So is there another way of accomplishing this?
I'm trying to implement a query that will show all records from three months prior to a certain date (that the user will input) and that date.
This is what I wrote:
SELECT Transactions.Date, Transactions.Details, FROM Transactions WHERE Transactions.DATE Between [Forms]![FormNAME]![Date] And DateAdd("M",-3,[Forms]![FormNAME]![Date]);
The idea is that the user is presented with a form which has a single text box to input the date. Then he/she clicks a button (which runs the above query) and the list is presented.
However, when I try to run it, I get back ALL records before a certain date...not just for the three months prior to it.
Dear Experts, Actually, ineed a function to display wether the given date is working day or not............
id the given date is a sunday or saturday, then it should display like...holiday. or else it should say working day..
please help me in this regard....... one more thing, if the given date is sunday, then it should give the previous sunday date (minus seven days)
thank you in advance......
I'm trying with this function
CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') --Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 --Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END
I need to sort some data by date: my date format is looks like this 5/7/2007 11:38:54 AM. but i need to sort sort by just the first part "5/7/2007" how do i achieve this.?
i have just started working on SQL and i am trying to solve this puzzle maybe smoe one could do it.. i have to make a query such that it will take all the transactions throughout the day sort them with the accnt number and then give total amount in the transaction, also the total amount should be > 10000 if some one can try and locate the problem with hte logic i would appreciate it..
just for instance what i did was i tried using the convert in group by and then sum(deposit) this doesnt work.. i am really confused please help.
i have written this query:
declare @datelastweek as datetime declare @yesterday as datetime declare @date as datetime set @Date = Convert(varchar,GetDate() - 1,1) SET @datelastweek = DATEADD(Day, 1, (DATEADD(Week, -1, @Date))) SET @yesterday = dateadd(day,1,(dateadd(day,-1,@date)))
select accountno, sum(amount), convert (varchar, TransactionTime -1,1) from deposit tb join transaction t on tb.id=t.id where TxnTime between @datelastweek and @date group by DATEADD(d,DATEDIFF(d,0,TxnTime),0),t.Accountno, txntime having sum(tb.amount)>10000
hai to all, i need a help ,that if i give the UTCdate as parameter in my stored proc i have to get the output as getdate() likewise if i give the getdate() i have to get the o/p as Getutcdate()
Hi!I have a report that needs to be run on the seventh of every month forthe dates from 6th of the previous months to the 5th of the currentmonth. For example, I have to run a report on February 7th for the01/06/2007 to 02/05/2007. Right now I am doing it manually but I wascurious if there a function or something that will give me therequired date range on the 7th of every month.Any ideas?Thanks,T.
Hi, As we know all the job system tables store date(run_date) and time(run_time) seperate feild. I have the situation compare this run_date & run_time with another date feild column. my datefield = 2003-02-27 08:02:01.000
and my msdb system table has run_date =20030227 run_time =60002
How I can compare this two value?. Anybody has any advice about this?. Thanks, Ravi
i have a field that date stored in it, format of this date is somethin like timestamp. for example today(9Nov2005) saved as 38665. how can i convert this value to a normal date format? any function or sp?
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
while using following function I am getting this Error "Msg 195, Level 15, State 10, Line 1 'DATE' is not a recognized built-in function name."
select callid, DATE(segstart) AS calldate from October_CLI.dbo.raw Where callid >0 Segstart Column = Data with call date & time Callid Column = Unique Call Id October_CLI.dbo.raw = Table Calldate = Where i want the only date to be capture from segstart column
I hate manipulating dates in SQL. One of the many things Access and VB handles much better!
I wanted a function like DateSerial(Year,Month,Day) so I created one.
One caveat: The Year must be >= 1800. But all other numbers can be most any int value that results in a valid date.
Which means you can do:
MDate(Year(@d),1,1) (first day of date @d) MDate(Year(@d), Month(@d) + 1, -1) (last day of month of date @d) MDate(2000,1,1) (create a date quickly and easily w/o convert) MDate(Year(@d)+1,1,1) (get first day of next year for date @d)
..etc... whatever you can do with VB's DateSerial() function, except for the year must be >=1800.
Or, does this exist already in SQL and I'm missing it somewhere??
Is there an easier/better way to do this?
* * * *
Create function MDate(@Year int, @Month int, @Day int) returns datetime AS BEGIN declare @d datetime; set @d = dateadd(year,(@Year - 1800),'1/1/1800'); set @d = dateadd(month,@Month - 1,@d); return dateadd(day,@Day - 1,@d) END
I am trying to compare Effective_Date, Expiration_Date and Cancellation_Date for particular Policies in a mock DB for class.
Is there a way that I can compare these three dates (which are defined as smalldatetime) then return the Num_Claims for the particular policy period. The database currently is hard-coded (w/ insert statements) and there are some Cancellation_Dates that are before the Expiration_Date.
Any help is greatly appreciated.
Select Policy_ID, Num_Claims, Claim_Date from Policy right join Claim on Claim.Policy_ID@ = Policy.Policy_ID where Policy.Num_Claims >='5' Between Effective_Date and Expiration_Date or Effective_Date and Cancellation_Date ;