Grupa SQL Server według miesięcy
Mam tabelę, która ma ten schemat
ItemID UserID Year IsPaid PaymentDate Amount
1 1 2009 0 2009-11-01 300
2 1 2009 0 2009-12-01 342
3 1 2010 0 2010-01-01 243
4 1 2010 0 2010-02-01 2543
5 1 2010 0 2010-03-01 475
Próbuję uruchomić zapytanie, które pokazuje sumy za każdy miesiąc. Do tej pory próbowałem DateDiff i zagnieżdżone selects, ale żaden nie daje mi tego, czego chcę. To chyba najbliżej:
DECLARE @start [datetime] = 2010/4/1;
SELECT ItemID, IsPaid,
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And DateDiff(m, PaymentDate, @start) = 0 AND UserID = 100) AS "Apr",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =1 AND UserID = 100) AS "May",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =2 AND UserID = 100) AS "Jun",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =3 AND UserID = 100) AS "Jul",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =4 AND UserID = 100) AS "Aug",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =5 AND UserID = 100) AS "Sep",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =6 AND UserID = 100) AS "Oct",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =7 AND UserID = 100) AS "Nov",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =8 AND UserID = 100) AS "Dec",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =9 AND UserID = 100) AS "Jan",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =10 AND UserID = 100) AS "Feb",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =11 AND UserID = 100) AS "Mar"
FROM LIVE L INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
Ale ja dostaję nulle, kiedy powinienem dostawać wartości. Coś przeoczyłem?
7 answers
SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120)
ORDER BY [Month]
Możesz też spróbować:
SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)
ORDER BY Year, Month
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-01 17:05:51
Ogranicz wymiar NVARCHARA do 7, dostarczonego do konwersji, aby pokazać tylko "RRRR-MM"
SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(7),PaymentDate,120)
ORDER BY [Month]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-12-09 23:48:02
Preferuję Łączenie DATEADD
i DATEDIFF
funkcji takich jak:
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0)
Razem te dwie funkcje zerują komponent date mniejszy niż podany datepart (tj. MONTH
w tym przykładzie).
Możesz zmienić bit datepart na YEAR
, WEEK
, DAY
, itd... co jest bardzo przydatne.
Twoje oryginalne zapytanie SQL wyglądałoby wtedy tak (nie mogę tego przetestować, ponieważ nie mam zestawu danych, ale powinno cię to umieścić po prawej stronie utwór).
DECLARE @start [datetime] = '2010-04-01';
SELECT
ItemID,
UserID,
DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0) [Month],
IsPaid,
SUM(Amount)
FROM LIVE L
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
AND PaymentDate > @start
Jeszcze jedno: kolumna Month
jest wpisywana jako DateTime
, co jest również miłą zaletą, jeśli chcesz dalej przetwarzać te dane lub zmapować obiekt.net na przykład.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-25 11:07:29
Jeśli musisz to robić często, prawdopodobnie dodałbym kolumnę obliczeniową PaymentMonth
do Tabeli:
ALTER TABLE dbo.Payments ADD PaymentMonth AS MONTH(PaymentDate) PERSISTED
Jest utrzymywany i przechowywany w tabeli - więc naprawdę nie ma nadmiarowej wydajności. Jest to 4 bajtowa wartość INT - więc narzut przestrzeni jest również minimalny.
Gdy już to masz, możesz uprościć swoje zapytanie, aby było czymś w stylu:
SELECT ItemID, IsPaid,
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 1 AND UserID = 100) AS 'Jan',
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 2 AND UserID = 100) AS 'Feb',
.... and so on .....
FROM LIVE L
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-01 17:16:25
DECLARE @start [datetime] = 2010/4/1;
Powinno być...
DECLARE @start [datetime] = '2010-04-01';
Ten, który masz, dzieli 2010 przez 4, potem przez 1, a następnie przekształca się w datę. Czyli 57,5 dnia od 1900-01-01.
Spróbuj SELECT @start
po zainicjowaniu, aby sprawdzić, czy jest to poprawne.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-01 17:10:15
Innym podejściem, które nie wymaga dodawania kolumn do wyniku, jest po prostu zerowanie day
składnika daty, więc 2016-07-13
i 2016-07-16
byłyby 2016-07-01
- co czyni je równymi według miesiąca.
Jeśli masz wartość date
(nie datetime
), możesz ją zerować bezpośrednio:
SELECT
DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] ),
COUNT(*)
FROM
[Table]
GROUP BY
DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] )
Jeśli masz datetime
wartości, musisz użyć CONVERT
, aby usunąć porcję czasu dnia:
SELECT
DATEADD( day, 1 - DATEPART( day, [Date] ), CONVERT( date, [Date] ) ),
COUNT(*)
FROM
[Table]
GROUP BY
DATEADD( day, 1 - DATEPART( day, [Date] ), CONVERT( date, [Date] ) )
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-23 11:44:38
Teraz Twoje zapytanie jest wyraźnie patrząc na tylko płatności za rok = 2010, jednak myślę, że miałeś na myśli, aby Twój Styczeń / Luty / Marzec faktycznie reprezentować 2009. Jeśli tak, musisz dostosować to trochę do tej sprawy. Nie żądaj wartości sumy dla każdej kolumny, tylko warunek różnicy daty w miesiącach. Resztę umieść w klauzuli WHERE.
SELECT
SUM( case when DateDiff(m, PaymentDate, @start) = 0
then Amount else 0 end ) AS "Apr",
SUM( case when DateDiff(m, PaymentDate, @start) = 1
then Amount else 0 end ) AS "May",
SUM( case when DateDiff(m, PaymentDate, @start) = 2
then Amount else 0 end ) AS "June",
SUM( case when DateDiff(m, PaymentDate, @start) = 3
then Amount else 0 end ) AS "July",
SUM( case when DateDiff(m, PaymentDate, @start) = 4
then Amount else 0 end ) AS "Aug",
SUM( case when DateDiff(m, PaymentDate, @start) = 5
then Amount else 0 end ) AS "Sep",
SUM( case when DateDiff(m, PaymentDate, @start) = 6
then Amount else 0 end ) AS "Oct",
SUM( case when DateDiff(m, PaymentDate, @start) = 7
then Amount else 0 end ) AS "Nov",
SUM( case when DateDiff(m, PaymentDate, @start) = 8
then Amount else 0 end ) AS "Dec",
SUM( case when DateDiff(m, PaymentDate, @start) = 9
then Amount else 0 end ) AS "Jan",
SUM( case when DateDiff(m, PaymentDate, @start) = 10
then Amount else 0 end ) AS "Feb",
SUM( case when DateDiff(m, PaymentDate, @start) = 11
then Amount else 0 end ) AS "Mar"
FROM
Payments I
JOIN Live L
on I.LiveID = L.Record_Key
WHERE
Year = 2010
AND UserID = 100
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-01 17:30:37