Query help in SQL 2012

Team - I am dire need of your help for the below problem. Kindly help me with a sample query.

I've attached the excel sample.

Sheet Name: Raw Data
This is how i get the raw data (ofcourse this is just a sample, actual file will have ten's of thousand's of rows & even the levels in customer / organization / product is dynamic (as in, it may have level 1 to level 9).

Sheet Name: Needed Values
This sheet is the required value from the raw data. as in the Raw data sheet may have more than 1 level of a customer with more than 100 / so unique customer id's however we may need only 10 / as required in this sheet.

Sheet Name: Report Format
This is a sample of final output what i need to present.

Ex: Look at Needed Values sheet, it has 4 customers. Every customer need to be checked amongst the levels & then it should filter the org and then the product then sum the value in "SU" and paste the output.

Please give me a sample query. Very urgent.
ManjuIT - Project ManagerAsked:
Who is Participating?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi Manju,

sorry for the delay but I needed more time to see a solution for you. The only thing that came to my mind it was to use a cursor and build the query dynamically:

DECLARE KeyLevel_cursor CURSOR FOR  
FROM [Mapping Sheet]

OPEN KeyLevel_cursor   
FETCH NEXT FROM KeyLevel_cursor INTO @KeyLevel   

	IF @MySelect IS NULL
		SET @MySelect = 'SELECT * FROM [Raw Data]'
		SET @MySelect = @MySelect + ' 
			SELECT * FROM [Raw Data]'
	SELECT @MySelect = @MySelect + '
					 WHERE [' + @KeyLevel + '] IN (' + STUFF((SELECT ''',''' + M.KeyName
														FROM [Mapping Sheet] M
														WHERE M.KeyLevel = @KeyLevel
														FOR XML PATH('')), 1, 2, '') + ''')'

	FETCH NEXT FROM KeyLevel_cursor INTO @KeyLevel   

CLOSE KeyLevel_cursor   
DEALLOCATE KeyLevel_cursor 

--PRINT @MySelect 
EXEC sp_executesql @MySelect 

Open in new window

Unfortunally your sample data has [Product_Total Products (ID)] = '[000000000]' in all rows so for me returned all rows. Check if with real data this works properly.
Mike EghtebasDatabase and Application DeveloperCommented:
Your data tables are not normalized and requires lots of unnecessary SQL acrobatic moves (bad skills to learn). Customer_Level1,  Customer_Level2, etc. is sign of poor data table design. The same with the products. I know you were hoping for a quick solution but got to fix the data tables first.

ManjuIT - Project ManagerAuthor Commented:
unfortunately this is how i get the data from clients in csv / xls format. the raw data format is out of our control. Hence the ask..,
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Mike EghtebasDatabase and Application DeveloperCommented:
As you are waiting for the other experts to give you possibly a quick solution to take care of your need, we can work towards normalizing and storing the data in a manner so we can query them. If this is okay with you please comment on the following:

Q1: What does SU and GGN stand for?
Q2: Could you put together some organization to your data where you never have to use CustomeLeve1, CustomerLevel2, etc?

Someting like:

CustomerID   CustomerName
1                        abc
2                        nbc
3                        sony

LevelID       LevelDescription
1                      ...
2                      ...
3                      ...

LevelID      CustomerID    SU      GGN     DATE
1                     1                   12        34
2                     1
1                     3


This will take time and effort and it will not be quick fix. It is like you need a room to stay on the 3rd floor while the foundation of the building is not built yet.
ManjuIT - Project ManagerAuthor Commented:
@eghtebas :
:D I understand.

Yes, I am willing to work with you on this.

To answer:
Q1: What does SU and GGN stand for?
These are measures / values for the respective line item.

Q2: Could you put together some organization to your data where you never have to use CustomeLeve1, CustomerLevel2, etc?

Let me tell you what I've done so far, atleast on the table front.

Since I cannot change the way I get the raw data, however the below are under my control.

I do know the customer's / Org / Product along with the required level.

Ex: My MasterLevel Table is built like below

ReportID - nvarchar(max),
KeyName - nvarchar(max),
KeyType - nvarchar(max),
KeyLevel - nvarchar(max)

Ex Data

The first field is auto-number, 2nd column is a unique identifier for the report (we have various reports / ids, hence the need for this column), 3rd column is the code which we need value from, 4th column is an identifier, i.e., if its Customer / Org / Product and Last column determines which level we need to search this data from.

So I have the raw data & this table.., :(
Mike EghtebasDatabase and Application DeveloperCommented:
KeyID	ReportID	KeyName	        KeyType 	KeyLevel
1	6501	        [2000000007]	Customer	Level2

Open in new window

This table has one row. In order for me to get a better sense of the requirement, please add 10 rows of data to include more variations.

What Level2 stands for? Any description? Are there fixed number of levels? Are they edited once in awhile?

KeyType, give variations other than Customer.

ManjuIT - Project ManagerAuthor Commented:
Actually had a call last night with the client & came up with new idea. as in,

if you look at the sample file i've attached, look at the Raw data sheet.

There are 3 customer levels, 3 org levels and 3 product levels with 2 measures. What i need to do is probability of those.., as in

assuming the below
customer.org.product.sum(values) for each measure
1.1.1.sum(values) for each measure
1.1.2.sum(values) for each measure
1.1.3.sum(values) for each measure
1.2.1.sum(values) for each measure
1.2.2.sum(values) for each measure

Here the only catch might be the levels might be dynamic.
ManjuIT - Project ManagerAuthor Commented:
Mike - I've added another sample sheet with the data you asked to test.

Raw Data: Raw data sheet
SU & GGN are values.

Mapping: This sheet has the variations you asked for. Key Level column will refer to the column name in Raw Data column.

Please help.
ManjuIT - Project ManagerAuthor Commented:
Mike -

Btw, I tried the below query for 1 customer, 1 org and 1 product. checked the values & its matching with what i want.

Can you help modify this code to pick up customer, org and product in loop to pick up all data in the Test_Mapping table?

Select [CBD Organization_Level 3 (ID)] as Organization,
[Customer_Customer Level 3 (ID)] as Customer, [Product_Category (ID)] as Product,
Convert(Decimal(18,1),(SUM(Convert(float,[SU]))/1000)) as 'SU', 
Convert(Decimal(18,0),(Sum(Convert(float,[GGN])))) as GGN
 from Test_CN
where [CBD Organization_Level 3 (ID)] in
(Select KeyName from Test_Mapping where KeyType = 'Organization')
Group by [CBD Organization_Level 3 (ID)], [Customer_Customer Level 3 (ID)],
[Product_Category (ID)]

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
Hi Manju,

It seems you have made some progress here. This is good but different than what we were trying to accomplish. To get others (most of them more experienced than me in subqueries) please consider deleting this question with points refunded but posting a new question with your latest T-SQL which seems so close to what you want.

I will try also to part take in your new question. With zero comment on a new (improved) question you will have a better  chance to get an answer.

ManjuIT - Project ManagerAuthor Commented:
Mike - I sort of wrote the code only coz of your pointers. so, deleting this question is something i do not yet want to do. If im closing, it will be "With points" for you. no doubt about it. Thanks for helping so far.

In the interim, I thought of an alternate approach & let me know if you can help me on the below:

Based on my above query, i sort of identified, this needs to be more of a probability, i.e., 1.1.1, 1.1.2, & so on for Org.customer.product combination.

I have had a detailed connect with my client & got the overall headers / static headers for my data table. From that, I realized i have a combination of 9 levels across org, customer & product. So technically my matrix should be 9 * 9 * 9 = 729 possible queries.

My Theory is..,
Let me create a table called "Matrix" with the below columns:
MatrixID - Unique identifier
CustomerLevel - nvarchar(max)
OrgLevel - nvarchar(max)
ProductLevel - nvarchar(max)
MatrixName - nvarchar(max)
MatrixQuery - nvarchar(max)

Ex Data:
1 | Customer_Level1 | Org_Level1 | ProductLevel1 | 111 | Org_Level1 as Organization, Product_Level1 as Product, Customer_Level1 as Customer

So, basically I will create 729 possible row items in this table.

I will have another table called, "Mapping Table" with the below columns:
MappingID - Unique Identifier
ReportID - nvarchar(max)
KeyName - nvarchar(max)
KeyType - nvarchar(max)
KeyLevel - nvarchar(max)

Ex: Data
1 | 123 | [90035878] | Customer | Customer_Level1
2 | 123 | [90035900] | Organization | Org_Levl2
3 | 123 | [90035901] | Product | Product_Levl2
& so on..,

so typically, using the 2 tables, i should be able to create the list of queries i need to run..,
lets say for report 123 i just need
1.2.2 Matrix name

so my stored procedure can be something like..,

Select @Matrix but it should be in loop to run all the possible output of the previous table..,

do you follow me? Im sorry.., working straight for 2 days now.., :(
ManjuIT - Project ManagerAuthor Commented:
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi Manju,

I just passed by and I don't want to be influenced by the last comments so I will need to know some things:
- How many files do you receive from the clients?
- You have a Mapping sheet. This is also coming from clients or it's already worked by you?
- Do you already have a process to import data from the files? If affirmative, what it looks like?
ManjuIT - Project ManagerAuthor Commented:
Victor -
1. I may get 1 / more than 2 files. it depends on the report requirement. None the less, if you look at the excel file "Test_EE" sample file, it has 2 tabs.

2. "Raw Data" Tab - this is how i will get the data from the clients.
"Mapping" Tab - I will create this basis on the requirements(Filters will be given by the client, Ex: KeyName column will be given. I will manually check in the "Raw Data" to know which header each line item belongs to  & will prepare the mapping sheet.

3. Importing data to SQL - yes. I use openrowset to import the data to SQL.

however I am not able to convert it into a dynamic query for calculation..,
Vitor MontalvãoMSSQL Senior EngineerCommented:
Let me know if I understood.
Mapping is a static (or almost) table and you'll only need to update it in very rare situations, right?
So, if I want to work with a KeyName I'll need first to find it in Mapping, like this:
FROM Mapping
WHERE KeyName='[90078406]'

Open in new window

This will return a single row:
KeyID      ReportID      KeyName      KeyType      KeyLevel
10              1011              [90078406]      Organization      CBD Organization_Level 5 (ID)

And now I know where to query in RawData, right? Like this:
FROM RawData
WHERE [CBD Organization_Level 5 (ID)]='[90078406]'

Open in new window

And what now? What should be the next steps?
ManjuIT - Project ManagerAuthor Commented:
Yes, Mapping sheet has KeyName & KeyLevel.

What I need to do is,

For every combination of Organization, Customer & Product, need to do a pivot equivalent.

For example, in the raw data, there are 6 levels(columns) for org, but according to mapping, i only need 5 distinct levels. (filter just for Org in Mapping sheet & remove duplicates in KeyLevel column)

Similarly, we need to do the same for Customer & product.., so

For each org, each customer & each product, sum(SU), sum(GGN) etc..,

so typically, raw data may have many columns but my output table should only have

Organization, Customer, Product, SU, GGN

Hope i was able to explain clearly.., sorry, been up since 2 days, only trying to crack this . :(
ManjuIT - Project ManagerAuthor Commented:
Vic - It throws me an error. will post the error tomorrow. sorry was stuck on something else..
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't forget to post the error.
ManjuIT - Project ManagerAuthor Commented:
Nevermind, tweaked your code a little to get it working. thanks a ton
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.