May 14, 2009 at 9:15 am
Hey Guys,
Sorry for misunderstanding the requirements, I didn't realize multiple rows needed to be summed, but SUM() is the answer.
Why do you need an extra COLUMN to hold totals? Why not a totals ROW at the end? It's much quicker and easier.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 14, 2009 at 9:34 am
Do you mean the totals at the last row ? I need them at the end of every person. As this query is supposed to produce a list of people, the amounts given and the totals for each person. There will be an empty column for signing off.
I am going to export the details to an excel sheet.
May 14, 2009 at 9:43 am
I went back and looked at your post again. To sum various amount columns into a single results column, you can do one of a couple of things. Let's say hypothetically that you only want to sum on two columns: ColA and Colb.
In the primary query you can say
SUM(ColA) as TotalA, SUM(ColB) as TotalB, SUM(ColA+ColB) as TotalWhatever
--
Or, you can make the primary query into a CTE and then take the total
SELECT *, ColA+ColB as TotalWhatever FROM cte
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 15, 2009 at 1:41 am
Using Tom's query :-
I have tried adding the total's column here
'SELECT U_Casual_N, SUBJOBID, ' + @DATELIST +
' Total FROM ParamQry ' +
But it overwrites on the last date column. Where can I add this extra column. How do I pick the amounts for a given row only ??
Phew !!! This sounds pretty tricky !
Should I create another pivot for this ?
May 15, 2009 at 3:29 am
Its tricky but if you look at Bobs post above on calculated columns ...
you want your dynamic SQL to dynamically create a the final column as
( [Date1] + [Date2] + ... [DateN] ) AS RowTotal
You may need a second @DATELIST variable to create it in, before adding it to the final SELECT
I don't have my SQL Server access at the moment, - but post what you come up with, whether it works or not ..
This is getting interesting 😉
May 15, 2009 at 5:42 am
Am getting this error here : -
WITH ParamQry AS ( SELECT TRANSDATE,COSTTOTAL, U_Casual_N,SUBJOBID FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW WHERE SUBJOBID = 1007001 AND TRANSDATE BETWEEN '2009-04-08' AND '2009-04-14' ) SELECT U_Casual_N, SUBJOBID, [Apr 8 2009], [Apr 9 2009] , [Apr 10 2009] , [Apr 11 2009] , [Apr 12 2009] , [Apr 13 2009] , [Apr 14 2009] SUM([Apr 8 2009]), SUM([Apr 9 2009]) , SUM([Apr 10 2009]) , SUM([Apr 11 2009]) , SUM([Apr 12 2009]) , SUM([Apr 13 2009]) , SUM([Apr 14 2009]) FROM ParamQry PIVOT ( sum(COSTTOTAL) FOR TRANSDATE IN ( [Apr 8 2009], [Apr 9 2009] , [Apr 10 2009] , [Apr 11 2009] , [Apr 12 2009] , [Apr 13 2009] , [Apr 14 2009] SUM([Apr 8 2009]), SUM([Apr 9 2009]) , SUM([Apr 10 2009]) , SUM([Apr 11 2009]) , SUM([Apr 12 2009]) , SUM([Apr 13 2009]) , SUM([Apr 14 2009]) ) ) AS PVT;
Msg 422, Level 16, State 4, Line 1
Common table expression defined but not used. I placed the summations on the columns.
-- Parameters
DECLARE @StartDate Datetime;
DECLARE @EndDate Datetime;
DECLARE @SubjID INT;
-- Parameter values
SET @StartDate = '04/08/2009';
SET @EndDate = '04/14/2009';
SET @SubjID = 1007001;
DECLARE @SQL nvarchar(max);
DECLARE @DATELIST nvarchar(4000);
SET @DATELIST = ''
SELECT @DATELIST = CASE @DATELIST
WHEN '' THEN '[' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + ']'
ELSE @DATELIST + ', [' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '] '
END
FROM ( SELECT DISTINCT TRANSDATE,COSTTOTAL FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW
WHERE SUBJOBID= @SubjID
AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates
ORDER BY MyDates.TRANSDATE;
DECLARE @DATELIST2 nvarchar(4000);
SET @DATELIST2 = ''
SELECT @DATELIST2 = CASE @DATELIST2
WHEN '' THEN 'SUM([' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '])'
ELSE @DATELIST2 + ', SUM([' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + ']) '
END
FROM ( SELECT DISTINCT TRANSDATE FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW
WHERE SUBJOBID= @SubjID
AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates
ORDER BY MyDates.TRANSDATE;
SET @SQL = 'WITH ParamQry AS ( ' +
' SELECT TRANSDATE,COSTTOTAL, U_Casual_N,SUBJOBID' +
' FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW ' +
' WHERE SUBJOBID = ' + convert(varchar(8),@SubjID) +
' AND TRANSDATE BETWEEN ''' + Convert(varchar(10),@StartDate,120) + ''' AND ''' + Convert(varchar(10),@EndDate,120) + ''' ' +
' ) ' +
'SELECT U_Casual_N, SUBJOBID, ' + @DATELIST + @DATELIST2 +
'FROM ParamQry ' +
'PIVOT ( sum(COSTTOTAL) ' +
'FOR TRANSDATE IN ( ' + @DATELIST + @DATELIST2 +') ' +
') AS PVT;'
PRINT @SQL;
EXEC (@SQL);
May 15, 2009 at 8:16 am
Hi - Try this, it should give a Rowtotal column at the end of each row
-- Parameters
DECLARE @StartDate Datetime;
DECLARE @EndDate Datetime;
DECLARE @SubjID INT;
-- Parameter values
SET @StartDate = '04/08/2009';
SET @EndDate = '04/14/2009';
SET @SubjID = 1007001;
DECLARE @SQL nvarchar(max);
DECLARE @DATELIST nvarchar(4000);
SET @DATELIST = ''
DECLARE @DATELIST2 nvarchar(4000);
SET @DATELIST2 = ''
SELECT @DATELIST = CASE @DATELIST
WHEN '' THEN '[' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + ']'
ELSE @DATELIST + ', [' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '] '
END,
@DATELIST2 = CASE @DATELIST
WHEN '' THEN ' IsNull([' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '],0)'
ELSE @DATELIST2 + ' + IsNull([' + Convert(nvarchar(11),MyDates.TRANSDATE,109) + '],0)'
END
FROM ( SELECT DISTINCT TRANSDATE,COSTTOTAL FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW
WHERE SUBJOBID= @SubjID
AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates
ORDER BY MyDates.TRANSDATE;
SET @SQL = 'WITH ParamQry AS ( ' +
' SELECT TRANSDATE,COSTTOTAL, U_Casual_N,SUBJOBID' +
' FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW ' +
' WHERE SUBJOBID = ' + convert(varchar(8),@SubjID) +
' AND TRANSDATE BETWEEN ''' + Convert(varchar(10),@StartDate,120) + ''' AND ''' + Convert(varchar(10),@EndDate,120) + ''' ' +
' ) ' +
'SELECT U_Casual_N, SUBJOBID, ' + @DATELIST + ', (' + @DATELIST2 + ') AS RowTotal ' +
'FROM ParamQry ' +
'PIVOT ( sum(COSTTOTAL) ' +
'FOR TRANSDATE IN ( ' + @DATELIST +') ' +
') AS PVT;'
PRINT @SQL;
EXEC (@SQL);
I've combined setting @DATELIST and @DATELIST2 in the same SELECT
@DATELIST2 should be a clause [Date] + ... + [DateN] - I've eliminated nulls to zero using IsNull
The Clause is only required in the SELECT list of the final SQL, not the PIVOT part - you are still pivoting on the same date list,
I can't check it as I don;t have dbo.ENPRISE_JOBCOST_JOBLINESVIEW but
May 15, 2009 at 1:43 pm
you want your dynamic SQL to dynamically create a the final column as
( [Date1] + [Date2] + ... [DateN] ) AS RowTotal
Now how could we do this? Let's think about it. We are dynamically creating a sql string... but it's just a string in a variable.... so we could attach another variable to it..... maybe we could at the same time develop another variable with all the same column names with plus signs between them and a constant "AS RowTotal" at the end... and then maybe just before we execute the SQL we tie the two (or three or four or.. ) strings together....
Yeah... just maybe.
Come on, guys. You can do this. It's string manipulation, not rocket science. 😉 It's fun to write code to write code. Next thing you know you'll be writing something that could evolve into SkyNet. :w00t:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 15, 2009 at 5:37 pm
Did I detect something flying through the air?
You old robot killing machine 😉
May 23, 2009 at 6:44 am
Woohooo ! It worked, it worked ! I wasn't really far from the solution.
I need to understand more about queries If am to move from the simple SELECT statements to t-sql dynamic queries. Where can I start ?
May 23, 2009 at 11:31 am
martin.edward (5/23/2009)
I need to understand more about queries If am to move from the simple SELECT statements to t-sql dynamic queries. Where can I start ?
Start here for a stong understanding of how to write crosstab/pivot code...
http://qa.sqlservercentral.com/articles/T-SQL/63681/
... then move on to making it dynamic...
http://qa.sqlservercentral.com/articles/cross+tab/65048/
--Jeff Moden
May 24, 2009 at 9:51 am
Ok guys, am not a SQL maestro, but I need to know it.
The parameters we were using were hardcoded. They need to be input by the user. I guess this is part of dynamic SQL. How can I place the prompt ?
May 24, 2009 at 5:10 pm
martin.edward (5/24/2009)
Ok guys, am not a SQL maestro, but I need to know it.The parameters we were using were hardcoded. They need to be input by the user. I guess this is part of dynamic SQL. How can I place the prompt ?
Where will the user be doing the input from? I'm thinking that if you make a parameterized stored procedure (a procedure that takes a parameters as inputs as part of it's call), then you should really be all set for most everything whether the user is doing it from a GUI or through SSMS.
--Jeff Moden
May 26, 2009 at 5:48 am
Thanks Tom for your help,
After running the query I realised there is something amiss.
Going back to the earlier structure
NAME ID 1/2 2/2 3/2 4/2 5/2 JOBID
Mark 9 200 NULL 200 NULL NULL 1001
Peter 3 300 NULL NULL 300 NULL 1001
Steve 1 400 200 NULL NULL 200 1001
With the query when there are entries for the same date for diffrent persons I get the error
The column 'Apr 9 2009' was specified multiple times for 'PVT'.
and the rest of the error
WITH ParamQry AS ( SELECT TRANSDATE,COSTTOTAL, U_Casual_N,SUBJOBID FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW WHERE SUBJOBID = 1007001 AND TRANSDATE BETWEEN '2009-04-08' AND '2009-04-14' ) SELECT U_Casual_N, SUBJOBID, [Apr 8 2009], [Apr 9 2009] , [Apr 9 2009] , [Apr 9 2009] , [Apr 10 2009] , [Apr 11 2009] , [Apr 12 2009] , [Apr 13 2009] , [Apr 14 2009] FROM ParamQry PIVOT ( sum(COSTTOTAL) FOR TRANSDATE IN ( [Apr 8 2009], [Apr 9 2009] , [Apr 9 2009] , [Apr 9 2009] , [Apr 10 2009] , [Apr 11 2009] , [Apr 12 2009] , [Apr 13 2009] , [Apr 14 2009] ) ) AS PVT;
I have tried making this change - adding the U_Casual_ID in the datelist query but the error persists
FROM ( SELECT DISTINCT TRANSDATE,COSTTOTAL,U_Casual_ID FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW
WHERE SUBJOBID= @SubjID
AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates
ORDER BY MyDates.TRANSDATE,U_Casual_ID;
May 26, 2009 at 10:01 am
It's ok guys. I sorted it out. I had added the cost total as part of the distinct query. This brought about the error.
This is what it should look like.
FROM ( SELECT DISTINCT TRANSDATE FROM dbo.ENPRISE_JOBCOST_JOBLINESVIEW
WHERE SUBJOBID= @SubjID
AND TRANSDATE BETWEEN @StartDate AND @EndDate ) AS MyDates
ORDER BY MyDates.TRANSDATE;
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply