Help With Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help With Stored Procedure

Author

Topic

therealrobstone
Starting Member

6 Posts

Posted - 2013-11-04 : 10:28:36

Trying to get the Select Count to appear in the the last column of the Results. Right now it is appearing in a separate Results window. Sorry for the newbie question. I just can't figure it out. Thanks for the help.

USE [TraceSystem_DEV]
GO
/****** Object: StoredProcedure [dbo].[QuantityParts14] Script Date: 11/4/2013 10:17:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[QuantityParts14](@p_StartDT DATETIME, @p_EndDT DATETIME, @p_PartNumber VARCHAR(128))
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT l.ShiftsDate, l.ShiftNum, l.StartDT, l.EndDT, p.PartNumber FROM ListShifts (@p_StartDT, @p_EndDT) l
INNER JOIN dbo.PartType p ON p.PartNumber = @p_PartNumber
INNER JOIN dbo.TestResult t ON p.ID = t.PartTypeID AND t.StationID='7' OR t.StationID='8' WHERE t.TestDT BETWEEN l.StartDT AND l.EndDT
SELECT COUNT(t.StationID) AS SumPartsTotals FROM dbo.TestResult t
WHERE StationID IN (7, 8)
;
END

-------------

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-04 : 12:48:25

You are seeing two result sets because you have two selects. They can be combined into one - but, the first result set appears to be the details, and the second one is the count. If you put them together, what will/should it look like? The thing to remember is that the result set from a query has to be of tabular form - with the same number of columns in every row. Given that, did you want to append the count to each row in the first result set?

therealrobstone
Starting Member

6 Posts

Posted - 2013-11-04 : 13:26:46

Hi James,

I need a query to determine how many parts are being produced per shift on the various product lines (4014/4015/4029/4019 only).

I need the query to get the following data:

Query Column / Data type / Description

ShiftDate / DATE /Date of the shift (i.e. 2013-01-01)

Shift / VARCHAR(10)/ Shift (i.e. 1st, 2nd, or 3rd)

StartDT / DATETIME / The date and time that the shift begins (i.e. 2013-01-01 07:00:00.000)

EndDT / DATETIME / The date and time that the shift ends (i.e. 2013-01-01 15:00:00.000)

PartNumber / VARCHAR(10) / Part number being counted (i.e. 4014, 4015, 4029, or 4019)

QtyParts / INT / Number of unique parts to be tested during the shift

QtyFTT / INT / Number of unique parts that passed the first time through during the shift

FTTRate / DECIMAL(8,5)/ Percentage of unique parts that passed through the first time through during the shift

I need the result to be one row with all of the columns listed above. QtyFTT is the Count. Once I get this portion working I need to add the FTTRate column.

The query should find the values for the past 90 days.

quote:
Originally posted by James K

You are seeing two result sets because you have two selects. They can be combined into one - but, the first result set appears to be the details, and the second one is the count. If you put them together, what will/should it look like? The thing to remember is that the result set from a query has to be of tabular form - with the same number of columns in every row. Given that, did you want to append the count to each row in the first result set?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 12:49:50

I think this is for report. then why not do aggregation in report end by adding the sum expression in group/table footer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

yoursqlmohan
Starting Member

1 Post

Posted - 2013-11-15 : 02:40:16

Thanks for the help

Mohan Kumar Mani
Yoursqlman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources