How to query to Fetch Data of each Name in List for a Date Range

Question

I have a table like

Name	Total	Date
A	10	2020-12-01
B	5	2020-12-01
A	10	2020-12-02
B	15	2020-12-02

now I have a List of Name and Date List Like

@NameList = '[A],[B],[C],[D]'
@DateList =  '[2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04]'

How to query to fetch data for each name and date available in @NameList and @DateList?

Expected Result

Name	Total	Date
A	10	2020-12-01
B	5	2020-12-01
C	0	2020-12-01
D	0	2020-12-01
A	10	2020-12-02
B	15	2020-12-02
c	0	2020-12-02
D	0	2020-12-02
A	0	2020-12-03
B	0	2020-12-03
c	0	2020-12-03
D	0	2020-12-03
A	0	2020-12-04
B	0	2020-12-04
c	0	2020-12-04
D	0	2020-12-04

Hi @DaleK , Name List And Date Rand Coming From UI – kundan kumar Dec 20 '20 at 06:48 — kundan kumar, Dec 20 '20 at 06:48

score 0 · Answer 1 · answered Dec 20 '20 at 12:12

How about a JSON approach?

Assuming that the names and dates never contain a double quote, you can turn them to JSON strings with string functions, and unnest each list as rows with openjson(). The last step is to bring the original table with a left join.

declare @NameList nvarchar(max) = '[A],[B],[C],[D]';
declare @DateList nvarchar(max) =  '[2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04]';

select n.name, coalesce(t.total, 0) as total, d.dt 
from openjson('[' + replace(replace(@NameList, '[', '"'), ']', '"') + ']') 
    with(name nvarchar(max) '$') n
cross join openjson('[' + replace(replace(@DateList, '[', '"'), ']', '"') + ']') 
    with(dt date '$') d
left join mytable t on t.name = n.name and t.date = d.dt

score 0 · Answer 2 · answered Dec 20 '20 at 14:32

You can break apart the lists using string_split() and then use left join to bring in the existing data:

declare @NameList nvarchar(max) = '[A],[B],[C],[D]';
declare @DateList nvarchar(max) =  '[2020-12-01],[2020-12-02],[2020-12-03],[2020-12-04]';

select v.name, v.date, coalesce(t.total, 0) as total
from string_split(@namelist, ',') n cross join
     string_split(@datelist, ',') d cross apply
     (values (replace(replace(n.value, '[', ''), ']', ''),  
              convert(date, replace(replace(d.value, '[', ''), ']', ''))
             )
     ) v(name, date) left join
     yourtable t
     on t.name = v.name and t.date = v.date

The only real trick here is to use apply to remove the square braces.

Here is a db<>fiddle.

score -1 · Answer 3 · answered Dec 20 '20 at 07:31

-1

use this

select t1.name,t2.[date]  from 
[table] t1
cross apply [table] t2

answered Dec 20 '20 at 07:31

nimajv

423
3
11

1

Please illustrate how this solves the OPs problem? It doesn't appear to take into account any of the ranges concerned. – Dale K Dec 20 '20 at 07:33
1

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – Donald Duck Dec 20 '20 at 09:08

How to query to Fetch Data of each Name in List for a Date Range

3 Answers3