Display Summary Week Total Rows From Sql Database

Sep 22, 2007

(I moved this thread from datagrid area) 
I have a sql database that has individual records consisting of name, date, hours worked among other fields.
Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date.
 My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.
TABLE:dan       12/13/2012 12:00:00 AM9.123dan       12/14/2012 12:00:00 AM3.123123cara      12/12/2012 12:00:00 AM4.222cara      12/16/2012 12:00:00 AM3.3333cara      12/17/2012 12:00:00 AM2
 
 
 CODE:
Select distinct(name),
 (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday,
 (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday,
 (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday,
 (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Wednesday')as Wednesday,
 (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Thursday')as Thursday,
 (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday,
 (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Saturday')as Saturday,
(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Total
from dbo.testtime YT
group by date, name, hours
 
RESULTS:
 
cara          NULL    NULL    NULL    4.222    NULL    NULL    NULL    4.222
cara          NULL    2    NULL    NULL    NULL    NULL    NULL    2
cara          3.3333    NULL    NULL    NULL    NULL    NULL    NULL    3.3333
dan           NULL    NULL    NULL    NULL    NULL    3.123123    NULL    3.123123
dan           NULL    NULL    NULL    NULL    9.123    NULL    NULL    9.123
 
Like I said, I am SO close, I just need it to look like;
NAME    SUN    MIN    TU    WED    TH    FR    SA    TOTAL 
 cara          3.333    2                            4.222            9.555

dan                        9.125    3.125                              12.5
 
 
TIA
dan 


 

View 6 Replies


ADVERTISEMENT

Power Pivot :: Cumulative Total For Current Week Days And Previous Week

Nov 30, 2015

We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week. 

I'm trying to produce a line chart with 2 axis:

- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc)
- y axis; the cumulative number of orders 

The chart needs two series:

Previous Week. The running count of orders placed that week. 
Current Week. The running count of orders placed this week. 

Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day. 

I have two tables:

Orders TableCalendar Table

The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc. 

My measure for counting orders is simply; # Orders: = countrows[orders].

How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:

- [URL] ...
- [URL] ...

But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.

View 3 Replies View Related

Summary Of Data By Business Week

Sep 6, 2006

I have a large query that returns a list of records that are marked by day of the business week (Monday, Tuesday, Wed, etc).

I am running into a challenge where I need to provide summaries of data from those days. For example, it needs to look like this:



Date | Day of Week | Total Widgets |

09/01/06 | M | 4
09/02/06 | Tu | 5
09/03/06 | We | 6
09/04/06 | Th | 7
09/05/06 | Fri | 8

Total Widgets: 30

09/08/06 | M | 1
09/09/06 | Tu | 2
09/10/06 | We | 3
09/11/06 | Th | 4
09/12/06 | Fri | 5

Total Widgets: 15

I'm using Reporting Services to format and display the report.

I've got the group by working for the entire data set, but I need to separate it by these sections. Any assisntance is appreciated.

View 2 Replies View Related

Alternate Week Database Records Display

Oct 4, 2013

In our organization we have fixed two weeks menu. On our intranet i have database entries with two weeeks menu without dates. I want first six entries to appear in one week and next six entries to appear in another week. How can i achieve this with SQL query.

View 17 Replies View Related

Group Total Summary Help

Apr 12, 2007

I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example










Group A


PK Field
Summary Data Field

1
250

2
300

Group A Total
550

Group B


3
100

4
50

Group B Total
150

Grand Total
700



The underlying query contains detail data and I am using a table with two group levels. All details are hidden.



To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.



Thanks,

Simone

View 9 Replies View Related

Calc A % Of A Summary Total

Jun 29, 2007

I have a report that requires 2 "tables". The first table summarizes total
lbs by a category and then provides a company total at the end:

cat 1 75
cat 2 100
cat 3 200
-------
total 375

The second table needs to display the % of the total for each category:

cat 1 20%
cat 2 27%
cat 3 53%
-------
total 100%

How can I reference the company total for doing the calculations for the
second table? I am working with Visual Studio 2003. My dataset pulls a
file from the AS400 using an sql statement.




Thank you, PB

View 4 Replies View Related

Resultset With Summary Rows

Feb 26, 2004

Hi,

I have problem with query/sp which need to return one resultset with strucure like:

summary row1
detail row1
detail row2
detail row3
summary row2
detail row4
detail row5
...

Tables:
T_HW (HW_ID INT, CatID INT, other cols (NVARCHARs, INTs, DATETIMEs, NTEXTs etc.)
T_Category (CatID INT, Manufact NVARCHAR (100), other cols)

The output I need is ( '|' means cols separator) :
Manufact1 | No of recs from HW for this Manufact | nulls ...
null | null | HW1 from Manufact1 details ...
null | null | HW2 from Manufact1 details ...
Manufact2 | No of recs from HW for this Manufact | nulls ...
null | null | HW3 from Manufact2 details ...
...

it needs to be one result recordset

Thx in advance,
MST78

View 4 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

Display Records For This Week

Apr 15, 2008

hi friends,

how to display the records for this week?

for today i used this query to dispaly the values

select count(*) from tbl_voting v,tbl_lkvotefor l where v.voteforid=l.voteforid and v.creationdatetime=CONVERT(VARCHAR(10), GETDATE(), 101)

View 4 Replies View Related

Help With Expression To Display A Per-week Daterange Value!

Jul 16, 2007

Here is the table I€™m working with:

dbo.Reject




Rejections

Amount

Date


5

12

1/4/07


9

3

1/6/07


1

6

1/23/07


0

0

2/11/07


7

8

2/20/07

Is it possible to write an expression for RS2005 in the details row in a table that will separate and add the rejection and amount field values for each calendar week and lay them out to look like below:





Week

Rejections

Amount


1

14

15


2

0

0


3

0

0


4

1

6


5

0

0


6

0

0


7

0

0


8

7

8

I€™m trying to make a report that shows the number of rejections and amounts on a week to week basis. So I need RS2005 to add the rejections and amounts for each week date range and display it line-by-line automatically on a per-week basis.

Week 1 is 1/1/07-1/6/07 according to DATEPART(WEEK,Date) and so on€¦

Thanks in advance!

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate And Display Week Between Two Dates

Nov 4, 2015

I want to display week between two dates as below.

requirement is as:

suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016

Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

Say for example the column date is coming as 10/23/2015 then it will fall in week-1

Same way if 11/01/2015 falls in week2.

View 3 Replies View Related

Display Invoice Total

Feb 6, 2007

Hi,

I need assistance with the following please.

I have to display the total for an invoice. The price for the products change over time.

I want the invoice date to correspond with the current period's price.

Thus if invoice date is y, it should calculate using the price between x and z.

Thanks,

Arend

View 7 Replies View Related

Transact SQL :: Display Total Of Company Expenses

Oct 6, 2015

In the below query  want to display total of company expenses. I am unable to get the sum of weeklycomexpenses

SELECT DISTINCT e.EmpID, e.EmpName, e.StartDate, e.EndDate, ee.WeeklyComTotalExpenses FROM
EMPArgentTimeSheet AS e LEFT OUTER JOIN (SELECT CExpID, WeeklyComTotalExpenses FROM
CompanyArgentExpenses GROUP BY CExpID, WeeklyComTotalExpenses) AS ee ON ee.CExpID = e.EmpID where EmpName = 'Eberhard Neumann'
and (e.StartDate >='8/1/2015')AND (e.EndDate <= '8/31/2015') order by EmpID desc

output:

empid empname startdate enddate WeeklyComTotalExpenses
397Eberhard Neumann2015-08-23 00:00:00.0002015-08-29 00:00:00.00019.20
393Eberhard Neumann2015-08-16 00:00:00.0002015-08-22 00:00:00.000NULL
387Eberhard Neumann2015-08-09 00:00:00.0002015-08-15 00:00:00.00078.00
382Eberhard Neumann2015-08-02 00:00:00.0002015-08-08 00:00:00.00081.99

Total 8/1/2015 8/31/2015 179.19 //want to display this total info

View 9 Replies View Related

Retrieve Rows That Are Less Than One Week Old

May 14, 2006

I have a table MyTable that contains two fields MyData and CreationTime. I would like to write a query
SELECT MyData FROM MyTable
WHERE CreationTime < less than one week from now
Does anyone know the real syntax for that?

Thanks in advance,
Joannès

View 3 Replies View Related

Groupwise Page Numbering And Also Display The Total No. Of Pages In That Particular Group.

Feb 14, 2008



Friends,


I am using SSRS (Sql server reporting services) for one of my report related to EIS-MIS.
i want group wise page numbering and also display the no. of pages for that particular group.

e.g suppose group 1 has 5 pages then it should display
page no. 1 /5, 2/5,3/5,4/5, 5/5. now suppose group change then it shold display 1/4 like this

I have already implemented groupwise page numbering using then custom code and i have call that function from the header portion of the report.
now the question is how can i display the total no. of pages in particular group?

For that i have add one count column in query itself but as we know we can't use field value in header and footer portion and also we can't use global variables in data portion.


So kindly guide me
Thanks,

Manoj Patel

View 2 Replies View Related

Database Summary Reports

Mar 7, 2006

I'm not sure this is the correct forum for this, but it seemed to be the best place to start.

I have been trying to manage my SQL 2000 Databases using Microsoft SQL Server Management Studio. It works well for most everything. The problem is, however, that there is no equivalent to the SQL 2000 Taskpad View in SQL Server Management Sudio.

In the Summary screen when connected to a 2000 Database the Reports button is disabled. If I restore a 2000 Database into a 2005 DBE then I get the message that the compatibilty Mode is 80 and I must set it to 90 to get this report.

I could switch this to a 90 compatibility mode, but I don't think I should need to do this. Additionally, I don't want to have 2000 and 2005 both installed to quicly examine the used to free space ratio on a Database.

How do I get around this? Is there a switch that I missed somewhere? Is it possible to get the Disk Usage report to work from the Database Summary Page for a 2000 Database or a Database running in 80 Compatibilty mode?

Thank you,
Jeffrey Irish
jeff.irish@apisoftwareinc.com

View 1 Replies View Related

Total No Of Rows In All Tables

Feb 26, 2008

Hi,

Does anyone know how to calcuate total no of rows of all tables from database in single query?

Thanks in advance

--kneel

View 11 Replies View Related

Total Of Previous Rows

May 13, 2008



Hi,

I have a table like this.









Portfolio
Name
Value

ALL UP
Charge Off
47

ALL UP
CLTV
212

ALL UP
DSR
441

ALL UP
Exc Total
719

ALL UP
FICO
11


I want to add a row to the bottom of the table so that it will give the total of all values above.
It's something like this.
SELECT Portfolio, Name, Value , Total
FROM table

Total is equal to sum of all above values. Can anyone help me to write this query?

Thanks


View 5 Replies View Related

Total Of Previous Rows

May 15, 2008

I have a table like this.


Name Amount

CBank 5
InstLend 4
DTC 1



Now i want write a query which give me the out put like this.



Name Amount

CBank 5
InstLend 4
DTC 1

Total 10

can any one help me to write this please?

View 5 Replies View Related

Counting Total Rows When Using GROUP BY

Oct 20, 2007

hi,
i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits
WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL)
GROUP BY UserName
UNION
SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits AS UserVisits_1
WHERE (ProductID = @ProductID) AND (UserName IS NULL)
GROUP BY AnonimIP
this will return something like:
zuperboy90 - 4 visits
ANONIMOUS - 6 visits
85.104.103 - 2 visits etc
how can i count the rows returned in both selections (4+6+2 = 12) ?
thank you

View 9 Replies View Related

Running Total On Rows Between Groups

Dec 5, 2006

I want to write a stored procedure to add all of the flying time on a given airplane for each flight until now and then reset the total when it gets to a new airplane. I want to be able to pull from the table all flying time from a given date for a given airplane.

For example:

AircraftNo Date Flight_Hrs Total
38 3/1/06 1 4.5
38 5/10/06 1.5 3.5
38 7/10/06 2 2

100 8/19/03 4 8.5
100 10/04/04 3 4.5
100 9/17/06 1.5 1.5

I pulled the following code from the internet but I'm getting errors.

select AircraftNo
,Actual_departing_date,Flight_Hours
,sum(Flight_hrs) over partition by AircraftNo
order by Date
rows between current row and unbounded following) total
from (Select Distinct
F.AircraftNo
,F.Date
,C.Flight_Hrs
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No

Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.

View 5 Replies View Related

Get Total Updated Rows Effected By A Statement

May 6, 2008

This was a usual day today in office and i was working on a requirement in which i was needed to fetch the total number of rows effected by an update query, so I asked my best code mate "Google" and to my surprised there was not enough correct answers at least the one i was looking for.There were suggestions that  you can use a select statement for the updated rows and make it like a select (count) which works fine, but just looking into the SQL server books online, it shows that there is even a better way to do it.After the update statement in my  stored procedure i used "@@ROWCOUNT" with a select statement and it works like a charm.so the little find for my first ever post on asp.net is that there is a better way to find the total updated rows by a query Example: DB: Northwind , Table Employeesupdate employees set extension='1234'select @@ROWCOUNT  This will return 9 (default rows in this table) as the rows effectedHope this helps 

View 2 Replies View Related

Transact SQL :: Total Count Of Table Rows

Sep 15, 2015

I have a question regarding the total count of the table rows

Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".

So now I have to store the count in 'nn'.

View 5 Replies View Related

Get Total Count And Top N Rows In Single Call

Dec 3, 2007



Hi,

I want to return the total number of rows and top n records from my query. Is it possible to do this by simply running a single query. My query is dynamic so I can not create any temp table or table variables.

Bacially I want to eliminate 2 calls for sp_executesql

Set @ColumnCount = "Count(*)"
Set @Columns = " Top 100 a,b,c"
Set @Filter = "a = 1001"

Set @sql = "select " + @ColumnCount + " FROM TABLEA WHERE " + @Filter
sp_executesql @Sql

Set @sql = "select " + @Columns + " FROM TABLEA WHERE " + @Filter
sp_executesql @Sql

Thanks
Pun

View 3 Replies View Related

Total Number Of Rows Output To A Textbox

Dec 5, 2007

Hi,

Im trying to output the number of rows in a table returned in a report, into a textbox for a total record count

How would I do this ?

thanks

View 2 Replies View Related

SQL Server 2008 :: Putting Rows Together To Get Total Times

Jan 30, 2015

I am building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistencies with this, and i'm hoping to exclude them.. Here is a sample table:

Function | Employee | DateTime
--------------------------------------------
1 | 1 | 1/30/2015 1:47 PM
2 | 1 | 1/30/2015 1:49 PM
2 | 1 | 1/30/2015 1:50 PM
3 | 2 | 1/30/2015 1:37 PM
3 | 2 | 1/30/2015 1:39 PM
3 | 1 | 1/30/2015 1:40 PM
4 | 1 | 1/30/2015 1:42 PM
4 | 1 | 1/30/2015 1:45 PM

Function 1 = Clock In Type 1
Function 2 = Clock Out Type 1
Function 3 = Clock In Type 2
Function 4 = Clock Out Type 2

Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type.

View 8 Replies View Related

Show Total Amount Of Rows In Specific Table

May 20, 2012

I need to show the total amount of rows in a specific table?

The query is as follows:

As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.

The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).

View 4 Replies View Related

SQL Server 2008 :: Get Week Ending Date Given Week Number

Apr 9, 2013

How can I get Saturday's date given the week number?

This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.

View 9 Replies View Related

T-SQL (SS2K8) :: Get Week Numbers From Dates - Saturday Being Start Of Week

Sep 17, 2015

i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday

CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES

[code]....

The output should look like this

weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

View 4 Replies View Related

Create A Yesterday, This Week, Last Week Calculated Member

May 23, 2007

Hi all,

I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.

At the moment, I'm doing this:




Code Snippet

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]
But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?

So I guess there's two questions:

1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)

2)How come the Yesterday member is not returned by the .members function?


Thanks

Greg

View 3 Replies View Related

Which Is The Most Efficient Query To Find Out The Total Number Of Rows In A Table?

Mar 30, 2006

which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query

View 10 Replies View Related

Displaying Total Number Of Rows In A Report In Page Header.

Jun 20, 2007

Hi,



I have requirement to display Total number of Rows in a Report in Page Header.



I have written the following code in Page header it shows RowCount for the Page only.

=Count(ReportItems!textboxInTableCell.Value)



Can anyone please help on this?



Regards

Raghav





View 3 Replies View Related

How Not To Display A Row Depending On Other Rows.

Oct 8, 2007

Hello,
I am very new to SQL and ran into a problem using Access. I hope you can help me here.
The question is the following:
I have to tables:
Table 4x4: CommodityCode(string)/NeedofBU(high,low)
Table ASDBComplete: CommodityCode/Manufacturer/Rating(red,green,yellow)

What I want to get as a result is to display all ComodityCodes that have a 'red' rating and a 'high' NeedOfBU. So far no problem. But now there usually is more than one manufacturer for one CommodityCode. What I need now is that the CommodityCode is not displayed if there actually exist a Manufacturer with 'green' or 'yellow' rating.
How would you do that?
What I have so far is:

SELECT [4x4].[CommodityCode], [4x4].NeedofBU, ASDBComplete.CommodityCode, ASDBComplete.Rating, ASDBComplete.Manufacturer
FROM 4x4 LEFT JOIN ASDBComplete ON [4x4].[CommodityCode] = ASDBComplete.CommodityCode
WHERE ((([4x4].NeedofBU)="high") AND ((ASDBComplete.Rating)="red"));

But this gives me all the Commodities with red ratings. Even if there is a supplier with a green or yellow rating.

I would need somting like:
if exists 'commodityCode.XY with 'manufacuturer rating = green OR yellow' do not display commodityCode.xy

I really appreciate your help

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved