|
I am trying to get the number of opened accounts per month in. For the most part I can get this information but if there was no account opening for March then March does not show up in the list. Here is what I have.
select Months, count(*) as 'Account Openings' from
(
SELECT
CASE
WHEN opened >= '2007-01-01' and opened <= '2007-02-01' THEN 'January'
WHEN opened >='2007-02-01' and opened <= '2007-03-01' THEN 'February'
WHEN opened >= '2007-03-01' and opened <= '2007-04-01' THEN 'March'
WHEN opened >= '2007-04-01' and opened <= '2007-05-01' THEN 'April'
WHEN opened >= '2007-05-01' and opened <= '2007-06-01' THEN 'May'
WHEN opened >= '2007-06-01' and opened <= '2007-07-01' THEN 'June'
WHEN opened >= '2007-02-01' and opened <= '2007-08-01' THEN 'July'
WHEN opened >= '2007-08-01' and opened <= '2007-09-01' THEN 'August'
WHEN opened >= '2007-09-01' and opened <= '2007-10-01' THEN 'September'
WHEN opened >= '2007-10-01' and opened <= '2007-11-01' THEN 'October'
WHEN opened >= '2007-11-01' and opened <= '2007-12-01' THEN 'November'
ELSE 'December'
END AS Months
FROM account
WHERE opened >= '2007-01-01' AND opened <= '2007-12-31'
) as OpenedPerMonth
group by Months
order by Months
I would get something like this as output
April 12
August 44
February 55
January 19
July 12
June 11
March 42
May 11
September 6
Thanks
Matt
|
|
|
|
|
Have you turned the ANSI_NULLS OFF?
Blog link to be reinstated at a later date.
|
|
|
|
|
select Months, isnull(count(*),0) as 'Account Openings' from<br />
(<br />
SELECT<br />
CASE<br />
WHEN opened >= '2007-01-01' and opened <= '2007-02-01' THEN 'January'<br />
WHEN opened >='2007-02-01' and opened <= '2007-03-01' THEN 'February'<br />
WHEN opened >= '2007-03-01' and opened <= '2007-04-01' THEN 'March'<br />
WHEN opened >= '2007-04-01' and opened <= '2007-05-01' THEN 'April'<br />
WHEN opened >= '2007-05-01' and opened <= '2007-06-01' THEN 'May'<br />
WHEN opened >= '2007-06-01' and opened <= '2007-07-01' THEN 'June'<br />
WHEN opened >= '2007-02-01' and opened <= '2007-08-01' THEN 'July'<br />
WHEN opened >= '2007-08-01' and opened <= '2007-09-01' THEN 'August'<br />
WHEN opened >= '2007-09-01' and opened <= '2007-10-01' THEN 'September'<br />
WHEN opened >= '2007-10-01' and opened <= '2007-11-01' THEN 'October'<br />
WHEN opened >= '2007-11-01' and opened <= '2007-12-01' THEN 'November'<br />
ELSE 'December'<br />
END AS Months<br />
FROM account<br />
WHERE opened >= '2007-01-01' AND opened <= '2007-12-31'<br />
) as OpenedPerMonth<br />
group by Months<br />
order by Months
Hope it will help.
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.
|
|
|
|
|
why dont you use month() function 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
I need to retreive some info from an SQL2005 db, but am not knowledgeable in SQL to work out an efficient statement. Your help will be greatly appreciated.
I need to retreive data for a given user(db_User) whilst counting info from a second column(db_Hours).
However to make things a bit more difficult id like to count/sum all the (db_Hours) values as two named fields. The values of these named fields will be grouped according to a 3rd column(db_Valid) will either be 'yes', 'edit' or 'no'.
SO in other words id like one statement (if possible) that will get me all the values of (db_Hours) for a named user, as two variables. One being the sume of all the 'db_Hours' where 'db_Valid' is equal to "yes", and a second where 'db_Valid' is not equal to 'yes'.
I hope this makes sense, and that some kind person is able to help me out.
Many Thanks guys! 
|
|
|
|
|
Why don't you give a sample of the tables you are trying to pull from and the relevant fields that are in those tables.
I'm guessing you are going to have a statemnet that will include a Group By clause and Sum function, but we can't create it for you without specifics. It may end up to be something like this:
SELECT User, Valid, SUM(Hours) FROM MyTableName GROUP BY User, Valid
|
|
|
|
|
Hi I am trying to find out how many records I have between two time periods. I have the following code:
SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE (Scan_Time between '9/29/2008 9:00:00 AM' AND '9/29/2008 09:59:59 AM')
This will work and give me the number of records that are between the specified time ranges. However, I will be running this query each day and would like to not have to go in each day and specify the date. I will be clearing the database each day and thought that I could just remove the date portion, however, when I do this there are no records reported.
I am new to SQL and any help would be greatly appreciated
Thanks
.NET Framework Data Provider for SQL Server Mobile Edition
version 3.00.000
|
|
|
|
|
You could use DATEPART function and perhaps even GROUP BY hour (again by using datepart) if needed. This way you would get all counts for all hours in single statement.
The need to optimize rises from a bad design
|
|
|
|
|
Thanks for the idea Mika. I had thought about using DATEPART, but was not sure if I would be able to shorten the time period if I needed to i.e. between '9:50:00 AM' AND '09:59:59 AM'
|
|
|
|
|
You're welcome. Just use hh for the first parameter and use the DATEPART function in either WHERE clause or GROUP BY.
The need to optimize rises from a bad design
|
|
|
|
|
Thanks that worked for reporting at 1 hour intervals. Any idea how I could search between '9:50:00 AM' AND '09:59:59 AM'
|
|
|
|
|
If you want to use 10 minutes interval, you could use a trick like this:
SELECT (DATEPART( minute, GETDATE()) / 10)
It's not pretty, but gives you full 10 minutes (0-5)
The need to optimize rises from a bad design
|
|
|
|
|
create a stored procedure and make the user/app calling it pass in the dates they wanted to count between. or if you just wanted to do 2 queries datepart with MIN in place of HH would work the same, mm is month as i remind my self every so often when i use date part to mess with minutes.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Thanks for the help. Using the datepart and using a union to have 2 queries appears to work.
Thanks for the help
SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE datepart(hh,Scan_Time) between 09 and 09 union SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE datepart(mi,Scan_Time) between 50 and 59
|
|
|
|
|
listen,
check functions:
dateadd(),getdate(),datediff()
will do fine
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
hi to all,
required connection string in asp for sql server 2005 express
its urgent.
please help me out.
i tried many... they are
1. objConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=ip address;UID=kjkl;PWD=jhjk"
2.objConn.ConnectionString = "Provider=SQLNCLI;Server=ip address\SQLEXPRESS;Database=645654;UID=321321;PWD=23164;"
3.objConn.ConnectionString = "Provider=SQLOLEDB.1;UID=321321;Password=mypassword; Initial Catalog=mydatabase; Data Source=.\SQLExpress"
4. 'DSN="Driver={SQL Server};Server=ip address;Database=cfsdf;UID=35131;PWD=23131"
nothing works
help me plz
thanks
|
|
|
|
|
This issue is answered several times.
Even in this forum.
Even within few days.
So, scroll questions down a bit in this forum and you may find what you need.
The need to optimize rises from a bad design
|
|
|
|
|
i did wat u said. but when i'm saying that i tried many it means i search many and even this forum also. And i found nothing helpful.
so help me.
|
|
|
|
|
There are several articles in BOL how to construct a connection string. Also there's a separate ConnectionStringBuilder -class for this purpose. And this site has been referred many times: http://www.connectionstrings.com/[^]
If you have a valid connection string, but you cannot connect to SQL Server for some reason, provide the corresponding error message along with the connection string you used (of course after googling that it isn't any common problem). We may then be able to help you to locate the problem.
The need to optimize rises from a bad design
|
|
|
|
|
listen,
create a *.udl file in your desktop. check connection_string from there.
ok?
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi
I have written a stored procedure as follows:
ALTER PROCEDURE dbo.VehiclesInsert
(
@TypeId int = 4,
@Reg varchar = 15,
@Mileage decimal = 9,
@Price money = 8,
@Fuel int = 4,
@Colour varchar = 80,
@BodyShape int = 4,
@Doors int = 4,
@Warranty int = 4,
@MoreInfo text,
@RegLetter int=4,
@CreatedBy int=4
)
AS
INSERT INTO Vehicles
(TypeId,Reg,Mileage,Price,Fuel,Colour,BodyShape,Doors,Warranty,MoreInfo,RegLetter,CreatedBy)
VALUES
(@TypeId,@Reg,@Mileage,@Price,@Fuel,@Colour,@BodyShape,@Doors,@Warranty,@MoreInfo,@RegLetter,@CreatedBy)
RETURN
Although I have defined the Reg variable as Varchar 15, it only writes the first letter into the Reg field of my DB. I am using ASP.NET and c# to call the sproc - I even tried hard-coding the parameter value e.g:
objCmd.Parameters.Add("@Reg", "Test Reg");
However it still only adds "T" into the Reg field. I can manually add Test Reg into the field in the DB table so the field lengths all seem to be ok.
I'm very new to SProcs so please excuse me if I'm missing something obvious!
Thanks
Lorna
|
|
|
|
|
try make this changes:
.<br />
.<br />
.<br />
@Reg varchar (15),<br />
.<br />
.<br />
.<br />
@Colour varchar (80),<br />
.<br />
.<br />
.
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.
|
|
|
|
|
Thanks - that was the problem (I knew it would be something obvious  )
I actually had int = 4 in other SProcs so I've changed those values to just int now in case anyone else is interested and they all work fine.
|
|
|
|
|
Mmm. Blue boy is right, your equals to syntax is just assigning default values to the parameters. Your varchar is being declared with the default length of 1.
|
|
|
|
|
try this ok?
sql_cmd.Parameters.Add("@country", SqlDbType.varchar).Value = textbox_GrandPrixCountry.text;
what do you get from this???
Member 3402886 wrote: objCmd.Parameters.Add("@Reg", "Test Reg");
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
