Missing dll file: zero

Newbie SQL Question.

I need to build a results grid that reports totals or zero for an attribute

SELECT ClaimType, VendorNumber,
COUNT (ClaimNumber) AS ClaimCount
FROM ClaimHeader
GROUP BY VendorNumber, ClaimType
ORDER BY VendorNumber ASC;

THe underlying data has two claim types (UB, and HCFA). Some vendor numbers have only UB claims or only HCFA claims. I want my totals to show a zero for those vendor numbers. Example

VendorNumber ClaimType ClaimCount
234 UB 21
234 HCFA 12
235 UB 0
235 HCFA 15

My current group by doesn't give me the 3rd row in my example. How do I change my query to add those rows that have no claims as a zero. Put another way, every vendorNumber has to report a HCFA total and a UB total, even if they're zero.

Thanks.This is flat-out "brute force" to get the job done using only what you've given us... There is almost certainly a better way.SELECT v.VendorNumber, c.ClaimType, (SELECT Coalesce(Count(*), 0)
FROM ClaimHeader AS ch
WHERE ch.VendorNumber = v.VendorNumber
AND ch.ClaimType = c.ClaimType) AS ClaimCount
FROM (SELECT DISTINCT VendorNumber
FROM ClaimHeader) AS v
CROSS JOIN (SELECT DISTINCT ClaimType
FROM ClaimHeader) AS c-PatP|||Is something else I could provide to help with my question? This might b closer but i'm still missing the zeros for providers with not HCFA or UB claims.

SELECT t.PeriodID, p.ProviderName, p.AltProviderID_1,
t.FormTypeID,
ClaimCount = CASE WHEN SUM(ClaimCount) IS NULL THEN 0 ELSE SUM(ClaimCount) END

FROM dvTATClaim t
INNER JOIN dimProvider p
ON t.ProviderID = p.ProviderID|||Sure...provide the DDL of the table

CREATE TABLE myTable...

Some Sample Data

INSERT INTO myTable(
SELECT 'data' UNION ALL
SELECT 'data' UNION ALL
ect

And what the results should look like...

You could probably use a stop watch to time how long it takes after you post that for your answer...|||Are there other tables that have:
1. Every possible VendorNumber
2. Every possible ClaimType

Also, did the previous query I provided give you the results that you want?

-PatP

Hello all,

I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.

Thanks,

MarkoTry it with VARCHAR , which accepts numbers and string

Jack
___________________________________
Post IT Jobs for FREE - www.ciquery.com
Over 14,000 registered IT focused individuals

"Marko" <nortel@.planet.nlwrote in message
news:1166012891.128359.186250@.16g2000cwy.googlegro ups.com...

Quote:

Originally Posted by

Hello all,
>
I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.
>
Thanks,
>
Marko
>

|||Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

Cell? Do you mean column value in a row? Or are you running a query
against Excel?

What exact query do you run?

All I can say is that the leading zero should normally not be dropped
for a character value, which this obviously is, there is a space in it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schreef:

Quote:

Originally Posted by

Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

>
Cell? Do you mean column value in a row? Or are you running a query
against Excel?
>
What exact query do you run?
>
All I can say is that the leading zero should normally not be dropped
for a character value, which this obviously is, there is a space in it.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>

Hello Erland,

It's a column value in a row, the query I run:

SELECT @.Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <= convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >= convert(CHAR(8),GETDATE(),108) AND

groupName = 'Application' AND shortname = 'Helpdesk' AND deleted =0
)

if (@.Registernum is null) Select @.Registernum=0
select @.Registernum
GO

What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).|||Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

It's a column value in a row, the query I run:
>
SELECT @.Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) =
convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <=
convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >=
convert(CHAR(8),GETDATE(),108) AND
>
groupName = 'Application' AND shortname = 'Helpdesk' AND
deleted =0
)
>
if (@.Registernum is null) Select @.Registernum=0
select @.Registernum
GO
>
>
What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).

You failed to include the declaration of @.Registernum. I would guess
you have declared it as integer or decimal.

Also, write conditions like

WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)

WHERE date >= convert(char(8), getdate(), 112)
AND date < convert(char(8), dateadd(DAY, getdate() + 1), 112)

This can be essential for performance. If the date column is indexed,
the index is no of use if you put the column into an expression.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for
SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schreef:

Quote:

Originally Posted by

Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

It's a column value in a row, the query I run:

SELECT @.Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) =
convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <=
convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >=
convert(CHAR(8),GETDATE(),108) AND

groupName = 'Application' AND shortname = 'Helpdesk' AND
deleted =0
)

if (@.Registernum is null) Select @.Registernum=0
select @.Registernum
GO

What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).

>
You failed to include the declaration of @.Registernum. I would guess
you have declared it as integer or decimal.
>
Also, write conditions like
>
WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)
>
as
>
WHERE date >= convert(char(8), getdate(), 112)
AND date < convert(char(8), dateadd(DAY, getdate() + 1), 112)
>
This can be essential for performance. If the date column is indexed,
the index is no of use if you put the column into an expression.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
>

Hello Erland,

I put the DECLARE above the Query:

DECLARE @.Registernum integer

but no results, I got still the value without the zero (0).
The Data Type of the Column is CHAR, is this maybe wrong?|||Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

I put the DECLARE above the Query:
>
DECLARE @.Registernum integer
>
but no results, I got still the value without the zero (0).
The Data Type of the Column is CHAR, is this maybe wrong?

Your result set consists of a single integer variable. QA presents integer
values without leading zeroes. How could it now that the integer value
comes from a char column?

If you want to see a leading zero, you should declare the variable as
char or varchar of suitable length.

Monday, March 19, 2012

Missing Value Grouping

Monday, March 12, 2012

Missing the zero (0)

Missing dll file

Blog Archive

About Me