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 tmpFirst, 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)|||My primary key is a uniqueid, so this query gives an error: count unique aggregate operation cannot take uniqueidentifier datatype as argument.|||Try count(Distinct cast([YourPrimaryKey] as char(36))|||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|||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.|||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?)
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|||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.|||Do you know how to script the DDL for the table?
Post it here...|||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]|||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?|||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)|||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.|||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...
