I have written the following query which returns the number of orders received grouped by the year and month:
SELECT DATEPART(yyyy, order_placeddate) AS year, DATEPART(mm, order_placeddate) AS month, count(order_id) AS orders
FROM orders
GROUP BY DATEPART(yyyy, order_placeddate), DATEPART(mm, order_placeddate)
ORDER BY year, month
year month orders
---- ----- ------
2004 6 17
2004 7 37
2004 8 30
2004 9 42
2004 10 34
2004 11 46
2005 1 25
2005 2 7
2005 4 1
The obvious problem with the above is that it misses out the months that have no orders, i.e. December, March, May, etc.
Is there a way I can amend my query so that it shows all months regardless of whether any orders were placed?
I have thought about trying to LEFT OUTER JOIN the above to a table that has rows with values of 1 – 12, but I’m not convinced this is the answer... and I don’t really know how to do it!
Do let me know if any of the above is unclear – what I’m after is the following:
year month orders
---- ----- ------
2004 6 17
2004 7 37
2004 8 30
2004 9 42
2004 10 34
2004 11 46
2004 12 0
2005 1 25
2005 2 7
2005 3 0
2005 4 1
2005 5 0
Many thanks
I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.
I am attaching a sample of what I would like to accomplish (months are columns).
Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:
Oracle: select * from all_tables select * from user_tables
SQLserver: select * from sysobjects where type'='U'
So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.
------------------------------------------------------------------------------------------- ID Name Start Date End Date -------------------------------------------------------------------------------------------- 001 Project 1 2-2-2003 2-3-2007 002 Project 2 1-24-2003 2-6-2007 003 Project 3 4-10-2005 2-10-2008 004 Project 4 5-20-2006 6-6-2008 ... 015 Project 15 2-20-2006 3-3-2009 ---------------------------------------------------------------------------------------------- What I want is the the following output.
Output 1:
------------------------------------------------------------------------------------------------------------------------------------------------ Year Projects No. Of Projects Starting Months ------------------------------------------------------------------------------------------------------------------------------------------------ 2003 Project 1 , Project 2 2 February, January 2005 Project 3 1 April 2006 Project 4, Project 15 2 May , Feb ------------------------------------------------------------------------------------------------------------------------------------------------
(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...) and also the following
Output 2: (this is optional view...) ----------------------------------------------------------------------------------------- Year Start Date Project Name ----------------------------------------------------------------------------------------- 2003
February 2 Project 1 January 24 Project 2 2005 April 4 Project 3 2006 May 5 Project 4 February 20 Project 15 -------------------------------------------------------------------------------------------
I am very much in need of Output 1. Could someone help me,
I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.There is no date column in the table and the total amount has to be splitted equally.Guess I can't use Pivot here because the date column is not there ...How can I achieve this ?
CREATE TABLE #tbl_data ( Region Varchar(25), Amount FLOAT,
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2) AS month_name FROM MG_BOOKING AS m WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
I have a line chart which works great except when the x axis displays duplicate months. I have tried format codes of MM-YYYY and MMM and have the "numeric or time-scale values" checkbox checked. The data displays fine but the X axis in the case of the MMM format code displays as:
Sep Oct Oct Nov Nov
leaving the user to wonder where exactly does October and November start...
I've googled around but don't see off-hand what I am doing wrong. I am display 3 simultaneous lines if that matters...
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing( DepDate datetime, val int) insert into testing values ('2014-01-10 00:00:00.000', 1) insert into testing values ('2014-05-19 00:00:00.000', 10) insert into testing values ('2014-08-15 00:00:00.000', 20) insert into testing values ('2014-11-20 00:00:00.000', 30)
Sub Page_Load Dim dstPubs As DataSet Dim conPubs As SqlConnection Dim dadTitles As SqlDataAdapter Dim dtblTitles As DataTable Dim drowTitle As DataRow Dim objCommandBuilder As New SqlCommandBuilder
' Grab Titles Table dstPubs = New DataSet() conPubs = New SqlConnection( "Server='(local)';Database=Pubs;trusted_connection=true" ) dadTitles = New SqlDataAdapter( "Select * from Titles", conPubs ) dadTitles.Fill( dstPubs, "Titles" ) dtblTitles = dstPubs.Tables( "Titles" )
' Display Original Titles Table dgrdOriginalTitles.DataSource = dstPubs dgrdOriginalTitles.DataBind()
select TableName, IndexName from SomeSystemTables where TableName like 'Src%' and IndexName not like '_WA%'
I plan on using this in a cursor in order to delete all indexes. Then I rebuild all tables from an old VMS.Ingres database and create new indexes.
I've looked over previous posts on listing indexes, but don't get the method for identifying both the tablename and the indexname in the same query. DB is SQL2000.
Does anyone know of a system stored procedure that I can run to print a list of indexes for a specific datbase. I know I can do it for a specified table, but I would like it for all tables. Thanks!
I have a query that lists the names of all employees and the number of training modules they have sat. The query is below:
SELECT distinct pps_principals.name AS principals_name, COUNT(*) AS coursecount FROM (PPS_SCOS JOIN PPS_TRANSCRIPTS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID) JOIN PPS_PRINCIPALS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID AND PPS_TRANSCRIPTS.STATUS like '[PCF]' AND PPS_TRANSCRIPTS.TICKET not like 'l-%' and pps_scos.name like 'MT%' and pps_principals.login like '%testlogin%' and pps_transcripts.date_created between '2006-10-01' and '2007-09-30' GROUP BY pps_principals.name ORDER BY coursecount desc
The cont goes all the way down to those who have sat 1 module.
I now however need to be able to report all those names of individuals who have sat 0 courses.
id uname punchdate punchtime 1 Â A Â Â Â Â 1/1/2007Â Â 7:00am 1 Â A Â Â Â Â 1/2/2007Â Â 8:00am 1 Â A Â Â Â Â 1/4/2007Â Â 7:30am 1 Â A Â Â Â Â 1/6/2007Â Â 7:40am
let say i want to get a result which punchdate is from 1/1/2007 to 1/8/2007, how can i get a result like this one.?
1 Â Â AÂ Â 1/1/2007 Â Â 7:00am 1 Â Â AÂ Â 1/2/2007 Â Â 8:00am 1 Â Â AÂ Â 1/3/2007 Â Â <null> 1 Â Â AÂ Â 1/4/2007 Â Â 7:30am 1 Â Â A Â Â 1/5/2007 Â Â <null> 1 Â Â A Â Â 1/6/2007 Â Â 7:40am 1 Â Â A Â Â 1/7/2007Â Â <null> 1 Â Â A Â Â 1/8/2007 Â Â <null>
listing all data even if theres no punchdate and time in the table.
Still using SQL7.I am wondering how come there is not an Information_Schema view thatlists indexes? Information_Schema is supposed to be the safest way toobtain information on metadata, but it appears that the only way toget a list of indexes is with a system stored proc.
I copied a database from my production server to the development server and now i dont see the users in the database from the enterprise manager,database and users folder. But, When i run a query to against the sysuser table from a query analyser I can see those users here. Why cant I see in from the Enterprise manager. Any advice please..
Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.
I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.
I'm suppose to List all orders where there is no shippedDate (just give their orderNumbers).
I have this: select orderNumber from orders where shippedDate= null;
I'm not sure what to put in place of the "= null" as that is clearly wrong
(Note: If I use "select orderNumber from orders where shippedDate;" it prints out all the orders, but I only want the orders where there are no ShippedDate...
I'm having trouble with an ODBC User that can't connect to 2003Windows Server with SQL2000.The ODBC fails when he tries to contact with TCP client connection.When I view the Network Connection it is displaying TCP is checked,but when I view the error log is doesn't display that it is listeningfor TCP.Is there any way to prove that my TCP connections are workingcorrectly or a way to make sure that my SQL server is listening forTCP?Thanks for reading my note and any help with this issue is greatlyappreciated.
I have 100s of packages under one project and finding it difficult to locate a package since it is not in any order. Is there a way to list packages by its name - alphabetically sort ?
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
where CCU.constraint_name = RC.constraint_name
order by CCU.table_name, CCU.constraint_name
I can't figure out what the problem is, the ones that are being left out of the first query ad the ones where the unique_constraint is set to the column name, not the constraint's name. For example, the first one appears in both query results, the second one only appears in the secod queries results:
src_table src_constraint src_col UNIQUE_CONSTRAINT_NAME CONSTRAINT_NAME UPDATE_RULE DELETE_RULE awards awards_FK00 award_type aaaaaaward_types_PK awards_FK00 NO ACTION NO ACTION awards awards_FK01 personid personid awards_FK01 NO ACTION CASCADE
I have installed SQL Server Express on several machines on my network using same command line, configuring remote connections to be allowed. I have also added SQL Server and SQL Browser to the exceptions list in Windows Firewall.
All the installed intances in the network are listed when using SMO enumeration to browse through the servers.
But, in one of the machines, only the local SQL Express Instance is listed. The same is the case when trying from SQL Server Management Studio. But when I manually type the remote instance name it successfully connects.
I also found that adding SQL Management Studio to the list of exceptions in the Firewall solves the problem. But I don't want to install Management Studio in my client machines.
I have a little problem that I just haven't been able to solve. I don't think it is very difficult but I can't seem to make it work. Here's the scenario:
I have a database with the following values: Model Make Price DA1100 GTN$88.00 DA1100 GTN $100.00 DA1000 GBN$110.00 DA668 GTN$100.00 DA880 GTN$200.00
In this case DA1100 is listed twice with 2 different prices. I only want to display the one with the lowest price. So the result I want is: Model Make Price DA1100 GTN$88.00 DA1000 GBN$110.00 DA668 GTN$100.00 DA880 GTN$200.00
I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.
What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.
Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?
Does anybody have any alternative approach to this problem?
Is it possible to get words from text columns in a sql server database in the order of their occurances in that column with full text search or by any other method.