Crystal Viewer 8.5 with vb6

Any body help me that I want to use Crystal Report viewer 8.5 to explore the conditional report. For Example
Show data from date to date, or show data for particular code(s).
Yours cooperation in this regard is highly appreciable
Thanking yours.http://support.businessobjects.com/communityCS/TechnicalPapers/apps_rdc.pdf

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_21312684.html

Crystal syntax date query

Does anyone know what the formula is to search for records between 01/04/04 and the end of the last full month?
Thanks.Does anyone know what the formula is to search for records between 01/04/04 and the end of the last full month?

i assume that date1 is ur field at the table1 that need to be search for the formula..at ur report->edit selection formula->record..at this

{table1.date} in date(year,month,day) to date(year,month,day)

regards

Crystal Rpt 10 - currentdate + 6 months

Hey all I am trying to use Crystal Reports 10 and have a date that I need to use from my table. I want to take that date and if it is greater than the CURRENTDATE + 6 months I need to display a message. I have part of it but not sure how to do the "6 month" thing.

if ({mytable.mydate}) > CurrentDate ("+6 months") then
'Length-Promo-Rate = 0'

I have tried this:
CurrentDate + Month({mytable.mydate},6) but it doesn't like it. Suggestions? Thanks.Hi,
for that you have to insert a formula field like :

dim msg as string
if {Report Date/date from table} >= DateAdd ("m",6 , CurrentDate) then
msg ="6 month old"
else
msg = "new"

end if

formula = msg

-Jayesh Mendpara

Crystal Reports- last receipt date

Here is the situation: I have 3 tables.
Table 1: part number, unit of measure, standard cost
Table 2: last price paid, and Date of last price paid (same as last receipt date)
Table 3: PO# of last price paid (same as last receipt date)

When I insert these fields into the details section in Crystal Reports, I get duplicate part numbers listed on the report. Each part number may have several vendors attached to it, so this is expected.

This is what I am getting:

ItemNumber UM Std Cost LastCost LastReceiptDate PONumber
88888 Each 1.00 1.50 7/19/04 11111
88888 Each 1.00 1.80 7/22/04 22222
88888 Each 1.00 2.00 7/20/04 33333

What I want:

ItemNumber UM Std Cost LastCost LastReceiptDate PONumber
88888 Each 1.00 1.80 7/22/04 22222

I want the report to only show the most recent receipt date, price paid, and PO. Basically I need to do some sort of "comparison" between multiple vendors and its last receipt date within each part number.

Please help~~~~ Thank you!Hi,

I had faced same problem but I think if u used Command Object insted of tables
u can get solution.

write SQL query in Command Object and use MAX function on Receipt Date

-Yags

Crystal Reports help!

I am connecting to an AS400 via ODBC using Crystal Reports 9.0. I have a date that is coming out in a 7 digit number format. I need a formula for Crystal Reports that will convert this to a date format.

For example 1051111 would return 11/11/05.

The formula will have to get rid of the first "1" and put the other numbers in the correct format.

If someone can provide me with this formula and where I need to put it in Crystal Reports V9.0 that would be great.

ThanksSomething like this should do it. Just place it in your detail section.

StringVar ConvertDate := Right (ToText({your_number_field),6 );
StringVar YY := ConvertDate [1 to 2];
StringVar MM := ConvertDate [3 to 4];
StringVar DD := ConvertDate [4 to 5];

DD & "/" & MM & "/" & YY;

Brian
http://www.briankuipers.com|||Thanks alot, that helped a little. Except I told you wrong. The original format actually comes down as 1,051,111.00 with the commas and the decimal point.

When I use your formula it is counting the commas as text. I need a formula that would pull out the commas and decimal points and just use the text string of 1051111 that I originally stated was being used.

Thanks

Crystal Reports 7 using wrong date format

Whenever one of my users prints any of our reports, the date is in dd/mm/yyyy format, when it should be in mm/dd/yyyy format. This might be more of a Windows settings issue rather than a CR7 issue, but it only seems to do this when a CR7 report is printed, anything else involving a date in any other software prints/displays correctly. I have checked the regional settings on their computer and they are all set properly. Uninstalling and reinstalling the CR7 runtime files didn't seem to help either.

Does anyone know if CR7 stores its own date formatting information? Thank you in advance.Use Field Formatting to set the format of the date. Else you can covert the date into a string also.

In case you need to pass date as a parameter, Crystal Reports allows you to pass the date in three pieces (Date, Month and Year).

Hope you will solve the problem.

Kangkan

<a href="http://links.10026.com/?link=http://www.geekays.net">geekays.net</a>sql

Crystal Report.passing para and fatch record in Crystal Report

Dear Sir,

I m newer to crystal Report.i become crystal Report.now i want ,

I become Vb form which have ID and Date ,
i want selct id and date from vb form and searching that matching records in table and display in crystal report ..


how can i do this...

plz...help me..it's urgent...


Thanx in Advance

Sabina Maniar
U.A.EYou can either use Parameters or Selection formula
Refer http://www.dev-archive.com/forum/showthread.php?t=395587

Crystal Report issue - passing text value in vb to crystal

I have a date time picker for begin date and end date to generate my report. I want the begin date and end date selected in my VB App to show on my crystal report as a selected date range. For example, I want my report header to show something like this:

Date Range: 12/14/06 - 12/31/06

This is most likely an easy solution, but I am new to Crystal. What would be the proper way to do this?Figured this one out.....|||How do you resolved this, can you put your code here?

crystal Report

Hello,

i m using CR 9,Vb 6 and MS-Access..

in CR ,3 paramaeters ID,from date and to date..
after that in VB i have to pass para by textboxes..

so how can i set this parameters.

how can i display report by date ranges...

plz..somebody help me...

Thanx in Advance

Regars,
SabinaPlease refer to
http://www.dev-archive.com/forum/showthread.php?t=397120

I hope you don't create duplicate thread and try put a title that straight to the point.
PS. Sorry, I am not a moderator.

crystal Report

hi All,

i m using Vb 6.0,M.Access and crystal report 9

i have one form in vb which have id and date field,
i want to select values from that vb form and seaching records in table in Access and display record in crystal report....

plz..help ma...it's urgent..

Thanx in Adavance

Regards..

Sabina Maniar

U.A.epass parameters to crystal report , then use those parameter in report query or in record selection formula.|||Hi..

Thankx to reply

i have already pased a para to crystal report ,

but i dont know how to pass CR para in vb in crystal viewer...

plz...if u have any related topics then plz..plz...reply me..

thanx again

Regards,

Sabina Maniar
U.A.E|||Set Report = crystal.OpenReport(App.Path & "\report1.rpt")

Report.DiscardSavedData
Report.Database.SetDataSource rs
Report.ParameterFields(1).AddCurrentValue ("aaa")

You can use this method to set parameters..

Regards,
Ahmed

Crystal Formula - SQL

I started with this selection criteria:

({@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date})

Then I did this:

{@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date} and
({LabSpecLPatientIndex.Prefixes} in ["IM", "R"] and {LabLSpecResultTests.Result} <> "")

I got data just fine on that.

Then I further altered it with this:

({@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date}) and
(({LabSpecLPatientIndex.Prefixes} ="IM" and {LabLSpecResultTests.Test} = "902.9550"
and {LabLSpecResultTests.Result} <> "") or ({LabSpecLPatientIndex.Prefixes} ="R" and
{LabLSpecResultTests.Result} <> ""))

And I get no data. What did I miss? I want all records with between those dates, that have prefixes = IM or R that have results (not null results). If the prefix = IM, then I only want it if Test = "902.9550" ...but I want all records with a Prefix R.

TIA.
Briana{@.Meditech Date to PC Date} in {?Beginning Date} to {?Ending Date} and
({LabSpecLPatientIndex.Prefixes} in ["IM", "R"] and {LabLSpecResultTests.Result} <> "") and {LabLSpecResultTests.Test} = "902.9550"

Crystal Date Array Calculations

I currenty an trying to get a datediff of each set of dates in an array

My array is populating with this in a fuction called getarray

Local DateTimeVar Array OpenAy := MakeArray (
if {pr_activity_type.name}="Open" or
{pr_activity_type.name}="More Info Required" or
{pr_activity_type.name}="Initiate NCR" or
{pr_activity_type.name}="Confirm" or
{pr_activity_type.name}="Complete Investigation" or
{pr_activity_type.name}="Close" or
{pr_activity_type.name}="Re-Open" then {pr_activity.date_posted}
);
Local NumberVar o := 1;
Local NumberVar countero := 1;

While o <= ubound(OpenAy) Do
(

//result:= OpenAy[i];
if countero <>o then
(
countero:= o;
);
o := o + 1

);

OpenAy[countero];

I tried to do datediff ("d", OpenAy[countero],OpenAy[countero+1])
however it errors out becuase the function has not populated all of the array yet

Is there away to call OpenAy in anouther formula so it already is populated with all the dates needed.

If you need more explanation let me know.

ThanksI just relized that with the code posted I am not populating more then one row of the Array

Example output

Rec State Date Performed FunctionOutput
1 Open 2002/02/02 12:12:12 2002/02/02 12:12:12
1 Initiate 2002/03/02 12:12:12 2002/03/02 12:12:12
1 Confirm 2002/06/02 12:12:12 2002/06/02 12:12:12
1 Complete 2002/12/02 12:12:12 2002/12/02 12:12:12

these records are one record per details line.

when I get and output my array it outputs the correct dates however It only stores one date at a time. If I out put it in the Page footer I get the last date. If I output the ubound I get 1.

Im not sure how to get this to work correctly. What I want as an output is this.

Rec State Date Performed FunctionOutput
1 Open 2002/02/02 12:12:12
1 Initiate 2002/03/02 12:12:12 1
1 Confirm 2002/06/02 12:12:12 3
1 Complete 2002/12/02 12:12:12 6

days in open 1 days in Initiate 3 days in confirm 6

however I need to do an array becuase It could go back to Open more then once.|||From what I've seen / understood here, I'd probably

1) Group on the Record id (assuming it's unique)
2) Sort on the date performed / date posted (same thing?)
3) Use the Previous function for the date comparison, e.g. a formula like:
whileprintingrecords;
if {table.record_id} = previous({table.record_id}) // to check if in the same record id group
then totext(datediff("d", previous({table.record_id}), {table.record_id}), 0)
else ""|||Thanks Jagan

I spaced the prevous function. With your Idea I was able to get the output I wanted.

Thanks|||With this code is there a way to sum.
I keeps telling me that "this field can not be summarized
However I changed my ouput to a number, Am I missing something.

numberVar DiffDate;

whileprintingrecords;
if {table.record_id} = previous({table.record_id})
then
DiffDate:=datediff("d", previous({table.record_id}), {table.record_id})
else 0

Second Function

SUM({abovefunction});|||Also why can some formulas not be summarized. using the E autosum function in crystal
the function below does not even show up in the list when I try and sum it.

Im guessing its the prevous function. If so is there any other way to do this. OK now I know its the next and Previous function. Is there any other way to sum when I need to use that function.

What I need to do is get the avg days it takes for some one to perform confirm. I am now outputing a datediff for the state of confimed and I have a function that retruns a 1 if the state is Confirm however If I sum if its at confirmed I get every record. I can put and if to see if the record number <> next(record number) because then I could not sum it.

Im not sure what I need to do next. Any help is appreciated.


numberVar output;
numberVar total:=0;
global numberVar som:=0;
global numberVar avg:=0;


If {PR_ACTIVITY_TYPE.NAME}="Confirm" then
(
if {pr.id} = previous({pr.id})
then
output:=tonumber (datediff("d", previous({PR_ACTIVITY.DATE_POSTED}), {PR_ACTIVITY.DATE_POSTED}))
else
output:=0;

if {pr.id} = previous({pr.id}) then
(
if output >=1 then
(
total:=1;

)
else
total:=0;
);

);


total;|||Could anyone look at my last question. This is still causing me issues.



Thanks

Cross-Tab vs SQL query

Hi,
I have a report like this...

Date Sam Tom Harry 2006 2005
02/01/2006 4 1 1 6 2
02/02/2006 3 1 1 4 5
02/03/2006 2 2 0 4 4
---
---

02/12/2006 1 1 1 3 1
-----------
Total -- -- -- -- --

Now I have all the data required for that in one table.

CREATE TABLE t1(
indate DATETIME
,agname VARCHAR(60)
,Polnumber VARCHAR(20)
)


So I have 2 options :
I have to do in in Cross tab or I have to create a query for this.
Is it possible to built a query like this,I mean assigning data as column?

Any help will be greatly appreciated.
Thanks!!Is it possible to built a query like this,I mean assigning data as column?Yep. If there are a fixed number of possible column values you can use CASE. If not then you need to use dynamic SQL (e.g. http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817,
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608,
http://www.sqlteam.com/item.asp?ItemID=2955). I've not used any of the above - I just have them bookmarked in case I need them.

BTW - your better option might be the front end doing this if at all possible

HTH|||Thanks a lot pottie for those links.I will give it a try tonight...Thanks once again..:cool:

crosstab stored procedure

I have a table that has the following information like this:

Name Date 1st item 2nd item 3rd item 4th item 5th item
Defaul name 11/1/2005 100 0 0 0 0
Defaul name 11/2/2005 100 0 0 0 0
Defaul name 11/3/2005 99.69 0 0 0 0

I need to create a crosstab query (NOT using crystal report) that will display the information like:

11/1/2005 11/2/2005 11/3/2005
1st item 100.00 100.00 99.69
2nd item 0.00 0.00 0.00
3rd item 0.00 0.00 0.00
4th item 0.00 0.00 0.00
5th item 0.00 0.00 0.00

I have never used a crosstab query in sql server before. Please help!First, look up CROSSTAB in Books Online and you will see the general method for handling this using CASE statements.
Unfortunately you are going to have problems if your column headers are dynamic, which is often the case when you are grouping your columns by date values.
If, as your example implies, you are only dealing with one year's worth of data at a time, then you can group your columns by datepart(month...), which will give you 12 consistent column labels.
Avoid dynamic crosstab queries if at all possible (or until you upgrade to SQL Server 2005...). And though you apparently already know this, I have to say that dynamic crosstab functionality does not really belong in SQL server anyway, SQL Server 2005 not withstanding. It is a presentation issue.|||Thanks for your reply. Unfortunately, the column values have to be dynamic. There is an easy way to present this in crystal reports but unfortunately i have to write directly to an excel file from a stored procedure. I'm at a loss right now as to how to accomplish this presentation in a stored procedure with dynamic columns.|||Not to beat a dead horse, but look at ags crosstab and/or RAC (used to be replacement for access crosstab, but they changed their name).

Regards,

hmscott

Cross-Tab Query

I need to produce a query in cross-tab format that displays the column data as the first day of the week for every week for a given date range.

i.e. for August
4 Aug 03 | 11 Aug 03 | 18 Aug 03 | 25 Aug 03

Row1
Row2
etc...

Any pointer in the right direction would be appreciated.
Thanks
TimThe basic idea is to make a query, which returns the value to be accumulated with the corresponding week indication, like:

SELECT YourGroupingField,
case datediff(wk, '2003-01-01', getdate()) < 30 THEN YourSumField ELSE 0 END AS Aug0,
case datediff(wk, '2003-01-01', getdate()) = 30 THEN YourSumField ELSE 0 END AS Aug1,
case datediff(wk, '2003-01-01', getdate()) = 31 THEN YourSumField ELSE 0 END AS Aug2,
case datediff(wk, '2003-01-01', getdate()) > 31 THEN YourSumField ELSE 0 END AS Aug3
FROM YourTable

You may use this query as a subquery or as a database view:

SELECT YourGroupingField, sum(Aug0), sum(Aug1), ...
FROM (YourQuery)
GROUP BY YourGroupingField

Let me know if this helps, or when you don't understand my outline.|||Thank you for your quick reply.
I think I did not explain myself that well. So here goes - the column dates should auto-generate based on a date range. This date range could potentially be several months or years. So if the date range was 1 March 03 - 30 June 03 this would produde 18 columns starting with 3 Mar 03.

I know how to write a normal cross-tab but it's the auto-column generation that's tricky for me.|||You want the heavy stuff? Look at this (www.sqlmag.com/Articles/Index.cfm?ArticleID=15608).

CrossJoin of two large sets....

I am looking for the best way to display a date related to a lot.

The problem is when I cross join to a date dimension the performance is terrible. Is there anyway to improve performance?

Code Snippet

WITH

MEMBER x

MEMBER y

SELECT NON EMPTY { [Measures].[X], [Measures].[Y] } ON COLUMNS,

NON EMPTY { ([Lot].[Lot].[Lot].ALLMEMBERS * [Voter Source].[Voter Source Code].[Voter Source Code].ALLMEMBERS)* [Date].ALLMEMBERS ) } ON ROWS

FROM [CUBE]

What do the definitions of X and Y look like - that would affect performance? And how many empty cross-joined rows are to be eliminated?

cross table

Sir,

My query is

select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/11/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/12/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/13/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/14/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/15/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/16/2006'
group by task_date

My out put is

Date Program Design Upload Testing Meeting Others

2006-09-11 00:00:00.000 42 0 0 8 2 1

2006-09-12 00:00:00.000 77 0 0 4 0 0

2006-09-13 00:00:00.000 56 0 0 8 0 1

2006-09-14 00:00:00.000 63 0 0 6 0 1

2006-09-15 00:00:00.000 63 0 0 6 0 1

Now i want in below format

2006-09-11 2006-09-11 etc

Program 42 77

Design 0 0

Upload 0 0

Testing 8 4

Meeting 2 0

Others 1 0

Total 53 81

How to convert in this format .

--From your output as table:transposetable$:

SELECT ISNULL(cat, 'Total') ,SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',

SUM([9/15/2006]) AS '9/15/2006'

FROM (SELECT cat, MIN(CASE WHEN t.tDate = '9/11/2006' THEN Program END) AS '9/11/2006',

MIN(CASE WHEN t.tDate = '9/12/2006' THEN Program END) AS '9/12/2006',

MIN(CASE WHEN t.tDate = '9/13/2006' THEN Program END) AS '9/13/2006',

MIN(CASE WHEN t.tDate = '9/14/2006' THEN Program END) AS '9/14/2006',

MIN(CASE WHEN t.tDate= '9/15/2006' THEN Program END) AS '9/15/2006'

FROM

(SELECT 'Program' as cat, tDate, Program FROM transposetable$

UNION ALL

SELECT 'Design', tDate, Design FROM transposetable$

UNION ALL

SELECT 'Upload',tDate, Upload FROM transposetable$

UNION ALL

SELECT 'Testing', tDate, Testing FROM transposetable$

UNION ALL

SELECT 'Meeting', tDate, Meeting FROM transposetable$

UNION ALL

SELECT 'Others', tDate, Others FROM transposetable$

) t

GROUP BY cat ) x

GROUP BY cat

WITH ROLLUP

|||

--SQL Server 2005, Louis's idea. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1

WITH myCTE as(

select tDate, cat, value

from ( select tDate, Program, Design, Upload, Testing, Meeting,Others

from transposetable$) p

UNPIVOT

(value for cat in ([Program], [Design], [Upload],[Testing],[Meeting],[Others])) as unpvt)

SELECT ISNULL(cat, 'Total'),SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',SUM([9/15/2006]) AS '9/15/2006'

FROM

(select cat, tDate,value

from myCTE) as rotated

PIVOT

(SUM(value) FOR tDate in ([9/11/2006],[9/12/2006],[9/13/2006],[9/14/2006],[9/15/2006])) as pvt

GROUP BY cat

WITH ROLLUP

|||

Hi,

have you looked at the UNPIVOT function?

Sten-Gunnar

Cross Tab Query

I have a table called summary. This table contains calldate, projects, hours
.
I need to create a report/table that shows the date down the left column and
the projects across the top.
Date ProjectA ProjectB ProjectC
20060401 12 0 2
20060402 2 5 4
20060403 3 5 1
How can I accomplish this?
Any help would be greatly appreciated.

code:

CREATE TABLE [#TEST] (
[sCalldate] [varchar]((20) NULL ,
[sProject] [varchar] (20) NULL ,
[dHours] numeric (10,4) NULL)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'A', 12)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'B', 0)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'C', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'A', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'C', 4)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'A', 3)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'C', 1)

Thanks,
Ninel
Message posted via http://www.webservertalk.comTry:
select
sCallDate
, sum (case when sProject = 'A' then dHours else 0 end) ProjectA
, sum (case when sProject = 'B' then dHours else 0 end) ProjectB
, sum (case when sProject = 'C' then dHours else 0 end) ProjectC
from
#TEST
group by
sCallDate
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ngorbunov via webservertalk.com" <
u9125@.uwe>
wrote in message
news:5e3bc0ad31e3a@.uwe...
I have a table called summary. This table contains calldate, projects,
hours.
I need to create a report/table that shows the date down the left column and
the projects across the top.
Date ProjectA ProjectB ProjectC
20060401 12 0 2
20060402 2 5 4
20060403 3 5 1
How can I accomplish this?
Any help would be greatly appreciated.

code:

CREATE TABLE [#TEST] (
[sCalldate] [varchar]((20) NULL ,
[sProject] [varchar] (20) NULL ,
[dHours] numeric (10,4) NULL)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'A', 12)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'B', 0)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'C', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'A', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'C', 4)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'A', 3)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'C', 1)

Thanks,
Ninel
Message posted via http://www.webservertalk.com|||--2000
SELECT
sCallDate
,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
FROM #TEST
GROUP BY sCallDate
--2005
SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
FROM #TEST
PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ngorbunov via webservertalk.com" <
u9125@.uwe>
wrote in message news:5e3bc0ad31e3a@.uwe...[color
=darkred]
>
I have a table called summary. This table contains calldate, projects, hour
s.
>
I need to create a report/table that shows the date down the left column a
nd
>
the projects across the top.
>
>
Date ProjectA ProjectB ProjectC
>
20060401 12 0 2
>
20060402 2 5 4
>
20060403 3 5 1
>
>
How can I accomplish this?
>
Any help would be greatly appreciated.
>
>

code:

>
 CREATE TABLE [#TEST] (
>
 [sCalldate] [varchar]((20) NULL ,
>
 [sProject] [varchar] (20) NULL ,
>
 [dHours] numeric (10,4) NULL)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'A', 12)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'B', 0)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'C', 2)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'A', 2)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'B', 5)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'C', 4)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'A', 3)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'B', 5)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'C', 1)
>

>
>
Thanks,
>
Ninel
>
>
--
>
Message posted via http://www.webservertalk.com[/color]|||How can I do this without hardcoding the projects? New projects are added
almost everyday.
Tibor Karaszi wrote:
>--2000
>SELECT
> sCallDate
>,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
>,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
>,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
>FROM #TEST
>GROUP BY sCallDate
>--2005
>SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
>FROM #TEST
>PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
>[quoted text clipped - 44 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||SELECT SCALLDATE, SUM([PROJECT A]) AS [PROJECT A], SUM([PROJECT B]) AS
[PROJECT B], SUM([PROJECT C]) AS [PROJECT C]FROM
(
SELECT SCALLDATE,
CASE WHEN SPROJECT = 'A' THEN DHOURS ELSE 0 END AS [PROJECT A] ,
CASE WHEN SPROJECT = 'B' THEN DHOURS ELSE 0 END AS [PROJECT B] ,
CASE WHEN SPROJECT = 'C' THEN DHOURS ELSE 0 END AS [PROJECT C]
FROM #TEST
)AS A
GROUP BY SCALLDATE|||Unfortunately, you'd have to use dynamic SQL to generate the statement and
then execute the statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message
news:5e3bec27d6652@.uwe...
How can I do this without hardcoding the projects? New projects are added
almost everyday.
Tibor Karaszi wrote:
>--2000
>SELECT
> sCallDate
>,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
>,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
>,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
>FROM #TEST
>GROUP BY sCallDate
>--2005
>SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
>FROM #TEST
>PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
>[quoted text clipped - 44 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||Try this,,
select sCalldate, ProjectA = ISNULL ((SELECT dHours FROM #Test WHERE
sProject = 'A' AND sCalldate =
Q.sCallDate),0),
ProjectB = ISNULL ((SELECT dHours FROM #Test WHERE sProject = 'B' AND
sCalldate =
Q.sCallDate),0),
ProjectC = ISNULL ((SELECT dHours FROM #Test WHERE sProject = 'C' AND
sCalldate =
Q.sCallDate),0)
FROM #test Q
GROUP BY sCallDate
Ref : EN-US;
q175574" target="_blank">http://support.microsoft.com/defaul...b;
EN-US;
q175574
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"ngorbunov via webservertalk.com" wrote:

>
I have a table called summary. This table contains calldate, projects, hou
rs.
>
I need to create a report/table that shows the date down the left column a
nd
>
the projects across the top.
>
>
Date ProjectA ProjectB ProjectC
>
20060401 12 0 2
>
20060402 2 5 4
>
20060403 3 5 1
>
>
How can I accomplish this?
>
Any help would be greatly appreciated.
>
>

code:

>
 CREATE TABLE [#TEST] (
>
 [sCalldate] [varchar]((20) NULL ,
>
 [sProject] [varchar] (20) NULL ,
>
 [dHours] numeric (10,4) NULL)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'A', 12)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'B', 0)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'C', 2)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'A', 2)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'B', 5)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'C', 4)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'A', 3)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'B', 5)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'C', 1)
>

>
>
Thanks,
>
Ninel
>
>
--
>
Message posted via http://www.webservertalk.com
>
|||Thats really informative sql 2005 way of doing cross tab.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Tibor Karaszi" wrote:

> --2000
> SELECT
> sCallDate
> ,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
> ,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
> ,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
> FROM #TEST
> GROUP BY sCallDate
> --2005
> SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
> FROM #TEST
> PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message news:5e3bc0ad3
1e3a@.uwe...
>|||Hi,
Check out the RAC utility.It will easily do what you want and
will not insult your intelligence:)
www.rac4sql.net
"ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message
news:5e3bec27d6652@.uwe...
> How can I do this without hardcoding the projects? New projects are added
> almost everyday.
> Tibor Karaszi wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||How much does it take for your intelligence to be insulted?
Over 5 years in development and an army to do it and this is what
they throw at users':)
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:B53C964A-F511-451D-8597-BF21640130BA@.microsoft.com...
> Thats really informative sql 2005 way of doing cross tab.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and
> time
> asking back if its 2000 or 2005]
>
> "Tibor Karaszi" wrote:
>