How to make group by revision_ID and when repeated display last check date separated by stick | ?

Question

0.00/5 (No votes)

See more:

, +

problem

How to make group by Revision_ID and when repeated display last check date separated by stick | ?

I need to group data by Revision_ID that make count to all zpartid

every revision_ID Have group of parts .

and when revision id repeated two time then lastcheckdate is firstdate | seconddate

and if more than two time then display text multi date

so How to do that please ?

SQL

CREATE TABLE [Parts].[LifeCycleMaster](
	[LifeCycleID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[ZPartID] [bigint] NOT NULL,
	[LastCheckDate] [date] NULL,
	[Revision_ID] [bigint] NULL,
 CONSTRAINT [PK_LifeCycleMaster_LifeCycleID] PRIMARY KEY CLUSTERED 
(
	[LifeCycleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_PartID] UNIQUE NONCLUSTERED 
(
	[ZPartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Revision_ID	ZPartID	LastCheckDate
12	          10	    12/12/2015
15	          120	    12/01/2014
15	          130	    05/05/2016
20	          170	    09/03/2013
20	          200	    09/05/2016
20	          300	    09/08/2017
		
	FinalResult	
Revision_ID  CountParts  LastCheckDate
12	         1	  12/12/2015
15	         2	  05/05/2016 |12/01/2014
20	         3	  Multi date

What I have tried:

SELECT lcm.Revision_ID,count(lcm.ZPartID)as CountParts,lcm.LastCheckDate  
FROM parts.LifeCycleMaster lcm group by Revision_ID

Posted 17-Jan-20 13:46pm

ahmed_sa

Updated 17-Jan-20 20:07pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2020-01-17T20:08:00

I would advice not to concatenate the dates into a single field when fetching the data. This makes parsing the data unnecessary complex on the client side.

Instead, why not just fetch the data in separate columns and add the logic to interpret the result to the calling program. This way the query is simple and you don't need to split the data from a single text field which may cause several kinds of problems, starting from date formats.

To fetch the data in separate columns, perhaps something like

SQL

WITH RevAmounts AS (
   SELECT lcm.Revision_ID,
          COUNT(lcm.ZPartID)                 AS CountParts,
          MAX(lcm.LastCheckDate)             AS LatestCheck,
          COUNT( DISTINCT lcm.LastCheckDate) AS DistinctCheckDates
   FROM parts.LifeCycleMaster lcm 
   GROUP BY Revision_ID
)
SELECT 
   ra.Revision_ID,
   ra.CountParts,
   ra.LatestCheck,
   (  SELECT MAX(lcm2.LastCheckDate)
      FROM parts.LifeCycleMaster lcm2
      WHERE lcm2.Revision_ID = ra.Revision_Id
      AND lcm2.LastCheckDate < ra.LatestCheck) AS PreviousCheck,
   ra.DistinctCheckDates
FROM RevAmounts ra