Results 1 to 7 of 7

Thread: Needs SQL Query

  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Smile Unanswered: Needs SQL Query

    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. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2011
    Posts
    4

    Post 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:
    Actual Sheet in excel spreadsheet
    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.
    I hope this information will help you. If you have any questions please let me know.

    Masam




    Quote Originally Posted by Wim View Post
    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. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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,
    	DataDate	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
    ID	MaxDate		LastDate	DataDate	DaValue
    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
    	DataDate >= LastDate
    Last edited by Wim; 04-20-11 at 11:50. Reason: Added PK
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Apr 2011
    Posts
    4

    Question 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. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Sorry, can't help you there, I am not a VBA programmer.

    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Apr 2011
    Posts
    4

    Lightbulb Thanks

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

    BRegards
    Masam

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •