|
Hi,
I have a column named posted date,
Depending on the above column I need to count No of records per year and
month wise asc.
I done by using group by function but am not able to get jan --- desc
order.
Result ie:MonthName,Year,Count columns
|
|
|
|
|
Show your code what you did so far.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi,
I have below records in my table 'Sales'
ID SalesName LastUpdated
-------------------------------------------------
1 S1 2009-07-16 13:27:23.890
2 S2 2009-08-16 13:32:58.127
3 S3 2009-08-16 13:33:01.987
4 S4 2009-08-16 13:34:21.733
5 S5 2009-09-16 13:32:40.703
6 S6 2011-01-16 13:32:23.703
7 S7 2011-02-16 13:32:23.703
8 S8 2011-02-16 13:32:23.703
9 S9 2011-03-16 13:32:23.703
10 S10 2011-07-16 13:32:23.703
--------------------------------------------------
Below query gives me the count of sales for different months on yearly basis.
SELECT DISTINCT MonthCount = (SELECT COUNT(DATEPART("mm",LastUpdated)) FROM Sales
WHERE DATEPART("yy",LastUpdated) = DATEPART("yy",p.LastUpdated)
AND DATEPART("mm",LastUpdated) = DATEPART("mm",p.LastUpdated)) ,
"Month" = DATENAME(Month,LastUpdated),"Year"=DATEPART("yy",p.LastUpdated)
FROM Sales p ORDER BY DATEPART("yy",p.LastUpdated)
Here is my desired result:
MonthCount Month Year
-----------------------------
1 July 2009
1 September 2009
3 August 2009
1 January 2011
1 July 2011
1 March 2011
2 February 2011
|
|
|
|
|
I have several servers. Server 1 is SQL Server 2000, Servers 2 and 3 are SQL Server 2005. I have linked 2 (2005) to 3 (2005) and 1 (2000) to 2 (2005). I have two windows logins that are in the sysadmin role on all servers. Neither login is an owner of a database. Both logins are members of the builtin/administrators group. Login A can connect to server 1 (2000) in mgmt studio and see all the databases on server 2 (2005). Login B connects to server 1 (2000) in mgmt studio but can only see one database on server 2 (2005). But both can connect to server 2 (2005) and see all the databases in the linked server 3. Server 1 (the SS2000 box) has a number of windows logins mapped to the sa, and one sql login mapped to a local login on server 2. When I change the "...be made using this security context" to sa, it instead inserts the local login.
I probably didn't explain this very well, but it has given me a migraine. Any idea what's going on here?
My other signature is witty and insightful.
|
|
|
|
|
goodideadave wrote: I probably didn't explain this very well
I don't think it is possible to explain your problem clearly.
I have a number of cardinal rules I apply when building a solution one of them is absolutely NO linked servers, now I remember why.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
They are very fond of linked servers here.
So how do you include data in tables on server 2 in a join executing on server 1? We've had to spread data out among different servers due to not having enough disk space on any one box. We're maxed out on our drives and can't afford to buy into the mother ship's SAN. Do you replicate the data between servers?
My other signature is witty and insightful.
|
|
|
|
|
I work for the mothership and disk space is not an issue. Now I understand why you have such a horrible setup.
I would think replication would not be a good solution with your space issue. That leaves your solution, linked server. I'd kill the lot and set up 1 id across all environments purely for this requirement. None of them integrated.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a simple table with 3 fields: ID, Tag1, Data
ID Tag1 Data
________________________________
1 2 6/1/2009 22:00
2 1 6/1/2009 23:00
3 2 6/2/2009 6:00
4 3 6/2/2009 7:00
5 2 6/2/2009 8:00
6 2 6/2/2009 9:00
7 2 6/2/2009 10:00
8 1 6/2/2009 11:00
9 2 6/2/2009 11:30
10 1 6/2/2009 13:00
11 2 6/2/2009 14:00
12 2 6/2/2009 15:00
13 2 6/2/2009 16:00
14 1 6/2/2009 17:00
15 2 6/2/2009 18:00
16 1 6/2/2009 19:00
17 1 6/2/2009 20:00
18 2 6/2/2009 21:00
19 2 6/2/2009 22:00
20 3 6/2/2009 23:00
21 1 6/2/2009 23:59
22 2 6/3/2009 1:00
23 3 6/3/2009 2:00
24 2 6/3/2009 3:00
25 3 6/3/2009 4:00
26 2 6/3/2009 5:00
27 3 6/3/2009 6:00
28 2 6/3/2009 7:00
29 3 6/3/2009 8:00
30 2 6/3/2009 9:00
31 3 6/3/2009 10:00
32 2 6/3/2009 11:00
33 1 6/3/2009 11:55
34 2 6/3/2009 22:00
35 2 6/3/2009 23:00
Must I do a average AVG(Tag1) after 3 hourly intervals over a period of time (3 tours).
Hourly intervals are:
10.30 PM - 6.30 AM, (represents tour1)
06.30 AM - 14.30 PM, (represents tour2)
14.30 PM - 22.30 PM, (represents tour3)
I tried this:
SELECT
AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'10:30:00 PM') AND DATEPART(hour,'06:30:00 AM') THEN Tag1 ELSE NULL END) AS tour1,
AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'6:30:00 AM') AND DATEPART(hour,'2:30:00 PM') THEN Tag1 ELSE NULL END) AS tour2,
AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'2:30:00 PM') AND DATEPART(hour,'10:30:00 PM') THEN Tag1 ELSE NULL END) AS tour3
FROM dbo.TableTest
WHERE (DataTime BETWEEN '6/1/2009 10:00:00 PM' AND '6/3/2009 11:00:00 PM')
GROUP BY DATEPART(DAY,DataTime)
The result looks like:
tour1 tour2 tour3
__________________________________________________________
NULL NULL 1.0
NULL 2.2222222222222223 1.7777777777777777
NULL 1.7142857142857142 2.0
Note:
For tour1 and tour2, belonging on the same day, is well calculated. The problem is tour1 - time which belongs to two days.
Can help me someone in this problem?
Thanks.
Regards,
Robert
|
|
|
|
|
Hi,
I'm no SQL expert however IMO DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'10:30:00 PM') AND DATEPART(hour,'06:30:00 AM') can't be right since that ignores the half hours completely.
Can't you just use some function to get the time, something like TIME(DataTime) NOT BETWEEN '06:30:00' AND '22:30:00'?
BTW: I felt a need to eliminate the 12hour timing, and to invert the statement.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Thanks for your answer.
Unfortunately, I can't waive these time intervals.
What I don't understand is why
SELECT ID
FROM dbo.TableTest
WHERE Data BETWEEN CONVERT(DATETIME,(CONVERT(varchar(10),Data, 101)+ ' 10:00:00 PM'),101) AND CONVERT(DATETIME,(CONVERT(varchar(10),DATEADD(DAY,1,Data), 101)+ ' 06:00:00 AM'),101)
return this: and not return:
ID ID
__ __
1 1
2 2
19 3
20 19
21 20
34 21
35 22
23
24
25
26
27
34
35
Regard,
Robert
|
|
|
|
|
Hello everyone;
I am not asking for Code or anything to time consuming , just would love to have some direction/web guides.
I've recently started a new job and my boss wants me to implement user account into their current website. I'll need to collect use information, send a verification email and than store their information.
a few problems i have at the moment
-Unsure of who is hosting the website, but i do have an ftp upload to it through fetch.
-office runs on macs
Any help would be very appreciated.
Thank you.
- Droz -
___ ___
/\/\ /\ | |
|
|
|
|
|
Not sure if this question would be more suited to the web dev forum.
What language is the current web site developed in?
What DB does it use?
|
|
|
|
|
The current website is using HTML/CSS/small amounts of javascript. I can recode the site if needed, i'll have to add a lot to it for the account things anyhow. I'll copy this to the web development forum also.
Thank you for any advice you are able to share.
- Droz -
___ ___
/\/\ /\ | |
|
|
|
|
|
|
Hello All,
I know it is out of scope of this group but I was wondering if anybody knows about AS400 whether or not it has a system database holding user_defined item names (tables fields etc).
Thanks alot.
|
|
|
|
|
Yes, it has all this information available in what is called the system catalog. You can get hold of the information through native commands like DSPFD or DSPFFD but you can also get hold of it via SQL queries if that's better for you. I'm a bit rusty with DB2/400 so I don't remember the exact table names to query but if you google for "AS/400 system catalog" you should find what you're looking for.
|
|
|
|
|
|
how to know current date and time.(i.e) date and time may vary across countries.how sql server will find the correct date and time.
|
|
|
|
|
Convert your DateTime to GMT; you'd convert every date you get from the user to GMT before sending it to the database. When reading the datetime from the database, adjust to the local timezone of the user. More information can be found on MSDN[^].
Good luck 
I remain troll
|
|
|
|
|
|
Hi..
You can use the following:
SELECT GATEDATE() AS CURRENT_DATE
Md. Marufuzzaman
|
|
|
|
|
It would be "SELECT GETDATE()" but that would not work if the user is in a different timezone.
|
|
|
|
|
how to display exactly 2 digits after decimal point in money???
|
|
|
|
|
If you mean when selecting a value in SQL Server (using TSQL), you can use cast like this:
select cast([FIELDNAME] as decimal(10,2))
|
|
|
|
|
Check out STR function in BOL.
My other signature is witty and insightful.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
