SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help With Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

therealrobstone
Starting Member

USA
6 Posts

Posted - 11/04/2013 :  10:28:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 11/04/2013 :  12:48:25  Show Profile  Reply with Quote
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?
Go to Top of Page

therealrobstone
Starting Member

USA
6 Posts

Posted - 11/04/2013 :  13:26:46  Show Profile  Reply with Quote
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?


Edited by - therealrobstone on 11/12/2013 09:39:59
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/12/2013 :  12:49:50  Show Profile  Reply with Quote
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
Go to Top of Page

yoursqlmohan
Starting Member

India
1 Posts

Posted - 11/15/2013 :  02:40:16  Show Profile  Reply with Quote
Thanks for the help

Mohan Kumar Mani
Yoursqlman
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000