Money Data Type: sum

Showing posts with label sum. Show all posts

Wednesday, March 28, 2012

Monday, March 26, 2012

More complex Sum and Group by a week period

First thanks to Mike and Baie for helping me before.
I have a set of data similar to the below one.
issueID IssueDate
1 1/17/2005
2 1/18/2005
3 1/19/2005
4 1/24/2005
5 1/27/2005
6 2/7/2005
I need to query the set between 2 dates for example,
1/3/2005 and 6/5/2005 and what I need to return is the
sum of issues per 7 day groups starting from 1/3/2005.
Even if there are no issues opened in the 7 days
following the 1/3/2005 date, I need the query to return
0.
I need The query output to look like this:
W

Start Count
1/3/2005 0
1/10/2005 0
1/17/2005 3
1/24/2005 2
1/31/2005 1
The reason I need to see this output this way, is that I
need to compare it to a projected set that the managers
estimated per 7 day periods starting from 1/3/2005.
Is this doable as a query? I only have read access to
the database, so I won't be able to create stored
procedures or views.
Please let me know if you have ideas.Having a calendar table (http://www.aspfaq.com/show.asp?id=2519), you can tr
y
something like:
declare @.sd datetime, @.ed datetime
set @.sd = '20050103'
set @.ed = '20050605'
select
min(c.td),
count(t.IssueID)
from
calendar as c
left join
t
on c.dt = t.IssueDate
where
c.dt >= @.sd and c.dt < dateadd(day, 1, @.ed)
group by
datediff(day, c.dt, @.sd) / 7
go
Not tested.
AMB
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> W

, 1, D.DT)
Group By D.DT
-- Something like that--
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> W

s that you wish to query for you could use
the following query
select dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') start_dt,
dateadd(ww, 1+a0+a1*2+a2*4+a3*8+a4*16, '20050103') exclusive_end_dt from
(select 1 a0 union all select 0 ) x0
cross join (select 1 a1 union all select 0 ) x1
cross join (select 1 a2 union all select 0 ) x2
cross join (select 1 a3 union all select 0 ) x3
cross join (select 1 a4 union all select 0 ) x4
where dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') < '20050605'
This will generate start and end dates for the target w

s which you could
put in a table variable as CBretana suggests. You can also embed this query
in your original query like so...
select start_dt, count(issueID)
from (select dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') start_dt,
dateadd(ww, 1+a0+a1*2+a2*4+a3*8+a4*16, '20050103') exclusive_end_dt from
(select 1 a0 union all select 0 ) x0
cross join (select 1 a1 union all select 0 ) x1
cross join (select 1 a2 union all select 0 ) x2
cross join (select 1 a3 union all select 0 ) x3
cross join (select 1 a4 union all select 0 ) x4
where dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') < '20050605') w

s
left outer join test1
on issuedate >= start_dt and issuedate < exclusive_end_dt
group by start_dt
This approach is likely only useful for small sets of w

s.
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> W

Start Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
A table of (nonnegative) integers is often handy.
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
CREATE TABLE N
(
i INT NOT NULL CHECK (i>=0) PRIMARY KEY
)
INSERT INTO N (i)
SELECT Ones.d + 10*Tens.d + 100*Hundreds.d + 1000*Thousands.d +
10000*TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands
CREATE VIEW Calendar (d)
AS
SELECT CAST('19000101' AS DATETIME) + N.i
FROM N
CREATE TABLE Issues
(
issue_id INT NOT NULL PRIMARY KEY,
issue_date DATETIME NOT NULL
)
-- Sample data
INSERT INTO Issues (issue_id, issue_date)
VALUES (1, '20050117')
INSERT INTO Issues (issue_id, issue_date)
VALUES (2, '20050118')
INSERT INTO Issues (issue_id, issue_date)
VALUES (3, '20050119')
INSERT INTO Issues (issue_id, issue_date)
VALUES (4, '20050124')
INSERT INTO Issues (issue_id, issue_date)
VALUES (5, '20050127')
INSERT INTO Issues (issue_id, issue_date)
VALUES (6, '20050207')
-- This is a purely relational solution. If you need to do
-- more general queries, this might be helpful.
-- start_date and end_date is the overall period
-- interval is a number of days
-- period_start and period_end is the period we're tallying where
-- period_start is inclusive and period_end is exclusive
-- issue_tally is the tally for the period
CREATE VIEW IssueTally
(start_date, end_date, interval,
period_start, period_end, issue_tally)
AS
SELECT SD.d, ED.d, L.i,
SD.d + L.i * T.i, SD.d + L.i * (T.i + 1),
COUNT(I.issue_id)
FROM Calendar AS SD -- start date
INNER JOIN
Calendar AS ED -- end date
ON ED.d > SD.d
INNER JOIN
N AS L -- interval length
ON L.i > 0
INNER JOIN
N AS T -- number of intervals
ON T.i <= DATEDIFF(DAY, SD.d, ED.d) / L.i
LEFT OUTER JOIN
Issues AS I
ON I.issue_date >= SD.d + L.i * T.i AND
I.issue_date < SD.d + L.i * (T.i + 1)
GROUP BY SD.d, ED.d, L.i, T.i
SELECT start_date, end_date, interval,
period_start, period_end, issue_tally
FROM IssueTally
WHERE start_date = '20050103' AND
end_date = '20050605' AND
interval = 7
ORDER BY period_start
JAG|||Thanks you very much. That helped.|||Thanks you very much. That helped.|||Thanks you very much. That helped.|||Thanks you very much. That helped.

Months string

can sum one help with this:
I want 3 strings what represent the last three months from today's date inclusive.
for eg: if today is november 19th, 2003.
How would i get 3 strings as '2003_11', '2003_10', '2003_09'
Thanks a million.declare @.DateValue datetime
set @.DateValue = getdate()

select replace(convert(varchar(7), @.DateValue, 120), '-', '_')
select replace(convert(varchar(7), dateadd(m, -1, @.DateValue), 120), '-', '_')
select replace(convert(varchar(7), dateadd(m, -2, @.DateValue), 120), '-', '_')

You can ignore the REPLACE functions if you are picky about the format returned, but SQL Server does not have a datetime format using underscore characters.

blindman|||Thanks for the reply appreciate it.

Well this is what i'm doing with these strings:

I have monthly tables named as 'Tablename_yyyy_mm' etc.
I want to make a view that will capture the current months table and the last 3 months data.
for eg: if today is november 19th, 2003.
The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables
if today is jan 01,2003
The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables

Thanks a lot.|||9 times out of 10 this is a bad database design. Unless you are dealing with terabytes of data, it is better to create a single table with 1 extra column indicating the appropriate month. Easier to code, and generally more efficient.

blindman|||Originally posted by blindman
9 times out of 10 this is a bad database design. Unless you are dealing with terabytes of data, it is better to create a single table with 1 extra column indicating the appropriate month. Easier to code, and generally more efficient.

blindman

Well the original table was getting unmanagable, ie, it had about 160 million records and was growing fast. That why archiving the table into monthly tables was adopted. Now the original table will be split into monthyl tables and views are created for say each quarter, annual etc.|||Had you fully exhausted all the other possibilities for performance improvement?

Drives.
Processors.
Memory.
Indexing.
Normalization.
Pre-aggregation.
Query optimization.

Magic 8 Ball says outlook not good.

blindman|||Yes, indexing would take alot of space itself (almost the same as the table). The table is being used for reporting purposes so most of the time previous years data is not even touched, but still it's there in one big table. The whole process became slow due to the size of the table. Memory is not an issue. The current year's data is being used most often so monthly tables would give us more flexibility in viewing data.|||Did you try a 12 month partitioned view before this dynamic creation thing?

Or how about 13...make luck 13 an archive of infrequently used data, and set up an archive method...|||yes the 12 month archiving was thought of too.
for reporting etc. last 6 months data could be required. that that would make the 12 or 13 month archival method bogus. the monthly tables would be most flexible.

Another question: Is there a way to get the records counts for all the tables on (server a) and save those counts on a table in server B. And we r running this script or proc from server b.

server a and server b or not on a trusted connection but a password and userid are given.|||The 13th "month" would all previous yeard data...then you create a partitioned view to see all of the data

As for counts

Did you set up a linked server?|||Well the original table has data from jan 2001 till present.

for counts

i'm using openrowset(...) to get all the table names on server a. Would linked server be better? if so how would u setup linked servers? ne sample code would be helpful

thx|||I have setup linked servers, but how would you get the tables counts of all the tables on the linked server?|||Do you mean like SELECT COUNT(*) FROM myTable99?

Theres alos sp_spaceused, and a system table contains the info...

All of which (except SELECT COUNT(*)) need to statistics run to make sure the current..|||I cant do the select count(*) becuase in the sysservers tabe the linked i added has a null value in the srvnetname column. But all the sp_columns_ex and sp_tables_ex command work.

Monday, February 20, 2012

Modulo Type Question

How do I get Modulo on:
select 200501 / 4
The sum = 50125.25
I want the .25
I tried:
select 200501 / 4
select CAST((200501 / 4) as float)
select CAST((200501 / 4) as real)
select CAST((200501 / 4) as numeric(16,3))
Also how can one use Modulo(%) with 200501 / 4 ?Modulo returns the remainder, which would be 1 in your case.
Try this. (When I ran your statements I only got a whole number without the
decimal portion.)
declare @.a float, @.b float
select @.a=200501, @.b=4
select @.a/@.b -floor(@.a/@.b)
"marcmc" wrote:

> How do I get Modulo on:
> select 200501 / 4
> The sum = 50125.25
> I want the .25
> I tried:
> select 200501 / 4
> select CAST((200501 / 4) as float)
> select CAST((200501 / 4) as real)
> select CAST((200501 / 4) as numeric(16,3))
> Also how can one use Modulo(%) with 200501 / 4 ?
>|||select (200501 % 4) / convert(money, 4)

> How do I get Modulo on:
> select 200501 / 4
> The sum = 50125.25
> I want the .25
> I tried:
> select 200501 / 4
> select CAST((200501 / 4) as float)
> select CAST((200501 / 4) as real)
> select CAST((200501 / 4) as numeric(16,3))
> Also how can one use Modulo(%) with 200501 / 4 ?
new|||modulo only operates on integer values and returns an integer which is
the remainder, so that's not really what you're looking for here.
the easiest way is to subtract the integer part from the result.
[make sure one of the values is a decimal since you've got two integers]
e.g.
select convert(numeric(16,2), (200501/4.0) - convert(int, (200501/4.0)))
are these for hard-coded numbers or variables/data, integers or numeric?
the actual code will be different depending
marcmc wrote:
> How do I get Modulo on:
> select 200501 / 4
> The sum = 50125.25
> I want the .25
> I tried:
> select 200501 / 4
> select CAST((200501 / 4) as float)
> select CAST((200501 / 4) as real)
> select CAST((200501 / 4) as numeric(16,3))
> Also how can one use Modulo(%) with 200501 / 4 ?
>

Wednesday, March 28, 2012

More grouping questions - sum of maximum

More grouping questions - sum of maximum

Monday, March 26, 2012

More complex Sum and Group by a week period

Months string

Monday, February 20, 2012

Modulo Type Question

Money Data Type

Blog Archive

About Me