1. Registered User
Join Date
Apr 2011
Posts
4

Hello Everyone,

Just facing the problem. I m getting data on daily basis against the ID in the form of of excel sheet. But in report/query i need the sum of the data row wise as the user given the dates (max date and last date) and it will show the sum result in next column. (date format DD-MM-YYYY)

Excel format
ID MaxDate Last Date 1-Mar-11 2-Mar-11 3-Mar-11 4-Mar-11
1543 04/03/2011 02/03/2011 21 7.6 8.2 9.1
1544 04/03/2011 01/03/2011 11 2.6 4.5 10.0

The excel columns are ID, MaxDate, LastDate, 1-Mar-11, 2-Mar-11, 3-Mar-11 , 4-Mar-11( the date columns shows the daily data and it will increase daily basis)
MAXDATE in most of the cases would be same but LastDate will be different. The result for should be like as the ID, MaxDate is 4-Mar-11 and LastDate is 02/03/2011 so I need to calculate only sum starts from 2-Mar-11 to 04-Mar-11.

ID MAXDATE LASTDATE SUM
1543 04/03/2011 02/03/2011 7.6+8.2+9.1 = 24.9

pls note that the user have to enter ID MAXDate and LASTDate to calculate sum. How can i write the sql query as I have already transfer the excel data into databases. I only sql query solution. Please anyone can help me

Regards
Masam

2. Registered User
Join Date
Nov 2004
Posts
1,428
I tried to solve your problem. But I don't understand your system.

Can you also place [CODE] tags around your data so it becomes more readable?
Code:
```ID	MaxDate		Last Date	1-Mar-11	2-Mar-11	3-Mar-11	4-Mar-11
1543	04/03/2011	02/03/2011	21		7.6		8.2		9.1
1544	04/03/2011	01/03/2011	11		2.6		4.5		10.0```
What is the meaning of the 1-Mar-11, 2-Mar-11 columns? How do they relate with the MaxDate and LastDate.
( the date columns shows the daily data and it will increase daily basis)
Are you referring to MaxDate and LastDate or to the 1-Mar-11, 2-Mar-11, ... columns? What will increase on a daily base: the number of records or will it add extra 5-Mar-11, 6-Mar-11 columns?

Give an example of the data that you receive on 03/03/2011, on 04/03/2011 and on 05/03/2011. Show the result that you expect for a few ID, MAXDate and LASTDate users could supply.

3. Registered User
Join Date
Apr 2011
Posts
4

## SQL Query

Code:
```ID	MaxDate		Last Date	1-Mar-11	2-Mar-11	3-Mar-11	4-Mar-11
1543	04/03/2011	02/03/2011	21		7.6		8.2		9.1
1544	04/03/2011	01/03/2011	11		2.6		4.5		10.0```
the meaning of the 1-Mar-11, 2-Mar-11 columns means Data Comes on Daily basis by date wise in the form of numerics and this column will increase daily
like if today is 04-Mar-11 then tomorrow one more column will add 05-Mar-11.

MaxDate is basically referring to the last date column. like 04-Mar-11 column means MaxDate will be 04/03/2011.
LastDate referring to the date from which we have to calculate the sum of different columns row wise against the provided ID.

Example:
Code:
```ID	MaxDate		Last Date	1-Mar-11	2-Mar-11	3-Mar-11	4-Mar-11
1543	04/03/2011	03/03/2011	21		7.6		8.2		9.1
1544	04/03/2011	01/03/2011	11		2.6		4.5		10.0```
Output Result should be in this format: (I need solution/query for this result)

Code:
```ID	MaxDate		Last Date	SUM
1543	04/03/2011	03/03/2011	17.3
1544	04/03/2011	01/03/2011	28.1```
Note: as I have mentioned only two records just for example but in a sheet its around 300 to 400 records on daily basis.

Masam

Originally Posted by Wim
I tried to solve your problem. But I don't understand your system.

Can you also place [CODE] tags around your data so it becomes more readable?
Code:
```ID	MaxDate		Last Date	1-Mar-11	2-Mar-11	3-Mar-11	4-Mar-11
1543	04/03/2011	02/03/2011	21		7.6		8.2		9.1
1544	04/03/2011	01/03/2011	11		2.6		4.5		10.0```
What is the meaning of the 1-Mar-11, 2-Mar-11 columns? How do they relate with the MaxDate and LastDate.
Are you referring to MaxDate and LastDate or to the 1-Mar-11, 2-Mar-11, ... columns? What will increase on a daily base: the number of records or will it add extra 5-Mar-11, 6-Mar-11 columns?

Give an example of the data that you receive on 03/03/2011, on 04/03/2011 and on 05/03/2011. Show the result that you expect for a few ID, MAXDate and LASTDate users could supply.

4. Registered User
Join Date
Nov 2004
Posts
1,428
You are dealing with an non-normalised data set. While this is somehow defensible for an Excel-sheet, it is not for a relational database. In the way it is in the Excel-sheet it is hard to program your requirement in SQL. If you could first normalise your data, things will become A LOT easier.

First make the table DaTable (I'm sure you can come up with a better name) in SQL Server. If you don't know what a normalised database is, use Google. For the moment lets say a normalised database is one where none of the tables have to be extended on a daily base with extra columns to store the data of that new day.

You will have to store your raw data in that table. You will notice that you will have to store part of the data over and over again (Id, MaxDate and Last Date). Instead of adding a column with a name that represents the date, you provide an extra column DataDate that will store that date.
Code:
```CREATE TABLE DaTable(
Id		INT	NOT NULL,
MaxDate		DATE	NOT NULL,
LastDate	DATE	NOT NULL,
DaValue		DEC(5, 1)	NOT NULL,
CONSTRAINT PK_DaTable PRIMARY KEY (Id, MaxDate, LastDate, DataDate)
)

-- Unnormalised raw data
ID	MaxDate		Last Date	1-Mar-11	2-Mar-11	3-Mar-11	4-Mar-11
1543	04/03/2011	02/03/2011	21		7.6		8.2		9.1
1544	04/03/2011	01/03/2011	11		2.6		4.5		10.0

-- normalised data, put this in the table
1543	04/03/2011	02/03/2011	01/03/2011	21
1543	04/03/2011	02/03/2011	02/03/2011	7.6
1543	04/03/2011	02/03/2011	03/03/2011	8.2
1543	04/03/2011	02/03/2011	04/03/2011	9.1

1544	04/03/2011	01/03/2011	01/03/2011	11
1544	04/03/2011	01/03/2011	02/03/2011	2.6
1544	04/03/2011	01/03/2011	03/03/2011	4.5
1544	04/03/2011	01/03/2011	04/03/2011	10.0

-- When all data is in the table, this will give you the required result
SELECT Id, MaxDate, LastDate, SUM(DaValue) as SumValues
FROM DaTable
WHERE Id = @Id AND
MaxDate = @MaxDate AND
LastDate = @LastDate AND
Last edited by Wim; 04-20-11 at 11:50. Reason: Added PK

5. Registered User
Join Date
Apr 2011
Posts
4

## If it is possible in Excel

Thanks Wim. Exactly you are very right. I have already know how to normalize the data but exactly i need to know the sql query structure.
I have tried my best to do in excel using even VBA but I am not as such very good. Is that possible you can help me out in excel as I have written some code. Below please find the pasted code.

In excel spreadsheet, the sheet1 contains 4th column in which I need DataSum. I have to pick sum of data from various dates columns from Sheet2.

Sheet 1:
Code:
```ID	MaxDate		Last Date	DataSum
1543	04/03/2011	02/03/2011
1544	04/03/2011	01/03/2011```
Sheet 2:

Code:
```ID		1-Mar-11	2-Mar-11	3-Mar-11	4-Mar-11
1543		21		7.6		8.2		9.1
1544		11		2.6		4.5		10.0```
Written Code on excel VBA as it needs to be more refine but i m stuck

Code:
```Sub Combined()
A = 1
B = 1

For I = 0 To 4
For j = 0 To 4
If Sheet1.Range("A1").Offset(I, 0) = Sheet2.Range("A1").Offset(j + 1, 0) Then
If Sheet1.Range("A1").Offset(I, 1) = Sheet2.Range("A1").Offset(0, j + 1) Then
Do
e = Sheet2.Range("A1").Offset(A, B) + Sheet2.Range("A1").Offset(A + j + 1, B + j + 1)
Loop Until Sheet1.Range("A1").Offset(d, 0) <> Sheet2.Range("A1").Offset(f, 0)
Sheet1.Range("A1").Offset(1, 3) = e
Else
B = B + 1
End If
Else
A = A + 1
End If
Next j
Next I
Sheet1.Range("A1").Offset(1, 3) = e

End Sub```
Regards
Masam

6. Registered User
Join Date
Nov 2004
Posts
1,428

Look at all the hoops you must jump through to try to get results out of that non-normalised data. Do one effort to normalise your data and export it to a database (SQL Server, Access, ...) . Once it gets there, you can do everything you want with very little effort in SQL. There are a lot of competent people on this forum who will gladly help you out if you get stuck with the SQL.

This is most probably not the quick solution you were hoping for, but it is in my opinion the best advice I can give you. But perhaps a VBA specialist on this forum can help you out. Or you could post your question on a VBA or Excel forum.

7. Registered User
Join Date
Apr 2011
Posts
4

## Thanks

It's OK but thanks so much for such a useful help/information.

BRegards
Masam