December 7, 2012 at 10:15 pm
Hello all but i'm trying to make a very basic query and i'll give an example of the db here but what I have is a table basically with some data such as ordernum, apples, oranges, bananas, orderdate. And what I would like to have a summary view where each row would be a month Jan, Feb, March, then we would have columns where it would give the sum of the number of apples purchased in Jan etc.
So the DB would look like
ordernum apples oranges bananas orderdate
1 1 1 0 '2012-1-14'
2 1 0 5 '2012-3-1'
3 1 1 1 ' 2012-4-22'
then the report would return
month apples oranges bananas
Jan 1 1 0
Feb 0 0 0
Mar 1 0 5
Apr 1 1 1
hopefully it's as simple as I think but i'm new to this stuff so any help is greatly appreciated.
December 8, 2012 at 4:16 am
Welcome to SSC.
The Query is fairly straight forward, all you're doing is grouping and summing the data up by the Month so this should work.
Select
DateName(Month,OrderDate) [Month]
,Sum(Apples) as TotalApples
,Sum(Oranges) as TotalOranges
,Sum(Bananas) as TotalBananas
From
[Order]
Group by
DateName(Month,OrderDate)
There are a few flaws with this in that if you have mutiple years then you also need to provide a Year to distinguish monthly sales between different years.
You may also want to consider normalising the table a little by adding Product and OrderLine Tables t to the DB, so that you can scale out easier, eg something like this
CREATE TABLE #Order (
OrderNumber Int NOT NULL PRIMARY KEY IDENTITY(1,1) NOT FOR REPLICATION
,OrderDate Int NOT NULL
)
CREATE TABLE #OrderLine(
OrderLineNumber Int NOT NULL PRIMARY KEY Identity(1,1) NOT FOR REPLICATION
,OrderNumber Int NOT NULL --FK to Order column
,ProductId Int NOT NULL -- FK to Product
,OrderQty Int NOT NULL
,OrderPrice Decimal(18,2) NOT NULL
)
CREATE TABLE #Product(
ProductId Int NOT NULL PRIMARY KEY Identity(1,1) NOT FOR REPLICATION
,ProductName varchar(100) NOT NULL
,ProductPrice Decimal(18,2)
)
I would suggest buying a book on database design and normalisation for more details on best practices.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 8, 2012 at 10:05 am
Thank you so much for your response, I relized you answered exactly what I asked but what I asked was close to what I wanted but a little to different to work with. So the background on this, it's not my DB but i'm trying to do some custom reporting off of it. so I'll show what I do have and you were absolutely right i'm going to need to get the year and month as well for some ordering and it is a multi-year db. I'm also going to post 5 rows of the data from this db (obviously it's not my db so I can't modify the db itself) and some of these DBs do have upwards of 50,000 rows.
Select
DateName(Month,dateopened) [Month],
(select Count(workorderid) from workorder where pmprojectid is null and Archived=0 and IsDeleted=0) as WOs,
(select Count(workorderid) from workorder where pmprojectid>0 and Archived=0 and IsDeleted=0) as PMs
From
workorder
Group by
DateName(Month, DateOpened)
there's the current query and here's some of the data...
WorkOrderIDWorkOrderIdentifierDateOpenedDateClosedPMProjectIDNoteIdentifierArchivedIsDeleted
46512_0026872012-08-02 02:14:59.0002012-08-11 01:53:21.000NULL823600
46612_0026882012-08-02 06:00:41.0002012-09-11 17:51:55.00014823900
46712_0026892012-08-02 06:00:43.0002012-09-11 17:50:47.00014824200
46812_0026902012-08-02 06:00:44.0002012-09-11 17:49:36.00014824500
46912_0026912012-08-02 06:00:45.0002012-09-11 17:48:08.00014824800
December 8, 2012 at 6:15 pm
blackhawkpowers (12/8/2012)
there's the current query and here's some of the data...WorkOrderIDWorkOrderIdentifierDateOpenedDateClosedPMProjectIDNoteIdentifierArchivedIsDeleted
46512_0026872012-08-02 02:14:59.0002012-08-11 01:53:21.000NULL823600
46612_0026882012-08-02 06:00:41.0002012-09-11 17:51:55.00014823900
46712_0026892012-08-02 06:00:43.0002012-09-11 17:50:47.00014824200
46812_0026902012-08-02 06:00:44.0002012-09-11 17:49:36.00014824500
46912_0026912012-08-02 06:00:45.0002012-09-11 17:48:08.00014824800
Howdy!
Welcome aboard! If you'll take the time to post readily consumable data IAW the first link in my signature line below, you'll likely get tested code back and get it very quickly.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2012 at 6:01 pm
thanks for the pointers so here's what we've got now based on your recommendations
/****** Object: Table [dbo].[WorkOrder] Script Date: 12/09/2012 16:21:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WorkOrder](
[WorkOrderID] [bigint] IDENTITY(1,1) NOT NULL,
[WorkOrderIdentifier] [varchar](255) NOT NULL,
[DateClosed] [datetime] NULL,
[DateOpened] [datetime] NULL,
[PMProjectID] [bigint] NULL,
[IsDeleted] [bit] NOT NULL,
[Archived] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[WorkOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[WorkOrder] WITH CHECK ADD CONSTRAINT [FK_WorkOrder_PMProject] FOREIGN KEY([PMProjectID])
REFERENCES [dbo].[PMProject] ([PMProjectID])
GO
ALTER TABLE [dbo].[WorkOrder] CHECK CONSTRAINT [FK_WorkOrder_WorkOrder]
GO
ALTER TABLE [dbo].[WorkOrder] ADD CONSTRAINT [DF_WorkOrder_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO
ALTER TABLE [dbo].[WorkOrder] ADD CONSTRAINT [DF__WorkOrder__Archi__105BFE92] DEFAULT ((0)) FOR [Archived]
GO
and the insert
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Workorder ON
--===== Insert the test data into the test table
INSERT INTO #Workorder
(WorkorderID, WorkorderIdentifier, DateOpened, DateClosed, Archived, IsDeleted, PMProjectID)
SELECT '999','12_003221','Oct 1 2012 6:00AM','Oct 3 2012 12:10PM','0','0','22' UNION ALL
SELECT '998','12_003220','Oct 1 2012 6:00AM','Oct 2 2012 8:28PM','0','0','22' UNION ALL
SELECT '997','12_003219','Oct 1 2012 6:00AM','Oct 2 2012 7:37PM','0','0','22' UNION ALL
SELECT '996','12_003218','Oct 1 2012 6:00AM','Oct 2 2012 7:34PM','0','0','22' UNION ALL
SELECT '995','12_003217','Oct 1 2012 6:00AM','Oct 2 2012 7:25PM','0','0','22' UNION ALL
SELECT '994','12_003216','Oct 1 2012 6:00AM','Oct 2 2012 7:23PM','0','0','22' UNION ALL
SELECT '993','12_003215','Oct 1 2012 6:00AM','Oct 9 2012 3:56PM','0','0','23' UNION ALL
SELECT '992','12_003214','Oct 1 2012 6:00AM','Oct 9 2012 8:09PM','0','0','23' UNION ALL
SELECT '991','12_003213','Oct 1 2012 6:00AM','Oct 10 2012 6:47PM','0','0','23' UNION ALL
SELECT '990','12_003212','Oct 1 2012 6:00AM','Oct 8 2012 5:02PM','0','0','23' UNION ALL
SELECT '989','12_003211','Oct 1 2012 6:00AM','Oct 8 2012 4:56PM','0','0','23' UNION ALL
SELECT '988','12_003210','Oct 1 2012 6:00AM','Oct 8 2012 2:37PM','0','0','17' UNION ALL
SELECT '985','12_003207','Sep 30 2012 6:00AM','Oct 2 2012 3:18PM','0','0','1' UNION ALL
SELECT '984','12_003206','Sep 30 2012 6:00AM','Oct 2 2012 3:16PM','0','0','1' UNION ALL
SELECT '966','12_003188','Sep 28 2012 5:00AM','Oct 1 2012 4:21PM','0','0','2' UNION ALL
SELECT '908','12_003130','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '907','12_003129','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '906','12_003128','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '905','12_003127','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '904','12_003126','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '903','12_003125','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '902','12_003124','Sep 25 2012 5:00AM','Sep 25 2012 3:08PM','0','0','1' UNION ALL
SELECT '901','12_003123','Sep 25 2012 5:00AM','Sep 25 2012 3:12PM','0','0','1' UNION ALL
SELECT '900','12_003122','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','18' UNION ALL
SELECT '899','12_003121','Sep 25 2012 5:00AM','Sep 27 2012 4:11PM','0','0','18' UNION ALL
SELECT '884','12_003106','Sep 24 2012 6:00AM','Sep 27 2012 4:11PM','0','0','22' UNION ALL
SELECT '883','12_003105','Sep 24 2012 6:00AM','Sep 27 2012 4:11PM','0','0','22' UNION ALL
SELECT '882','12_003104','Sep 24 2012 6:00AM','Sep 27 2012 4:11PM','0','0','22' UNION ALL
SELECT '881','12_003103','Sep 24 2012 6:00AM','Sep 27 2012 4:11PM','0','0','22'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Workorder ON
December 10, 2012 at 12:49 am
Thanks for the table scripts though you dont need the FK as we dont have the table it references.
You original query wont work as this sums all rows, regardless of period, so you also need to add a filter to restrict the query on period.
The following query will give you the correct totals.
Select
DateName(Month,dateopened) [Month],
(select Count(workorderid) from #workorder
where pmprojectid is null
and Archived=0
and IsDeleted=0
AND DateName(Month,dateopened)=DateNAme(Month,wo.dateopened)) as WOs,
(select Count(workorderid)
from #workorder
where pmprojectid>0
and Archived=0
and IsDeleted=0
AND DateName(Month,dateopened)=DateNAme(Month,wo.dateopened)) as PMs
From
#workorder wo
Group by
DateName(Month, DateOpened)
However it can also be re-written to run without the co-related Sub-queries, simply by doing this
Select
DateName(Year,dateopened) Year
,DateName(Month,dateopened) Month
,SUM(CASE when pmprojectid is null and Archived=0 and IsDeleted=0 Then 1 Else 0 end) WOs
,SUM(CASE when pmprojectid>0 and Archived=0 and IsDeleted=0Then 1 Else 0 end) PMs
From #WorkOrder
Group by
DateName(Year,dateopened)
,DateName(Month,dateopened)
Regards.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply