Help on database choice?

Hi there,
I am currently writing an application for NNTP, the application requires
very FAST inserts, updates etc.
I started to write it with mySQL and I got good results, I was wondering if
anyone can confirm speed comparisions (and other comparisons) using MSDE
rather than mySQL.
As far as i know my app which runs on the desktop requires no special
licenses to use MSDE but mySQL does.
The application with only have 1 concurrent user and is being developed with
c# Visual Studio.net
Any help or information anybody can offer would be gratefully appreciated
Thanks in advance
Ian
Oh I forgot to mention, there is only 1 concurrent user but that user has
approximately 10 connections to the database. Maybe this will have an effect
on performance,.
THanks again
Ian
"Ian" <ianinmalaga@.hotmail.com> wrote in message
news:eLL8qdnZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I am currently writing an application for NNTP, the application requires
> very FAST inserts, updates etc.
> I started to write it with mySQL and I got good results, I was wondering
if
> anyone can confirm speed comparisions (and other comparisons) using MSDE
> rather than mySQL.
> As far as i know my app which runs on the desktop requires no special
> licenses to use MSDE but mySQL does.
> The application with only have 1 concurrent user and is being developed
with
> c# Visual Studio.net
> Any help or information anybody can offer would be gratefully appreciated
> Thanks in advance
> Ian
>
sql

help needed in writing a recursive function

Guys, I have a table with following data
Table Name :TblRecursive
Sample records:
ID, Name, ParentID
1, A, 0
2, B, 1
3, C, 2
4, D, 2
5, E, 1
Using the above data I just want to generate a result as below
A
A\B
A\B\C
A\B\D
A\E
Can you help in writing a query for this?
Regards
PradeepLook at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
In SQL 2005 you can use a common table expression with a recursive member.
ML
http://milambda.blogspot.com/|||http://vyaskn.tripod.com/hierarchie...r_databases.htm
Madhivanan|||Thanks for your response ML.
As I told earlier .. I want to generate the string as A\B and so on. But
your UDF gives me 3 columns.
Also I don't want to provide any input to the function also. I want it to
just start from the beginning and go on till the end. Any idea on how to
achieve this?
Regards
Pradeep
"ML" wrote:

> Look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> In SQL 2005 you can use a common table expression with a recursive member.
>
> ML
> --
> http://milambda.blogspot.com/|||Concatenating values into a single string really should be done on the
cilent, as it has no relational value - it's pure presentation.
There are a few ways to do it on the server if absolutely necesary:
declare @.result varchar(8000)
set @.result = N''
select @.result = <column> + '/' + @.result
from <function>
order by <level?>
ML
http://milambda.blogspot.com/|||Check whether this sample helps you!
--Table creation
Create table tblEmployeeInfo
(
EmpId int primary key,
EmpName varchar(30),
MgrId int
)
--Insert test data into it
Insert into tblEmployeeInfo values(1, 'Director', null)
Go
Insert into tblEmployeeInfo values(2, 'Joint Director', 1)
Go
Insert into tblEmployeeInfo values(3, 'Secretary', 2)
Go
Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3)
Go
Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1)
Go
-- UDF for your requirement
Create function GetEmpPath ( @.pEmpid int ) Returns varchar(8000)
As
Begin
Declare @.intMgrid int
Declare @.strEname varchar(500)
Declare @.strPath varchar(500)
Select @.intMgrid = mgrid, @.strEname = empname From tblEmployeeInfo where
EmpId = @.pEmpid
If (@.intMgrid is null)
Begin
Set @.strPath = @.strEname
End
Else
Set @.strPath = dbo.GetEmpPath(@.intMgrid) + '' + @.strEname
return @.strPath
End
Go
--Test the code
Select dbo.GetEmpPath(empid) as Hierarchy, empname as 'Employee Name' from
tblEmployeeInfo
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"SqlBeginner" wrote:
> Thanks for your response ML.
> As I told earlier .. I want to generate the string as A\B and so on. But
> your UDF gives me 3 columns.
> Also I don't want to provide any input to the function also. I want it to
> just start from the beginning and go on till the end. Any idea on how to
> achieve this?
> Regards
> Pradeep
>
> "ML" wrote:
>|||This is what I was looking for. Thanks a lot Vadivel. It really helped me.
Regards
Pradeep
"Vadivel" wrote:
> Check whether this sample helps you!
> --Table creation
> Create table tblEmployeeInfo
> (
> EmpId int primary key,
> EmpName varchar(30),
> MgrId int
> )
> --Insert test data into it
> Insert into tblEmployeeInfo values(1, 'Director', null)
> Go
> Insert into tblEmployeeInfo values(2, 'Joint Director', 1)
> Go
> Insert into tblEmployeeInfo values(3, 'Secretary', 2)
> Go
> Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3)
> Go
> Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1)
> Go
> -- UDF for your requirement
> Create function GetEmpPath ( @.pEmpid int ) Returns varchar(8000)
> As
> Begin
> Declare @.intMgrid int
> Declare @.strEname varchar(500)
> Declare @.strPath varchar(500)
> Select @.intMgrid = mgrid, @.strEname = empname From tblEmployeeInfo wher
e
> EmpId = @.pEmpid
> If (@.intMgrid is null)
> Begin
> Set @.strPath = @.strEname
> End
> Else
> Set @.strPath = dbo.GetEmpPath(@.intMgrid) + '' + @.strEname
> return @.strPath
> End
> Go
> --Test the code
> Select dbo.GetEmpPath(empid) as Hierarchy, empname as 'Employee Name' from
> tblEmployeeInfo
> Hope this helps!
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "SqlBeginner" wrote:
>|||Take into acount that in SQL 2000 the maximum recursion level is 32. And
quite frankly, recursion is not necesary here.
In my other post there's a solution that does not use a rexursive function.
Another example can also be found here:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
http://milambda.blogspot.com/

Help Needed in writing a function to help daily nightmare with Debugging.

I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is.
Once I have established which SP is the main problem I need to debug the line of code.

What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..

Now what i thought is to write an SP or Function where I pass :

SP name and Parameters that profiler genererates
and returns me Declare Statements and Set Statements with parameters filled.

EG

Profiler Returns
Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'

I would call my new SP =PopulateSPParams and cut and paste the profiler's string
PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'

this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.

DECLARE
@.CustomerID int,
@.CustomerName varchar(50),
@.CustomerSurname varchar(50),
@.CustomerAddress varchar(100)

SET @.CustomerID =1
SET @.CustomerName='JO'
SET @.CustomerSurname='BLOGG'
SET @.CustomerAddress='5 London Road'

Can you help in writing something that generates and populate parameters?

Check this out. It only handles three parms, but it certainly could be made to handle more:

create procedure buildParms
(
@.procName nvarchar(128),
@.parm1 varchar(max),
@.parm2 varchar(max),
@.parm3 varchar(max)
) as
begin
set nocount on

select 'DECLARE @.retval int'
union all
select ' ,' + parameters.name + ' ' + types.name +
case
when types.name like 'n%char' then '(' + cast(parameters.max_length /2 as varchar(10)) + ')'
when types.name like '%char' then '(' +cast(parameters.max_length as varchar(10)) + ')'
when types.name like '%binary' then '(' +cast(parameters.max_length as varchar(10)) + ')'
when types.name = 'numeric' then '(' + cast(parameters.precision as varchar(10)) + ',' + cast(parameters.scale as varchar(10)) + ')'
else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName
union all
select ' '
union all

select 'SET ' + parameters.name + ' = ' +
case when types.name like '%char' then '''' else '' end +
case parameters.parameter_id --to add more parms, add to this list
when 1 then @.parm1
when 2 then @.parm2
when 3 then @.parm3
end +
case when types.name like '%char' then '''' else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName

union all
select ' '
union all
select 'EXECUTE @.retval = ' + @.procName
union all
select ' ' + parameters.name + ' = ' + parameters.name
+ case when parameters.is_output = 1 then ' OUTPUT ' else '' end +
case when parameter_id <> (select max(parameter_id) from sys.parameters where object_name(object_id) = @.procName)
then ',' else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName
end
go

--test with this proc

alter procedure test$paramTest
(
@.value1 int,
@.value2 nvarchar(10) output,
@.value3 numeric (6,2)
) as
select @.value1, @.value2, @.value3
go

Execute this

buildParms 'test$paramTest','1','x','200'

returns:

DECLARE @.retval int
,@.value1 int
,@.value2 nvarchar(10)
,@.value3 numeric(6,2)

SET @.value1 = 1
SET @.value2 = 'x'
SET @.value3 = 200

EXECUTE @.retval = test$paramTest
@.value1 = @.value1,
@.value2 = @.value2 OUTPUT ,
@.value3 = @.value3

|||

Fantastic.Sorry for late reply.I could not access Computer.

Let me check.Wll be back to you.

I was thinking of passing a delimited string so that it could handle all sorts of params.

Very very grateful!!!!!!!!!

|||

Thanks

This works like a dream.I beleive this only works on sql server 2005

To make it work on sql 2000 I will change the varchar(max) to varchar(8000) But still get errors on all the sys stuff.

Any more suggestions?

very very grateful for your help

|||

Try this on 2000. I used the information_schema views this time. I thought of doing this the first time, but thought it would be too much bother (actually it was about the same:)

alter procedure buildParms
(
@.procName nvarchar(128),
@.parm1 varchar(8000),
@.parm2 varchar(8000),
@.parm3 varchar(8000)
) as
begin
set nocount on

select 'DECLARE @.retval int'
union all
select ' ,' + PARAMETER_NAME + ' ' + data_type +
case
when data_type like 'n%char' then '(' + cast(CHARACTER_MAXIMUM_LENGTH /2 as varchar(10)) + ')'
when data_type like '%char' then '(' +cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) + ')'
when data_type like '%binary' then '(' +cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) + ')'
when data_type = 'numeric' then '(' + cast(NUMERIC_PRECISION as varchar(10)) + ',' + cast(NUMERIC_SCALE as varchar(10)) + ')'
else '' end
from information_schema.parameters
where SPECIFIC_NAME = @.procName
union all
select ' '

union all

select 'SET ' + PARAMETER_NAME + ' = ' +
case when data_type like '%char' then '''' else '' end +
case ordinal_position --to add more parms, add to this list
when 1 then @.parm1
when 2 then @.parm2
when 3 then @.parm3
end +
case when data_type like '%char' then '''' else '' end
from information_schema.parameters
where SPECIFIC_NAME = @.procName

union all
select ' '
union all
select 'EXECUTE @.retval = ' + @.procName
union all
select ' ' + PARAMETER_NAME + ' = ' + PARAMETER_NAME
+ case when PARAMETER_MODE = 'INOUT' then ' OUTPUT ' else '' end +
case when ordinal_position <> (select max(ordinal_position) from information_schema.parameters where SPECIFIC_NAME = @.procName)
then ',' else '' end

from information_schema.parameters
where SPECIFIC_NAME = @.procName

end
go

|||

What can I say?

You made my day ,

Thanks a lot I had started to convert it to 2000 but I had got stuck on the join between systypes and Information_schema.parameters i could not find the equivalent to join on.

thanks a lot again

Help needed for creating view

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required......

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.

ThanksQuestion: If anything in an itemgroup does not say complete, then it's incomplete?

Sounds simple enough...|||Is that an anwer or a question?|||Well it was a question...but...how's about

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(item int, itemgroup int, status varchar(25))
GO

INSERT INTO myTable99(item, itemgroup, status)
SELECT 1, 1, 'complete' UNION ALL
SELECT 2, 1, 'Xyz' UNION ALL
SELECT 3, 2, 'complete' UNION ALL
SELECT 4, 2, 'complete' UNION ALL
SELECT 5, 2, 'complete'
GO

CREATE VIEW myView99
AS
SELECT DISTINCT l.itemgroup
, CASE WHEN Status_COUNT IS NULL THEN 'Complete' ELSE 'Incomplete' END AS Status
FROM myTable99 l
LEFT JOIN ( SELECT itemgroup, COUNT(*) AS Status_COUNT
FROM myTable99
WHERE status <> 'Complete'
GROUP BY itemgroup) AS r
ON l.itemgroup = r.itemgroup
GO

SELECT * FROM myView99
GO|||Oh, oh! Can I play too?SELECT DISTINCT a.itemgroup
, CASE
WHEN EXISTS (SELECT *
FROM myTable AS b
WHERE b.itemgroup = a.itemgroup
AND b.status <> 'complete') THEN 'incomplete'
ELSE 'complete'
END AS groupStatus
FROM myTable AS a-PatP|||I like that one better....|||Thanks Guys...Both of them are much better than the function I have

Help Merge Replication

Hi to all,

I'm writing an application with Comact Framework 2.0 and I'd like to use merge replication to synchronize data among the sever and 25 Pocket PC.

The DataBase contains static tables (for configuration) and dynamic tables that I want to publish.

Configuration tables are small so no problem to publish them. The problem is with dynamic table and that's my first question:

One of these dynamic tables contains 150,000 records and data inside it (I can't use filter because all data must be "seen" to all POcket PC) should be published on each Pocket PC so: Is it possible to use merge replication with such big table?

Or is there another method to treat this kind of problem?

Should I use merge replication only for configuration tables?

If it's possible to use merge replication, what's the best hardware configuration for my server?

For example:

1) One PC for IIS and one for SQL Server with 2 CPU

2) or two PC in NLB so that request from Pocket PC's to IIS are balanced?

I'musing SQL Server 2005 SP1 and Windows CE 5.0

TIA.

Flavio

there's no table size limit for a table to be published by merge replication, 150k rows should not be a problem. Regarding your hardware configuration, it's up to you as it will depend if you're getting bottlenecks or not. Test with one configuration to see if there are any bottlenecks that can be relieved by the other configuration.

help me return the second highest date based on another field.

Forgive me, i'm still very new and learning everyday. I'm writing a select statement in visual studio 05 under ms sql 2005. i have 4 fields in my result set:

transactions.accountnumber

max(statements.todate) AS LastStatementDate

transactions.postingdate

DATEDIFF(d,max(statements.lastdate),transactions.postingdate) AS Aging

From the DATEDIFF line, you can tell i'm trying to count how many days have past from when a statement goes out (statements.lastdate) to when a transaction was posted (transactions.postingdate)

My problem occurs when i get a negative days due to the fact that a transaction was placed before a statement goes out. In this case, what i want to do is to return the second latest statement date that is older than the posting date. This will give me a positive days count and not a negative.

For example, if the last statement date was 4/25/2007 and the posted date was 4/4/2007, i'll get a -21 for the aging days. I need to pull the latest statement that is dated before 4/4/2007 so i get a positive days count. What is the syntax to do this automatically?

Which function do i use? I've played with row_number, rank, case, and a few others. Any advice is appreciated.

Code Snippet

SELECT

transactions.accountnumber

max(statements.todate) AS LastStatementDate

transactions.postingdate

DATEDIFF(d,max(statements.lastdate),transactions.postingdate) AS Aging

FROM myTbl

WHERE DATEDIFF(d,max(statements.lastdate),transactions.postingdate) > -1 --or 0 if it has to be atleast 1 day old

GROUP BY transactions.accountnumber, transactions.postingdate

{just guessing on parts of that since I don't know your actual schema et al.}

Depending on the size your tables, could be a potential performance issue regarding index usage given that where clause.

HTH

|||It would be so much easier to help if you would post the table DDL and some sample data in the form of INSERT statements|||

dvan,

When posting this kind of question / probem, it is very helpful posting DDL, including constraints and indexes, sample data and expected results. The help should be in both ways.

- How are those tables ([statements] and [transactions]) related?

Try:

Code Snippet

select

t.accountnumber,

t.transaction_number,

t.postingdate,

max(s.todate) as LastStatementDate

datediff(day, max(s.lastdate), t.postingdate) as aging

from

dbo.transactions as t

left join

dbo.statements as s

on t.accountnumber = s.accountnumber

and s.lastdate = (

select max(s1.lastdate)

from statements as s1

where s1.accountnumber = t.accountnumber and s1.lastdate < t.postingdate

)

group by

t.accountnumber,

t.transaction_number,

t.postingdate,

go

AMB

|||

I'm still very new to SQL, so all I'm writing are only SELECT statements. Below is the actual select statement that I have written so far. My superiors are asking for the query to show total aging days from the most current date a statement was sent out (LastStatement) to the date a transaction was posted (ActualPostingDate), which yields a positive number. Those are working beautifully.

However, if a transaction was posted prior to the date the statement was sent out, it yields a negative. If it is a negative, we'd like to see the date of a statement that is most prior to the posting date.

So, let's say there are 3 dates of statements: 4/25/2007, 3/28/2007, & 4/15/2007. The posting date we're looking at is on the 4/19/2007.

My query will show a result set of: LastStatement = 4/25/2007 and Aging = -6

I'd like it to know how to look for the difference and show: LastStatement = 4/15/2007 and Aging = 3

Hope this makes sense. I think I'm looking for an IF...ELSE or CASE or something along those lines so that it works for all scenarios. Here's my query so far... sorry about the lack of info, I'm still learning.

Code Snippet

SELECT
CreditTransactions.AccountNumber,
MAX(StatementsHistory.ToDate) AS LastStatement,
CreditTransactions.ActualPostingDate,
DATEDIFF(d, MAX(StatementsHistory.ToDate), CreditTransactions.ActualPostingDate) AS Aging

FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber

WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:00 PM')

GROUP BY
CreditTransactions.AccountNumber,
CreditTransactions.ActualPostingDate

|||

See if this gives you what you need:

Code Snippet

;with sh as (

SELECT AccountNumber, MAX(StatementsHistory.ToDate) AS LastStatement

FROM StatementsHistory

GROUP BY AccountNumber

), agingData as

(

SELECT

ct.AccountNumber,

sh.LastStatement,

ct.ActualPostingDate,

DATEDIFF(d, sh.LastStatement, ct.ActualPostingDate) AS Aging

FROM CreditTransactions ct INNER JOIN sh

ON ct.AccountNumber = sh.AccountNumber

WHERE

(ct.TransactionType = 'T58') AND

(ct.Reversed = 'False') AND

(ct.ActualPostingDate >= '4/1/2007') AND

(ct.ActualPostingDate <= '4/30/2007 11:59:59 PM')

)

SELECT AccountNumber, LastStatement, ActualPostingDate, Aging

FROM agingData

WHERE Aging > -1

GROUP BY

AccountNumber,

ActualPostingDate

|||

I appreciate your time and effort greatly in writing your code for me DaleJ (and everyone else that's responded!), however, what you wrote basically avoids all the negative days results.

What I truly need it to do is when it sees the negative day, it will look into the other statements dates and pick the next lower date to compare it against the posting date so that after the DATEDIFF calculation, the result set has positive days and not negative.

I ran your code against an account that has a negative result, and your code returns no results due to the "WHERE Aging > -1" line.

I augmented your code a little bit cause I was getting errors and to make it easier for me to read/understand, here's what it looks like so far... i put '-30' in the "WHERE Aging >" line toward the end to show the negative results set for the account number i listed, otherwise at the orginal '-1', it would not produce a result set at all.

Code Snippet

;with sh as

(

SELECT AccountNumber, MAX(StatementsHistory.ToDate) AS LastStatement

FROM StatementsHistory

GROUP BY AccountNumber

),

AgingData as

(

SELECT

CreditTransactions.AccountNumber,

sh.LastStatement,

CreditTransactions.ActualPostingDate,

DATEDIFF(d, sh.LastStatement, CreditTransactions.ActualPostingDate) AS Aging

FROM CreditTransactions INNER JOIN sh

ON CreditTransactions.AccountNumber = sh.AccountNumber

WHERE

(CreditTransactions.TransactionType = 'T58') AND

(CreditTransactions.Reversed = 'False') AND

(CreditTransactions.ActualPostingDate >= '4/1/2007') AND

(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:59 PM')

)

SELECT AccountNumber, LastStatement, ActualPostingDate, Aging

FROM AgingData

WHERE Aging > -30 and AccountNumber = 110774

GROUP BY AccountNumber,ActualPostingDate, LastStatement, Aging

The result set for the query above is: (example A)

AccountNumber LastStatement ActualPostingDate Aging

0000110774 2007-04-25 00:00:00.000 2007-04-04 13:18:46.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:19:03.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:19:38.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:19:49.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:00.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:11.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:20.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:31.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:42.000 -21

There's 9 records because there's 9 dates of service that isn't showing, i believe a DISTINCT would change this to 1 record. Either way, the aging is still negative. If we would just query the statement dates and ordered by DESC for this account, we'd get the following:

The result set is: (example B)

2007-04-25 00:00:00.000
2007-03-23 00:00:00.000
2007-02-20 00:00:00.000
2007-01-19 00:00:00.000
2006-12-20 00:00:00.000
2006-11-17 00:00:00.000
2006-10-17 00:00:00.000

I would like the result set to not show 2007-04-25 00:00:00.000 because that date causes the DATEDIFF to create a negative. I would like the query (if the aging is a negative) to go and find the second date of 2007-03-23 00:00:00.000 and plug this into the result causing DATEDIFF to create a positive. In conclusion, i'd like the result set to look like this instead of example A above:

The result set IF IT WORKED LIKE I WANTED is: (example C)

0000110774 2007-03-23 00:00:00.000 2007-04-04 13:18:46.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:03.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:38.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:49.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:00.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:11.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:20.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:31.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:42.000 12

Is this possible to do? I'd like it to this for all accounts should this scenario come up. I hope this clears things up a bit, I'm very new to SQL and am evolving everyday with it. Your time and help is very, very much appreciated.

|||

Hey dvang

See if this is closer:

Code Snippet

;with ct as

(

SELECT distinct c.AccountNumber,

convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate

FROM CreditTransactions c

WHERE

(c.TransactionType = 'T58') AND

(c.Reversed = 'False') AND

(c.ActualPostingDate >= '4/1/2007') AND

(c.ActualPostingDate <= '4/30/2007 11:59:59 PM')

), sh as

(

SELECT s.AccountNumber, MAX(s.ToDate) AS LastStatement

FROM StatementsHistory s

INNER JOIN ct

ON s.AccountNumber = ct.AccountNumber

AND s.ToDate <= ct.ActualPostingDate

GROUP BY s.AccountNumber

)

SELECT ct.AccountNumber, sh.LastStatement, ct.ActualPostingDate,

DATEDIFF(d, sh.LastStatement, ct.ActualPostingDate) AS Aging

FROM ct

INNER JOIN sh

ON ct.AccountNumber = sh.AccountNumber

|||

Hey DaleJ, thanks much! The secret was in the INNER JOIN syntax. Your new code was very deep and extensive, and I greatly appreciate the work you put into writing it for me. It taught me more than I was ready to know or learn! The entire code itself was still producing some negatives because in a few scenarios, we needed to look further back than just 1 statement date. For those instances we needed to choose the third highest statement date and not the second highest. Either way, you laid down a strong foundation for me to build on and I THANK YOU for that!

After reviewing, disecting, and testing your code piece by piece, I realized that all I had to do was incorporate the "AND s.ToDate <= ct.ActualPostingDate" logic into my joins and that alone would give me the proper result set I was seeking. That gave me the max date of statements that was less than the posting date, therefore yielding a positive aging days for every record. I've verified this against other live data and it's working out well so far. Please share any caveats if you know of any.

I never realized you could use operators such as "<", ">", "<>", etc. in the joins. I assumed we only used "=". Again, I'm extremely new to SQL, but am learning at an exponential rate.

I do have one request (and it goes out to all who read this), could someone point me to a webpage or site, or even explain to me how the abbreviations work. The code that DaleJ wrote for me has a lot of abbreviations. Below is a section of his code and I've underlined/bolded the abbreviations that doesn't make sense to me or where they're coming from. I'm kinda getting it, but I need to solidify my assumptions. It's confusing to me because the code works beautifully and I'm dumbfounded on why?!

Code Snippet

SELECT distinct c.AccountNumber,

convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate

FROM CreditTransactions c

WHERE

(c.TransactionType = 'T58') AND

(c.Reversed = 'False') AND

(c.ActualPostingDate >= '4/1/2007') AND

(c.ActualPostingDate <= '4/30/2007 11:59:59 PM')

These abbreviations are throwing me off cause when I'm reading on msdn2.microsoft.com, their examples have lotsa those abbreviations and it would be such a great help in understanding the examples if I could follow along without wondering what they mean.

I started SQL on visual studio 2005, but seem to be migrating to the server management studio more and more. I've noticed some of the syntax cannot be understood by visual studio which results in some seriously funny-looking text parsing (I hope that makes sense).

|||

Hey dv

I'm not sure how you're still getting negative aging numbers.

That code should only be using any statement date that is on or before the posting date, which should produce 0 as the lowest aging number.

You could run in to situations where some of the credit transactions won't show up should they not have any statement date on or before their posting date.

The 'abbreviations' is an alias for referencing the table(s).

In the above snippet, the FROM CreditTransactions c tells the parser that any place it sees c. it should substitute CreditTransactions.

So, SELECT distinct c.AccountNumber, translates to SELECT distinct CreditTransactions.AccountNumber,

but the alias (abbreviation) requires less typing and makes it easier to read.

HTH

|||

I'm not sure where the negative is coming from either. In some scenarios, the negatives were being produced because there was no other statement dates prior to pick from, so the result set showed the only statement date there was for that account. The others however had prior dates that should have been displayed in the result set but did not? Let me try to break it down... we'll use account #16322 and your code, plus I added a WHERE line to specify the account number (in highlights).

the table ct coding produces this result set:

Code Snippet

SELECT distinct c.AccountNumber,

convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate

FROM CreditTransactions c

WHERE

(c.TransactionType = 'T58') AND

(c.Reversed = 'False') AND

(c.ActualPostingDate >= '4/1/2007') AND

(c.ActualPostingDate <= '4/30/2007 11:59:59 PM') AND

(c.AccountNumber = 16322)

AccountNumber | ActualPostingDate
0000016322 | 04/13/2007
0000016322 | 04/18/2007
0000016322 | 04/27/2007

the table sh coding produces this result set:

Code Snippet

;with ct as

(

SELECT distinct c.AccountNumber,

convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate

FROM CreditTransactions c

WHERE

(c.TransactionType = 'T58') AND

(c.Reversed = 'False') AND

(c.ActualPostingDate >= '4/1/2007') AND

(c.ActualPostingDate <= '4/30/2007 11:59:59 PM')

)

SELECT s.AccountNumber, MAX(s.ToDate) AS LastStatement

FROM StatementsHistory s

INNER JOIN ct

ON s.AccountNumber = ct.AccountNumber

AND s.ToDate <= ct.ActualPostingDate

WHERE s.AccountNumber = 16322

GROUP BY s.AccountNumber

AccountNumber | LastStatementDate
0000016322 | 2007-04-25 00:00:00.000

so the actual select statement (or entire code) produces the final result set:

Code Snippet

;with ct as

(

SELECT distinct c.AccountNumber,

convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate

FROM CreditTransactions c

WHERE

(c.TransactionType = 'T58') AND

(c.Reversed = 'False') AND

(c.ActualPostingDate >= '4/1/2007') AND

(c.ActualPostingDate <= '4/30/2007 11:59:59 PM')

), sh as

(

SELECT s.AccountNumber, MAX(s.ToDate) AS LastStatement

FROM StatementsHistory s

INNER JOIN ct

ON s.AccountNumber = ct.AccountNumber

AND s.ToDate <= ct.ActualPostingDate

GROUP BY s.AccountNumber

)

SELECT ct.AccountNumber, sh.LastStatement, ct.ActualPostingDate,

DATEDIFF(d, sh.LastStatement, ct.ActualPostingDate) AS Aging

FROM ct

INNER JOIN sh

ON ct.AccountNumber = sh.AccountNumber

WHERE

(ct.AccountNumber = 16322)

AccountNumber | LastStatementDate | ActualPostingDate | Aging
0000016322 | 2007-04-25 00:00:00.000 | 4/13/2007 | -12
0000016322 | 2007-04-25 00:00:00.000 | 4/18/2007 | -7
0000016322 | 2007-04-25 00:00:00.000 | 4/27/2007 | 2

I hope I did this correctly each step as I broke it down. Am I not seeing it or does the final select statement not really checking to see that the statement date has to be lower than the posting date, hence, causing the negative aging in the first 2 records. The 3rd record has a positive.

The statment date for the first 2 records should have been 2007-03-26 00:00:00.000, which would generate positive aging days. I've tried to add "AND sh.LastStatement <= ct.ActualPostingDate" to the INNER JOIN line in the final select statement, but that produced a result set of just the 3rd record from above and ignoring the 1st and 2nd record?

I'm not trying to point out fault or error. My query is working fine from doing what I said in the above posts. I'm just trying to help break down the code so we can understand together why it didn't produce the result set we wanted from the beginning.

Also, THANK YOU VERY MUCH for explaining the abbreviations/aliases. That's makes perfect sense and will save me a lot of time in the future when writing codes.

|||

OK, good having some sampling of the data greatly helps

The following should find the "on or before" statement date relative to each posting date.

Code Snippet

create table #StatementsHistory(AccountNumber int, ToDate datetime)

insert into #StatementsHistory values(0000110774, '2007-04-25')

insert into #StatementsHistory values(0000110774, '2007-03-23')

insert into #StatementsHistory values(0000110774, '2007-02-20')

insert into #StatementsHistory values(0000110774, '2007-01-19')

insert into #StatementsHistory values(0000110774, '2006-12-20')

insert into #StatementsHistory values(0000110774, '2006-11-17')

insert into #StatementsHistory values(0000110774, '2006-10-17')

create table #CreditTransactions(AccountNumber int, ActualPostingDate datetime,

TransactionType varchar(5), Reversed varchar(5))

insert into #CreditTransactions values(0000110774, '2007-04-04 13:18:46', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-04-04 13:19:03', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-04-04 13:19:38', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-04-04 13:19:49', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:00', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:11', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:20', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:31', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:42', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:18:46', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:19:03', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:19:38', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:19:49', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:00', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:11', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:20', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:31', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:42', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:18:46', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:19:03', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:19:38', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:19:49', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:00', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:11', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:20', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:31', 'T58', 'False')

insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:42', 'T58', 'False')

;with ct as

(

SELECT distinct AccountNumber,

convert(varchar(10), ActualPostingDate, 101) as ActualPostingDate

FROM #CreditTransactions

WHERE

(TransactionType = 'T58') AND

(Reversed = 'False') AND

(ActualPostingDate >= '2/1/2007') AND

(ActualPostingDate <= '4/30/2007 11:59:59 PM')

), sh as

(

SELECT DISTINCT s.AccountNumber,

ct.ActualPostingDate,

max(s.ToDate) as LastStatement

from #StatementsHistory s

inner join ct

on s.AccountNumber = ct.AccountNumber

and s.ToDate <= ct.actualpostingdate

group by s.accountnumber, ct.ActualPostingDate

)

SELECT ct.AccountNumber,

ct.ActualPostingDate,

sh.LastStatement,

DATEDIFF(d, sh.LastStatement, ct.ActualPostingDate) AS Aging

FROM ct

INNER JOIN sh

on ct.AccountNumber = sh.AccountNumber

and ct.ActualPostingDate = sh.ActualPostingDate

|||hi dvang,

this is from your last post, actually you're in the right track here you just need to get the top 2 dates from the statementhistory and get the mininum from those two..

Code Snippet

--this is from you previous post

SELECT
CreditTransactions.AccountNumber,
MAX(StatementsHistory.ToDate) AS LastStatement,
CreditTransactions.ActualPostingDate,
DATEDIFF(d, MAX(StatementsHistory.ToDate), CreditTransactions.ActualPostingDate) AS Aging

FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber

WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:00 PM')

GROUP BY
CreditTransactions.AccountNumber,
CreditTransactions.ActualPostingDate

-- the solution would then be

SELECT

CreditTransactions.AccountNumber,
MIN(StatementsHistory.ToDate) AS LastStatement, -- you should use min to get the 2nd highest
CreditTransactions.ActualPostingDate,
DATEDIFF(d, MIN(StatementsHistory.ToDate), CreditTransactions.ActualPostingDate) AS Aging
FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber
WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:00 PM') AND
(StatementsHistory.ToDate IN (
SELECT TOP 2
a.ToDate
FROM StatementsHistory a
WHERE a.AccountNumber = StatementsHistory.AccountNumber
ORDER BY
a.ToDate DESC
)) -- this will get the top 2 dates for each account
GROUP BY
CreditTransactions.AccountNumber,
CreditTransactions.ActualPostingDate
ORDER BY
LastStatement

this would result to..

dvang wrote:

The result set IF IT WORKED LIKE I WANTED is: (example C)

0000110774 2007-03-23 00:00:00.000 2007-04-04 13:18:46.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:03.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:38.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:49.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:00.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:11.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:20.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:31.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:42.000 12

|||oops sorry, i found a bug on my prev post when not filtering the actualpostingdate

here's another alternative, i'm not sure if this would fit your requirement

SELECT DISTINCT
CreditTransactions.AccountNumber,
MAX(StatementsHistory.ToDate) AS LastStatement,
CONVERT(varchar(10),CreditTransactions.ActualPostingDate,101) AS ActualPostingDate,
DATEDIFF(d, MAX(StatementsHistory.ToDate), CreditTransactions.ActualPostingDate) AS Aging
FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber
WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
--(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
--(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:00 PM') AND
(StatementsHistory.ToDate <= CONVERT(varchar(10),CreditTransactions.ActualPostingDate,101))
GROUP BY
CreditTransactions.AccountNumber,
CreditTransactions.ActualPostingDate
ORDER BY
LastStatement|||

Forgive me for not replying sooner, I've been away on business. I just wanted to thank everyone that has contributed to helping me resolve this code. Everyone has been helpful, especially DaleJ.

It's nice to know I can always come back here when I run into SQL problems (which I will sooner or later) and have friendly people help out!

THANKS AGAIN!

Help me in writing sql query

When a user logs in the system I mark a Table in the database(that is a new id is generated)
Now I need to write a query which actually gets the following things--

1)Total no of people logged in the system for a given date
2)Minimum no of people logged in the system for a given date
3)Maximum no of people logged in the system for a given date
4)Average no of people logged in the system for a given date.

Hope You guys would help me in writing this query

ThanksWell, if all you're logging is when someone logged in, the only thing I can see you being able to get is the count of how many people logged in on a given date.
How long can people keep their connections? Overnight?
Unless you've also kept track of when they logged out, I don't see any way to know how many people were logged in at a given time. So I don't see a way to get the minimum, maximum or average.

Help me how to build this

Hi
Please help me writing the sql query .

I have got a DataBase table with fields and Data like this
------------------
job_id hours invoice_id
-------------------
1 2 null(MEANS NOT INVOICED)

1 3 2(MEANS INVOICED)

2 4 3(MEANS INVOICED)

2 5 null(MEANS NOT INVOICED)

---------------------
Now I want to show a report which shows HOW many hours are invoiced and not invoiced
on a JOB. The Report should be some thing like this

-----------------------
Job_Id Hours_Invoiced Hours_Not_Invoiced
1 3 2
2 4 5
------------------------

Please help me writing the sql query to return data like above
ThanksAssuming Invoice_id is a number greater than or equal to zero, use IsNull to convert null Invoice_id's to -1 and then use that to segregate the values. Something like:

SELECT
 job_Id,
 Sum( CASE WHEN IsNull(invoice_id,-1) >= 0 THEN hours ELSE NULL END) as Hour_Invoiced,
 Sum( CASE WHEN IsNull(invoice_id,-1) = -1 THEN hours ELSE NULL END) as Hours_Not_Invoiced
FROM YourTable
Group BY Job_Id
Order BY JobId