Thursday, May 23, 2013

[SQL Server] Sort by Calendar month

[SQL Server] Sort by Calendar month

Sort by Calendar month

Posted: 23 May 2013 03:27 AM PDT

All;I am having an issue where my rows are calendar months and my columns are different vendors. I can get the information, but the months are sorted alphabetically not in calendar order. Here is my code:I am having the same issue, but I'm using case statements to get the month names.: SELECT Category, COUNT(Category) AS CatCount, (CASE WHEN Month(OpenDate) = 1 THEN 'January' WHEN Month(OpenDate) = 2 THEN 'February' WHEN Month(OpenDate) = 3 THEN 'March' WHEN Month(OpenDate) = 4 THEN 'April' WHEN Month(OpenDate) = 5 THEN 'May' WHEN Month(OpenDate) = 6 THEN 'June' WHEN Month(OpenDate) = 7 THEN 'July' WHEN Month(OpenDate) = 8 THEN 'August' WHEN Month(OpenDate) = 9 THEN 'September' WHEN Month(OpenDate) = 10 THEN 'October' WHEN Month(OpenDate) = 11 THEN 'November' WHEN Month(OpenDate) = 12 THEN 'December' END) AS mopendate FROM Cloud_Tickets WHERE (YEAR(OpenDate) = '2013') GROUP BY MONTH(OpenDate), Category ORDER BY MONTH(OpenDate) Also, if there is a better way to do this, please enlighten me. I am a novice to programming. Thank you Brian

Result should display only certain information in select command

Posted: 23 May 2013 03:46 AM PDT

I am new to SQL enviornment. I have question regarding using select command. Current SQL command Select caseid, casetype, Casesubtype and casedescription field from table. The report comes out in the following format (e.g) .caseid Casetype casesubtype casedescription1234586 customercomplaint Billing Customer called to complaint they called at 2.00 PM , they had to leave the message on answering machine 4578555 Billinginfo water call came at 10.00 PM to pay the bill 7854555 Billininfo water1 customer called to zxcvbbbbbbbbbbbbbb End user requested they would like to see only selected information in casedescription field like 2.00PM, 10.00 PM if time is not enter then provide all the informationcaseid Casetype casesubtype casedescription1234586 customercomplaint Billing 2.00 PM4578555 Billinginfo water 10.00 PM 7854555 Billininfo water1 customer called to zxcvbbbbbbbbbbbbbbcasedescription field in free form field. (1000 charchater)please advise. Thank you

Sending a Null Value in a Store Proc

Posted: 23 May 2013 02:25 AM PDT

Newbie question here. But...if I don't ask, I dont learn.Writing and testing a new SP which will insert data to a table. While testing, I'm trying to essentially send NULL (is that even possible) since the field accepts Null values. For example....EXEC sp_InsertCustomer@strFirstName = 'Peter',@strLastName = 'Parker',@strAddress1 = '1000 Street',@strAddress2 = '#500',@strAddress3 = '',@strCity ...etc...The problem is @strAddress3 is sending an empty string. I do not want that. I want nothig/Null to go into that field.I've tried various versions of this:@strAddress3 = Null,@strAddress3 Null,@strAddress3 Is Null,..etc...but doesn't work.If address3 is truly Null, should I just omit this declaration and one line of code? Or is there way to test using a Null?

mirroring intervals for automatic failover

Posted: 22 May 2013 07:44 PM PDT

Hi,in my production server i configured mirroring with automatic fail over in sql server 2008 r2 can anyone tel me how much interval want to set as per standard my database size is 250 gb

Check 2 date fields for a particular date?

Posted: 22 May 2013 04:53 PM PDT

Hi everyone, I've created the table below to keep a record of office staff's annual leave (or vacation) each line in the table consists of the persons name, their staff code, the start of their leave and then the end date of their leave. I need to query the data in 2 ways, first of all I need to supply a date and return everyone that is off on that day, the second query I need to supply a person's name and a date to see if that individual is off on that date. Is this even possible? I don't even know where to start. Can some kind soul point me in the right direction please?[code]USE resource GO CREATE TABLE leavecard(wtname VARCHAR(50), ein VARCHAR(20),startdate datetime,endate datetime,RecID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ) GOINSERT INTO leavecard(wtname,ein,startdate,endate) VALUES('Chris Searle','802502270','01/Apr/2013','20/Apr/2013') INSERT INTO leavecard(wtname,ein,startdate,endate) VALUES('Mike Smith','802502276','03/Feb/2013','12/Feb/2013')INSERT INTO leavecard(wtname,ein,startdate,endate) VALUES('Tony Jones','802502277','05/Jan/2013','11/Jan/2013') INSERT INTO leavecard(wtname,ein,startdate,endate) VALUES('Mike Smith','802502276','28/Feb/2013','02/Mar/2013')INSERT INTO leavecard(wtname,ein,startdate,endate) VALUES('Tony Jones','802502277','20/Feb/2013','22/Feb/2013')INSERT INTO leavecard(wtname,ein,startdate,endate) VALUES('Tony Jones','802502277','19/Apr/2013','01/May/2013')INSERT INTO leavecard(wtname,ein,startdate,endate) VALUES('Chris Searle','802502270','23/Apr/2013','09/May/2013')[/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog