CODEWITHSUNDEEP
sql-server
DoArNa
DoArNa

Reputation: 532

Three dimensional array in SQL Server

I apologize in advance if the title of this question is not right but I do not know how to word it. What I am trying to do is:

I want to show a table on this format. 

                  week_1      week_2         week_3
sub_account_1     25 calls    35 calls       15 calls
sub_account_2     35 calls    17 calls       13 calls      
sub_account_3     26 calls    5 calls       11 calls

I am trying something like:

SELECT count(calls), date, sub_account_name
WHERE master_account = 12
AND date IN ('2017-01-07','2017-01-14','2017-01-21')
GROUP BY sub_account_name, date

This query is not showing what I want, instead is showing:

sub_account_1    week_1   35 calls
sub_account_1    week_2   25 calls
sub_account_1    week_3   17 calls
sub_account_2    week_1   13 calls
sub_account_2    week_2   11 calls 
......
......
sub_account_3    week_3    15 calls

I would appreciate if someone can help me how to write the query to produce the desired table.

Edit to show sample data:

There are two tables (Account and Call)

Account
ID           int
AccountId    int
Name         varchar(50)
master       int

master accounts and subaccounts are both same datatype and stored on the same table. If an account has accountId = master it means that it is a master account. All its subaccount will hold the accountId value of their master as a master value.

Call
ID            int
callId        int
date          datetime
f_accountId   int

I need all the calls on the following dates ('2017-01-07','2017-01-14','2017-01-21') on the format showed above.

Upvotes: 0

Views: 692

Answers (1)

Jason
Jason

Reputation: 945

SELECT Name,[14] as 'Week 1',[15] as 'Week 2',[16] as 'Week 3'
FROM (SELECT [Name],DATEPART (WK,c.date) as 'Week'   
    FROM Account a  --Highlight this subquery to determine the week numbers
        JOIN [CALL] c ON a.AccountId = c.f_accountId
    WHERE [master] = 2
     ) p
PIVOT
(
COUNT(WEEK)
FOR WEEK IN ([14],[15],[16]) --These numbers are the week numbers
) as pvt

Upvotes: 1

Related Questions