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
 General SQL Server Forums
 New to SQL Server Programming
 Help required in writing sql statement

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.
Go to Top of Page

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 GroupID
1 Drive Shut Down of conveyor_C1 1
2 Drive Shut Down of conveyor_C1_a 1
3 Drive Shut Down of conveyor_C2. Check for Fault 1
44 Pneumatic Supply Fault 2
45 Drive Shut Down of conveyor_C42. Check for Fault 1
46 E Stop Request_CP01 4
47 E Stop Request_OP01 3
48 E Stop Request_OP02 3


SQL -
SELECT S.CP, COUNT(ID), SUM(DATEDIFF(MINUTE, stop_time, start_time)) AS TIMEINSEC FROM START_STOPLOG S, REASONMST R
WHERE 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.CP

CurrentDt = '2014/03/22' [sample date]
Go to Top of Page

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 commands
2. Sample output for your input data
Go to Top of Page

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 commands
2. 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 4
47 E Stop Request_OP01', 3)
insert into Start_StopLog (ReasonID, ReasonDescription, RGroup) values (48, 'E Stop Request_OP02', 3)






Go to Top of Page

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
Go to Top of Page

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 4
47 E Stop Request_OP01', 3)
insert into ReasonMst ( ReasonDescription, RGroup) values ( 'E Stop Request_OP02', 3)


Go to Top of Page

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 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


Go to Top of Page

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 AvgTime

FROM (

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
) sub

Group by sub.DESCIPTION, sub.RGroup
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -