Forum Discussion

ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago

Treemap - dynamic grouping question.

Hi,   I have a treemap that has supplies category, each colored either green (if the sales for that supplies category is above 90%), or red (if the sales is below 90%). So, my treemap has bunch of ...
Need Help
ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago

Hi Hardik,

 

I had been exploring option to attach and send you the file. Unfortunately the onedrive, dropbox etc is blocked at my work place. So we will need to go to longer path. I am sending you the complete insert statements that I am running along with the calculation I am performing with screenshot. Hopefully you can recreate the problem at your end.

 

For creating table:

 

create table testtbl (healthplan varchar(20), claimkey varchar(20), receiveddt datetime, reportdate datetime)

--

INSERT into testtbl VALUES (N'ABC', N'111', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'ABC', N'222', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'ABC', N'333', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'ABC', N'444', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'DEF', N'555', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'DEF', N'666', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'DEF', N'777', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'DEF', N'888', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'GHI', N'999', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'GHI', N'000', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-30 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'GHI', N'123', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-12 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'GHI', N'234', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-13 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'345', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'456', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'567', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'678', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'131', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'132', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'133', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'134', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'135', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'136', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'137', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'138', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'146', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'147', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'139', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'140', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'141', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'142', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'143', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'144', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'JKL', N'145', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'MNO', N'124', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-20 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'MNO', N'125', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-30 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'MNO', N'126', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-25 00:00:00.000' AS DateTime))

INSERT into testtbl VALUES (N'MNO', N'127', CAST(N'2018-01-01 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime))

 

--