Pages

Thursday, September 8, 2016

SQL syntax to show all days in a week

This is SQL SERVER trick is to show the entire daily data from database even there is no transaction data in a particular day

Let say you have a table with the below structure

Table: dailysharelike
Columns:
datetimetrans DATETIME
share_number INT
like_number INT

The data rows are
2016-09-05 00:00:00 | 2   | 1
2016-09-05 00:00:00 | 2   | 3
2016-08-25 00:00:00 | 10  | 1
2016-08-30 00:00:00 | 100 | 90
2016-08-25 00:00:00 | 150 | 80

If you do the query with simple query:

SELECT DATEPART(dw,datetimetrans) AS weekdaynum, DATENAME(dw,datetimetrans) AS weekdayname,
 ISNULL(SUM(like_number),0) AS like_number,
 ISNULL(SUM(share_number),0) AS share_number
 FROM dailysharelike
 GROUP BY DATEPART(dw,datetimetrans), DATENAME(dw,datetimetrans)
 GROUP BY DATEPART(dw,datetimetrans), DATENAME(dw,datetimetrans)

then you will get this (without Sunday, Wednesday, Friday and Saturday)

2 | Monday   | 4   | 4
3 | Tuesday  | 100 | 90
5 | Thursday | 160 | 81

To show all the days in a week, you can use this syntax

SELECT DISTINCT weekdaynum, weekdayname, SUM(like_number) AS like_number,
SUM(share_number) AS share_number FROM
(SELECT 1 AS weekdaynum, 'Sunday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 2, 'Monday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 3, 'Tuesday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 4, 'Wednesday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 5, 'Thursday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 6, 'Friday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT 7, 'Saturday' AS weekdayname, 0 AS like_number, 0 AS share_number UNION
 SELECT DATEPART(dw,selfie_upload) AS weekdaynum, DATENAME(dw,selfie_upload) AS weekdayname,
 ISNULL(SUM(like_number),0) AS like_number,
 ISNULL(SUM(share_number),0) AS share_number
 FROM selfie
 GROUP BY DATEPART(dw,selfie_upload), DATENAME(dw,selfie_upload)
) AS hasil GROUP BY weekdaynum, weekdayname
ORDER BY weekdaynum, weekdayname

1 | Sunday    | 0   | 0
2 | Monday    | 4   | 4
3 | Tuesday   | 100 | 90
4 | Wednesday | 0   | 0
5 | Thursday  | 160 | 81
6 | Friday    | 0   | 0
7 | Saturday  | 0   | 0

1 comment :

  1. hi expert,.
    Can u help me about to show point in chart line powerbuilder,

    anazezh@gmail.com - 081290071777

    ReplyDelete