Author |
Topic |
nitinarm
Starting Member
8 Posts |
Posted - 2014-04-15 : 09:31:25
|
Test |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-15 : 10:05:24
|
quote: Originally posted by nitinarm Please review attached screen shot.I was able to write sql statement and generate result for columns :1. Sr. No, 2. Stoppage Reason, 3. Qty, 4. Time (in Mins), 5. Control Panel No.But I want to write sql statement and generate columns -> Group ID, Groupwise Qty, Group wise Time, ID, Avg Time. All this data is coming from two tables. One is Reason Master, and other one is Stoppages.Can anybody help me in writing this statement?Its really urgent.
We really can't do much without some sample data for the source tables. |
|
|
nitinarm
Starting Member
8 Posts |
Posted - 2014-04-16 : 00:43:21
|
quote: Originally posted by gbritton
quote: Originally posted by nitinarm Please review attached screen shot.I was able to write sql statement and generate result for columns :1. Sr. No, 2. Stoppage Reason, 3. Qty, 4. Time (in Mins), 5. Control Panel No.But I want to write sql statement and generate columns -> Group ID, Groupwise Qty, Group wise Time, ID, Avg Time. All this data is coming from two tables. One is Reason Master, and other one is Stoppages.Can anybody help me in writing this statement?Its really urgent.
We really can't do much without some sample data for the source tables.
Dear sir,Thank you for your response.Here are table structures -CREATE TABLE [dbo].[Start_StopLog]( [id] [int] IDENTITY(1,1) NOT NULL, [CP] [int] NOT NULL, [ReasonID] [int] NULL, [ReasonDescripton] [text] NULL, [Start_Time] [datetime] NULL, [Stop_Time] [datetime] NULL, [flag] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] CREATE TABLE [dbo].[ReasonMst]( [ReasonID] [int] IDENTITY(1,1) NOT NULL, [ReasonDescription] [text] NULL, [RGroup] [int] NULL, CONSTRAINT [PK_ReasonMst] PRIMARY KEY CLUSTERED ( [ReasonID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]Sample Data of Stoppage Table - ===============================ID CP ReasonID Start_Time Stop_Time 2256 2 170 22-03-2014 06:29:49 22-03-2014 06:28:01 2257 2 165 22-03-2014 06:36:32 22-03-2014 06:35:54 2258 3 104 22-03-2014 06:36:52 22-03-2014 06:36:38 2259 2 159 22-03-2014 06:47:04 22-03-2014 06:45:40 2260 2 159 22-03-2014 06:48:44 22-03-2014 06:47:13 2261 1 65 22-03-2014 06:51:29 22-03-2014 06:51:17 2262 1 67 22-03-2014 06:52:25 22-03-2014 06:52:13 2263 2 166 22-03-2014 07:01:24 22-03-2014 06:58:51 2264 3 138 22-03-2014 07:02:30 22-03-2014 07:02:25 2265 3 107 22-03-2014 07:17:42 22-03-2014 07:17:30 2266 2 168 22-03-2014 07:20:25 22-03-2014 07:17:44 2267 2 154 22-03-2014 07:38:07 22-03-2014 07:31:52 Sample data of reason master-===============================ReasonID ReasonDescription GroupID1 Drive Shut Down of conveyor_C1 12 Drive Shut Down of conveyor_C1_a 13 Drive Shut Down of conveyor_C2. Check for Fault 144 Pneumatic Supply Fault 245 Drive Shut Down of conveyor_C42. Check for Fault 146 E Stop Request_CP01 447 E Stop Request_OP01 348 E Stop Request_OP02 3SQL -SELECT S.CP, COUNT(ID), SUM(DATEDIFF(MINUTE, stop_time, start_time)) AS TIMEINSEC FROM START_STOPLOG S, REASONMST RWHERE R.REASONID = S.REASONID AND s.STOP_TIME Between '" & CurrentDt & " 07:00:00' AND '" & CurrentDt & " 15:00:00' " AND DATEDIFF(MINUTE, S.stop_time, S.start_time) != 0 AND S.REASONID != 0 GROUP BY S.CP ORDER BY S.CPCurrentDt = '2014/03/22' [sample date] |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-16 : 08:39:52
|
THis is a help but we really need:1. data for your tables in a format that can be cut and pasted into SSMS -- that is, complete INSERT INTO commands2. Sample output for your input data |
|
|
nitinarm
Starting Member
8 Posts |
Posted - 2014-04-17 : 08:01:39
|
quote: Originally posted by gbritton THis is a help but we really need:1. data for your tables in a format that can be cut and pasted into SSMS -- that is, complete INSERT INTO commands2. Sample output for your input data
quote: Following are insert statements for your reference -insert into Start_StopLog (ID, CP, ReasonID, Start_Time, Stop_Time, flag) values(2256, 2, 170, '22-03-2014 06:29:49', '22-03-2014 06:28:01', 0) insert into Start_StopLog (ID, CP, ReasonID, Start_Time, Stop_Time, flag) values(2257, 2, 165, '22-03-2014 06:36:32', '22-03-2014 06:35:54', 0)insert into Start_StopLog (ID, CP, ReasonID, Start_Time, Stop_Time, flag) values(2258, 3, 104, '22-03-2014 06:36:52', '22-03-2014 06:36:28', 0)insert into Start_StopLog (ID, CP, ReasonID, Start_Time, Stop_Time, flag) values(2259, 2, 159, '22-03-2014 06:47:04', '22-03-2014 06:45:28', 0)insert into Start_StopLog (ID, CP, ReasonID, Start_Time, Stop_Time, flag) values(2260, 2, 159, '22-03-2014 06:48:44', '22-03-2014 06:47:13', 0)insert into Start_StopLog (ID, CP, ReasonID, Start_Time, Stop_Time, flag) values(2261, 1, 67, '22-03-2014 06:51:04', '22-03-2014 06:52:28', 0)insert into Start_StopLog (ID, CP, ReasonID, Start_Time, Stop_Time, flag) values(2262, 2, 166, '22-03-2014 07:01:13', '22-03-2014 06:58:51', 0)================================insert into Start_StopLog (ReasonID, ReasonDescription, RGroup) values (1, 'Drive Shut Down of conveyor_C1', 1)insert into Start_StopLog (ReasonID, ReasonDescription, RGroup) values (2, 'Drive Shut Down of conveyor_C1_a', 1)insert into Start_StopLog (ReasonID, ReasonDescription, RGroup) values (3, 'Drive Shut Down of conveyor_C2. Check for Fault', 1)insert into Start_StopLog (ReasonID, ReasonDescription, RGroup) values (44, 'Pneumatic Supply Fault', 2)insert into Start_StopLog (ReasonID, ReasonDescription, RGroup) values (45, 'Drive Shut Down of conveyor_C42. Check for Fault', 1)insert into Start_StopLog (ReasonID, ReasonDescription, RGroup) values (46, 'E Stop Request_CP01 447 E Stop Request_OP01', 3)insert into Start_StopLog (ReasonID, ReasonDescription, RGroup) values (48, 'E Stop Request_OP02', 3)
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-17 : 08:09:37
|
I still can't use this! For one thing, your DDL doesn't match your INSERT command (e.g. the column name is ReasonDescripton, the INSERT uses the name ReasonDescription (with an "i")). Worse yet, the Start_StopLog table has no column called RGroup. Also there is no code to populate the ResasonMST table |
|
|
nitinarm
Starting Member
8 Posts |
Posted - 2014-04-17 : 23:07:19
|
quote: Originally posted by gbritton I still can't use this! For one thing, your DDL doesn't match your INSERT command (e.g. the column name is ReasonDescripton, the INSERT uses the name ReasonDescription (with an "i")). Worse yet, the Start_StopLog table has no column called RGroup. Also there is no code to populate the ResasonMST table
quote: Dear sir,Extremely sorry...Please find new modified sql insert statements...insert into Start_StopLog (CP, ReasonID, Start_Time, Stop_Time, flag) values(2, 1, '2014-03-09 09:14:42.000', '2014-03-09 09:14:38.000', 0)insert into Start_StopLog (CP, ReasonID, Start_Time, Stop_Time, flag) values(2, 1, '2014-03-09 09:18:12.000', '2014-03-09 09:17:42.000', 0)insert into Start_StopLog (CP, ReasonID, Start_Time, Stop_Time, flag) values(3, 2, '2014-03-09 09:24:15.000', '2014-03-09 09:22:42.000', 0)insert into Start_StopLog (CP, ReasonID, Start_Time, Stop_Time, flag) values(2, 2, '2014-03-09 09:27:42.000', '2014-03-09 09:24:00.000', 0)insert into Start_StopLog (CP, ReasonID, Start_Time, Stop_Time, flag) values(2, 1, '2014-03-09 09:34:42.000', '2014-03-09 09:28:42.000', 0)insert into Start_StopLog (CP, ReasonID, Start_Time, Stop_Time, flag) values(1, 1, '2014-03-09 09:40:42.000', '2014-03-09 09:38:42.000', 0)insert into Start_StopLog (CP, ReasonID, Start_Time, Stop_Time, flag) values(2, 2, '2014-03-09 09:42:42.000', '2014-03-09 09:41:42.000', 0)======================================================insert into ReasonMst ( ReasonDescription, RGroup) values ( 'Drive Shut Down of conveyor_C1', 1)insert into ReasonMst ( ReasonDescription, RGroup) values ('Drive Shut Down of conveyor_C2. Check for Fault', 1)insert into ReasonMst ( ReasonDescription, RGroup) values ( 'Pneumatic Supply Fault', 2)insert into ReasonMst ( ReasonDescription, RGroup) values ( 'Drive Shut Down of conveyor_C42. Check for Fault', 1)insert into ReasonMst ( ReasonDescription, RGroup) values ( 'E Stop Request_CP01 447 E Stop Request_OP01', 3)insert into ReasonMst ( ReasonDescription, RGroup) values ( 'E Stop Request_OP02', 3)
|
|
|
nitinarm
Starting Member
8 Posts |
Posted - 2014-04-18 : 02:04:28
|
quote: Reference Query -================SELECT S.CP, CONVERT(VARCHAR(max), R.REASONDESCRIPTION) AS DESCIPTION, R.REASONID, Count(S.ID) as Qty, SUM(DATEDIFF(MINUTE, stop_time, start_time)) AS TIMEINMINUTE FROM START_STOPLOG S, REASONMST RWHERE R.REASONID = S.REASONID AND s.STOP_TIME Between '2014-03-26 07:00:00' AND '2014-03-27 07:00:00'AND DATEDIFF(MINUTE, S.stop_time, S.start_time) != 0 AND S.REASONID != 0GROUP BY R.REASONID, S.CP, CONVERT(VARCHAR(max), R.REASONDESCRIPTION) ORDER BY R.REASONID, S.CP
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-21 : 08:54:50
|
Something like this may be what you want, or at least is a starting point:select sub.DESCIPTION , sum(qty) as GroupWiseQty , sum(TIMEINMINUTE) as GroupWiseTime , sub.RGroup as ID , Avg(TimeInMinute) AS AvgTimeFROM ( SELECT S.CP, CONVERT(VARCHAR(max), R.REASONDESCRIPTION) AS DESCIPTION , R.REASONID , Count(S.ID) as Qty , SUM(DATEDIFF(MINUTE, stop_time, start_time)) AS TIMEINMINUTE , max(r.RGroup) as RGroup FROM START_STOPLOG S , REASONMST R WHERE R.REASONID = S.REASONID --AND s.STOP_TIME Between '2014-03-26 07:00:00' AND '2014-03-27 07:00:00' AND DATEDIFF(MINUTE, S.stop_time, S.start_time) != 0 AND S.REASONID != 0 GROUP BY R.REASONID, S.CP, CONVERT(VARCHAR(max), R.REASONDESCRIPTION) --ORDER BY R.REASONID, S.CP) subGroup by sub.DESCIPTION, sub.RGroup |
|
|
nitinarm
Starting Member
8 Posts |
Posted - 2014-04-22 : 05:56:14
|
Dear sir,Thanks for sharing modified SQL Query.I will study and will try to manipulate and match it with my requirements.Thank you once again!!Regards,Nitin |
|
|
|
|
|