Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    35

    Unanswered: counting rows by date (was "Help on Query")

    I hate to ask such silly helps..but I'm missing something here..need help.
    I have a table having columns for createddate and deleteddate. The data gets created and deleted periodically and I need to find out the number of created,deleted and remaining number of records on each day. This query works, but takes a lot of time...not sure if there is a more better way to do this.. Please help
    SELECT
    CAST(createddate AS DATETIME) AS createdDate,
    Created,
    Deleted,
    Remaining
    FROM(
    SELECT
    CONVERT(VARCHAR,createdon,102) AS CreatedDate,
    COUNT(1) created,
    (SELECT COUNT(1) FROM table ta2
    WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
    CONVERT(VARCHAR,ta.createdon,102)) Deleted,
    ((SELECT COUNT(1) FROM table ta1
    WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
    CONVERT(VARCHAR,ta.createdon,102)) -
    (SELECT COUNT(1) FROM table ta1
    WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
    CONVERT(VARCHAR,ta.createdon,102))) Remaining
    FROM table ta
    WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
    GROUP BY CONVERT(VARCHAR,createdon,102)
    ORDER BY CONVERT(VARCHAR,createdon,102) DESC)
    AS tmp

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, your outer select is completely unnecessary. All it does is display the data exactly as it is returned from the inner query. Drop it.
    Second, please use count(*) instead of count(1). Why? Just to make the rest of us happy. That's why.
    Third, use the DATEDIFF function instead of subtracting an integer from GETDATE().
    Fourth, lets hope to God your table has a primary key, which in keeping with your naming convention we will name "primarykey".

    Then see if this doesn't run faster:

    select Convert(char(10), ta1.createdon, 120) CreateDate,
    count(distinct ta1.primarykey) Created,
    count(distinct ta2.primarykey) Deleted,
    count(distinct ta3.primarykey) - count(distinct ta4.primarykey) Remaining
    from table ta1
    left outer join table ta2 on datediff(day, ta2.deletedon, ta1.createdon) = 1
    left outer join table ta3 on datediff(day, ta3.createdon, ta1.createdon) >= 0
    left outer join table ta4 on datediff(day, ta4.deletedon, ta1.createdon) >= 0
    where datediff(day, ta1.createdon, getdate()) <= 90
    group by Convert(char(10), ta1.createdon, 120)
    order by Convert(char(10), ta1.createdon, 120)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Posts
    35
    My primary key is a uniqueid, so this query gives an error: count unique aggregate operation cannot take uniqueidentifier datatype as argument.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try count(Distinct cast([YourPrimaryKey] as char(36))
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2004
    Posts
    35
    Well. I tried that. It worked, but it takes a lot more time than before, could be because of outer joins. I am trying to replace the date comparision with datediff, but getting 'not contained in aggregate function or group by' error. still working out

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is not going to be a fast query, whatever you do. If you post the table structure I or somebody else here may be able to find a more efficient execution. Perhaps a multi-step procedure using a table variable, for instance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    This is not going to be a fast query, whatever you do. If you post the table structure I or somebody else here may be able to find a more efficient execution. Perhaps a multi-step procedure using a table variable, for instance.
    Really? You give us too much credit...

    I'd like to see the DDL AND an explanation of what you're trying to do...

    IN Business terms...nothing technical

    Sample data would be helpful as well

    Oh and what the final result should be...

    Do that and THEN I'll agree with the Blind dude...

    (amazing how he can find the keys...no?)



    Code:
    SELECT 
    	  CAST(createddate AS DATETIME) AS createdDate 
    	, Created
    	, Deleted 
    	, Remaining 
     FROM	( SELECT 
    		, CONVERT(VARCHAR,createdon,102) AS CreatedDate
    		, COUNT(1) created
    		, ( SELECT COUNT(1) FROM table ta2 
    		     WHERE CONVERT(VARCHAR,ta2.deletedon,102) = 
    			   CONVERT(VARCHAR,ta.createdon,102)
    		   ) Deleted
    		, (( SELECT COUNT(1) FROM table ta1 
    		      WHERE CONVERT(VARCHAR,ta1.createdon,102) <= 
    			    CONVERT(VARCHAR,ta.createdon,102)) - 
    			  ( SELECT COUNT(1) FROM table ta1 
    			     WHERE CONVERT(VARCHAR,ta1.deletedon,102) <= 
    				   CONVERT(VARCHAR,ta.createdon,102))) Remaining
    	    FROM table ta 
    	    WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
    	 GROUP BY CONVERT(VARCHAR,createdon,102) 
    	 ORDER BY CONVERT(VARCHAR,createdon,102) DESC
    	) AS tmp
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2004
    Posts
    35
    Ok. Basically, the table has order entries created by the sales men and it gets reviewed by the manager. Once they review it, it becomes invalid the table(logically deleted, not physically). So, I am storing the created date and deleted date in this table. For audit purposes, I need a query to give how many orders were created during a day, how many were reviewed(or deleted) and how many were remaining at the end of day. The primary key is the order id which has an index on it. I also have a column that stores 1 or 0 depending on whether the order is deleted or not.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you know how to script the DDL for the table?

    Post it here...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Posts
    35
    CREATE TABLE [dbo].[Order] (
    [OrderId] [uniqueidentifier] NOT NULL ,
    [CustomerNo] [varchar] (100) NULL ,
    [Address] [varchar] (255) NULL ,
    [DocNo] [varchar] (100) NULL ,
    [DocDate] [datetime] NULL ,
    [OrderType] [uniqueidentifier] NULL ,
    [SalesRep] [uniqueidentifier] NULL ,
    [Value] [money] NULL ,
    [Comments] [varchar] (2048) NULL ,
    [CreatedBy] [uniqueidentifier] NULL ,
    [CreatedOn] [datetime] NULL ,
    [UpdatedBy] [uniqueidentifier] NULL ,
    [UpdatedOn] [datetime] NULL ,
    [Deleted] [bit] NOT NULL ,
    [DeletedBy] [uniqueidentifier] NULL ,
    [DeletedOn] [datetime] NULL ,
    [Status] [char] (1) NULL,
    CONSTRAINT [I_ORDER] PRIMARY KEY CLUSTERED
    (
    [OrderId]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by nanadmin
    Ok. Basically, the table has order entries created by the sales men and it gets reviewed by the manager. Once they review it, it becomes invalid the table(logically deleted, not physically). So, I am storing the created date and deleted date in this table. For audit purposes, I need a query to give how many orders were created during a day, how many were reviewed(or deleted) and how many were remaining at the end of day. The primary key is the order id which has an index on it. I also have a column that stores 1 or 0 depending on whether the order is deleted or not.
    Well...is that the difference of the 2?

    Also, you mention you have a column that stores 0 or 1 to show that it's logically deleted...

    But doesn't the existence of a date in the DeletedOn column infer that?

    Same thing with the CreatedOn Column for New Orders?

    Yes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jan 2004
    Posts
    35
    It is not the difference of 2. Because, there may be 10 orders created yesterday and 5 today; and if 6 were deleted yesterday and 4 today, then my end of day remaining count will be 4 for yesterday and 5 for today. The result would be 10,6,4 and 5,4,5

    'Deleted' column has the value 0 or 1. Deleted date is enough, but I needed a flag for other querying purposes, since it is programatically good to check (deleted =1) instead of (deleteddate <> null)

  13. #13
    Join Date
    Jan 2005
    Posts
    1
    1. From DDL statement there is only index on the table - PK by order ID. So your query are processed like 4 full table scans.
    2. Do not compare DateTime fields this way: CONVERT(VARCHAR,ta2.deletedon,102) = CONVERT(VARCHAR,ta.createdon,102). This way query engine cannot use indexes and it lowering comparision performance.
    3. If create indexes on createdon and deletedon fields and remove CONVERT on comparision operations your select would be much faster.
    Last edited by palex; 01-30-05 at 21:19.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Palex, welcome to the forum. But if you are going to start responding to year-old posts then you are going to have a lot of catching up to do.

    Hope your calendar is clear for the next month or two...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •