Solved

sql syntax

Posted on 2014-10-09
4
137 Views
Last Modified: 2014-10-24
I have below sql script. It works fine but I need to make it like summary in ID.
Attached excel has my original result from below query, and also the new result I would like to see.
Overall, I want to have a list of ID into a row. so I can create the report the way I want.
See any experts can help me. Thanks

SELECT     ID, Status, SectorVP, SectorVPApproval, SectorVPComments, DateSubmission, CURRENT_TIMESTAMP AS SystemCurrentTimeStamp, DATEDIFF(hh, DateSubmission,
                      CURRENT_TIMESTAMP) AS Hours_Difference
FROM         TravelApprovalForms
WHERE     (Status = 'Pending') AND (SectorVPApproval = 'Pending') AND (DateSubmission >= '09/01/2014') AND (DATEDIFF(hh, DateSubmission, CURRENT_TIMESTAMP) >= 24)
ORDER BY ID DESC

Original Result set:							
ID	STATUS	SECTORVP	SECTORVPAPPROVAL	SECTORVPCOMMENTS	DATESUBMISSION	SYSTEMCURRENTTIMESTAMP	HOURS_DIFFERENCE
548	Pending	TESTER	Pending		8/10/2014 9:34	9/10/2014 17:16	32
546	Pending	TESTER	Pending		7/10/2014 15:55	9/10/2014 17:16	50
545	Pending	TESTER	Pending	xxx	7/10/2014 15:54	9/10/2014 17:16	50
534	Pending	TESTER1	Pending	testing	7/10/2014 11:52	9/10/2014 17:16	54
580	Pending	TESTER1	Pending	testing	7/10/2014 11:52	9/10/2014 17:16	54
							
							
New Result set							
							
TESTER	548,546	
TESTER1	534, 580
					

Open in new window

0
Comment
Question by:ITsolutionWizard
  • 3
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40372164
Seems you are seeking a comma separated list of IDs per SECTORVP, but why is 545 missing in the result? commas.png
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40372335
This result
| SECTORVP |       COLUMN_1 |
|----------|----------------|
|   TESTER |  545, 546, 548 |
|  TESTER1 |       534, 580 |

Open in new window


Produced by this query:
SELECT
      TAF.SECTORVP
    , MAX(CA.IDS)
FROM TravelApprovalForms AS TAF
    CROSS APPLY (
        SELECT
          STUFF((
                SELECT
                      ', ' + CAST(TAF2.ID AS varchar(20))
                FROM TravelApprovalForms AS TAF2
                WHERE TAF.SECTORVP = TAF2.SECTORVP
                ORDER BY TAF2.ID
                FOR XML PATH ('')
                )
             , 1, 1, '')
         ) AS CA (IDS)
GROUP BY
      SECTORVP
;

Open in new window

0
 

Author Comment

by:ITsolutionWizard
ID: 40381104
Great. thank but I try to add below
 where VPname <> null and totalListItem <> null

and the full script is below.

are not working :-(


SELECT ltrim(TAF.SECTORVP) as VPname, MAX(CA.IDS) as TotalListItem FROM TravelApprovalForms  

AS TAF
    CROSS APPLY (
        SELECT
          STUFF((
                SELECT
                      ', ' + CAST(TAF2.ID AS varchar(20))
                FROM TravelApprovalForms AS TAF2
                WHERE TAF.SECTORVP = TAF2.SECTORVP
                          ORDER BY TAF2.ID
                FOR XML PATH ('')
                )
             , 1, 1, '')

         ) AS CA (IDS)  
GROUP BY
      where VPname <> null and totalListItem <> null

      ltrim(SECTORVP)
        
        
;
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40381149
NULL cannot equal anything and it cannot be unequal to anything

when filtering for NULLs you MUST use
IS NULL
IS NOT NULL


additionally the WHERE clause MUST happen before the GROUP BY clause


WHERE VPname IS NOT NULL and totalListItem IS NOT NULL
GROUP BY
      ltrim(SECTORVP)
       
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Help - 12 42
Access MDB/PDF 21 32
Why is my report opening in "MAX" view? 7 10
Access 2010 report is rearranging data 9 18
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now