query formating without using procs

Question

query formating without using procs

krishna-439630

SSC Journeyman

Points: 99
More actions
May 15, 2007 at 8:16 am

#371235

Hi,
I have the following table with me.
can i help me i formating the query
Create   Table iw_patstrat_genpact_workdb.days_area (
ind integer,
current_area varchar(30),
run_date date,
invo_num integer
)
Primary Index (ind)
;

select * from   iw_patstrat_genpact_workdb.days_area
order by ind,invo_num,run_date

        ind   current_area    run_date        invo_num
        1       AU      4/19/2007       41574351
        2       AU      4/19/2007       41574351
        3       AU      4/20/2007       41574351
        4       AU      4/19/2007       41574352
        5       AU      4/20/2007       41574352
        6       B15     4/23/2007       41594992
        7       B16     4/24/2007       41594993
        8       B16     4/25/2007       41594993
        9       SP      4/19/2007       41593391
        10      SP      4/19/2007       41593391
        11      SP      4/20/2007       41593391
        12      SP      4/21/2007       41593391
        13      SP      4/22/2007       41593391
The desired ouput is
Output:-
ind current_area    run_date        invo_num        days_in_area
1       AU         4/19/2007       41574351
2       AU         4/19/2007       41574351
3       AU          4/20/2007       41574351        2
4       AU          4/19/2007       41574352
5       AU          4/20/2007       41574352        2
6       B15         4/23/2007       41594992        1
7       B16          4/24/2007       41594993
8       B16          4/25/2007       41594993        2
9       SP            4/19/2007       41593391
10      SP          4/19/2007       41593391
11      SP          4/20/2007       41593391
12      SP         4/21/2007       41593391
13      SP          4/22/2007       41593391        4

The logic behind this is:-
1.If Invoice number was not on 'yesterday's' downloaded data:
a. Days_in_area = 1
2. we havee to Compare current area from 'today's' downloaded data to current area from 'yesterday's downloaded data:
a. If both are equal, add 1 to number of days in area
b. If different, days_in_area = 1.

there are restrictions for me of not using any procs.
Thanks in advance.
Regards,
krishna

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Krishna

I think I might understand what you want... for each invoice, you want to populate the days_in_area column with the number of different days that invoice has appeared on, but you only want to do that on the most recent date for each invoice. Is that right?

Also, you say that you have to compare the areas for each invoice, and yet your sample data doesn't contain any invoices that have more than one different area. If I alter your sample data as shown in red below, please will you show what the output should be?

A couple more questions: you've posted in a SQL 2000 forum - please confirm you are indeed using 2000. I've a feeling this would be easier with 2005! And why are you not allowed to use stored procedures?

John

ind current_area run_date invo_num

1 AU 4/19/2007 41574351

2 AU 4/19/2007 41574351

3 AU 4/20/2007 41574351

4 AU 4/19/2007 41574352

5 AU 4/20/2007 41574352

6 B15 4/23/2007 41594992

7 B16 4/24/2007 41594993

8 B16 4/25/2007 41594993

9 AU 4/19/2007 41593391

10 AU 4/19/2007 41593391

11 AU 4/20/2007 41593391

12 SP 4/21/2007 41593391

13 SP 4/22/2007 41593391

krishna-439630 SSC Journeyman Points: 99 More actions · Answer 2

Hi,

Here is the updatde table.

its restricetd not to use procs,this is restriction from my manager.

Create Table iw_patstrat_genpact_workdb.days_area (

ind integer,

current_area varchar(30),

run_date date,

invo_num integer

)

Primary Index (ind)

;

select * from iw_patstrat_genpact_workdb.days_area

order by ind,invo_num,run_date

ind current_area run_date invo_num

1 AU 4/19/2007 41574351

2 AU 4/19/2007 41574351

3 AU 4/20/2007 41574351

4 AU 4/19/2007 41574352

5 AU 4/20/2007 41574352

6 B15 4/23/2007 41594992

7 B16 4/24/2007 41594993

8 B16 4/25/2007 41594993

9 SP 4/19/2007 41593391

10 SP 4/19/2007 41593391

11 B16 4/20/2007 41593391

12 SP 4/21/2007 41593391

13 SP 4/22/2007 41593391

Output:-

ind current_area run_date invo_num days_in_area

1 AU 4/19/2007 41574351

2 AU 4/19/2007 41574351

3 AU 4/20/2007 41574351 2

4 AU 4/19/2007 41574352

5 AU 4/20/2007 41574352 2

6 B15 4/23/2007 41594992 1

7 B16 4/24/2007 41594993

8 B16 4/25/2007 41594993 2

9 SP 4/19/2007 41593391

10 SP 4/19/2007 41593391 1

11 B16 4/20/2007 41593391 1

12 SP 4/21/2007 41593391

13 SP 4/22/2007 41593391 2

Im using Teradata.

Thanks in Advance

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Krishna

OK, that's different from what you gave before, but at least I understand it! Now, please will you confirm what version of SQL Server you're using? And WHY does your manager not allow you to use procs? (S)he must have given you a reason? What is Teradata?

Thanks

John

krishna-439630 SSC Journeyman Points: 99 More actions · Answer 4

Hi,

Teradata is a relational database management system (RDBMS) that provides the foundation enabling a company's IT systems to achieve the goal of "Transforming Transactions into Relationships." Teradata's scalability allows the system to grow as the business grows, from gigabytes to terabytes and beyond. Teradata's unique technology has been proven at customer sites across industries and around the world.

Teradata offers a choice of several operating systems:

NCR UNIX SVR4 MP-RAS, a variant of System V UNIX from AT&T

Microsoft Windows 2000 and Windows Server 2003

SUSE Linux on 64-bit Intel servers has been pre-announced for 2006.

Teradata Enterprise Data Warehouses are often accessed via ODBC or JDBC by applications running on operating system such as Microsoft Windows or flavors of UNIX. The warehouse typically sources data from operational systems via a combination of batch and trickle loads.

The largest and most prominent customer of this DBMS is Wal-Mart, which runs its central inventory and other financial systems on Teradata. Wal-Mart's Teradata Data Warehouse is generally regarded by the DBS industry as being the largest data warehouse in the world. Other Teradata customers include companies like AT&T (formerly SBC), Dell, Continental Airlines, National Australia Bank, FedEx, Vodafone, Gap Inc, Safeway Inc, eBay and Kaiser Permanente.

Teradata's main competitors are other high-end solutions such as Oracle and IBM's DB2.

right from the begininng of the project starts they never used procs.

and they are continuing the same.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Teradata is an RDBMS used in data warehousing. It's owned, I think, by NCR.

Krishna, rather than starting multiple threads about the same subject, it's less confusing for those wanting to offer help if you just edit or add to the thread you've already posted.

Greg

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 6

Here's a partial answer:

select ID, CurrentArea, RunDate, InvoiceID, count(*) as Days
from
    (select ID, CurrentArea, RunDate, InvoiceID
        from dbo.DaysArea Today
    union all
    select Today.ID, Today.CurrentArea, Today.RunDate, Today.InvoiceID
        from dbo.DaysArea Today inner join
            (select distinct CurrentArea, RunDate, InvoiceID
                from DaysArea) as Yesterday
                    on  Yesterday.InvoiceID = Today.InvoiceID
                    and Yesterday.CurrentArea = Today.CurrentArea
                    and Yesterday.RunDate = DateAdd(day, -1, Today.RunDate
            )            
    ) as SubQ
group by ID, CurrentArea, RunDate, InvoiceID

The result set is not quite what you want but maybe close enough depending on your use.

ID CurrentArea   RunDate        InvoiceID     Days
== ===========  ==========      =========     ====
1AU2007-04-19 415743511
2AU2007-04-19 415743511
3AU2007-04-20 415743512
4AU2007-04-19 415743521
5AU2007-04-20 415743522
6B152007-04-23 415949921
7B162007-04-24 415949931
8B162007-04-25 415949932
9SP2007-04-19 415933911
10SP2007-04-19 415933911
11B162007-04-20 415933911
12SP2007-04-21 415933911
13SP2007-04-22 415933912

Note that this will not work for more than 2 adjacent days. To make it work for more than 2, you will need to tweek the subquery that brings out the days with a duplicate entry on the previous day. I'll pull out that subquery so you can better see which one it is:

select Today.ID, Today.CurrentArea, Today.RunDate, Today.InvoiceID
        from dbo.DaysArea Today inner join
            (select distinct CurrentArea, RunDate, InvoiceID
                from DaysArea) as Yesterday
                    on  Yesterday.InvoiceID = Today.InvoiceID
                    and Yesterday.CurrentArea = Today.CurrentArea
                    and Yesterday.RunDate = DateAdd(day, -1, Today.RunDate
            )

It shouldn't be too hard to do but I've put in enough time already.

BTW, I have heard of management not allowing the use of stored procedures before. However, I have never heard of a good reason. Does anyone know of a good reason? Until I hear of one, I will continue to think that such people are ignorant savages who shouldn't be in management... well, maybe waste management! 8)

Tomm

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

There is a "good" reason ... they're probably not allowed to put their own stored procedures in and it must pass a DBA review... so lot's of folks in departments like Finance, Billing, or Invoicing write their own queries in something like AQT, and then cut and paste into a spreadsheet.

What they don't realize is that much of that code is, well..., crap code that sometimes uses heavy resources that can really drag down a production system. Some companies recognize that circumstance early on and actually maintain a separate "reporting" database to support such ad hoc queries.

"Ignorant savages" and "Waste Management" seem to be appropriate terms in this case and so they're given a database that is snapshot updated every midnight. No harm can be done there...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krishna-439630 SSC Journeyman Points: 99 More actions · Answer 8

krishna-439630

SSC Journeyman

Points: 99

May 17, 2007 at 12:21 am

#707014

hi,

there is some probs with the query.

thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

John Mitchell-245523

SSC Guru

Points: 148809

May 17, 2007 at 1:25 am

#707024

Krishna

Care to tell us what the problems are?

John

krishna-439630 SSC Journeyman Points: 99 More actions · Answer 10

the errors are DateAdd(day, -1, Today.RunDate) is showing some prob

and it coming like curren_area is ambigious.

thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

Krishna

"Some prob" is too vague. And I don't see "curren_area" anywhere in the query. If there is an error message, please post it. If the query is not returning the expected results, please post the result set and say how it differs from what you require. If there's some other problem, please state clearly what it is.

John

krishna-439630 SSC Journeyman Points: 99 More actions · Answer 12

hi along with date function,

it telling that CurrentArea is ambigious.

no result is coming.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

Krishna

I notice you still haven't posted the (exact) error message. However, from what you have said, I think I can help to point you in the right direction: you should be perfectly capable of solving this yourself.

The error message will have a line saying what line of the query the error occurred in. Double-click on that line in the error message and it will highlight the line in the query. I imagine what is happening is that somewhere CurrentArea needs to be qualified with a table name or subquery alias.

Good luck!

John

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 14

Here's a solution. It involves the use of a user function so it's not useful to the original poster. But is does illustrate how absurdly complicated a restriction on the use of stored procedures can make even the simplest tasks. See how easy it is without the restriction?

I know, I'm letting this get to me. But our jobs are difficult enough without [CENSORED] people making it even more difficult.

Also, I noticed I changed the names of everything in my example. This is an old habit of mine as I didn't really expect to respond. So I am including all the appropriate DDL to completely reproduce my example. So, sorry for the length of this post.

use test;   -- You will, of course, change this to your database
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE dbo.DaysArea(
ID          int         IDENTITY(1,1) PRIMARY KEY,
CurrentArea varchar(30) NOT NULL,
RunDate     datetime    NOT NULL,
InvoiceID   int         NOT NULL,
)

GO
-- Note the creation of an index. This will much increase the performance of the self join
-- if your table has a significant number of records.
CREATE NONCLUSTERED INDEX X_DaysArea_InvoiceID ON dbo.DaysArea (
InvoiceID ASC
)
go
SET ANSI_PADDING OFF
go

Insert into dbo.DaysArea (
    CurrentArea,  RunDate,  InvoiceID )
select 'AU', '2007-04-19', '41574351'
union all
select 'AU', '2007-04-19', '41574351'
union all
select 'AU', '2007-04-20', '41574351'
union all
select 'AU', '2007-04-19', '41574352'
union all
select 'AU', '2007-04-20', '41574352'
union all
select 'B15', '2007-04-23', '41594992'
union all
select 'B16', '2007-04-24', '41594993'
union all
select 'B16', '2007-04-25', '41594993'
union all
select 'SP', '2007-04-19', '41593391'
union all
select 'SP', '2007-04-19', '41593391'
union all
select 'SP', '2007-04-20', '41593391'
union all
select 'SP', '2007-04-21', '41593391'
union all
select 'SP', '2007-04-22', '41593391'
go

create function dbo.GetDaysInArea (
@ID     int
)
RETURNS varchar(5)  -- Allows us to return a blank for zero.
AS
  begin
    declare
        @Result     varchar(5),
        @DayCount   int,
        @RunDay     datetime;

    set @Result     = '';   -- Initialize to an empty string
    
    -- Retrieve the run date of the record of interest
    select  @RunDay = RunDate
        from dbo.DaysArea
        where ID = @ID;

    -- See if there are any records for this invoice/area that were entered the day after.
    select      
        @DayCount   = count(*)
        from dbo.DaysArea DA1 inner join
            dbo.DaysArea DA2
                on DA1.CurrentArea  = DA2.CurrentArea
                and DA1.InvoiceID   = DA2.InvoiceID
        where  DA1.ID  = @ID
            and DateDiff( day, DA1.RunDate, DA2.RunDate ) = 1;

if @DayCount = 0    -- Zero means no entries for 'tomorrow'
  begin
    
    -- So now we walk backward and count the number of previous days (if any)
    while @ID is not null
      begin
        select  @ID     = Min( DA2.ID ), @DayCount = @DayCount + 1
            from dbo.DaysArea DA1 inner join
                    dbo.DaysArea DA2
                        on DA1.CurrentArea  = DA2.CurrentArea
                        and DA1.InvoiceID   = DA2.InvoiceID
                where   DA1.ID  = @ID
                    and DateDiff(day, DA1.RunDate, DA2.RunDate) = -1;
      end--while
    
    set @Result = Convert( varchar, @DayCount);
  end--if
    
-- Return the result of the function
return @Result

  end--function
go

-- Test the function
select ID,  CurrentArea,  RunDate,  InvoiceID,  dbo.GetDaysInArea( ID ) as DaysInArea 
from dbo.DaysArea;

/*
 The result... formatted to look nice.

  ID  CurrentArea RunDate     InvoiceID   DaysInArea
  ==  =========== ==========  =========   ==========
  1       AU      2007-04-19  41574351
  2       AU      2007-04-19  41574351
  3       AU      2007-04-20  41574351        2
  4       AU      2007-04-19  41574352
  5       AU      2007-04-20  41574352        2
  6       B15     2007-04-23  41594992        1
  7       B16     2007-04-24  41594993
  8       B16     2007-04-25  41594993        2
  9       SP      2007-04-19  41593391
  10      SP      2007-04-19  41593391
  11      SP      2007-04-20  41593391
  12      SP      2007-04-21  41593391
  13      SP      2007-04-22  41593391        4
*/

Tomm

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms