December 19, 2007 at 6:06 am
Please take a look at the table data along with the structure in the attached excel sheet[tblBuildPlan.xls].
You can upload this to your SQL Server.
I want this data should come like Sample_Output.xls
For example after uploading the data into a table you need to write a query and for that query i give day as input then it should show the result for that by grouping as shown in Sample_Output.xls and if i give weeknumber then it should do the grouping for whole week and show the data.
If anyone is interested to do this project, you can do and let me know... I'm also doing ||ly but i am not expert so i can compare myself with experts query....
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
December 19, 2007 at 7:25 am
Use logarithms.
1*2*3*4*5*6 = 720
DECLARE@Sample TABLE (i INT)
INSERT@Sample
SELECT1 UNION ALL
SELECT2 UNION ALL
SELECT3 UNION ALL
SELECT4 UNION ALL
SELECT5 UNION ALL
SELECT6
SELECTEXP(SUM(LOG(i)))
FROM@Sample
N 56°04'39.16"
E 12°55'05.25"
December 19, 2007 at 7:45 am
DECLARE@Sample TABLE (SegmentID INT, TestType VARCHAR(30), FirstYield NUMERIC(12, 4), Hour INT, DayDate DATETIME)
SET DATEFORMAT MDY
INSERT@Sample
SELECT4, 'QA Inspection', 100.00, 12, '11/13/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 12, '11/16/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 11, '11/26/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 15, '11/26/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 16, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 8, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 10, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 12, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 13, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 15, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 16, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 17, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 18, '11/26/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 20, '11/26/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 9, '11/27/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 10, '11/27/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 16, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 6, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 7, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 9, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 10, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 12, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 14, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 15, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 16, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 18, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 19, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 20, '11/27/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 21, '11/27/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 12, '11/28/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 15, '11/28/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 16, '11/28/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 9, '11/28/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 10, '11/28/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 11, '11/28/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 12, '11/28/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 9, '11/29/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 10, '11/29/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 11, '11/29/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 12, '11/29/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 15, '11/29/2007' UNION ALL
SELECT4, 'QA Inspection', 100.00, 7, '12/1/2007' UNION ALL
SELECT4, 'Test Station', 100.00, 11, '12/1/2007' UNION ALL
SELECT5, 'BTS Testing', 100.00, 13, '12/11/2007' UNION ALL
SELECT5, 'BTS Testing', 100.00, 14, '12/11/2007' UNION ALL
SELECT5, 'BTS Testing', 100.00, 15, '12/11/2007' UNION ALL
SELECT5, 'BTS Testing', 100.00, 16, '12/11/2007' UNION ALL
SELECT5, 'BTS Testing', 80.00, 17, '12/11/2007' UNION ALL
SELECT5, 'BTS Testing', 100.00, 19, '12/11/2007' UNION ALL
SELECT5, 'BTS Testing', 66.67, 20, '12/11/2007' UNION ALL
SELECT5, 'BTS Testing', 100.00, 21, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 9, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 10, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 14, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 15, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 16, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 17, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 18, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 19, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 20, '12/11/2007' UNION ALL
SELECT5, 'Mechanical Assembly_Checkpoint', 100.00, 21, '12/11/2007' UNION ALL
SELECT5, 'QA Inspection', 100.00, 13, '12/11/2007' UNION ALL
SELECT5, 'QA Inspection', 100.00, 15, '12/11/2007' UNION ALL
SELECT5, 'QA Inspection', 100.00, 18, '12/11/2007' UNION ALL
SELECT5, 'QA Inspection', 100.00, 19, '12/11/2007' UNION ALL
SELECT5, 'QA Inspection', 100.00, 20, '12/11/2007' UNION ALL
SELECT5, 'QA Inspection', 100.00, 21, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 9, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 10, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 14, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 15, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 16, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 17, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 18, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 19, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 20, '12/11/2007' UNION ALL
SELECT5, 'Wiring Testing', 100.00, 21, '12/11/2007' UNION ALL
SELECT7, 'QA Inspection', 100.00, 11, '12/11/2007' UNION ALL
SELECT7, 'QA Inspection', 100.00, 12, '12/11/2007' UNION ALL
SELECT7, 'QA Inspection', 100.00, 14, '12/11/2007'
SELECTSegmentID,
TestType,
FirstYield,
DayDate
FROM(
SELECTSegmentID,
TestType,
FirstYield,
DayDate
FROM@Sample
WHEREDayDate >= '20071120'
AND DayDate < '20071221'
UNION ALL
SELECTSegmentID,
'Total Yield',
EXP(SUM(LOG(FirstYield / 100.0))),
MIN(DayDate)
FROM@Sample
WHEREDayDate >= '20071120'
AND DayDate < '20071221'
GROUP BYSegmentID
) AS d
ORDER BYSegmentID,
CASE TestType
WHEN 'Total Yield' THEN 1
ELSE 0
END,
TestType
N 56°04'39.16"
E 12°55'05.25"
December 19, 2007 at 9:47 am
Hi,
First thanks for your detailed reply...
What shall we do when there is a NULL or a 0 value in any of the Firstyield row? Then there is no data returned from the below query when you run agains the data i have attached in the excel sheet.
SELECTSegmentID, 'Total Yield',
EXP(SUM(LOG(FirstYield / 100.0))),
MIN(DayDate)
FROM
tblbuildplan
WHERE
DayDate >= '2007-12-14 00:00:00.000'AND DayDate < '2007-12-17 00:00:00.000'
GROUP BY
SegmentID
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
December 19, 2007 at 12:59 pm
Either exclude it from the SELECT like this
SELECTSegmentID,
EXP(SUM(LOG(FirstYield / 100.0))) AS [Total Yield],
MIN(DayDate)
FROMtblBuildPlan
WHEREDayDate >= '2007-12-14'
AND DayDate < '2007-12-17'
AND FirstYield > 0
GROUP BYSegmentIDor change the value {NULL, 0} to 1, like thisSELECTSegmentID,
EXP(SUM(LOG(COALESCE(NULLIF(FirstYield, 0), 1) / 100.0))) AS [Total Yield],
MIN(DayDate)
FROMtblBuildPlan
WHEREDayDate >= '2007-12-14'
AND DayDate < '2007-12-17'
GROUP BYSegmentID
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply