I have a table in which I have to count total records assigned to each USER by weekly (monday to sunday).
Table BooksIssued
BOOKID USER DATE
1 A 20211001
2 A 20211002
3 A 20211003
4 A 20211004
5 B 20211009
6 C 20211008
7 C 20211008
20211001 is friday.
output of sql query is as follows, the WEEKDATE column shows the week end date (i.e sunday)
WEEKCOUNT USER WEEKDATE
3 A 10/03
1 A 10/10
1 B 10/10
2 C 10/10
I am unable to get the date in output containing day, as grouping is done based on user and week part of date. Please suggest on getting above output. I am using vertica DB. Below is sample query i tried (though i could not get the day part of date)
SELECT USER, date_part('WEEK', date)) as WEEKDATE
SUM(CASE WHEN DATE >= timestampadd(WEEK, DATEDIFF(WEEK, date('1900-01-01 00:00:00.000'), date(sysdate)), date('1900-01-01 00:00:00.000'))
AND DATE < timestampadd(WEEK, DATEDIFF(WEEK, date('1900-01-01 00:00:00.000'), date(sysdate)) + 1, date('1900-01-01 00:00:00.000'))
THEN 1 ELSE 0 END) AS WEEKCOUNT,
FROM BOOKSISSUED
GROUP BY USER, date_part('WEEK', date)
when i add date_part('DAY', date) in select clause, i get error as its not in group by. Please help.
Do you mean this?
WITH
-- your input ...
indata(BOOKID,USR,DT) AS (
SELECT 1,'A',DATE '20211001'
UNION ALL SELECT 2,'A',DATE '20211002'
UNION ALL SELECT 3,'A',DATE '20211003'
UNION ALL SELECT 4,'A',DATE '20211004'
UNION ALL SELECT 5,'B',DATE '20211009'
UNION ALL SELECT 6,'C',DATE '20211008'
UNION ALL SELECT 7,'C',DATE '20211008'
)
SELECT
COUNT(*) AS week_count
, usr
, TO_CHAR(
DATE_TRUNC('WEEK',dt) + INTERVAL '6 DAYS'
, 'MM/DD'
) AS trcweek
FROM indata
GROUP BY 2,3
ORDER BY 2,3
;
week_count | usr | trcweek
------------+-----+---------
3 | A | 10/03
1 | A | 10/10
1 | B | 10/10
2 | C | 10/10