T-SQL Cross Tab /w Dates HELP!

Jan 16, 2008


I would like to be able to enter a date range and allow sql to format the result set as follows:

The two dates I would pass in are 7/1/2007 (start) to 12/31/2007 (end date)

I have three fields called:
Amount ($10.50)
Date (7/11/2007)
Key ( A, B, C, etc )

Table looks like this:
Key Amount Date
A 10.50 7/11/2007
C 1.00 8/1/2007
D 2.00 12/20/2007
B 3.45 8/5/2007


The result should look like this:
Key Jul07 Aug Sep Oct Nov Dec07 Total
A 10 40 0 0 5 10 65
B 30 10 10 5 10 10 75
Etc.

If I changed the date range to 1/1/2007 (start) to 12/31/2007 the result should look like this:
Key Jan07 Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec07 Total
A 4 10 42 0 0 3 10 40 0 0 5 10 124
B 1 6 10 0 2 0 30 10 10 5 10 10 94
Etc.

Thanks in advance.

View 5 Replies


ADVERTISEMENT

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

SQL 2005 Dev

How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Comparing Dates With Today Dates

Jun 21, 2005

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date.  the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?

View 1 Replies View Related

Generate Dates Between Two Dates

Nov 8, 2006

Hi,

I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.

I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:

SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)

Any advice will be greatly appreciated!

View 1 Replies View Related

Getting Individual Dates Between 2 Dates

Mar 4, 2008

Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?

So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?

View 13 Replies View Related

T-sql Cross Tab

Jul 20, 2004

I should've posted this thread here....

http://www.dbforums.com/t1005070.html

View 1 Replies View Related

Cross Tab?

May 18, 2004

I need to format the following tables:


tbEmployees
EmployeeID | fName | lName
-------------------------------------------
jdoe | Joe | Doe
bsmith | Blake | Smith

tbDepartments
DepartmentID | Department
-------------------------------
ENG | Engineering
DET | Detailing

tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
------------------------------
ENG | jdoe
DET | bsmith

tbProjects
ProjectID |
---------------
1001

tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
-------------------------------------------
1001 | jdoe | ENG
1001 | bsmith | DET


To the following view :


vProjects
ProjectID | Engineer | Detailer
-------------------------------------
1001 | Joe Doe | Blake Smith


Any Ideas?
Mike B

View 1 Replies View Related

Cross Tab

Jun 17, 2008

Is it possible change to cross tab using code?

Before code:

NAMETYPEVALUE
AAAHEIGHT66
AAAWEIGHT198

After code

NAMEHEIGHTWEIGHT
AAA66198

View 5 Replies View Related

Cross Tab

Oct 20, 2006

Hi,
I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.

I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.

It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.

AcademicYearId StudentId coursecode completionstatusid
05/06 912345 FWFT1 CONTINUING
05/06 914587 FWFT1 CONTINUING
05/06 987654 FWFT1 WITHDRAWN
05/06 954376 FWFT1 CONTINUING
05/06 934512 FWFT1 TRANSFERRED
05/06 923000 FWFT1 TRANSFERRED
04/05 945109 FWFT1 COMPLETED

I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this

Year CourseCode Students Continuing Withdrawn Transferred Completed
05/06 FWFT1 6 3 1 2 0
04/05 FWFT1 1 0 0 0 1

Can anyone help me with the the extra columns please.

View 12 Replies View Related

Cross Tab??

Jul 20, 2005

thought my question was misinterpreted.actual table can be smaller or larger. but,within a procedure need to turn a created #temptablesimilar to(examid is in asc order):(id, p/f, examid, examname)1 f xxxx xxxxx1 f xxxx xxxxx1 f xxxx xxxxx2 p3 p4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx(note, id = 4 has 7 failed exams).to a table with up to five columns of failedexamnames:1 f xxxxx xxxxx xxxxx2 p2 p4 f xxxxx xxxxx xxxxx xxxxx xxxxxcan get rid of exameid if helps.--Sent by ricksql from yahoo subpart from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new

View 2 Replies View Related

Cross Tab Query

Sep 7, 2007

 Hi!
I m using sql 2005.I have a table as showing below.










year
Region
loan_amt
pur_id
purpose


1981
Andhra pradesh
$20,000.00
     1
Animal Husbandary

1981
Arunachal Pradesh
$110,000.00
     1
Animal Husbandary

1981
Assam
$240,000.00
     1
Animal Husbandary

1981
Bihar
$75,000.00
     1
Animal Husbandary

1981
Chhattisgarh
$55,500.00
     1
Animal Husbandary

1981
Gujarat
$77,500.00
     1
Animal Husbandary

1982
Goa
$44,888.00
     1
Animal Husbandary

1982
Himachal pradesh
$4,000.00
     1
Animal Husbandary

1982
Himachal pradesh
$20,000.00
     1
Animal Husbandary

1982
Jammu and kashmir
$30,000.00
     1
Animal Husbandary

1882
Jharkhand
$35,000.00
     1
Animal Husbandary

1982
Karnataka
$40,000.00
     1
Animal Husbandary

1982
Kerala
   $20,000.00   
     1
Animal Husbandary

1982
Madhya pradesh
$5,000.00
     1
Animal Husbandary
 
 
I want to produce report as  by using crosstab query as showing bellow
 
Year      Asam     Hyadrabad   goa  arunachal pardesh etc............
1981      1000.00    2000.00       8000.2  00000        000000 ......
1981
'
'
Is it possible by crosstab query ?or please suggest me another way as early as possible.
 
Thanx in advance.
Abhishek
 
 
 
 
 

View 6 Replies View Related

Hi Need Help In Cross Tab Query

Apr 14, 2008

I wanna develope a report using cross  tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format
merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008
test                   45          50            25          10            11
 
Please can any one help me how to do this this is very urgent ?

View 2 Replies View Related

Cross Tab Problem

Oct 14, 2004

I'm trying to run the cross tab query so that it returns only one row. If there is data I want to dump in the field value (fText), otherwise I put in a dash. The problem I'm having is that it's returning multiple rows when I want a single one. I could create a # table and dump the data in, but it seems a bit long winded.

Many thanks.


SELECT distinct (CASE WHEN f.TypeID=1 THEN 1 ELSE 0 END) f1,
(CASE WHEN f.TypeID=2 THEN f.fText else '-' END) f2,
(CASE WHEN f.TypeID=3 THEN f.fText else '-' END) f3,
(CASE WHEN f.TypeID=4 THEN f.fText else '-' END) f4,
(CASE WHEN f.TypeID=5 THEN f.fText else '-' END) f5,
(CASE WHEN f.TypeID=6 THEN f.fText else '-' END) f6,
(CASE WHEN f.TypeID=7 THEN f.fText else '-' END) f7,
(CASE WHEN f.TypeID=8 THEN f.fText else '-' END) f8,
(CASE WHEN f.TypeID=9 THEN f.fText else '-' END) f9,
(CASE WHEN f.TypeID=10 THEN f.fText else '-' END) f10
FROM tblTest f WHERE (f.fID=219)

View 1 Replies View Related

Cross Database

May 23, 2005

" I have two sql server2000 database named db1 and db2.
i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1.
now this table was droped and created in db2.

what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.

i have created a view in db1 with the same name as

'create view tableOne as select * from db2..tableOne'

now i can access.
Is there some othere way with out creating view?

View 7 Replies View Related

Reverse Cross Tab

Feb 19, 2002

Is is possible to write a query that does the reverse of a cross tab query, I have a table that is mirrored for our Main frame, the table contains a item number a warehouse number and a date field and then 43 fields like field01 thru field43 which store quantities, each field out from 01 is another date our from the date field and I need to look for the item, warehouse and return a date where one of the many field is greater than or equal to the quantity I am looking for, I obviously will have to calculate the date field but thought If I could do the reverse of a cross tab giving a resuld set of 43 records for everyone in the first file I could do a quick update for item number, warehouse match and quantity field greater than what i need returning the date

blah blah blah , sorry I made it sound so confusing

THANKS IN ADVANCE

View 2 Replies View Related

Oposite To Cross Tab?

Jun 15, 2004

I need to format the table shown :


Estimate | Quantity | Admin_can | Amind_am | Manu_fix | Manu_var
-------------------------------------------------------------------
5 | 1000 | .10 | 0.10 | 0.75 | 0.7


as the following :

Estimate | Variable | Quantity | Value | Total
---------------------------------------------------
5 | Admin_ca | 1000 | 0.1 | 100
5 | Admin_am | 1000 | 0.1 | 100
5 | Manu_fix | 1000 | 0.75 | 750
5 | Manu_var | 1000 | 0.7 | 700


I need to do this so it would be the correct format for a report. Can anyone suggest how to do this? I need to create a view with this format, so speed is
probably an issue?

Mike B

View 1 Replies View Related

Cross-Tab Vs SQL Query

Sep 15, 2006

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!!

View 2 Replies View Related

Cross-tab Query

Jan 2, 2007

Hi all.

we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?

Thanks so much.
ODaniels

View 6 Replies View Related

Cross Tab Report

Dec 11, 2007

Using SQL Server 2005. Is there a way to create a cross tab report and then save it into a table only using SQL Code. Thank you for your help. David

View 5 Replies View Related

Can I Use PIVOT / Cross Tab For This? If Not...?

May 8, 2008

I have the following data structure (simplified)


declare @log table (
date smalldatetime,
category char(3),
value1 int,
value2 int)

insert into @log(date, category, value1, value2)
select '2008-01-01', 'ABC', 11,12 union all
select '2008-01-02', 'ABC', 35,53 union all
select '2008-01-03', 'ABC', 38,62 union all
select '2008-01-05', 'ABC', 59,95 union all
select '2008-01-02', 'XYZ', 42,21 union all
select '2008-01-04', 'XYZ', 9,7 union all
select '2008-01-05', 'XYZ', 89,45 union all
select '2008-01-01', 'HHH', 70,52 union all
select '2008-01-03', 'HHH', 3,83 union all
select '2008-01-05', 'HHH', 26,77


where
1) date is always up to the day (no time variation)
2) date and category can be considered a composite unique key

Given a date range (let's say, from 2008-01-01 to 2008-01-05) I need to get the below:

date abc_value1 abc_value2 xyz_value1 xyz_value2 hhh_value1 hhh_value2
---------- ----------- ----------- ----------- ----------- ----------- -----------
01/01/2008 11 12 NULL NULL 70 52
01/02/2008 35 53 42 21 NULL NULL
01/03/2008 38 62 NULL NULL 3 83
01/04/2008 NULL NULL 9 7 NULL NULL
01/05/2008 59 95 89 45 26 77

Ideally, the results include
- every day in the date range (even if there is no corresponding data for that date)
- the columns values to be dependent on the categories found within the date range

I came up with this

-- to fulfill requirement "every day in the date range"
declare @dt table (d smalldatetime)
insert into @dt
select '2008-01-01' union all
select '2008-01-02' union all
select '2008-01-03' union all
select '2008-01-04' union all
select '2008-01-05'

-- to fulfill display all the categories (manually determined)
select convert(varchar(10),d,101) as date,
abc.value1 as abc_value1, abc.value2 as abc_value2,
xyz.value1 as xyz_value1, xyz.value2 as xyz_value2,
hhh.value1 as hhh_value1, hhh.value2 as hhh_value2
from @dt dt
left join @log abc on dt.d = abc.date and abc.category = 'ABC'
left join @log xyz on dt.d = xyz.date and xyz.category = 'XYZ'
left join @log hhh on dt.d = hhh.date and hhh.category = 'HHH'


just for the purpose of generating the end result example, but in a real life situation, both the date range and the categories that may fall within that date range... are dynamic. To make my head spin even more, I also suspect the issue of value2 AND value3 being pulled is making this one complicated statement.

Any ideas? Thoughts? Suggestions?

View 3 Replies View Related

Cross Apply

May 22, 2008

What is Cross Apply, when it will be used ?

View 2 Replies View Related

Cross Tab Formula

May 26, 2008

in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.

my table data consists like below:
country_name Revenue PERIOD_TYPE_OUT Amount_out
UK Solutions01 Apr 2007 To 31 Mar 200856.83000000
UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000

while iam using the cross tab in front end application data view is coming as
country_name

01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008
solution solution
uk 116.07 56.83


Actual o/p should be:

01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth
solution solution
uk 116.07 56.83 -0.51


can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate
is :
(56.83-116.07)/116.07
([current year]-[previous year])/[previous year]

View 2 Replies View Related

Cross-Tab Question

May 29, 2008

Hi

I am using SQL server 2005 and want to write a cross-tab query. i have managed a basic one but can't seem to write a more complex one.
if i have a table with columns called id, person_id, field_description, and content. e.g. image that the two values i could have in field description are age and favourite_colour, and then the corrosponding content for these fields would be 56yrs and blue or something.

now i want column names as person_id, age and favourite_colour. and the actual data should be content.

Have i made sense? I am sure this must be simple, but can't figure the syntax

thanks

pete

View 4 Replies View Related

Cross Query

Oct 11, 2007

I would like to make 3x3 table

as following
john | mike | wendy
< 100€ | 3 0 0
100-200€ | 0 2 0
> 200€ | 1 0 1

table:
name (john, mike, wendy)
money (numer)


how should i do it?

i tried with inner join and subquery and a case but it doesn't work.

thank you

View 4 Replies View Related

Cross Tab Query

Jul 20, 2005

Hi - I have what I think is a "simple problem".We currently have a database table that stores ItemProperties byItemID, PropertyID and Value. (The PropertyID references another tablefor property names and types.)This ItemProperties table is indexed and provides a flexible way ofstoring our item metadata. However, I would now like to returnrecordsets to the application layer that list these properties incolumn fashion, grouped by ItemIDI have seen that most cross-tab queries examples assume numerical dataand are based around using SUM and the GROUP BY phrase. But our datais a mixture of string and numbers (of various formats) and so GROUPBY is not an obvious solution. I have tried using CASE in the selectlist but this returns one row for each property with one column havingthe correct value, and all the other colums are NULL. I cannot thinkof how to combine these into one full record!!I could achieve the desired resultset by using a SELECT sub-statementfor every column, but I was hoping there was a more efficient method.Can anyone offer advice on this? It would be most appreciated.Best,Bill

View 1 Replies View Related

Problem In A CROSS To SQL

Sep 8, 2006

Dear friends;

I have a CROSS table in ACCESS that I want to export to SQL 2005. The query is:
TRANSFORM Count(GD_PROFILE.cnRow) AS CountOfcnRow
SELECT GD_PROFILE.ID
FROM GD_PROFILE
GROUP BY GD_PROFILE.ID
PIVOT GD_PROFILE.ID_APLIC;



The RESULT is:






ID

20

22

77

122

141

179

211

327

334

370

388

405

524

584

645

687

826

865

914


CN1



1















1





1

1





1






CN2



1















1







1





1






CN3



1















1







1





1






CN4



1















1





1

1





1






CN5



1















1







1





1






CN8

1

1



1

1









1

1





1

1

1

1

1




CN14

1

1



1

1









1

1





1





1






CN15

1

1

1

1

1



1





1

1





1





1






CN22



1







1





1

1



1

1

1





1






CN29

1

1















1



1



1





1



1


CN36







1







1











1










I want to create a query in SQL Server that give me rows from the cross query that are equal. For example, the row 1 and 4 are equal. Has the same values in each row. How can I make it programatically?

The Cross is a simple cross query, and the goal is return this final result:

Profile 1 - ID CN1, ID CN4
Profile 2 - ID CN2, ID CN3, ID CN5
Profile 3 - ID CN8
Profile 4 - ID CN14
Profile 5 - ID CN15
Profile 6 - ID CN22
Profile 7 - ID CN29
Profile 8 - ID CN36

Could you HELP me??
THANKS!!!!







View 5 Replies View Related

Cross Join

Dec 25, 2007



good morning
I wanna to put a sub query in the cross join like that



SELECT *

FROM table1

CROSS JOIN (select field

from table2

cross join table3)


but i got this error


Msg 102, Level 15, State 1, Line 7

Incorrect syntax near ')'.

can anyone help me!!!!

View 1 Replies View Related

Cross Tab Report

Apr 5, 2007

Hi,



I need some help on crosstab query. I have tried the examles, but somehow, not able to find a right solution.

Any help will be appreciated. The report I would like to have is the one at the bottom.

thx.

Josh



Table Structure>











Brand
Product
Line
Jan
Feb
Mar
Total

A
Chocolate
Volume
500
200
100
800

B
FizzDrink
Volume
250
300
200
750

A
Chocolate
Volume
230
100
120
450

A
Chocolate
Revenue
2,000
600
350
2,950

B
FizzDrink
Revenue
1,000
700
250
1,950

A
Chocolate
Revenue
800
400
550
1,750

A
Chocolate
AvgEarningPerProduct
4.00
3.00
3.50
3.69

B
FizzDrink
AvgEarningPerProduct
4.00
2.33
1.25
2.60

A
Chocolate
AvgEarningPerProduct
3.48
4.00
4.58
3.89

How the resultset should look like>











Brand
Product
Volume
Revenue
AvgEarningPerProduct
Month

A
Chocolate
500
2,000
4.00
Jan

A
Chocolate
200
600
3.00
Feb

A
Chocolate
100
350
3.50
Mar

A
Chocolate
800
2,950
3.69
Total

A
Chocolate
230
800
3.48
Jan

A
Chocolate
100
400
4.00
Feb

A
Chocolate
120
550
4.58
Mar

A
Chocolate
450
1,750
3.89
Total

B
FizzDrink
250
1,000
4.00
Jan

B
FizzDrink
300
700
2.33
Feb

B
FizzDrink
200
250
1.25
Mar

B
FizzDrink
750
1,950
2.60
Total

View 1 Replies View Related

Cross Table

Oct 5, 2006

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 .

View 3 Replies View Related

Cross Tab Query

Aug 7, 2006

Is there any simple cross tab query template, which can used by general user?

I need it so that I can provide it to our Power users, so that they can use it themselves, instead of coming back to the IT Department.

(something similar like the one found in the Access)

Tomal

View 6 Replies View Related

Leap Year Dates Causing Error When You Pull Dates For Non Leap Year

Feb 28, 2008



I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error


Here is what I'm trying to generate






InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev

ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65


ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
NULL
NULL



Here is the error that I'm getting:



Code Snippet

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(4834 row(s) affected)



Here is my Transact-SQL Syntax (summarized because I Couldn't post it):

SELECT

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'

AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'

THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT Trans1.TR_Amount

FROM dbo.Trans Trans1

WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)

AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)

AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,


--AA Script Here AS AARmRev,

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)

= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT SUM(Trans1.TR_Amount)

FROM dbo.Trans Trans1

WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND

trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))

+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND

trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev



FROM dbo.EntityDef INNER JOIN

dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN

dbo.CurrentDate INNER JOIN

dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate

WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')

ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate


I appreciate all your help

Thanks

View 13 Replies View Related







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