CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2008-r2
SkeetJon
SkeetJon

Reputation: 1491

SQL JOIN combining columns to a single column

I've written the following query for Microsoft SQL Server 2008 R2 ...

with 
downloads as
(
    select convert(varchar(10), timestamp, 112) as downloadDate, COUNT(*) as counter 
    from <download_table> 
    group by convert(varchar(10), timestamp,112)
),
uploads as 
(
    select CONVERT(varchar(10), dateadded, 112) as uploadDate, COUNT(*) as counter
    from <upload_table>
    group by CONVERT(varchar(10), dateadded, 112)
)
select 
    downloads.downloadDate, 
    uploads.uploadDate, 
    downloads.counter as dCount,
    uploads.counter as uCount
from downloads 
full join uploads on uploads.uploadDate = downloads.downloadDate
order by downloadDate desc;

which returns the following table...

downloadDate    uploadDate  dCount  uCount
20121211    NULL        40  NULL
20121210    NULL        238 NULL
20121207    20121207    526 4
20121206    20121206    217 12
20121205    NULL        108 NULL
20121204    20121204    190 13
20121203    NULL        141 NULL
20121130    20121130    248 187
20121129    NULL        134 NULL
20121128    NULL        102 NULL
20121127    20121127    494 57
20121126    NULL        153 NULL
20121119    20121119    319 20
20121118    NULL        4   NULL
20121116    20121116    215 16
20121112    20121112    431 144
20121109    20121109    168 48
20121108    20121108    132 181
NULL        20121125    NULL    3

but I can't get the two dates to combine into a single 'date' column without getting some NULL entries, nor can I get the NULL values in the dCount or uCount to display 0 instead of NULL.

Can somebody help me with this please ?

Upvotes: 2

Views: 754

Answers (4)

GarethD
GarethD

Reputation: 69759

Firstly, it is much better to cast a datetime to a date if you want to remove the time element, rather than converting to varchar, in SQL-Server 2008 you can simply use:

CAST(DateAdded AS DATE)

Then rather than using a FULL JOIN I would do this do this using UNION ALL, it should perform better (although I can't say 100% without testing on your actual data).

WITH Data AS
(   SELECT  [Date] = CAST(Timestamp AS DATE),
            [Downloads] = 1,
            [Uploads] = 0
    FROM    Download_Table
    UNION ALL
    SELECT  [Date] = CAST(DateAdded AS DATE),
            [Downloads] = 0,
            [Uploads] = 1
    FROM    Upload_Table
)
SELECT  [Date], 
        [Downloads] = SUM(Downloads),
        [Uploads] = SUM(Uploads)
FROM    Data
GROUP BY [Date]
ORDER BY [Date];

Upvotes: 1

Taryn
Taryn

Reputation: 247690

In SQL Serve,r you can use COALESCE around the date field which returns the first non-null value and ISNULL around the count totals to replace the null value with zero:

with 
downloads as
(
    select convert(varchar(10), timestamp, 112) as downloadDate, COUNT(*) as counter 
    from download_table 
    group by convert(varchar(10), timestamp,112)
),
uploads as 
(
    select CONVERT(varchar(10), dateadded, 112) as uploadDate, COUNT(*) as counter
    from upload_table
    group by CONVERT(varchar(10), dateadded, 112)
)
select 
    coalesce(downloads.downloadDate, uploads.uploadDate) as dDate, 
    isnull(downloads.counter, 0) as dCount,
    isnull(uploads.counter, 0) as uCount
from downloads 
full join uploads 
  on uploads.uploadDate = downloads.downloadDate
order by downloadDate desc;

See SQL Fiddle with Demo

Result:

|    DDATE | DCOUNT | UCOUNT |
------------------------------
| 20121211 |      2 |      0 |
| 20121210 |      1 |      1 |
| 20121207 |      1 |      0 |
| 20121206 |      2 |      1 |
| 20121208 |      0 |      1 |
| 20121209 |      0 |      1 |
| 20121204 |      0 |      1 |
| 20121205 |      0 |      1 |

Upvotes: 2

wxyz
wxyz

Reputation: 707

You can use coalesce and nvl like this:

with 
downloads as
(
    select convert(varchar(10), timestamp, 112) as downloadDate, COUNT(*) as counter 
    from <download_table> 
    group by convert(varchar(10), timestamp,112)
),
uploads as 
(
    select CONVERT(varchar(10), dateadded, 112) as uploadDate, COUNT(*) as counter
    from <upload_table>
    group by CONVERT(varchar(10), dateadded, 112)
)
select 
    coalesce(downloads.downloadDate, uploads.uploadDate) as dDate, 
    nvl(downloads.counter, 0) as dCount,
    nvl(uploads.counter, 0) as uCount
from downloads 
full join uploads on uploads.uploadDate = downloads.downloadDate
order by downloadDate desc;

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65274

Depending on your SQL dialect, something like IFNULL(), NVL(), COALESCE(), IIF() etc. will help you get rid of the NULLs in favour of a date in the past, such as '18000101'.

After having done that, you can use MAX(), SWITCH(), IIF(), IF() or friends to make a single "last usage date" column.

Upvotes: 2

Related Questions