Group Data by Date Column

rusmanicai · December 29, 2016, 7:59pm

My boss is asking me for some Sci Fi stuff.
We have n machines that print jobs and every so often get maintenance on them.
He wants to know how many impressions we ran on each machine between each service.
So, I put together a view that pulls the machine id, each time was used - Usage Date, Job Number, Impressions.
I am trying to figure out how to write my group by statement so that it shows me something like this:

ScreenNumber Maint Date Impressions
A1 8/19/2016 701
A1 10/12/2016 645
A1 11/3/2016 7

where the 200 impressions in the first line are a sum of all impressions made on machine A1 after the first maintenance recorded 8/19/16 and before 10/12/16,
500 = sum (impressions) where usagedate>10/12/16 and < 11/3/16.

I tried to upload the raw data, but new users cannot upload files unfortunately.

Admin, please give me permission to upload files. Thank you!*

This is the code that I have so far:

select distinct
sn.ScreenNumber,
sr.PONumber,
sr.ReceiptDate,
sum(grosscount) Over (partition by sn.screennumber,sr.ponumber) as Impressions

from dbo.MSI_ScreenNumbers sn
left join dbo.MSI_ScreenClockIns sc on sn.screennumber=sc.screennumber
left join dbo.MSI_ScreenReceipts sr on sr.ScreenNumber=sn.ScreenNumber
left join OrderValue on ordervalue.JobNumber=sc.JobNumber
INNER JOIN OrderQtyTable ON OrderValue.JobNumber = OrderQtyTable.JobNumber
AND OrderValue.ComponentNumber = OrderQtyTable.ComponentNumber
AND OrderValue.QuantityLineNo = OrderQtyTable.QuantityLineNo
INNER JOIN OrderProcess ON OrderValue.JobNumber = OrderProcess.JobNumber
AND OrderValue.ComponentNumber = OrderProcess.ComponentNumber
AND OrderValue.GroupNo = OrderProcess.GroupNo
AND OrderValue.ItemNumber = OrderProcess.ItemNumber
AND OrderValue.PartNumber = OrderProcess.PartNumber
INNER JOIN OrderComponent ON OrderValue.JobNumber = OrderComponent.JobNumber
AND OrderValue.ComponentNumber = OrderComponent.ComponentNumber
AND OrderValue.QuantityLineNo = OrderComponent.QtyOrdIndex
where not(ordercomponent.description like 'FAI%' or ordercomponent.description like '%FPA%' or ordercomponent.description like '%charge%')
and ordervalue.JobNumber=sc.jobnumber
and ordervalue.groupno=5
and OrderComponent.ComponentNumber=1
and sc.ScreenNumber <>'' and sc.ScreenNumber is not null
and not(CDPDF like '%overlay%' or CDPDF like '%spacer%')
--and sc.JobNumber='412001'
group by sn.ScreenNumber,ReceiptDate, usagedate,GrossCount, PONumber
having sc.UsageDate>=sr.ReceiptDate

I get:

ScreenNumber Maint Date Impressions
A1 8/19/2016 1353
A1 10/12/2016 652
A1 11/3/2016 7

While the group by does the job, it returns ALL the impressions on that machine on the first line, because they all happened after or on the maintenance date, and basically just the last sum of impressions is correct, since the last maintenance date.
I don't know what to do next.

Thank you,
Julia.

bitsmed · December 31, 2016, 12:14pm

In which table are the fields "cdpdf" and "grosscount" located?

ps.: this would probably be what you're looking for: sum(grosscount) over(partition by sn.screennumber,sr.ponumber rows between unbounded preceding and current row) as Impressions

rusmanicai · January 3, 2017, 3:04pm

Hi,
The CDPDF column doesn't really matter and that's why I removed it from my select statement, but I forgot to remove it from my where clause.
I forgot to mention that I work in SQL Server 2008 R2 and this version does not support the ROWS clause.
I do have installed SQL 2014 and tried to write this statement but it fails..."incorrect syntax near ROWS":

Here is the new code:

select distinct
sn.ScreenNumber,
sr.PONumber,
sr.ReceiptDate,
CONVERT(varchar(20),sum(grosscount) Over (partition by sn.screennumber,sr.ponumber order by datepart(mm,sr.receiptdate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),1) as Impressions

from dbo.MSI_ScreenNumbers sn
left join dbo.MSI_ScreenClockIns sc on sn.screennumber=sc.screennumber
left join dbo.MSI_ScreenReceipts sr on sr.ScreenNumber=sn.ScreenNumber
left join OrderValue on ordervalue.JobNumber=sc.JobNumber
INNER JOIN OrderQtyTable ON OrderValue.JobNumber = OrderQtyTable.JobNumber
AND OrderValue.ComponentNumber = OrderQtyTable.ComponentNumber
AND OrderValue.QuantityLineNo = OrderQtyTable.QuantityLineNo
INNER JOIN OrderProcess ON OrderValue.JobNumber = OrderProcess.JobNumber
AND OrderValue.ComponentNumber = OrderProcess.ComponentNumber
AND OrderValue.GroupNo = OrderProcess.GroupNo
AND OrderValue.ItemNumber = OrderProcess.ItemNumber
AND OrderValue.PartNumber = OrderProcess.PartNumber
INNER JOIN OrderComponent ON OrderValue.JobNumber = OrderComponent.JobNumber
AND OrderValue.ComponentNumber = OrderComponent.ComponentNumber
AND OrderValue.QuantityLineNo = OrderComponent.QtyOrdIndex
where not(ordercomponent.description like 'FAI%' or ordercomponent.description like '%FPA%' or ordercomponent.description like '%charge%')
and ordervalue.JobNumber=sc.jobnumber
and ordervalue.groupno=5
and OrderComponent.ComponentNumber=1
and sc.ScreenNumber <>'' and sc.ScreenNumber is not null
--and sn.ScreenNumber='A15'
--and sc.JobNumber='412001'
group by sn.ScreenNumber,ReceiptDate, usagedate,GrossCount, PONumber
having sc.UsageDate>=sr.ReceiptDate

bitsmed · January 3, 2017, 5:41pm

Please provide:

table definitions in the form of create statements
sample data in the form of insert statements
expected output from your provided sample data

so we have something to work with

rusmanicai · January 3, 2017, 9:39pm

Thank you so much for your interest in helping me
This is not easy...

so...the first table contains all the screens by screen number. I only inserted one screen: A15

CREATE TABLE [dbo].[MSI_ScreenNumbers](
[ScreenNumber] varchar NOT NULL,
PRIMARY KEY CLUSTERED
(
[ScreenNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [Enterprise32].[dbo].[MSI_ScreenNumbers]
([ScreenNumber])
VALUES
(A15)
GO

Second table: captures every job that was clocked into and has the screen A15 in it's comments. There might have been other screens used in the job and listed in comments separated by commas, this is why the very last column of this table extracts the screen number using a UDF that I put together.

CREATE TABLE [dbo].[MSI_ScreenClockIns](
[ProductionCode] [int] NULL,
[JobNumber] varchar NULL,
[CustAccount] varchar NULL,
[CustName] varchar NULL,
[ProcessCode] [int] NULL,
[ScreensUsed] varchar NULL,
[UsageDate] [datetime] NULL,
[CDPDF] varchar NULL,
[ReasonCode] varchar NULL,
[ScreenNumber] varchar NULL
) ON [PRIMARY]

INSERT INTO [Enterprise32].[dbo].[MSI_ScreenClockIns]
([ProductionCode]
,[JobNumber]
,[CustAccount]
,[CustName]
,[ProcessCode]
,[ScreensUsed]
,[UsageDate]
,[CDPDF]
,[ReasonCode]
,[ScreenNumber])
VALUES
(1707930, '412065', '1442', 'Thomas & Betts Power Solutions', 2007, 'A15,A74', '2016-12-13 13:38:00.000', 'P:\CD-PDF Database\TBP\126-0316-002 Rev 3.pdf', 'A15'),
(1698877, '408582', '2830', 'Variosystems', 2007, 'A15,A71', '2016-12-02 13:58:00.000', 'P:\CD-PDF Database\VSI\PLATINUM EDGE 600 OVERLAY.pdf', 'A15'),
(1668048, '410026', '3677', 'Thermo Fisher ---Minneapolis', 2007, 'A15', '2016-11-01 13:17:00.000', 'P:\CD-PDF Database\TFS\092239 Rev C.pdf', 'A15'),
(1667331, '409963', '2221', 'NI Hungary Kft.', 2007, 'A15', '2016-11-01 06:45:00.000', 'P:\CD-PDF Database\NI\193183A-01 Rev 3.pdf', 'A15'),
(1664501, '409524', '3129', 'B&B Smartworx', 2007, 'A15', '2016-10-28 06:39:00.000', 'P:\CD-PDF Database\BBE\7974 R0.pdf','A15'),
(1661217, '409369', '741', 'KCI', 2007, 'A15', '2016-10-25 16:07:00.000', 'P:\CD-PDF Database\KCI\320004 Rev G.pdf', 'A15'),
(1659628, '409500', '2221', 'NI Hungary Kft.', 2007, 'A15', '2016-10-24 15:56:00.000', 'P:\CD-PDF Database\NI\191433A-01 REV 1.pdf', 'A15'),
(1659614, '409654', '3697', 'Standard Register LatinoAmerica, S DE RL DE CV', '2007', 'A15', '2016-10-24 15:54:00.000', 'P:\CD-PDF Database\SRR\30010-471-01.pdf','A15'),
(1652478, '409213', '4391', 'Parker Chomerics', 2007, 'A15', '2016-10-17 16:12:00.000', 'P:\CD-PDF Database\PCM\HA390-0001-X505_01.pdf', 'A15'),
(1652468, '408960', '4391', 'Parker Chomerics', 2007, 'A15', '2016-10-17 16:08:00.000', 'P:\CD-PDF Database\PCM\HA390-0001-X505_01.pdf', 'A15'),
(1651084, '408828', '2221','NI Hungary Kft.', 2007, 'A 10/15/07', '2016-10-14 16:12:00.000', 'P:\CD-PDF Database\NI\156218B-01 Rev 2.pdf', 'A15'),
(1648112, '408480', '4707', 'Wayne-Dalton', 2007, 'A15', '2016-10-12 16:22:00.000', 'P:\CD-PDF Database\OHD\409846-0000 REV C.pdf','A15')

This table shows how many times screen A15 was rebuilt - an outside company does this.

CREATE TABLE [dbo].[MSI_ScreenReceipts](
[ID] [int] NOT NULL,
[PONumber] varchar NULL,
[Comments] varchar NULL,
[ReceiptDate] [date] NULL,
[ScreenNumber] varchar NULL
) ON [PRIMARY]

INSERT INTO [Enterprise32].[dbo].[MSI_ScreenReceipts]
([ID]
,[PONumber]
,[Comments]
,[ReceiptDate]
,[ScreenNumber])
VALUES

       (72400,	'74020',	'A15,A79',	'2016-11-23','A15'),
	   (71583,  '73574',	'A32,A14,A15,A8',	'2016-10-13',	'A15'),
	   (70466,	'72813',	'A15,A3,A32,A56,A76,A74,A107,A94,A44',	'2016-08-19',	'A15')

GO

Table Order Value contains the quantity of impressions made for each job. This is a huge table but for the purpose of this exercise I narrowed it to the columns I need: job number, gross count and group number --the group that counts the impressions:

CREATE TABLE [dbo].[OrderValue](
[JobNumber] varchar NULL,
[GroupNo] [int] NULL,
[GrossCount] [int] NULL
) ON [PRIMARY]

INSERT INTO [Enterprise32].[dbo].[OrderValue]
([JobNumber],[GroupNo],[UsageDate],[GrossCount] )
VALUES
('408480',5, 4082),('408582',5,126),('408828',5,530),('408960',5,158),('409213',5,144),('409369',5,252),('409500',5,212),('409524',5,38),('409654',5,91),
('409963',5,729),('410026',5,89),('412065',5,67)

This is the simplified query. If this works I can add more restrictions later:

select distinct
sn.ScreenNumber,
sr.PONumber,
sr.ReceiptDate,
CONVERT(varchar(20),sum(grosscount) Over (partition by sn.screennumber,sr.ponumber))-- order by datepart(mm,sr.receiptdate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),1)
as Impressions

from dbo.MSI_ScreenNumbers sn
left join dbo.MSI_ScreenClockIns sc on sn.screennumber=sc.screennumber
left join dbo.MSI_ScreenReceipts sr on sr.ScreenNumber=sn.ScreenNumber
left join OrderValue on ordervalue.JobNumber=sc.JobNumber
where
ordervalue.groupno=5
and sc.ScreenNumber <>'' and sc.ScreenNumber is not null
and sn.ScreenNumber='A15'

group by sn.ScreenNumber,ReceiptDate, usagedate,GrossCount, PONumber
having sc.UsageDate>=sr.ReceiptDate

What I would like to see is:

ScreenNumber PONumber ReceiptDate Impressions
A15 72813 2016-08-19 4082
A15 73574 2016-10-13 2243
A15 74020 2016-11-23 67

We made a total of 6392 impressions on this screen since it was first used on 10/12/16 after teh first service dated 8/19/16.

I hope this makes sense.

Thank you!

bitsmed · January 4, 2017, 2:17am

This will Work on mssql 2008 r2:

with screenreceipts1
  as (select sn.screennumber
            ,sr.ponumber
            ,sr.receiptdate
            ,row_number() over(partition by sn.screennumber order by sr.receiptdate) as rn
        from dbo.msi_screennumbers as sn
             inner join dbo.msi_screenreceipts as sr
                     on sr.screennumber=sn.screennumber
     )
    ,screenreceipts2
  as (select sr1.screennumber
            ,sr1.ponumber
            ,sr1.receiptdate
            ,isnull(sr2.receiptdate,cast('9999-12-31' as date)) as receiptdateend
        from screenreceipts1 as sr1
             left outer join screenreceipts1 as sr2
                          on sr2.screennumber=sr1.screennumber
                         and sr2.rn=sr1.rn+1
     )
select sr.screennumber
      ,sr.ponumber
      ,sr.receiptdate
      ,sum(ov.grosscount) as impressions
  from screenreceipts2 as sr
       inner join dbo.msi_screenclockins as sc
               on sc.screennumber=sr.screennumber
              and sc.usagedate>=sr.receiptdate
              and sc.usagedate<sr.receiptdateend
       inner join dbo.ordervalue as ov
               on ov.jobnumber=sc.jobnumber
              and ov.groupno=5
 group by sr.screennumber
         ,sr.ponumber
         ,sr.receiptdate
;

It produces this result (I think you forgot to include jobnumber 408583 in your output):

screennumber  ponumber  receiptdate  impressions
A15           72813     2016-08-19   4082
A15           73574     2016-10-13   2243
A15           74020     2016-11-23   193

rusmanicai · January 4, 2017, 2:41pm

bitsmed:

with screenreceipts1
as (select sn.screennumber
,sr.ponumber
,sr.receiptdate
,row_number() over(partition by sn.screennumber order by sr.receiptdate) as rn
from dbo.msi_screennumbers as sn
inner join dbo.msi_screenreceipts as sr
on sr.screennumber=sn.screennumber
)
,screenreceipts2
as (select sr1.screennumber
,sr1.ponumber
,sr1.receiptdate
,isnull(sr2.receiptdate,cast('9999-12-31' as date)) as receiptdateend
from screenreceipts1 as sr1
left outer join screenreceipts1 as sr2
on sr2.screennumber=sr1.screennumber
and sr2.rn=sr1.rn+1
)
select sr.screennumber
,sr.ponumber
,sr.receiptdate
,sum(ov.grosscount) as impressions
from screenreceipts2 as sr
inner join dbo.msi_screenclockins as sc
on sc.screennumber=sr.screennumber
and sc.usagedate>=sr.receiptdate
and sc.usagedate<sr.receiptdateend
inner join dbo.ordervalue as ov
on ov.jobnumber=sc.jobnumber
and ov.groupno=5
group by sr.screennumber
,sr.ponumber
,sr.receiptdate
;

Thank you so much for your help.
I did think of using a cte and the row_number() function but I only used one cte. I understand what you did here and it's awesome. The deadline for this project was today at 2:00 so you saved me.