IS it possible to insert a blank line in my report

Hello,

I my dataset is like following

sql:

select quarter, sum(amount) as amount from table1 group by quarter

then I get the dataset

field: quarter,amount

date: quarter1,100

quarter2,500

Is it possible to get my report from the dataset above.

my report:

quarter,amount

quarter1,100

quarter2,500

quarter3,0

quarter4,0

thanks!

This is possible if you have a time (or quarter) table in your database:

SELECT

q.quarter,

ISNULL(t1.amount,0) as amount

FROM quarters q

LEFT JOIN table1 t1

ON q.quarter = t1.quarter

|||

thank you for reply

I can't get the tabe quarters ...

|||

Use this SQL:

SELECT

q.quarter,

ISNULL(t1.amount,0) as amount

FROM quarters q LEFT JOIN

(SELECT 'quarter1' AS quarter UNION SELECT 'quarter2' AS quarter UNION SELECT 'quarter3' AS quarter UNION SELECT 'quarter4' AS quarter) AS qrt

ON quarters.quarter = qrt.quarter

This will work.

Please mark the post as answer.

Shyam

|||

In order to more easily manage date groupings (weeks, months, quarters, years, etc.) it is a VERY good idea to have a Calendar table in your database. Having a Calendar table makes tasks such as this one very simple.

Here is additional information about creating and using a Calendar table.

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

is it possible to do this without cursors?

i have the following vb code that i want to turn into a stored procedure.
Can it be done without using cursors? thanks for any help!
what this code does is it says for each item, which other items reference it
in the column called source.
Set rst = CurrentDb.OpenRecordset("SELECT [Name], [Type], [ReferencedBy]
FROM [Catalog]")
If Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
objName = rst![Name]
objRefs = ""
Set findrst = CurrentDb.OpenRecordset("SELECT DISTINCT [Name],
[Type] FROM [Catalog] WHERE [Name] <> '" & objName & "' AND [Source] LIKE '*"
+ objName + "*';")
If Not findrst.EOF Then
findrst.MoveFirst
Do While Not findrst.EOF
objName = findrst![Name]
objType = findrst![Type]
objRefs = IIf(Len(objRefs) > 0, objRefs & ", " & objName
& " (" & objType & ")", objName & " (" & objType & ")")
findrst.MoveNext
Loop
End If
rst.Edit
rst![ReferencedBy] = objRefs
rst.Update
rst.MoveNext
Loop
End IfBen,
Please post the table DDL and sample data and desired results.
HTH
Jerry
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:97650560-E7C1-40A4-B7FA-0443DC0FB20F@.microsoft.com...
>i have the following vb code that i want to turn into a stored procedure.
> Can it be done without using cursors? thanks for any help!
> what this code does is it says for each item, which other items reference
> it
> in the column called source.
>
> Set rst = CurrentDb.OpenRecordset("SELECT [Name], [Type],
> [ReferencedBy]
> FROM [Catalog]")
> If Not rst.EOF Then
> rst.MoveFirst
> Do While Not rst.EOF
> objName = rst![Name]
> objRefs = ""
> Set findrst = CurrentDb.OpenRecordset("SELECT DISTINCT [Name],
> [Type] FROM [Catalog] WHERE [Name] <> '" & objName & "' AND [Source] LIKE
> '*"
> + objName + "*';")
> If Not findrst.EOF Then
> findrst.MoveFirst
> Do While Not findrst.EOF
> objName = findrst![Name]
> objType = findrst![Type]
> objRefs = IIf(Len(objRefs) > 0, objRefs & ", " &
> objName
> & " (" & objType & ")", objName & " (" & objType & ")")
> findrst.MoveNext
> Loop
> End If
> rst.Edit
> rst![ReferencedBy] = objRefs
> rst.Update
> rst.MoveNext
> Loop
> End If|||create table catalog (name varchar(255), type varchar(50), source text,
referencedby text)
sample data before running the stored procedure
name type source referencedby
red hat mens red shirt
red shirt mens
after the stored procedure runs, i need the table to look like
name type source referencedby
red hat mens red shirt
red shirt mens red hat
the end result says that the red shirt is referenced in the source column by
the red hat.
thanks for any and all help!
"Jerry Spivey" wrote:

> Ben,
> Please post the table DDL and sample data and desired results.
> HTH
> Jerry
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:97650560-E7C1-40A4-B7FA-0443DC0FB20F@.microsoft.com...
>
>|||SELECT c.[name], c.[type], c.[source], r.[name] AS ReferencedBy
FROM [catalog] c
LEFT JOIN [catalog] r ON c.[name] = r.[source]
HTH,
John Scragg
"Ben" wrote:
> create table catalog (name varchar(255), type varchar(50), source text,
> referencedby text)
> sample data before running the stored procedure
> name type source referencedby
> red hat mens red shirt
> red shirt mens
> after the stored procedure runs, i need the table to look like
> name type source referencedby
> red hat mens red shirt
> red shirt mens red hat
>
> the end result says that the red shirt is referenced in the source column
by
> the red hat.
> thanks for any and all help!
>
> "Jerry Spivey" wrote:
>|||A couple tips.
I assume you are not using the "text" data type for your source column. If
so, why? It is a FK column and should have the same data type as the related
column (in this case [name]). You can enforce referential integrity even
with self referenceing table relationships. I would suggest you do that.
Also, try not to use keywords for column or table names and try not to put
spaces in your column names.
Best of luck,
John
"Ben" wrote:
> create table catalog (name varchar(255), type varchar(50), source text,
> referencedby text)
> sample data before running the stored procedure
> name type source referencedby
> red hat mens red shirt
> red shirt mens
> after the stored procedure runs, i need the table to look like
> name type source referencedby
> red hat mens red shirt
> red shirt mens red hat
>
> the end result says that the red shirt is referenced in the source column
by
> the red hat.
> thanks for any and all help!
>
> "Jerry Spivey" wrote:
>|||Thank you for the reply but unfortuanately that doesnt work (i dont think)
because the column source can have any number of items in it that it
references. i guess my sample wasnt clear enough, let me try again
the column name is a database object name
the column type is the type of database object (user table, stored procedure
)
the column source is the source code for the object (eg, the code/text of a
stored procedure)
the column referencedby contains a list of objects where the value in this
records name field can be found in all other records source column
i hope that is a little clearer. i know the vb code i posted earlier works
for this exact task, but i was hoping to have a stored procedure version as
well that didnt use cursors.
thanks for any help again.
ben

Is it possible to create an IIF function for SQL Server?

Hello!

I tried the following code:

create function dbo.iif
(
@.Expression bit,
@.TruePart sql_variant,
@.FalsePart sql_variant
)
returns sql_variant
as
begin
declare @.ReturnValue sql_variant

if @.Expression=1
begin
set @.ReturnValue=@.TruePart
end
else
begin
set @.ReturnValue=@.FalsePart
end

return @.ReturnValue
end

It works fine with statements like this:
select dbo.iif(1,'True','False')

However, when trying a "real" expression, an error appears:
select dbo.iif((1=0),'True','False')
Line 1: Incorrect syntax near '='.

How can I work around this?

Thank you very much in advance.define a variant, and set the value to be the expression. Use the variant in your iif function instead.|||would CASE serve the purpose?

Is it possible in single SQL statement

Does anyone know how should I write the sql for getting the following result?

Original Table like below.
----------
[WorkDay] [AgentCode]
06/12/01 3
06/12/02 2
06/12/02 3
06/12/03 2
06/12/03 3
----------

Curernt SQL:

When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row.

Example,
SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2
----------
[WorkDay] [AgentCode]
06/12/02 2
06/12/03 2
----------

I would like to know the agent is in the specified date.
The expected result like below.
----------
[WorkDay] [AgentCode]
06/12/01 NULL
06/12/02 2
06/12/03 2
----------

Please help its urgentwhy would the result have a row for 06/12/01 when you are telling it to get the rows for agentcode=2... it will discard all the rows having agentcode <> 2.
I think you need to use a self outer join to get the expected result.|||SELECT DISTINCT
Workday,
CASE WHEN AgentCode = 2 THEN
2
ELSE
NULL
END AS AgentCodeIfTwo
FROM OriginalTable|||or when your table is called justanotherday

SELECT DISTINCT
a.workday, b.agentcode
FROM
justanotherday a
LEFT JOIN
justanotherday b
ON
(a.workday = b.workday AND b.agentcode = 2)|||I suppose if you are trying to kill time and want to scan the table twice...|||:D

Well, that shouldn't be too hard... in five years are only 1825 date records... so...|||...assuming only 1 record per day, in which case why select DISTINCT? There is no way to tell how many records are in the table.|||no... i mean.. it's 'only' 1825 records against x records in 5 years... if x is not extremely large, no SQL interpreter should have problems with it in my opinion... or am i totally wrong here?|||The issue isn't that there would be a noticable difference in performance against small record sets. The issue is that the solution you proposed is sub-optimal, and might encourage someone to use the same method against larger databases. We try to propose (and frequently argue about) "best practices" on this forum. If you think that the algorithm you proposed is more efficient than Pootle's method, then take the opportunity to justify your opinion.|||ok... you are right about that... my solution is not really an optimal one :)

Is it not possible to use UPDATE within a function?

Is it not possible to use UPDATE within a function? I get the following err
or message for the following function:
Evan
- - - - - - - - - - - - - -
Server: Msg 443, Level 16, State 2, Procedure fn_GetNextDataVersion, Line 10
Invalid use of 'UPDATE' within a function.
CREATE FUNCTION dbo.fn_GetNextDataVersion(@.tb_pk int)
RETURNS bigint
AS
BEGIN
DECLARE @.nextDataVersion bigint
SET @.nextDataVersion = 1 + (SELECT DataVersion FROM tb_TableList WHERE tb_pk
= @.tb_pk)
UPDATE tb_TableList SET DataVersion = @.nextDataVersion WHERE tb_pk = @.tb_pk
RETURN @.nextDataVersion
ENDDDL statements inside user-defined functions are only allowed on table
variables local to the function.
http://msdn.microsoft.com/library/d...>
_08_460j.asp
Objects cannot be created, altered or dropped if they exist outside the
scope of the user-defined function.
ML
http://milambda.blogspot.com/|||Table variables IF a table is created in that function?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:B2FA2009-C30A-4DF4-B8CE-DC110EA8F77A@.microsoft.com...
> DDL statements inside user-defined functions are only allowed on table
> variables local to the function.
> http://msdn.microsoft.com/library/d...
es_08_460j.asp
> Objects cannot be created, altered or dropped if they exist outside the
> scope of the user-defined function.
>
> ML
> --
> http://milambda.blogspot.com/|||Yes. Locally. Variables are always local in SQL.
("This is a local shop. For local people." -- from 'A League of Gentlemen'
(BBC))
What is your goal exactly - there might be another way. If you share the
goal. :)
ML
http://milambda.blogspot.com/|||Create a function which gets a counter from a table and return the counter
WHILE updating the counter table to PLUS ONE
Evan
"ML" <ML@.discussions.microsoft.com> wrote in message
news:854D1DA1-3C1C-45B7-8A9B-E787B3856E55@.microsoft.com...
> Yes. Locally. Variables are always local in SQL.
>
> ("This is a local shop. For local people." -- from 'A League of Gentlemen'
> (BBC))
>
> What is your goal exactly - there might be another way. If you share the
> goal. :)
>
> ML
> --
> http://milambda.blogspot.com/|||for now i am working with a stored proc but a function is more elegant|||If you want to do it in a single step, then the procedure is the way to go.
ML
http://milambda.blogspot.com/

Is it a bug of SQL Server 2000 SP4?

Database backup file:
http://www.keepmyfile.com/download/c58b2a565144
Environment:
SQL Server 2000 SP4
Problem:
The following two statements returns different number of records:
Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
SELECT *
FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
WHERE flow_id = 102
This problem wasn't found in SQL Server 2000 original version and SQL Server
2005.
Any help is appreciated!Well, seems like a bug.
You can fix it by rearranginf the FROM clause in the function in the
following manner.
FROM action_room_req2 arr
JOIN flow_action fa ON arr.flow_id = fa.flow_id
JOIN cust_action ca ON ca.valid = 0 AND fa.action_id = ca.id
JOIN action_room ar ON ar.action_id = ca.id AND ar.code = 0
JOIN customer c ON ca.customer_id = c.id
LEFT JOIN turn_rule tr ON arr.req_type = 'Mall' and arr.ref_id = tr.id
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"hghua" <hghua@.discussions.microsoft.com> wrote in message
news:A9FB112B-AD45-463A-A167-CFAF77E20B5D@.microsoft.com...
> Database backup file:
> http://www.keepmyfile.com/download/c58b2a565144
> Environment:
> SQL Server 2000 SP4
> Problem:
> The following two statements returns different number of records:
> Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
> SELECT *
> FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
> WHERE flow_id = 102
> This problem wasn't found in SQL Server 2000 original version and SQL
> Server
> 2005.
> Any help is appreciated!|||Thanks a lot! That works!
Hope Microsoft will solve the problem.
"Roji. P. Thomas" wrote:

> Well, seems like a bug.
> You can fix it by rearranginf the FROM clause in the function in the
> following manner.
> FROM action_room_req2 arr
> JOIN flow_action fa ON arr.flow_id = fa.flow_id
> JOIN cust_action ca ON ca.valid = 0 AND fa.action_id = ca.id
> JOIN action_room ar ON ar.action_id = ca.id AND ar.code = 0
> JOIN customer c ON ca.customer_id = c.id
> LEFT JOIN turn_rule tr ON arr.req_type = 'Mall' and arr.ref_id = tr.id
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "hghua" <hghua@.discussions.microsoft.com> wrote in message
> news:A9FB112B-AD45-463A-A167-CFAF77E20B5D@.microsoft.com...
>
>

Is it a bug of SQL Server 2000 SP4?

Database backup file: 
http://www.keepmyfile.com/download/c58b2a565144

Environment:
SQL Server 2000 SP4

Problem:
The following two statements returns different number of records:
Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0 

SELECT *
FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
WHERE flow_id = 102

This problem wasn't found in SQL Server 2000 original version and SQL Server 
2005.
Any help is appreciated!

Is it possible to see text for dbo.OtherFee and GenPeriodical1?|||

Thanks for your reply!

I've got the answer from the newsgroup. The replyer said it seems a bug of SP4 and gave a work-around. If you are interested in this issue, you can download the backup file, it's just 1.18MB.

The store proc and function call other functions, thus not convenient to paste them here.

Is it a bug in SQL CE?

Hi!

I use SQL CE with VS.NET. I find the following bug 2th.

The table has an "ID int IDENTITY(0,1) PRIMARY KEY,". That is my row identity.

I add rows to the table, then I realized that the ID order not in the general order (from 0 to ........)

For example: 6,7,8,0,1,2,3,4,5.

Of course row 6,7 and 8 was added the very last.

The content of each row is not mixed, only the ID order.

Is it a very confused, because we develop mobile invoice programs for PDAs.

What I did wrong?

Thank you!

Does that happen in the Query Analyzer on the PDA?

Maybe it does not order the rows by the primary key column by default (?). I don't know if that would be a bug, although it sounds more convenient if it did order on any key columns.

|||I moved this thread to the SQL Mobile's team forusm|||

Neither SQL Server nor SQL Mobile guarrenty you about the physical order of the rows and you are not expected to concluded something from running multiple queries. It can always change. If you want the rows to be ordered on a column, you should ideally use ORDER BY. Your query result (with out ordering) always depends on the cursor position.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Is DEFAULT a constraint?

Hi,

I see the following in Books Online: CONSTRAINT--Is an optional keyword
indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN
KEY, or CHECK constraint definition...

But I have a table column defined as follows:

[MONTH] [decimal] (2, 0) NOT NULL CONSTRAINT
[DF__TBLNAME__MONTH__216361A7] DEFAULT (0)

My question: Is "DEFAULT" a constraint, or is it called something else?

Thanks,
EricEric Bragas (ericbragas@.yahoo.com) writes:

Quote:

Originally Posted by

I see the following in Books Online: CONSTRAINT--Is an optional keyword
indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN
KEY, or CHECK constraint definition...
>
But I have a table column defined as follows:
>
[MONTH] [decimal] (2, 0) NOT NULL CONSTRAINT
[DF__TBLNAME__MONTH__216361A7] DEFAULT (0)
>
My question: Is "DEFAULT" a constraint, or is it called something else?

There are actually two sorts of defaults: bound defaults and default
constraints. Bound defaults are deprecated, but are useful when you
bind them to types.

What you have above, is indeed a default constraints.

--
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|||Thank you for the clarification.

IS ASP.Net 1.x Working together with SQL 2005?

Hi

Should ASP.Net 1.x work togheter with sql 2005 without problems? I have try to open a web project but I got the following error:

Cannot open database "crm" requested by the login. The login failed. Login failed for user 'crmuser'.

This crmuser is "promoted" to owner of the crm base, but still I got the problem.

I upgrade this web project to ASP.Net 2.0 and I don't have the login problems, that's why I'm wondring.

Hope someone can answear me on this question. Thanks!

Jan

SQL Server 2005 does not care what version is your application all you need is correct permissions and the correct connection string for 1.1, there are two permissions in SQL Server both are covered in the thread below and look up connections string for 1.1 in the product docs. Hope this helps.

http://forums.asp.net/thread/1492092.aspx

|||You solve my problems, thanks!!|||

lsoljf:

You solve my problems, thanks!!

I am glad I could help.