August 6, 2009 at 1:29 pm
Ok, I'm really needing help on this out. I have a table that has 12 fields. They are:
GROSWAGS_1
GROSWAGS_2
GROSWAGS_3
GROSWAGS_4
GROSWAGS_5
GROSWAGS_6
GROSWAGS_7
GROSWAGS_8
GROSWAGS_9
GROSWAGS_10
GROSWAGS_11
GROSWAGS_12
each one of these fields is the gross wages for an employee for Jan - Dec. So, what I need to do is write a loop that if I select month 1-3, it will automatcially add whats in field GROSWAGS_1+GROSWAGS_2+GROSWAGS_3 and give me an answer. Does that make sense? Can you help???
Thanks,
Jordon
August 6, 2009 at 1:32 pm
It makes sense. It's the reason that data of that sort should be stored in rows, not columns, but it can be made to work.
Will the query always be a contiguous range of numbers? Like 1-3, not 1, 3, 7? Or will it allow for discontinuities like that? From that, how are you planning on passing that data to the query? As a comma-delimited list of the months desired, or as a range with a dash in it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2009 at 1:36 pm
It's always going to be a range, even though the range can be 1-1, which of course would only select the first column. I didn't setup the table, just working with what I've got. The number is going to be inputted from crystal reports, but I'm just working on the sql statement, so I can use static numbers for testings. I just need to be able to get it to work right now and then can make it more advance later on.
August 6, 2009 at 1:40 pm
Also, I'm not limiting my records, so if I run the loop, I want it to return for all records in the table. Meaning, if I run the report for Jan - Mar, I should get a result that shows me all employee's salary for jan - mar!
August 6, 2009 at 1:45 pm
jordon.shaw (8/6/2009)
Also, I'm not limiting my records, so if I run the loop, I want it to return for all records in the table. Meaning, if I run the report for Jan - Mar, I should get a result that shows me all employee's salary for jan - mar!
If you can, you really SHOULD change the way the table is built, to format the data as rows, and not columns, like Gus proposed, it's gonna be much easier to get the data you need, and a lot less maintenance from weird inserting or updating the data.
Cheers,
J-F
August 6, 2009 at 1:48 pm
I appreciate the suggestions, just not possible. This is a Microsoft product that we can't edit how the tables are setup, because of how the program was writen. With that being said, I need to write this query that goes beyond what the application will allow me to report on. Just needing a little help in the right direction.
Thanks,
Jordon
August 6, 2009 at 1:58 pm
Please provide the DDL of the table, and some sample data, in a ready to execute way, along with any query you have built so far, and I'll try to help.
Cheers,
J-F
August 6, 2009 at 2:11 pm
Here's a sample solution that you should be able to modify to fit your needs:
set nocount on;
if object_id(N'tempdb..#T') is not null
drop table #T;
--
-- Table with similar structure
create table #T (
ID int identity primary key,
Col1 float,
Col2 float,
Col3 float,
Col4 float,
Col5 float);
-- (This line intentionally left blank)
-- 10,000 rows in the table
insert into #T (Col1, Col2, Col3, Col4, Col5)
select checksum(newid()), checksum(newid()), checksum(newid()), checksum(newid()), checksum(newid())
from dbo.Numbers;
--
-- Param
declare @Range varchar(10);
select @Range = '1-1';
-- Query
;with CTE as
(select ID, 1 as Col, Col1
from #T
union all
select ID, 2 as Col, Col2
from #T
union all
select ID, 3 as Col, Col3
from #T
union all
select ID, 4 as Col, Col4
from #T
union all
select ID, 5 as Col, Col5
from #T)
select ID, sum(Col1) as Total
from CTE
where Col in
(select number -- Parses out @Range
from dbo.Numbers
where number >= left(@Range, charindex('-', @Range)-1)
and number <= reverse(left(reverse(@Range), charindex('-', reverse(@Range))-1)))
group by ID;
I use a Numbers table in this. The first use is just to generate sample data, which you don't need since you have a dev/test database with the real data or a reasonable facsimile thereof. The second is to generate a range of the numbers you'll be using. You could use a CTE for that, of course, if you don't have a Numbers table. Anything that contains numbers 1-12 ought to do for what you need.
You should be able to modify this for your table.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2009 at 2:14 pm
USE [db_table]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[UPR00900](
[EMPLOYID] [char](15) NOT NULL,
[GROSWAGS_1] [numeric](19, 5) NOT NULL,
[GROSWAGS_2] [numeric](19, 5) NOT NULL,
[GROSWAGS_3] [numeric](19, 5) NOT NULL,
[GROSWAGS_4] [numeric](19, 5) NOT NULL,
[GROSWAGS_5] [numeric](19, 5) NOT NULL,
[GROSWAGS_6] [numeric](19, 5) NOT NULL,
[GROSWAGS_7] [numeric](19, 5) NOT NULL,
[GROSWAGS_8] [numeric](19, 5) NOT NULL,
[GROSWAGS_9] [numeric](19, 5) NOT NULL,
[GROSWAGS_10] [numeric](19, 5) NOT NULL,
[GROSWAGS_11] [numeric](19, 5) NOT NULL,
[GROSWAGS_12] [numeric](19, 5) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [db_table].[dbo].[UPR00900]
([EMPLOYID]
,[GROSWAGS_1]
,[GROSWAGS_2]
,[GROSWAGS_3]
,[GROSWAGS_4]
,[GROSWAGS_5]
,[GROSWAGS_6]
,[GROSWAGS_7]
,[GROSWAGS_8]
,[GROSWAGS_9]
,[GROSWAGS_10]
,[GROSWAGS_11]
,[GROSWAGS_12])
VALUES
(1718
,350.75000
,475.82000
,736.76000
,383.52000
,383.52000
,671.16000
,584.41000
,767.04000
,715.06000
,735.02000
,234.38000
,149.25000)
GO
I don't have any T-SQL code yet; however, I do have PHP code that will do it:
$fields = array(
'GROSWAGS_1',
'GROSWAGS_2',
'GROSWAGS_3',
'GROSWAGS_4',
'GROSWAGS_5',
'GROSWAGS_6',
'GROSWAGS_7',
'GROSWAGS_8',
'GROSWAGS_9',
'GROSWAGS_10',
'GROSWAGS_11',
'GROSWAGS_12'
);
$start = 1;
$end = 3;
$total = 0;
for ($i = $start; $i <= $end; $i++) {
$total += $sql_row[$fields];
}
Let me know if you have any questions.
Thanks,
Jordon
August 6, 2009 at 2:44 pm
I'm really new at T-SQL. I do have a background in PHP, so I know programming and I know SQL, just T-SQL is new for me, so I'm not positive that I fully understand how to get this script to work to give me what I want.
August 6, 2009 at 2:45 pm
Tell me if that helps, even though I just saw Gus already had a solution before you even posted your data.. grrr, 😉
Hope it helps..
USE tempdb
GO
IF EXISTS (SELECT 1
FROM sys.objects
WHERE object_Name(object_ID) = 'UPR00900')
BEGIN
DROP TABLE UPR00900
END
GO
CREATE TABLE [dbo].[UPR00900] (
[EMPLOYID] [CHAR](15) NOT NULL,
[GROSWAGS_1] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_2] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_3] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_4] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_5] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_6] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_7] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_8] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_9] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_10] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_11] [NUMERIC](19,5) NOT NULL,
[GROSWAGS_12] [NUMERIC](19,5) NOT NULL)
ON [PRIMARY]
GO
INSERT INTO [dbo].[UPR00900]
([EMPLOYID],
[GROSWAGS_1],
[GROSWAGS_2],
[GROSWAGS_3],
[GROSWAGS_4],
[GROSWAGS_5],
[GROSWAGS_6],
[GROSWAGS_7],
[GROSWAGS_8],
[GROSWAGS_9],
[GROSWAGS_10],
[GROSWAGS_11],
[GROSWAGS_12])
VALUES (1718,
350.75000,
475.82000,
736.76000,
383.52000,
383.52000,
671.16000,
584.41000,
767.04000,
715.06000,
735.02000,
234.38000,
149.25000)
GO
INSERT INTO [dbo].[UPR00900]
([EMPLOYID],
[GROSWAGS_1],
[GROSWAGS_2],
[GROSWAGS_3],
[GROSWAGS_4],
[GROSWAGS_5],
[GROSWAGS_6],
[GROSWAGS_7],
[GROSWAGS_8],
[GROSWAGS_9],
[GROSWAGS_10],
[GROSWAGS_11],
[GROSWAGS_12])
VALUES (1709,
350.75000,
475.82000,
7336.76000,
38323.52000,
3853.52000,
671.16000,
58164.41000,
7567.04000,
715.06000,
7365.02000,
2734.38000,
1249.25000)
INSERT INTO [dbo].[UPR00900]
([EMPLOYID],
[GROSWAGS_1],
[GROSWAGS_2],
[GROSWAGS_3],
[GROSWAGS_4],
[GROSWAGS_5],
[GROSWAGS_6],
[GROSWAGS_7],
[GROSWAGS_8],
[GROSWAGS_9],
[GROSWAGS_10],
[GROSWAGS_11],
[GROSWAGS_12])
VALUES (1703,
1350.75000,
1475.82000,
7236.76000,
3833.52000,
3813.52000,
6711.16000,
5824.41000,
7637.04000,
7145.06000,
73521.02000,
2354.38000,
14629.25000)
GO
DECLARE @StartMonth INT,
@EndMonth INT
SET @StartMonth = 1
SET @EndMonth = 8;
WITH EmployeeCte(EMPLOYID,MONTH,GROSWAGS)
AS (SELECT EMPLOYID,
1,
GROSWAGS_1
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
2,
GROSWAGS_2
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
3,
GROSWAGS_3
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
4,
GROSWAGS_4
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
5,
GROSWAGS_5
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
6,
GROSWAGS_6
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
7,
GROSWAGS_7
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
8,
GROSWAGS_8
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
9,
GROSWAGS_9
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
10,
GROSWAGS_10
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
11,
GROSWAGS_11
FROM [UPR00900]
UNION ALL
SELECT EMPLOYID,
12,
GROSWAGS_12
FROM [UPR00900])
SELECT EmployID,
cast(sum(GROSWAGS) AS MONEY) AS TotalWage
FROM EmployeeCte
WHERE [Month] BETWEEN @StartMonth AND @EndMonth
GROUP BY EmployID;
Cheers,
J-F
August 6, 2009 at 3:02 pm
jordon.shaw (8/6/2009)
I'm really new at T-SQL. I do have a background in PHP, so I know programming and I know SQL, just T-SQL is new for me, so I'm not positive that I fully understand how to get this script to work to give me what I want.
The first part of it is just setting up the test. You can copy and paste that into Management Studio and it will run just as it is, to give you the data from the test table.
To modify it, you'd take the second part of the script, that starts with ";with CTE as", and you'd put in your column names and table name instead of "select ID, ... from #T". (In you're not familiar with them, CTE = Common Table Expression, and it's just a way to do a sub-query.)
The key to this CTE is the part with "1 as Col". You have a sequential number there, with 1 for the first column (January), 2 for the second column (Feb), and so on. What this does is pivot the data into rows from columns, and assign a "Col" value that says which column it was originally in.
The CTE thus results in data that would look like:
ID Col Col1
1 1 1252.1
1 2 1252.1
1 3 1255
...
1 12 1260
2 1 2348.6
...
2 12 2348.6
You'd have your wages column instead of "Col1", of course. The ID would repeat for each month, with an incrementing number for the month, based on which column it's in, and then would have the value from the parent table. In your case, you'd have the value for GROSWAGS_1 where I have Col1.
Once you've broken it down that way, you need to parse out the range of columns that you want. That's where I use a Numbers table. You can create one with this script:
create table dbo.Numbers (Number int primary key);
go
insert into dbo.Numbers (Number)
select top 10000 row_number() over (order by t1.object_id)
from sys.all_objects t1
cross join sys.all_objects t2;
That will give you one with the numbers from 1 to 10,000. It's a very useful table to have. Comes in handy all over the place.
Once you have the Numbers table, try this:
-- Parameters
declare @Range varchar(10);
select @Range = '1-3';
--
select number -- Parses out @Range
from dbo.Numbers
where number >= left(@Range, charindex('-', @Range)-1)
and number <= reverse(left(reverse(@Range), charindex('-', reverse(@Range))-1));
The first bit pulls off the part before the hyphen, the second grabs the part after it, and it should give you a range of numbers based on what you assign to the @Range variable. Try it with different ranges. Note: I didn't set this up to correct for @Range having impossible values, like '3-1' or '1-B', it assumes it's being sent a valid range.
Once it's done those two things, break down the columns into rows and break down the range into a sequence of numbers, it can compare the two, accept only the rows that match the range, and then sum them up.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2009 at 3:08 pm
It would look like this for you:
declare @Range varchar(10);
select @Range = '1-3';
-- Query
;with CTE as
(select EMPLOYID, 1 as Col, GROSWAGS_1 as MonthlyWages
from dbo.UPR00900
union all
select EMPLOYID, 2 as Col, GROSWAGS_2
from dbo.UPR00900
union all
select EMPLOYID, 3 as Col, GROSWAGS_3
from dbo.UPR00900
union all
select EMPLOYID, 4 as Col, GROSWAGS_4
from dbo.UPR00900
union all
select EMPLOYID, 5 as Col, GROSWAGS_5
from dbo.UPR00900
union all
select EMPLOYID, 6 as Col, GROSWAGS_6
from dbo.UPR00900
union all
select EMPLOYID, 7 as Col, GROSWAGS_7
from dbo.UPR00900
union all
select EMPLOYID, 8 as Col, GROSWAGS_8
from dbo.UPR00900
union all
select EMPLOYID, 9 as Col, GROSWAGS_9
from dbo.UPR00900
union all
select EMPLOYID, 10 as Col, GROSWAGS_10
from dbo.UPR00900
union all
select EMPLOYID, 11 as Col, GROSWAGS_11
from dbo.UPR00900
union all
select EMPLOYID, 12 as Col, GROSWAGS_12
from dbo.UPR00900)
select EMPLOYID, sum(MonthlyWages) as Total
from CTE
where Col in
(select number -- Parses out @Range
from dbo.Numbers
where number >= left(@Range, charindex('-', @Range)-1)
and number <= reverse(left(reverse(@Range), charindex('-', reverse(@Range))-1)))
group by EMPLOYID;
Very similar to what J-F posted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2009 at 3:17 pm
Thank y'all very much for your help! I do believe that I've got it figured out! Y'all are awesome!!!
August 6, 2009 at 3:20 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply