Calculate sum data and insert into another in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate sum data and insert into another in SQL

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-05-17 : 11:13:46

I need to calculate sum of hours based on period column and insert into another table. Please see below example data.


tbl_Summary
-------------
Client_id	Store_id	attribute1	Period	  Hours	    attributes
1	           16	          1	       2/25/2007  1054.8	 1
1	           16	          1	       3/11/2007  1112.8	 1
1	           16	          1	       3/25/2007  1164.8	 1
1	           16	          1	       4/8/2007	  1383.2	 1
1	           16	          1	       4/22/2007  1351.6	 1
1	         16	      1	         9/21/2008 1523.6	 1
1	         16	      1	        10/5/2008  1020.26	 1
1	         16	      1	        10/19/2008 939.94	 1
1	         16	      1	        11/2/2008  903.14	 1
1	         16	      1	        8/9/2009   866.66	 1
1	         16	      1	        8/23/2009  915.48	 1
1	         16	      1	        9/6/2009   894.26	 1
1	         16	      1	        9/20/2009 1458.58	 1
1	           18	          1	       6/1/2008	  1112.8	 1
1	           18	          1	       6/15/2008  1164.8	 1
1	           18	          1	       6/29/2008  1383.2	 1 
1	         18	       1	 7/13/2008 1351.6	 1
1	         18	       1	12/28/2008 1523.6	 1
1	         18	       1	 1/11/2009  979.2	 1
1	         18	       1	 1/25/2009  913.2	 1
1	         18	       1	 2/8/2009   930.6	 1
1	         18	       1	 2/22/2009 1143.4	 1
1	         18	       1	 5/31/2009 1066.16	 1
1	         18	       1	 6/14/2009  1174.8	 1
1	         18	       1	 6/28/2009  1099.2	 1
1	         18	       1	 7/12/2009  1014.5	 1

Out put table will be like this.
tbl_history
--------------

client_id	store_id	attribute1	hours	attributes
1	        16	         1	       8521.92	  1
1	        18	         1	      11196.92	  1

Conditions for sp

1. Parameters to sp are only @client_id, @attribute1, @attributes

2. Find Max(Period) and go back to 52 weeks and calculate Sum(Hours) where Store_id= ? (in example 16 and 18 or each store_id) and client_id= @client_id and attribute1 = @attribute1 and attributes = @attributes. ie. Sum(Hours) will change based on store_id and when store_id will change period will change. See BOLD

3. Insert into another table all output tbl_history.

Please give me solution. If you have any question ask me.

Thanks in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-17 : 12:14:16

Here is the main statement to get your data. If you leave any of the attributes NULL then you'll get data for all. Do you need help inserting the results to the history table or turning this into a stored procedure?


select s.client_id
       ,s.store_id
       ,s.attribute1
       ,sum(s.[hours]) [hours]
       ,s.attributes
from   tbl_summary s
join   (
       select client_id
              ,store_id
              ,attribute1
              ,attributes
              ,max(period) maxperiod
       from   tbl_summary
       where  client_id = isNull(@client_id, client_id)
       and    attribute1 = isNull(@attribute1, attribute1)
       and    attributes = isNull(@attributes, attributes)
       group by client_id
              ,store_id
              ,attribute1
              ,attributes
       ) d
              on d.client_id = s.client_id
              and d.store_id = s.store_id
              and d.attribute1 = s.attribute1
              and d.attributes = s.attributes
where  s.period >= dateadd(week, -52, d.maxperiod)
group by s.client_id
       ,s.store_id
       ,s.attribute1
       ,s.attributes

Be One with the Optimizer
TG

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-05-17 : 13:02:41

@TG: Thanks. This is works for me.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-17 : 13:53:45

You're welcome.

Be One with the Optimizer
TG

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-05-18 : 11:05:16

@TG: I am facing a problem here.

1. @client_id = 1, @attribute1 =1, @attributes =1

It is working fine.

2. @client_id = 1, @attribute1 =1,@attribute1 =2, @attributes =3
and used below for above condn.


select s.client_id
       ,s.store_id
       ,s.attribute1
       ,sum(s.[hours]) [hours]
       ,s.attributes
from   tbl_summary s
join   (
       select client_id
              ,store_id
              ,attribute1
              ,attribute2
              ,attributes
              ,max(period) maxperiod
       from   tbl_summary
       where  client_id = isNull(@client_id, client_id)
       and    attribute1 = isNull(@attribute1, attribute1)
       and    attribute2 = isNull(@attribute2, attribute2)
       and    attributes = isNull(@attributes, attributes)
       group by client_id
              ,store_id
              ,attribute1
              ,attribute2
              ,attributes
       ) d
              on d.client_id = s.client_id
              and d.store_id = s.store_id
              and d.attribute1 = s.attribute1
              and d.attribute2 = s.attribute2
              and d.attributes = s.attributes
where  s.period >= dateadd(week, -52, d.maxperiod)
group by s.client_id
       ,s.store_id
       ,s.attribute1
       ,,s.attribute2
       ,s.attributes

This is also work fine this condn, but for
1. @client_id = 1, @attribute1 =1, @attributes =1 setting(updating) sum(s.[hours]) to NULL in historical_table

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-18 : 11:33:56

Looks like you've got two commas in front of ",,s.attribute2" in the outer group by clause. But if that is just a typo of this post then:

It sounds like the problem may be caused by some NULL values somewhere. If any of those attribute columns are nullable you'll need to define what you want to happen when the data contains nulls.

It is hard to trouble shoot without some sample data and expected results. If you want help then please provide that data. Make it the form of executable code (DDL and DML)

Be One with the Optimizer
TG

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-05-18 : 12:17:36

@TG: This iput data. For Output tbl_history conditions are same for 52 weeks.

tbl_Summary
-------------
Client_id	Store_id	attribute1 attribute2	Period	  Hours	    attributes
1	         16	      1	         	Null	9/21/2008 1523.6	 1
1	         16	      1	        	Null	10/5/2008  1020.26	 1
1	         16	      1	        	Null	10/19/2008 939.94	 1
1	         16	      1	        	Null	11/2/2008  903.14	 1
1	         16	      1	        	Null	8/9/2009   866.66	 1
1	         16	      1	        	Null	8/23/2009  915.48	 1
1	         16	      1	        	Null	9/6/2009   894.26	 1
1	         16	      1	        	Null	9/20/2009 1458.58	 1
1	         16	      1 	    	3	7/13/2008 1351.6	 2
1	         16	      1          	3	12/28/2008 1523.6	 2
1	         16	      1     	 	3	1/11/2009  979.2	 2
1	         16	      1	         	3	1/25/2009  913.2	 2
1	         16	      1	         	3	2/8/2009   930.6	 2
1	         16	      1	         	3	2/22/2009 1143.4	 2
1	         16	      1	         	3	5/31/2009 1066.16	 2
1	         16	      1	         	3	6/14/2009  1174.8	 2
1	         16	      1	         	3	6/28/2009  1099.2	 2
1	         16	      1	         	3	7/12/2009  1014.5	 2

Out put table
tbl_history
----------

client_id	store_id	attribute1 attibute2	hours	attributes
1	        16	         1	       NULL     8521.92	  1
1	        16	         1	       3        1196.92	  2

The code in SP I am using like this

if (@attributes = 1)
begin
					
	update tbl_History
	set  hours_all52wks = ( select sum(s.[hours]) [Hours]
	from reg_summary_rowno s
	join ( select client_id ,store_id ,attribute1 ,attributes  
              ,max(period) maxperiod
              from   reg_summary_rowno  where  client_id = @client_id
               and    attribute1 =@attribute1
	       and    attributes = @attributes
		group by client_id,store_id,attribute1,attributes
          ) d
		on d.client_id = s.client_id and d.store_id =  
                s.store_id  and d.attribute1 = s.attribute1
		and d.attributes = s.attributes
       where  s.period >= dateadd(week, -52, d.maxperiod) 
       and tbl_History.client_id = s.client_id
	and tbl_History.store_id = s.store_id  and 
        tbl_History.attribute1 = s.attribute1 
	and tbl_History.attributes = s.attributes
	group by 
        s.client_id,s.store_id 
                   ,s.attribute1,s.attributes )
end
else if (@attributes = 2)
begin
					
	update tbl_History
	set  hours_all52wks	= ( select sum(s.[hours]) [Hours]
	    from reg_summary_rowno s
	     join ( select client_id ,store_id ,attribute1,attribute2 
                   ,attributes ,max(period) maxperiod
		   from   reg_summary_rowno  where  client_id = 
                   @client_id and    attribute1 = @attribute1
		   and    attribute2 = @attribute2
		   and    attributes = @attributes
		   group by 
                   client_id,store_id,attribute1,attribute2,attributes
             ) d
		  on d.client_id = s.client_id and d.store_id = 
                  s.store_id  and d.attribute1 = s.attribute1
		  and d.attribute2 = s.attribute2 and d.attributes = 
                  s.attributes
	where  s.period >= dateadd(week, -52, d.maxperiod) 
        and tbl_History.client_id = s.client_id
	and tbl_History.store_id = s.store_id  and 
        tbl_History.attribute1 = s.attribute1 
	and tbl_History.attribute2 = s.attribute2 and 
        tbl_History.attributes = s.attributes
        group by s.client_id,s.store_id 
                  ,s.attribute1,s.attribute2,s.attributes )
end

If I use @client_id = 1, @attribute1 =1, @attributes =1. @client_id = 1, @attribute1 =1, @attribute1 =3 @attributes =2 of [hours] is setting 'Null'

If @client_id = 1, @attribute1 =1, @attribute1 =3 @attributes =2. @client_id = 1, @attribute1 =1, @attributes =1 of [hours] is setting 'Null'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-18 : 13:30:03

quote:
I provide sample data and expected results. Please provide solution

Getur,
Please don't send personal email requests like this. We (contributors) all have actual jobs and can't necessarily drop everything to make sure you can do your job. Also, if you are in a hurry you should have provided the sample data in the form that I asked for so I don't need to translate your text into sql statements. "Make it the form of executable code (DDL and DML)"

Here's how if you don't know what DDL/DML is:

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources