February 16, 2012 at 5:19 am
I have the following table:
SELECT TOP 1000 [CardOperationId]
,[CardOperationTypeId]
,[TenantId]
,[CardId]
,[UserId]
,[LocationId]
,[Date]
,[Value]
,[BalanceAfterOperation]
,[AdditionalInfo]
FROM [Cards].[dbo].[cmCardOperation]
I am trying to show for each location:
1. how many operations of type 1 were
2. how many operations of type 2 were
3. sum of values pertaining to operations of type 3
4. sum of values pertaining to operations of type 4
DECLARE @since DATETIME
DECLARE @until DATETIME
DECLARE @locatie VARCHAR(255)
DECLARE @Operatie1 INT
DECLARE @Operatie2 INT
SET @since = '20100101'
SET @until = '20130101'
SET @Operatie1 = '1'
SET @Operatie2 = '2'
SET @locatie = 'DemoLocation1'
SELECT l.LocationName LOCATIA
,COUNT( ALL co.CardOperationTypeId|@Operatie1) AS NUMARACTIVARI
,COUNT( ALL co.CardOperationTypeId|@Operatie2) AS NUMARDEZACTIVARI
,co.Value VALUEIN
,co.Value VALUEOUT
FROM cmCardOperation co
JOIN cmCard c ON co.CardId = c.CardId
JOIN cmLocation l ON co.LocationId = l.LocationId
JOIN cmCardOperationType coty ON co.CardOperationTypeId = coty.CardOperationTypeId
WHERE c.ActivationDate >= @since
AND c.ActivationDate <= @until
AND l.LocationName = @locatie
AND ((co.CardOperationTypeId = @Operatie1) OR (co.CardOperationTypeId = @Operatie2) ) /* Conditia asta face sa arate 2 randuri in loc de 13.*/
GROUP BY l.LocationName, co.Value, co.CardOperationTypeId
What am I doing wrong? Thank you in advance for your help!
February 16, 2012 at 6:18 am
The output I want to get is:
Location | Numar activari(OP.1) | Nr dezactivari(OP.2) | Valoare IN(sum(OP3)) | Valoare OUT (sum(OP4))
DemoLocation1 | 3 | 2 | sum(OP3) for DEMOLOCATION1 | sum(OP4) for DEMOLOCATION1
DemoLocation2 | 1 | 1 | sum(OP3) for DEMOLOCATION2 | sum(OP4) for DEMOLOCATION2
February 16, 2012 at 6:54 am
The first two are COUNT - while the other two are SUM. Nevertheless here's what I have right now:
DECLARE @since DATETIME
DECLARE @until DATETIME
DECLARE @Operatie1 INT
DECLARE @Operatie2 INT
DECLARE @Operatie3 INT
SET @since = '20100101'
SET @until = '20130101'
SET @Operatie1 = '1'
SET @Operatie2 = '2'
SET @Operatie3 = '3'
SELECT l.LocationName LOCATIA,
COUNT(CASE WHEN co.CardOperationTypeId = 1 THEN 1 ELSE 0 END) AS NUMARACTIVARI,
count (CASE WHEN co.CardOperationTypeId = 2 THEN 1 ELSE 0 END) AS NUMARDEZACTIVARI,
SUM (CASE WHEN co.CardOperationTypeId = 3 THEN co.Value ELSE 0 END) AS ValueIn,
SUM (CASE WHEN co.CardOperationTypeId = 4 THEN co.Value ELSE 0 END) AS ValueOut
FROM cmCardOperation co
JOIN cmCard c ON co.CardId = c.CardId
JOIN cmLocation l ON co.LocationId = l.LocationId
JOIN cmCardOperationType coty ON co.CardOperationTypeId = coty.CardOperationTypeId
WHERE c.ActivationDate >= @since
AND c.ActivationDate <= @until
AND ((co.CardOperationTypeId = @Operatie1) OR (co.CardOperationTypeId = @Operatie2) ) /* Conditia asta face sa arate 2 randuri in loc de 13.*/
GROUP BY l.LocationName, co.Value, co.CardOperationTypeId
==========================================================================================
OUTPUT is 🙁 different from what I require...:
DemoLocation1330.000.00
DemoLocation1220.000.00
February 16, 2012 at 7:05 am
Why not to follow the forum etiquette when asking this sort of question? Follow the link at the bottom of my signature and I can guarantee you that you will get more relevant answers shortly after... 😉
February 20, 2012 at 1:10 am
Thank for your responses and your help! Much obliged!
I have solved it and created the following procedure:
CREATE PROCEDURE Raport4
@since DATETIME,
@until DATETIME,
@Location VARCHAR(255)
AS
BEGIN
SELECT LocationName, SUM(NUMARACTIVARI) NUMARACTIVARI, SUM(NUMARDEZACTIVARI) NUMARDEZACTIVARI, SUM(VALOAREINTRARI) VALOAREINTRARI, SUM(VALOAREIESIRI) VALOAREIESIRI FROM
(SELECT l.LocationName, COUNT(*) NUMARACTIVARI, 0 AS NUMARDEZACTIVARI, CAST(0 AS MONEY) AS VALOAREINTRARI, CAST(0 AS MONEY) AS VALOAREIESIRI
FROM dbo.cmCardOperation co JOIN dbo.cmLocation l ON co.LocationId = l.LocationId
WHERE CardOperationTypeId = 1 AND (co.Date >= @since AND co.Date <= @until) AND l.LocationName = @Location
GROUP BY l.LocationName
UNION
SELECT l.LocationName, 0 NUMARACTIVARI, COUNT(*) AS NUMARDEZACTIVARI, CAST(0 AS MONEY) AS VALOAREINTRARI, CAST(0 AS MONEY) AS VALOAREIESIRI
FROM dbo.cmCardOperation co JOIN dbo.cmLocation l ON co.LocationId = l.LocationId
WHERE CardOperationTypeId = 2 AND (co.Date >= @since AND co.Date <= @until) AND l.LocationName = @Location
GROUP BY l.LocationName
UNION
SELECT l.LocationName, 0 NUMARACTIVARI, 0 AS NUMARDEZACTIVARI, SUM(Value) AS VALOAREINTRARI, CAST(0 AS MONEY) AS VALOAREIESIRI
FROM dbo.cmCardOperation co JOIN dbo.cmLocation l ON co.LocationId = l.LocationId
WHERE CardOperationTypeId = 3 AND (co.Date >= @since AND co.Date <= @until) AND l.LocationName = @Location
GROUP BY l.LocationName
UNION
SELECT l.LocationName, 0 NUMARACTIVARI, 0 AS NUMARDEZACTIVARI, CAST(0 AS MONEY) AS VALOAREINTRARI, SUM(Value) AS VALOAREIESIRI
FROM dbo.cmCardOperation co JOIN dbo.cmLocation l ON co.LocationId = l.LocationId
WHERE CardOperationTypeId = 4 AND (co.Date >= @since AND co.Date <= @until) AND l.LocationName = @Location
GROUP BY l.LocationName
) XYZ
GROUP BY XYZ.LocationName
END
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply