Testing For Null In Crosstab

Jan 27, 2005

I'm sure that I'm being stupid but this is driving me nuts. The case statements in the following query always return the ELSE and never return the THEN for the NULL.

Code:

select day(ums_users.created), count(*) as Total,
SUM(CASE ums_user_info.info_key WHEN NULL THEN 1 ELSE 0 END) AS UnReg,
SUM(CASE ums_user_info.info_key WHEN NULL THEN 0 ELSE 1 END) AS Reg
from ums_users
left join ums_user_map
on ums_users.user_id = ums_user_map.user_id
left join ums_groups
on ums_user_map.group_id = ums_groups.group_id
left join ums_user_info
on ums_user_info.user_id = ums_users.user_id

where (ums_groups.group_id = '43A73FAE-0C2C-4ED8-BE1E-B32C12DB163D'
or ums_groups.group_parent_id = '43A73FAE-0C2C-4ED8-BE1E-B32C12DB163D')
AND year(ums_users.created) = '2005' and month(ums_users.created) = '01'
GROUP BY day(ums_users.created)
ORDER BY day(ums_users.created)


I'd be really grateful if somebody could explain what I'm doing wrong.

TIA

Nick

View 7 Replies


ADVERTISEMENT

Testing If Value Is Null

Mar 31, 2008

Hi,How do I write a query where if a column, "value," is NULL I returnthe phrase "No value entered" but otherwise return the column's value,even if it is the empty string? I'm tried to modify this simple querySELECT value FROM meta_dataThanks, - Dave

View 2 Replies View Related

Testing For Null Dataset

Dec 11, 2007



Hi All,

I need to find out how I can detect a NULL dataset at runtime. I have just released my first report using SQLServer Reporting services and all went well until I had an empty dataset on the server. There was no problem ever on my development system, running under Visual Studio 2005 Pro, even if I got no data back. Shortly after release to the server though I found that the report reported javascript errors. After a lot of searching I nailed it down to my code that alternated the background color of the rows in the report. The code looks like this:

=iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")

I had this line in the BackGround property for my table row. It works great, unless DataRecords is Null and RowNumber is 0. I ended up taking the expression out, because I could not find a way to test if DataRecords was null or RowNumber was 0, without getting a compiler error.

Can anyone here tell me what I need to do so I can put that expression back in and alternate the color of my data rows again? I get the data using a stored procedure, if that matters.

TIA
URW

View 2 Replies View Related

Testing Null Values In SqlDataReader Row

Jun 3, 2008

Hi gang,
When reading a record from my SQL database for display on a web form, I am checking for null before I assign the value to a TextBox on the form. Here is an example:
 // normal database setup code hereSqlDataReader rdr = cmd.ExecuteReader();if (!Convert.IsDBNull(rdr["FirstName"])){    txtFirstName.Text = (string)rdr["FirstName"];}// code here for other record fields
Is this the correct way to do this? Is there a simpler way to do this? I am writing a bunch of database code and I want to do it right...
Thanks,Kevin

View 5 Replies View Related

Null Testing In Select Query

May 9, 2007

I am obviously a newbie to SQL Server but have been using Access for years and I have come up against what appears to be an age old issue concerning nulls and specifically the functions to test for them etc.

Below is the Access Query I am having difficulty with...
----------------------------------------------------------
SELECT DISTINCT Max(tbl_ScheduleDate.SchDate) AS CurrentDate, ([SchDate],"mmmm yyyy") AS MonthYear, tbl_Schedule.Area, tbl_Schedule.CP, IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded") AS Loaded

FROM (tbl_Schedule INNER JOIN tbl_ScheduleDate ON tbl_Schedule.MaxMinID = tbl_ScheduleDate.MaxMinID) LEFT JOIN tbl_tmp_CP ON tbl_Schedule.CP = tbl_tmp_CP.CP

GROUP BY Format([SchDate],"mmmm yyyy"), tbl_Schedule.Area, tbl_Schedule.CP, IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded"), tbl_ScheduleDate.SchDate, tbl_tmp_CP.CPID;
-------------------------------------------------------------

Ignore the Format() function as I know this is a problem and have a fix.

The problem is this field...
IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded") AS Loaded

I am testing to see if data is loaded into table 'tbl_tmp_CP' that is related to 'tbl_Schedule' by checking if there is a null value or not and then adding the right response string value to a column called Loaded.

Can anyone suggest a way for me to do the above?

View 2 Replies View Related

Access Crosstab -&> SQL Crosstab

Jan 9, 2006

Hi all
I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.

Thanks.

TRANSFORM Min(tCompany.cCompanyName) AS CompanyName
SELECT tProjContacts.ProjectID
FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID
WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*"))
GROUP BY tProjContacts.ProjectID
PIVOT tCompanyType.CoType;

View 1 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off


go

declare

@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)

AND DR.[ID]= @ID

)

go

set ansi_nulls on

View 3 Replies View Related

DTS & Crosstab

Apr 19, 2000

I am a neewbie to SQL 7 and having previously used Access for some time.. I have a table with a date field in a want to create a table with the values filling columns in another table by month ie April,may ,Jun and so on. Ia m using the Month function to get the month number but I dont know how to crosstab this into the relevant columns in the new table...I thinks DTS is the way and do a transformation on the month field but am struglling at the mo...Any help greatfully appreciated...

Regards

Andrew Wall

View 2 Replies View Related

Please Help Me In Crosstab

Jun 18, 2007

Hi i am new to crosstab feature.i want a crosstab dynamically with this below table please help me .Thanks in advance.

Table :-

idnamecosttypemonth&yearamount

1a1Bcost9/2005300
2a1Ecost9/2005200
3a1Acost9/2005100
4a2Bcost10/2005150
5a2ECost10/2005100
6a2Acost10/2005150
7a3Bcost11/2005150
8a3ECost11/2005100
9a3Acost11/2005150



I want result table like this :-

ID Name Cost_Type 9/2005 10/2005 11/2005

1 a1Bcost 300 -
2 a1Ecost 200 -
3 a1Acost 100 -
4 a2Bcost -150
5 a2ECost - 100
6 a2Acost - 150
7 a3Bcost - -150
8 a3ECost - -100
9 a3Acost - -150


I want above result Please Give me your help.

Thanks in advance,
Rajeev

View 1 Replies View Related

CrossTab

Aug 23, 2005

Hi Guys!Is there anything like cross tab of access in sql server?Thanks.

View 1 Replies View Related

Crosstab Help

Jul 13, 2007

Hello,



I need to accomplish the turning column data into row data via SQL. I can sorta get what I want with creating the report as a matrix report. However, I always seem to need one little thing to happen to shape my data as I need it. Anyway, here goes...



I have a table with 4 columns



UNIQUE | Code | FieldID | CustomField



The Code column is the customer code. FieldID numbers 1-100 and CustomField has string data.



My problem. I need to be able to choose ALL Customers [Code] where FieldID/s ="6", "7" & "8" and the CustomField rowdata corresponding to the FieldID data. BUT I then need to be able to use a daterange parameter on any row with a FieldID of "6" but that is not a datetime format it is in a string format.



Currently my SQL is:



SELECT
[Unique ID], Code, [Field ID] AS IUdate, [Field ID] AS IUNote, [Field ID] AS IUReq, [Custom Field]

FROM
dbo.[Customer Custom Field]

WHERE
(Code = '07-8111')AND ([Field ID] = 6) OR (Code = '07-8111')AND ([Field ID] = 7) OR (Code =

'07-8111')AND ([Field ID] = 8)



However, everytime I try to run a daterange against, I get all kinds of data I don't need because the parameter is running against all the fields and NOT just the stringdate.



My idea is to convert the column data into row data (like a crosstab query) to sharpen it up for parameter ranges.



Is there something i am missing here? Can someone point me in the right direction?



Thanks in advance!



phorest

View 6 Replies View Related

Crosstab Query

Mar 14, 2002

Hello everyone,

I have a tabel which looks like the follows:

CODE Type Number
1 account 20
1 empl 3
2 account 15
2 empl 6
3 account 32
3 empl 7

I need to show the results like

CODE Account Empl
1 20 3
2 15 6
3 32 7

I've tried different attempts but so far I always get 2 rows for each code,where one column shows a NULL value.
How do I have to do this ?

View 1 Replies View Related

Crosstab Query

Apr 5, 2001

Is there a way to write Crosstab query in SQL 7.0.

I have a Table which has partner, usernames and the city they are from, apart from other fields. I have another table which tracks the user activity on a day by number of visits to the web site. What i want is like this :-

Date Total Visits Atlanta Connecticut .... .... ....

03/01/2001 5025 567 324 .... .... ....
03/02/2001 6789 423 146 .... .... ....
... ... ... ... .... .... ....

I.E. I wan't the city names to appear as columns. The main catch here is that this data is for a given partner. So the city names could be different for different users from different partners. i.e. city names have to be generated on the fly by looking at the partner table and then by getting all its users and seeing what all are the cities involved.

Any help would be appreciated.

Thanks
Sumit.

View 1 Replies View Related

Crosstab Reports

Jun 4, 2001

I am trying to run a report off a crosstab query. The report calls for columns A - F, which are returned from the crosstab query. The only problem is that at times some of the columns are not returned by the query because there is no data associated with them. The report asks for column X, and no such columns exists in the query result; thus, an error is generated. My question is: how do I deal with this problem? I would like to avoid having to build the report from scratch. Is there some way that at runtime I could programatically tell the report to ignore these fields?

View 2 Replies View Related

Crosstab Problem

Nov 10, 2005

I have a table called Ideas and a table called Users_Ideas. In Users_Ideas, there can be up to four rows referencing Ideas with a foriegn key. I need to select all rows from Ideas and for each Idea display the users associated with it.

View 8 Replies View Related

Yet Another Crosstab Problem

Nov 14, 2005

Hi all,
i see there are many posts on crosstab queries in this forum but i can't seem to find a solution to my problem. hope you can help me.

I have a view of server crashes:

[dirty shutdown] [previous clean] [server name]
2005-10-01 2005-09-01 srv1
2005-11-01 2005-10-10 srv2

which displays the date of a dirty shutdown of a server, and the date of the previous clean shutdown of this server plus the server name

Also, i have a view of alerts per server:

[alert id] [server name] [alert date] [alert name] [repeat count]
123 srv1 2005-09-05 an alert 0
124 srv1 2005-09-10 another alert 1
125 srv1 2005-09-20 an alert 0
126 srv2 2005-10-20 something else 0

If [repeat count] is 0, the alert was given once, if it is 1, there were 2 alerts etc.

Now, what i want (well not me but the guy i work for) is a view that displays all alerts between the clean and the dirty shutdown, per server:

[dirty] [clean] server total "an alert" "another alert" "something else"

2005-10-01 2005-09-01 srv1 4 2 2 0
2005-11-01 2005-10-10 srv2 1 0 0 1

Total is the total number of alerts for the server between the two dates. Of course, there are many servers, and the number of alert names varies over time which means i can't use "case when...".

Is this possible??? Would be extremely thankful for any help!

Regards,
Elisabet

View 1 Replies View Related

CrossTab Query

Mar 8, 2006

Is there a SQL version of a Crosstab Query??

View 14 Replies View Related

No Crosstab Like Access?

Mar 30, 2006

I'm trying to port a dymanically renderred form from a .mdb to a .adp. In the Access .mdb. The form is a representation of a crosstab query with unknown column headings (hence, the need for the form to be dynamically rendered at runtime).

I tried to port the crosstab from the .mdb to a stored procedure, but SQL Server doesn't like TRANSFORM, and perhaps the PIVOT as well. How do you do this in sql server?:

TRANSFORM Count(d.CAR_INIT) AS CountOfCAR_INIT
SELECT b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
FROM ((TSA_HS_MPCT_CNT a INNER JOIN TSA_HS_COMB2 b ON a.RECC_COMB_ID = b.COMB_ID)
INNER JOIN TSA_HS_WKLD c ON b.WKLD_ID = c.WKLD_ID) INNER JOIN TSA_HS_OBJ_TRN d ON c.WKLD_ID = d.WKLD_ID
GROUP BY b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
PIVOT d.LST_HMP_DTM + d.OBJ_DEP_TRN

Thanks,
Carl

View 1 Replies View Related

Crosstab Problem Help

Sep 20, 2006

Hi,
I have tried with the solutions available in the links provided by Pootle,but still I think I have to go a long way.My main problem is with the second aggregate column of previous year.
Let me put it again....


I have a table policy :

CREATE TABLE Policy(
Policyno VARCHAR(20)
,Inceptiondate DATETIME
,agentid VARCHAR(20))



I want a report like this (dates in DD/MM/YYYY)

date 32001 32002 32003 32004 2006 2005
01/08/2006 3 1 11 1 16 12
02/08/2006 1 1 1 2 5 22
03/08/2006 1 1 1 1 4 3



Now the problem is I can get a report using crosstab like this in crystal report

| agentid
-----------------
date | sum(policyno)
-----------------

which is giving a result like this

date 32001 32002 32003 32004 2006/ToTal
01/08/2006 3 1 11 1 16
02/08/2006 1 1 1 2 5
03/08/2006 1 1 1 1 4
Total 4 3 13 4 25


But I can't bring the previous year aggregate column ,i.e 2005 in the above case.

So can you suggest me a way or direction to acheive that?

Any help would be really appreciated.
BTW,Pootle gave these links in my previous post...
link1 (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817)
Link2 (http://www.sqlteam.com/item.asp?ItemID=2955)

View 3 Replies View Related

Crosstab Select!!

Apr 3, 2008

Hi
I have table called MyTable that has 3 columns (City , Brand, Price)
I want to select from this table in Cross Tab format
i.e.
My table has
CityBrandPrice
LondonDELL1227
LondonToshiba1100
LondonAcer1007
LondonHP1467
LondonIBM1193
SydneyToshiba2100
SydneyAcer2219
SydneyApple2589
SydneyVAIO2122
SydneyHP1929
SydneyIBM2877
TokyoToshiba7200
TokyoAcer5299
TokyoCOMPAQ9200
TokyoIBM8779
TokyoHP6286
ParisDELL1670
ParisApple1825
ParisVAIO1267
ParisHP1882
ParisCOMPAQ1636
ParisIBM1332
NewYorkDELL2000
NewYorkToshiba1288
NewYorkAcer2333
NewYorkApple2299
NewYorkVAIO2327

__________________________________

i want the select statment result to be like this
DELLToshibaAcerHPIBMAppleVAIOCOMPAQ
London12271100100714671193N/AN/AN/A
SydneyN/A2100221919292877N/AN/AN/A
TokyoN/A7200529962868779N/AN/A9200
Paris1670N/AN/A18821332182512671636
NewYork200012882333N/AN/A22992327N/A


City & Brand can be anything so i will not be able to hard code them in my Select.

who can help with that?!

View 2 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 3 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

CrossTab Query

Sep 17, 2007

Hi,

Currently working on a Attendance System project .
Iam storing the data in table against the employee code, date and status (basically a rowwise data).

Would require a query which can generate a cross-tab display with Employee Codes (on X axis), Dates (on Y-Axis) and with the Attendance Status.

The sample data for the same is as follows:

EmpCode Att_Date Att_Status
------- -------- ----------
001 01/01/2007 P
001 01/02/2007 A
.. ... ..
001 01/31/2007 P

002 01/01/2007 P
. . .
. . .
. . .

Would require the output as..

EmpCode 01/01/2007 01/02/2007 ...... 01/31/2007
------- ---------- ---------- ----------
001 P A P
002 A P P
. . . .
. . . .
. . . .

Thanking you in anticipation.

Jabez.

View 10 Replies View Related

Crosstab Query

Jul 23, 2005

Can someone show me how to write a query to convert this:Year, Account, Qtr1, Qtr2, Qtr3, Qtr42004, 12345, 100, 200, 300, 4002005, 23456, 200, 300, 400, 500to this:Year, Account, Quarters, Amount2004, 12345, Qtr1, 1002004, 12345, Qtr2, 2002004, 12345, Qtr3, 3002004, 12345, Qtr4, 4002005, 23456, Qtr1, 2002005, 23456, Qtr2, 3002005, 23456, Qtr3, 4002005, 23456, Qtr4, 500So far I've only found examples which convert values to columns but notfor columns to values. TIA... AL

View 2 Replies View Related

Crosstab Queries

Jul 23, 2005

Dear friends,I wonder if exist a way to make crosstab queries in SQL Serverlike those in Access without "external" programming. Does theSQL Server supports the "TRANSFORM" SQL-extension?Thanks in advance, Sotiris.

View 1 Replies View Related

Crosstab Query

Sep 7, 2005

I have two tables Bill and Location.Bill(location_id int,prod_period datetime,consumption float,demand float)Location(location_id int,location_name varchar(45))I want to create a stored procedure that takes a parameter of @year. Ibasically want the procedure to return results that show locationswhere consumption and/or demand IS NULL or 0 for each month throughouta given year. I would like my results to look something like this:location_id year Jan Feb Mar Apr May Jun Jul Aug Sept OctNov Dec1 2005 0 0 02 2005 0 0 0 03 2005 0 0If data does exist for consumption or demand, I would like to show it,however I am really interested in the locations that have no dataassociated with them.Any ideas of how I can accomplish this?

View 1 Replies View Related

Normalizing A Crosstab

Aug 24, 2006

I re-designed a predecessor's database so that it is more properlynormalized. Now, I must migrate the data from the legacy system intothe new one. The problem is that one of the tables is a CROSSTABTABLE. Yes, the actual table is laid out in a cross-tabular fashion.What is a good approach for moving that data into normalized tables?This is the original table:CREATE TABLE [dbo].[Sensitivities]([Lab ID#] [int] NULL,[Organism name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[Source] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[BACITRACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CEPHALOTHIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CHLORAMPHENICOL] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CLINDAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ERYTHROMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SULFISOXAZOLE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[NEOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OXACILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TETRACYCLINE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[TOBRAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[VANCOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TRIMETHOPRIM] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CIPROFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[AMIKACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AMPICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CARBENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CEFTAZIDIME] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GENTAMICIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[POLYMYXIN B] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MOXIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[GATIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SENSI NOTE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]

View 5 Replies View Related

Crosstab. 2 Totals ?

Dec 29, 2006

Hi,Am using robvolks crosstab-procedure to generate a crosstab query.I get this result:Total A B Cjuli 455 1 107 347okt 83 1 9 73aug 612 1 113 498juni 451 1 108 342So I get a total for each month. But I would also like a total of eachletterTotal A B Cjuli 455 1 107 347okt 83 1 9 73aug 612 1 113 498juni 451 1 108 342Total 1601 4 337 1260Is that possible?/jim---call to procedureexecute crosstab 'select DATENAME(month,(theDate)) as '' '', count(*) as'MonthsTotal'' from tblData group byDATENAME(month,(theDate))','count(letter)','letter ','tblData'-----------Robvolks procedure----CREATE PROCEDURE crosstab@select varchar(8000),@sumfunc varchar(100),@pivot varchar(100),@table varchar(100),@where varchar(1000)='1=1'ASDECLARE @sql varchar(8000), @delim varchar(1)SET NOCOUNT ONSET ANSI_WARNINGS OFFSET LANGUAGE DanishEXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE1=2')EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +' WHERE '+ @where + ' AND ' + @pivot + ' Is Not Null')SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date',data_type) )WHEN 0 THEN '' ELSE '''' ENDFROM tempdb.information_schema.columnsWHERE table_name='##pivot' AND column_name='pivot'SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM##pivotDROP TABLE ##pivotSELECT @sql=left(@sql, len(@sql)-1)SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql+ ' ')EXEC (@select)SET ANSI_WARNINGS ONGO

View 3 Replies View Related

Crosstab Queries

Jan 1, 2008

The view looks like this.


TableName Caption Value


Master First Hello

Master Second World

Master Third ValueOfSomeKind
Master First NextRowHello
Master Second NextRowWorld
Master Third NextRowValueOfSomeKind

The result has to look like this.

First Second Third .... so on
Hello World ValueOfSomeKind .... if needed
NextRowHello NextRowWorld NextRowValueOfSomeKind ....


The amount of caption values are unknown at compile time.

View 4 Replies View Related

Need Help With Crosstab Query

Mar 10, 2008

I cant figure out what is going wrong here. I created this query to be able to show values across periods , and put it inside of a table instead of a matrix, for easier totals and subtotals. but for some reason my subquery is not pullin up the right amounts for "Goal Amount". The fields i have labeled as Period_1 , period_2 and so on work just fine. But the goals are not coming up with the same number as in the Sub query, if i ran it by its self. I dont know if maybe its the join, or what. but for some reason the numbers are way high then they should be. am i doing something wrong. Please let me know if i need to give more information.

This is what the output looks like now:
0.0000 0.0000 0.0000
4516182.0000 5569957.8000 2559169.8000
0.0000 0.0000 0.0000
0.0000 0.0000 0.0000
0.0000 0.0000 0.0000
3915126.9500 3275922.5500 1598011.0000
4526861.2500 6880829.1000 3078265.6500
629010.4000 589697.2500 353818.3500
2218175.9000 1984683.7000 1517699.3000
0.0000 0.0000 0.0000
0.0000 0.0000 0.0000
0.0000 0.0000 0.0000

this is what it looks like in the subquery:
150539.4000 2008 1 80 12
150539.4000 2008 2 80 12
150539.4000 2008 3 80 12
39313.1500 2008 1 81 12
39313.1500 2008 2 81 12
39313.1500 2008 3 81 12
181074.4500 2008 1 82 12
181074.4500 2008 2 82 12
181074.4500 2008 3 82 12
116746.1000 2008 1 86 12
116746.1000 2008 2 86 12
116746.1000 2008 3 86 12
79900.5500 2008 1 87 12
79900.5500 2008 2 87 12
79900.5500 2008 3 87 12
the two numbers highlighted should have the same amounts.





Code Snippet








SELECT cusSales.Report_Level_Id AS Report_Level, cusSales.Customer_Sales_Summary_Year AS Year, Goals.Region_Key,
Qry_Report_Level_Brand.Report_Level_Description, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Name,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_1,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_10,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN Goal_Amount ELSE 0 END) AS GOAL_1,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN Goal_Amount ELSE 0 END) AS GOAL_2,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN Goal_Amount ELSE 0 END) AS GOAL_3,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN Goal_Amount ELSE 0 END) AS GOAL_4,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN Goal_Amount ELSE 0 END) AS GOAL_5,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN Goal_Amount ELSE 0 END) AS GOAL_6,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN Goal_Amount ELSE 0 END) AS GOAL_7,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN Goal_Amount ELSE 0 END) AS GOAL_8,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN Goal_Amount ELSE 0 END) AS GOAL_9,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN Goal_Amount ELSE 0 END) AS GOAL_10,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN Goal_Amount ELSE 0 END) AS GOAL_11,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN Goal_Amount ELSE 0 END) AS GOAL_12,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN Goal_Amount ELSE 0 END) AS GOAL_13
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales INNER JOIN
Qry_Report_Level_Brand ON cusSales.Sub_Brand_Id = Qry_Report_Level_Brand.Sub_Brand_Id ON
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer.Customer_Code = cusSales.Customer_Code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension ON
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer.Territory_Code = RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code FULL
OUTER JOIN
(SELECT SUM(cusSales.Customer_Sales_Summary_Amount) AS Goal_Amount, cusSales.Customer_Sales_Summary_Year AS Year,
cusSales.Customer_Sales_Summary_Period AS Period, cusSales.Report_Level_Id,
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Region_Key
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer AS Tbl_Territory_In_Customer_1 ON
cusSales.Customer_Code = Tbl_Territory_In_Customer_1.Customer_Code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region ON
Tbl_Territory_In_Customer_1.Territory_Code = RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Territory_Code
WHERE (cusSales.Customer_Sales_Summary_Year = 2008) AND (cusSales.Consolidated_Sales_Tables_Id = 8) and region_key=12
GROUP BY cusSales.Customer_Sales_Summary_Year, cusSales.Customer_Sales_Summary_Period, cusSales.Report_Level_Id,
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Region_Key) AS Goals ON
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Key = Goals.Region_Key AND
cusSales.Report_Level_Id = Goals.Report_Level_Id AND cusSales.Customer_Sales_Summary_Year = Goals.Year AND
cusSales.Customer_Sales_Summary_Period = Goals.Period
WHERE (cusSales.Customer_Sales_Summary_Year IN (2007, 2008)) AND (cusSales.Consolidated_Sales_Tables_Id = 6) AND
(NOT (cusSales.Bill_Customer_Code IN ('11825', '11990', '11971'))) AND (cusSales.Report_Level_Id IN (78, 80, 81, 82, 86, 87)) AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'BREEDER') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'OTHER') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'Vet - Corporate') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'export') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'Vet - Other') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Key = 12)
GROUP BY cusSales.Report_Level_Id, cusSales.Customer_Sales_Summary_Year, Goals.Region_Key, Qry_Report_Level_Brand.Report_Level_Description,
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Name

View 1 Replies View Related







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