Production Data

Transact-SQL
1

Hello,

Can someone help a newbie with some code -

As an example -
I have a table ProductionRaw
[DTStamp] [timestamp] NOT NULL,
[Product] nvarchar NOT NULL,
[Shift] [int] NOT NULL,
[Weight] [real] NOT NULL

With Values -

07-1-2018 7:10:05, A, 1, 10
07-1-2018 12:10:05, A, 2, 10
07-1-2018 20:10:05, A, 3, 10

09-10-2018 7:10:05, A, 1, 5
09-10-2018 12:10:05, A, 2, 10
09-10-2018 20:10:05, A, 3, 15
09-11-2018 7:10:05, B, 1, 1
09-11-2018 12:10:05, B, 2, 2
09-11-2018 20:10:05, B, 3, 3
09-12-2018 7:10:05, A, 1, 10
09-12-2018 12:10:05, A, 2, 20
09-12-2018 20:10:05, A, 3, 30
09-13-2018 7:10:05, B, 1, 50
09-13-2018 12:10:05, B, 2, 25
09-13-2018 20:10:05, B, 3, 35

On insert of each row in the above table, I need to populate a table to display as below -

ProductName, 1stShift, 2nd Shift, 3rdShift, Total Monthly, YTD
A, 15, 30, 45, 90, 120
B, 51, 27, 38, 116, 116

Thanks,

Mohit.

2

Something like this:

select product
      ,sum(case
              when datepart(month,dtstamp)=datepart(month,current_timestamp)
               and [shift]=1
              then weight
              else 0
           end
          )
       as shift1
      ,sum(case
              when datepart(month,dtstamp)=datepart(month,current_timestamp)
               and [shift]=2
              then weight
              else 0
           end
          )
       as shift2
      ,sum(case
              when datepart(month,dtstamp)=datepart(month,current_timestamp)
               and [shift]=3
              then weight
              else 0
           end
          )
       as shift3
      ,sum(case
              when datepart(month,dtstamp)=datepart(month,current_timestamp)
              then weight
              else 0
           end
          )
       as total_monthly
      ,sum(weight) as ytd
  from productionraw
 where dtstamp>=dateadd(month,datediff(month,0,current_timestamp),0)
   and dtstamp<dateadd(month,datediff(month,0,current_timestamp)+12,0)
 group by product
 order by product
;
3

Thanks for your reply Bitsmed !

How about a another table, which holds the data from the ProductionRaw table. What I have in mind is that as soon as we have an insert on the ProductionRaw table, it runs an on insert procedure which will insert the data into Lets call it, ProductionStat table.

The productionStat table will be used for other purposes as well. The ProductionStat will be used as a Dashboard in the production area. So, with a lot of data, processing will be easier if we have a table ready for viewing.

Any ideas ?

Thanks

Mohit.

4

Hi Bitsmed,
I think the YTD is erroneous in calculation. I will repost with some data.

5

Here's the table with sample data -

Please replace Invalid Date with DateTime

CREATE TABLE [dbo].[ProductionRaw](
[DTStamp] NOT NULL,
[Product] nvarchar NOT NULL,
[Shift] [int] NOT NULL,
[Current_Weight] [real] NOT NULL
) ON [PRIMARY]

Insert into ProductionRaw Values ('07-07-2018 4:15:50 PM', 'A', '1','0.10')
Insert into ProductionRaw Values ('07-07-2018 4:25:50 PM', 'A', '2','0.20')
Insert into ProductionRaw Values ('07-07-2018 4:35:50 PM', 'A', '3','0.30')

Insert into ProductionRaw Values ('09-26-2018 9:15:50 PM', 'A', '1','0.15')
Insert into ProductionRaw Values ('09-26-2018 9:20:50 PM', 'A', '2','0.25')
Insert into ProductionRaw Values ('09-26-2018 9:25:50 PM', 'A', '3','0.35')
Insert into ProductionRaw Values ('09-26-2018 9:35:50 PM', 'B', '1','0.20')
Insert into ProductionRaw Values ('09-26-2018 9:40:50 PM', 'B', '2','0.30')
Insert into ProductionRaw Values ('09-26-2018 9:45:50 PM', 'B', '3','0.40')

6

Works perfectly here. You are aware that working with datatype "real" is inaccurate by the nature of reals? So you might want to change it to ex. decimal in either the table og the query.

7

Hi Bitsmed,

Here is the result at my end -

product Shift1 Shift2 Shift3 Total_Monthly YTD
A 0.14 0.25 0.34 0.73 0.75
B 0.2 0.29 0.4 0.89 0.9

The YTD accumulation should be from Jan 2018 to Today. In my data I have 3 DTStamps dated 2 months prior to today. So, the Monthly and YTD need to be different.

Thanks,

Mohit.

8

My bad - change this:

where dtstamp>=dateadd(month,datediff(month,0,current_timestamp),0)

to this:

where dtstamp>=dateadd(year,datediff(year,0,current_timestamp),0)
9

Thanks bitsmed !

That worked.