April 25, 2012 at 12:00 pm
Hi,
I need to write SQL query to assign a running total.
Scenario:
A running total of everytime a Worker comes into a building has to be maintained. This has to be grouped by building name and day of the week.
Everytime a person enters the building a record is created, and a column has the value of 1 assigned to it ( a default column )
An additional column has to be created with the running total, so the first time will be 1 and the second 2 and so on.
Example Required Output:
Name Default RunningTotal Day Building
MrA 1 1 monday A
MrA 1 2 monday A
MrA 1 3 monday A
MrB 1 1 Monday B
MrB 1 2 Monday B
MrA 1 1 Tuesday B
MrA 1 2 Tuesday B
I would be grateful if you could help me out writing this query.
Thanks
April 25, 2012 at 1:02 pm
From our very own Jeff Moden...
Solving the Running Total and Ordinal Rank Problems[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 1:07 pm
are you trying to update this new field how many times a person enters a building? or do you need to report on how many times a person enters a specific building?
If your updating your table and if you have an id collumn you could do something like
; with CTE
as
(
select id,ROW_number() over (partition by name,dayy,build order by id) as total from t1
)
update t1 set total=cte.total
from CTE inner join t1 on t1.id = cte.id
OR i would drop the`running total collumn as really this not required you can still diplay the data in what ever format you want IE
if my table looks like this
ID NAME VALUE TOTAL DAYY BUILD
1 MR A 1 NULL Monday A
2 MR A 1 NULL Monday A
3 MR A 1 NULL Monday A
4 MR A 1 NULL Tuesday A
5 MR A 1 NULL Tuesday A
6 MR B 1 NULL Tuesday A
7 MR B 1 NULL Tuesday A
8 MR B 1 NULL Tuesday B
9 MR B 1 NULL Tuesday B
10 MR B 1 NULL Tuesday B
11 MR B 1 NULL Tuesday B
12 MR B 1 NULL Tuesday B
i can retrive the data like so
select name,ROW_number() over (partition by name,dayy,build order by id) as total,dayy,Build
from t1
Results look like
NAME TOTAL DAYY BUILD
MR A 1 Monday A
MR A 2 Monday A
MR A 3 Monday A
MR A 1 Tuesday A
MR A 2 Tuesday A
MR B 1 Tuesday A
MR B 2 Tuesday A
MR B 1 Tuesday B
MR B 2 Tuesday B
MR B 3 Tuesday B
MR B 4 Tuesday B
MR B 5 Tuesday B
OR
select name,SUM(value),dayy,build
from t1
group by name,dayy,build
results will look like this
NAME TOTAL DAY BUILD
MR A 3 Monday A
MR A 2 Tuesday A
MR B 2 Tuesday A
MR B 5 Tuesday B
Hope this helps
***The first step is always the hardest *******
April 25, 2012 at 1:09 pm
Or you might be able to use ROW_NUMBER() based on your brief sample data and the output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 1:23 pm
I don't think you really need a 'running total' if visits are just incremented by 1. You could set up a trigger, or just use something like this for your inserts, changing @name and @building values as needed:
declare @name varchar(10), @building varchar(10)
set @name = 'MrA'
set @building = 'A'
insert into bldg_maintenance_records
values(@name,1,
(select isnull(max(running_total),0)+1 from bldg_maintenance_records where Name = @name and Day= datename(weekday,getdate()) and Building = @building),
datename(weekday,getdate()),
@building)
go
select * from bldg_maintenance_records
go
_________________________________
seth delconte
http://sqlkeys.com
April 25, 2012 at 1:26 pm
Personally I would not store this total in your database. It makes updates and deletes way more difficult than they need to be. If you need this type of thing the time to calculate is when you pull the data out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 1:32 pm
This example works with your sample data.
;with Data(Name, DefaultValue, DayValue, Building) as
(
select 'MrA', 1, 'monday', 'A' union all
select 'MrA', 1, 'monday', 'A' union all
select 'MrA', 1, 'monday', 'A' union all
select 'MrB', 1, 'Monday', 'B' union all
select 'MrB', 1, 'Monday', 'B' union all
select 'MrA', 1, 'Tuesday', 'B' union all
select 'MrA', 1, 'Tuesday', 'B'
)
select *, ROW_NUMBER() over(partition by DayValue, Building order by DayValue, building, Name) as RowNum
from Data
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 2:19 pm
Hi .. Thank you all for the suggestions ..
I need create this temp table and join this to main table in a Ssis package .. So it will be more like a look up table and not something I will store in the database.
I can't try out any of the suggestions right now .. All I have is this phone with a broken screen.
I thank you for your help
April 25, 2012 at 3:37 pm
I don't want a total but an out like you displayed ie
Results look like
NAME TOTAL DAYY BUILD
MR A 1 Monday A
MR A 2 Monday A
MR A 3 Monday A
MR A 1 Tuesday A
MR A 2 Tuesday A
MR B 1 Tuesday A
MR B 2 Tuesday A
MR B 1 Tuesday B
MR B 2 Tuesday B
MR B 3 Tuesday B
MR B 4 Tuesday B
MR B 5 Tuesday B
I will try out the code you suggested
April 25, 2012 at 7:10 pm
Why would you:
1. Store a 1 in column default? In what way, is "1" a unique attribute of the entities in the table object?
2. Store a running total? As others has observed, calculate this when needed.
3. Store "Monday" or a weekday name? You should be storing a DATETIME value. This will facilitate sorting you report chronologically.
Here's a solution on this basis:
DECLARE @entries TABLE (person VARCHAR(20), date DATETIME, BLDG VARCHAR(10))
INSERT INTO @entries
SELECT 'MR A','2012-04-23 07:00', 'A'
UNION ALL SELECT 'MR A','2012-04-23 08:00', 'A'
UNION ALL SELECT 'MR A','2012-04-23 09:00', 'A'
UNION ALL SELECT 'MR A','2012-04-23 10:00', 'A'
UNION ALL SELECT 'MR A','2012-04-24 07:00', 'A'
UNION ALL SELECT 'MR B','2012-04-24 08:00', 'A'
UNION ALL SELECT 'MR B','2012-04-24 09:00', 'A'
UNION ALL SELECT 'MR B','2012-04-24 10:00', 'B'
UNION ALL SELECT 'MR B','2012-04-24 11:00', 'B'
UNION ALL SELECT 'MR B','2012-04-24 12:00', 'B'
UNION ALL SELECT 'MR B','2012-04-24 13:00', 'B'
UNION ALL SELECT 'MR B','2012-04-24 14:00', 'B'
SELECT person, Entry, WeekDay, BLDG
FROM (
SELECT person
,ROW_NUMBER() OVER
(PARTITION BY person, DATEADD(day, DATEDIFF(day, 0, date), 0) ORDER BY person, date) As Entry
,DATENAME(weekday, date) As WeekDay, BLDG, DATEDIFF(day, 0, date) AS date
FROM @entries) x
ORDER BY Person, date, BLDG, Entry
Example output:
person Entry WeekDay BLDG
MR A 1 Monday A
MR A 2 Monday A
MR A 3 Monday A
MR A 4 Monday A
MR A 1 Tuesday A
MR B 1 Tuesday A
MR B 2 Tuesday A
MR B 3 Tuesday B
MR B 4 Tuesday B
MR B 5 Tuesday B
MR B 6 Tuesday B
MR B 7 Tuesday B
Only a minor modification is required to PARTITION/ORDER BY if you want to count entries by building.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply