Nasıl SQL bir ay içinde birden fazla günlük değerleri birleştiren?

oy
0

Çeşitli eyaletlerde çok sayıda cihaz (aslında işletilmesi veya aşağı, ama başka durumlar vardır). Bir anlık bir cihazın bulunduğu devlete ne kaydetmek için her günün sonunda alınır. Ben genel bir işletim veya aşağı için cihazların COUNT kadar hürmetlerimle (örneğin 726 CCTVs 01-01- üzerine işletim dir 18 ve 4 CCTVs 728 CCTVs 01-02-18 ve 2 CCTVs üzerinde çalışma, aşağı doğru 01-02-18 tarihinde, vb) vardır, 01-01-18 aşağı dir.

Bu benim şimdiki kodudur:

SELECT oper.failurecode AS 'Device Type', 
       COUNT(hist.status) AS 'Count', 
       hist.status AS 'Status', 
       CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)) AS 'Date'
FROM locations loc
        INNER JOIN locoper oper ON oper.location = loc.location
        INNER JOIN serclochistory hist ON hist.location = loc.location
WHERE hist.status NOT IN ('DECOMMISSIONED', 'TRANSITION', 'NOT READY', 'NOT READY - Fiber') 
  AND CONVERT(TIME, hist.datadatetime) > '23:00:00'
GROUP BY 
    CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)), 
    oper.failurecode, 
    hist.status
ORDER BY 
    CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)), 
    oper.failurecode, 
    hist.status;

Bu bana aşağıdaki gibi bir sonuç verir:

CCTV   6    DOWN       2018-01-01
CCTV   763  OPERATING  2018-01-01
CCTV   18   DOWN       2018-01-02
CCTV   748  OPERATING  2018-01-02

Ne yapmak istiyorum ben aşağıdaki gibi bir sonuç görebilirsiniz ay için tüm cihazların birleştirmek geçerli:

CCTV   24   DOWN       2018-01
CCTV   1511 OPERATING  2018-01

Ben tek bir satır öğeleri birleştirmek nasıl örnekler bulabilirsiniz, ancak bu şekilde değerler eklemek değil.

Oluştur 27/11/2018 saat 18:10
kaynak kullanıcı
Diğer dillerde...                            


2 cevaplar

oy
0

Ben ilk veri yakalama için bir ortak tablo İfade (CTE) ile gitmek için cazip olacaktır. Bu şekilde sadece bir kez tarih değerlerini dönüştürmek gerekir. Aynı zamanda gruplama ve sıralama okumak için biraz daha kolay.

Ben tarih değeri 30 indirim dakika alma gerekliliği olarak belirsiz değilim, ama niyet durum kayıtları 30 dakika gece yarısı her iki tarafında yakalamak için olduğunu düşünüyorum. zaman filtresi ayarlanan değerin olmadığı için yayınlanmıştır Olarak sorgu bu yapmaz.

Neyse - bu denemek. Umut ediyorum bu yardım eder.

;WITH statusFilteredRecords ([Device Type], [Status], [Date])
-- Set of records filtered by status, with the time adjustment of -30 minutes applied to the date.
AS
(
    SELECT      oper.failurecode AS 'Device Type', 
                hist.status AS 'Status', 
                DateAdd(MINUTE, -30, hist.dateadatetime) [Date] -- This normalises "End of Day" as being midnight +/- 30 minutes
    FROM        locations loc
    INNER JOIN  locoper oper 
            ON  oper.location = loc.location
    INNER JOIN  serclochistory hist 
            ON  hist.location = loc.location
    WHERE       hist.status NOT IN ('DECOMMISSIONED', 'TRANSITION', 'NOT READY', 'NOT READY - Fiber')       
)
, eodFilteredRecords ([Device Type], [Status], [Date])
-- Set of records filtered by Time, so that only EOD records are included, with the date normalized to the first of the month for grouping
AS  
(
    SELECT  [Device Type],
            [Status],
            CAST(DateAdd(dd, -(day([Date])-1), [Date]) as Date) AS [Date] -- Force the date to the first of the month.
    FROM    statusFilteredRecords
    WHERE   CONVERT(TIME, [Date]) > '23:00:00' -- Because we're operating on the normalized datetime value, this is really > 23:30 or < 00:30
)
SELECT      [Device Type], 
            count([Status]) as [Count],
            [Status], 
            [Date]
FROM        eodFilteredRecords
GROUP BY    [Device Type], 
            [Status], 
            [Date]
ORDER BY    [Date], 
            [Device Type], 
            [Status]

<== DÜZENLEME: Commentary bir açıklama OP'ın isteğini cevaplamak için ==>

Count()bir toplama işlevi - bu sonuçlar bir alt grup içindeki veriler üzerinde çalışır. Bu durumda, alt gruplar üzerinde maç olanlardır [Device Type], [Status]ve [Date]bu belirtilen - GROUP BYsorgunun maddesi.

GROUP BY    [Device Type], 
            [Status], 
            [Date]

Orijinal sorguda GROUP BYmaddesi dönüştürülmüş DateTimealanda değerleri hist.dateadatetimeiçin Date(süre bileşenle) değerleri. Çıkarma zaman değerin bir kısmını belirli bir Tüm değerler anlamına gelir gün hemen eşittir ve aynı alt-grubunun bir parçasını oluşturmaktadır. ( 01-Jan-2018 15:22:33Olur 01-Jan-2018olduğu gibi, 01-Jan-2018 22:51:00vs).

GROUP BY 
    CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)), 
    oper.failurecode, 
    hist.status

Ben ne yaptım etkili bir kaldırma bunu bir adım ileri almaktır zaman bölümünü ve gündüz ayın ilk olmak her tarihlerinin ayarlanması, değerin bir kısmını.

CAST(DateAdd(dd, -(day([Date])-1), [Date]) as Date) AS [Date]

Kullandığım, 28. Eylül bir tarih varsayalım 2018. Let day([Date])ayın gün olsun (örneğin 28) ve (27 verir) bu değerden 1 çıkarma ve tarihe gün kapalı bu sayıyı almak, bu nedenle 1 geri düşüyor ayın günü.

Yani şimdi değerlendirirken GROUP BY [Device Type], [Status], [Date]birlikte aynı dahilinde gerçekleşen tüm kayıtları, biz grup aya ( 11-Sep-2018olur 01-Sep-2018, yaptığı gibi 30-Sep-2018, vs).

Toplama işlevi uygulamak Count()(günün daha benim ay gruplandırılmış) kayıtlarının bu daha az taneli gruplara Aradığınız yuvarlanmış sonucunu verir.

Cevap 27/11/2018 saat 21:34
kaynak kullanıcı

oy
0

Sorgu önermek SQL Server, o zaman bunu yapabilirsiniz eğer sözdizimi:

SELECT oper.failurecode AS 'Device Type', COUNT(hist.status) AS 'Count', hist.status AS 'Status', CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)) AS 'Date'
FROM locations loc INNER JOIN 
     locoper oper 
     ON oper.location = loc.location INNER JOIN 
     serclochistory hist 
     ON hist.location = loc.location
WHERE hist.status NOT IN ('DECOMMISSIONED', 'TRANSITION', 'NOT READY', 'NOT READY - Fiber') AND CONVERT(TIME, hist.datadatetime) > '23:00:00'
GROUP BY CONVERT(VARCHAR(7), DATEADD(MINUTE, -30, hist.datadatetime), 120), oper.failurecode, hist.status
ORDER BY Date, oper.failurecode, hist.status;
Cevap 27/11/2018 saat 18:19
kaynak kullanıcı

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more