October 13, 2010 at 11:28 am
Hi,
I need some help to write a query using SQL Server 2005. Consider a table "CardHistory"
Create Table CardHistory
(
SerialNo INT IDENTITY(1,1),
ModificationDatetime DATETIME,
OldCardNo INT,
NewCardNo INT
)
Some times "OldCardNo" and "NewCardNo" can be same.
I need to find out very first CardNo, most recent (last) CardNo and ModificationsCount.
Example:
CardNo 100 changed to 101
CardNo 101 changed to 102
CardNo 102 changed to 103
CardNo 103 changed to 104
then query should return 100, 104, 4
Please help as soon as possible.
Thanks in advance ...
October 13, 2010 at 12:05 pm
You need to look at the MIN, MAX and COUNT functions, as well as the GROUP BY clause of the SELECT statement.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 12:13 pm
There are multiple set of such data.
Example:
CardNo 100 changed to 101
CardNo 101 changed to 102
CardNo 102 changed to 103
CardNo 103 changed to 104
CardNo 200 changed to 201
CardNo 201 changed to 202
CardNo 202 changed to 203
CardNo 203 changed to 204
CardNo 1200 changed to 1201
CardNo 1201 changed to 1202
CardNo 1202 changed to 1203
CardNo 1203 changed to 1204
CardNo 1204 changed to 1206
It should result:
100, 104, 4
200, 204, 4
1200, 1206, 5
October 13, 2010 at 12:18 pm
WayneS (10/13/2010)
You need to look at the MIN, MAX and COUNT functions, as well as the GROUP BY clause of the SELECT statement.
He's going to need a self-referencing CTE for this one to build the org-chart chain and the sub-counts. I just don't remember how to do them offhand and have to go research it again. I think I did maybe one a year before I started goofing off on this site again. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 13, 2010 at 12:23 pm
Yes right but could not find any solution yet ...
Thanks
October 13, 2010 at 12:39 pm
harjeet_21 (10/13/2010)
Hi,I need some help to write a query using SQL Server 2005. Consider a table "CardHistory"
Create Table CardHistory
(
SerialNo INT IDENTITY(1,1),
ModificationDatetime DATETIME,
OldCardNo INT,
NewCardNo INT
)
Please post sample data, in the form of INSERT statements, to populate this table that shows the problem that you're asking us to help you solve.
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 12:46 pm
Here is the test data -
Create Table #CardHistory
(
SerialNo INT IDENTITY(1,1),
ModificationDatetime DATETIME DEFAULT GETDATE(),
OldCardNo INT,
NewCardNo INT
)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 100,101)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 101,102)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 102,103)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 103,104)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 200,200)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 200,201)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 201,202)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 202,206)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 1202,1206)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 1206,1207)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 1207,1207)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 1207,1208)
Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 1208,1209)
Expected result is:
100, 104, 4
200, 206, 4
1202, 1209, 5
Thanks
October 13, 2010 at 1:34 pm
Does this do it for you?
if OBJECT_ID('tempdb..#CardHistory') IS NOT NULL DROP TABLE #CardHistory;
Create Table #CardHistory
(
SerialNo INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, -- a CLUSTERED INDEX is REQUIRED!
ModificationDatetime DATETIME DEFAULT GETDATE(),
OldCardNo INT,
NewCardNo INT,
StartCardNo INT
)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (100,101)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (101,102)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (102,103)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (103,104)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (200,200)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (200,201)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (201,202)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (202,206)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (1202,1206)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (1206,1207)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (1207,1207)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (1207,1208)
Insert #CardHistory(OldCardNo,NewCardNo) VALUES (1208,1209)
-- This form of the UPDATE statement has some particular rules.
-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
-- for a complete discussion of how this works, and all of the rules for utilizing it.
-- declare and initialize some variables for use in the update statement.
declare @SerialNo INT,
@StartCardNo INT,
@Sequence INT,
@LastNewCardNo INT;
SET @Sequence = 0;
SET @StartCardNo = 0;
WITH SafeTable AS
(
-- this CTE assigns a row number in the order of the clustered index
-- when compared to below, if it's not the same then it throws an error.
SELECT *, Sequence = ROW_NUMBER() OVER (ORDER BY SerialNo)
FROM #CardHistory
)
UPDATE t1
-- check to ensure everything is being updated in the proper order
SET @Sequence = CASE WHEN Sequence = @Sequence + 1 THEN Sequence
ELSE 1/0 END, -- not in correct order, so throw an error
@StartCardNo = StartCardNo = CASE WHEN OldCardNo = @LastNewCardNo THEN @StartCardNo
ELSE OldCardNo END,
@LastNewCardNo = NewCardNo,
@SerialNo = SerialNo -- anchor column - REQUIRED!
FROM SafeTable t1 WITH (TABLOCKX) -- REQUIRED! (prevent any others from modifying data)
OPTION (MAXDOP 1); -- REQUIRED! (to prevent parallelism)
-- get the results.
SELECT StartCardNo, MAX(NewCardNo), COUNT(*)
FROM #CardHistory
GROUP BY StartCardNo
ORDER BY StartCardNo;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply