Select statement question?????

RinoDM · March 16th, 2009, 11:32 AM

Hi to all...I have 6 select statements that start the sameSELECT Store,SUM(ABS(Qty)*Flag) Qty,SUM((abs(Price)*Flag)) Returns$ FROM Transactionswhere Modifier4 and Modifier7 andDate between '03/01/09' and '03/16/09' andup to this point just that in each other where cases i have different modifier or flags to select...is there a way i can make one select statement instead of 6...thanking you in advance...Rino

joefawcett · March 16th, 2009, 11:49 AM

It depends on the modifier. If you are changing both the left hand side and the right hand side of the expression, or the operator then unless you use dynamic SQL it is probably not possible.
Can you show a couple of real queries illustrating the differences?

RinoDM · March 16th, 2009, 11:54 AM

-- Returns by Store**SELECT StoreID, SUM(ABS(Quantity)*PosNegFlag) qty,SUM((abs(ExtendedSellingPrice)*PosNegFlag)) Returns$ FROM TransactionDetailswhere TransactionModifier4 and TransactionModifier7 andTransactionDetailDate between '03/01/09' and '03/16/09' and(TransactionModifier=1 OR TransactionModifier=3 )GROUP BY StoreIDorder by 1-- Post Voids by Store**SELECT StoreID, SUM(ABS(Quantity)*PosNegFlag) qty,SUM((abs(ExtendedSellingPrice)*PosNegFlag)) PostVoid$ FROM TransactionDetailswhere TransactionModifier4 and TransactionModifier7 andTransactionDetailDate between '03/01/09' and '03/16/09' andTransactionVoidFlag=1GROUP BY StoreIDorder by 1-- Exchanges by Store**SELECT StoreID, SUM(ABS(Quantity)*PosNegFlag) qty,SUM((abs(ExtendedSellingPrice)*PosNegFlag)) Exchanges$ FROM TransactionDetailswhere TransactionModifier4 and TransactionModifier7 andTransactionDetailDate between '03/01/09' and '03/16/09' andTransactionModifier=5GROUP BY StoreIDorder by 1--the other 2 would be one with TransactionModifier=2 and the other with TransactionModifier=6

Old Pedant · March 16th, 2009, 03:38 PM

...like this:

Code:

-- Returns by Store

SELECT StoreID, SUM(ABS(Quantity)*PosNegFlag) AS qty, SUM((abs(ExtendedSellingPrice)*PosNegFlag)) AS Returns$
FROM TransactionDetails 
where TransactionModifier4 
and TransactionModifier7 
and TransactionDetailDate between '03/01/09' and '03/16/09' 
and (TransactionModifier=1 OR TransactionModifier=3 )
GROUP BY StoreID
order by 1

-- Post Voids by Store

SELECT StoreID, SUM(ABS(Quantity)*PosNegFlag) qty,SUM((abs(ExtendedSellingPrice)*PosNegFlag)) PostVoid$ 
FROM TransactionDetails
where TransactionModifier4 
and TransactionModifier7 
and TransactionDetailDate between '03/01/09' and '03/16/09' 
and TransactionVoidFlag=1
GROUP BY StoreID
order by 1

-- Exchanges by Store

SELECT StoreID, SUM(ABS(Quantity)*PosNegFlag) qty,SUM((abs(ExtendedSellingPrice)*PosNegFlag)) Exchanges$ 
FROM TransactionDetails
where TransactionModifier4 
and TransactionModifier7 
and TransactionDetailDate between '03/01/09' and '03/16/09' 
and TransactionModifier=5
GROUP BY StoreID
order by 1

--the other 2 would be one with TransactionModifier=2 and the other with TransactionModifier=6

So are you wanting to use a Stored Procedure to do all this?

I don't think it would be hard.

But what database is this????

You APPEAR to be using BOOLEAN fields:

Code:

where TransactionModifier4 
and TransactionModifier7

And SQL Server doesn't support boolean fields. Are those actually BIT fields???

Old Pedant · March 16th, 2009, 03:45 PM

I thnk this does it:

Code:

CREATE PROCEDURE storeReports
    @startDate DATETIME,
    @endDate DATETIME,
    @firstModifier INT DEFAULT -1,
    @secondModifier INT DEFAULT -1,
    @voidFlag BIT DEFAULT = 0
AS
SELECT StoreID, 
       SUM(ABS(Quantity)*PosNegFlag) AS qty,
       SUM((abs(ExtendedSellingPrice)*PosNegFlag)) AS dollarAmount
FROM TransactionDetails
WHERE TransactionModifier4 = 1
AND TransactionModifier7 = 1
AND TransactionDetailDate between @startDate and @endDate
AND (    TransactionModifier IN ( @firstModifier, @secondModifier ) 
      OR ( @voidFlag = 1 AND TransactionVoidFlag = 1 ) 
    )
GROUP BY StoreID
ORDER BY StoreID

Again, that assumes the TransactionModifier4 and TransactionModifier7 are BIT fields in the DB.

Couldn't somebody come up with more meaningful names than "TransactionModifier7" et al.??

RinoDM · March 16th, 2009, 03:46 PM

it's my own DB... it's not a boolean field... I'm using the less then & greater then signs... the not equal too...I don't know why when i copy pasted it in it didn't work...let me try againwhere TransactionModifier4 and TransactionModifier7

RinoDM · March 16th, 2009, 03:53 PM

don't know why when i put in "the not equal signs" it doesn't work...

Old Pedant · March 16th, 2009, 05:30 PM

So edit your posts after the copy/paste.

What do you mean "it's my own DB"??? You mean it's not SQL Server?

If it's a non-standard DB, I don't know that anybody but the creator of the product will know all the answers.

RinoDM · March 16th, 2009, 05:35 PM

you asked me before "But what database is this????"it's my own DB "database" i created... with tables and etc. to my needs...of course it's SQL server...or else i wouldn't be writing in this section...

Old Pedant · March 16th, 2009, 05:37 PM

But if it is SQL Server, are you saying that what you really want is:

Code:

CREATE PROCEDURE storeReports
    @startDate DATETIME,
    @endDate DATETIME,
    @firstModifier INT DEFAULT -1,
    @secondModifier INT DEFAULT -1,
    @voidFlag BIT DEFAULT = 0
AS
SELECT StoreID, 
       SUM(ABS(Quantity)*PosNegFlag) AS qty,
       SUM((abs(ExtendedSellingPrice)*PosNegFlag)) AS dollarAmount
FROM TransactionDetails
WHERE TransactionModifier <> 4 
AND TransactionModifier <> 7
AND TransactionDetailDate between @startDate and @endDate
AND (    TransactionModifier IN ( @firstModifier, @secondModifier ) 
      OR ( @voidFlag = 1 AND TransactionVoidFlag = 1 ) 
    )
GROUP BY StoreID
ORDER BY StoreID

The <> 4 and <> 7 is a bit redundant when you DO specify the first and second modifier values, but they won't hurt and you'd need them for the void case.

Hmmm... Maybe could simplify a little bit:

Code:

CREATE PROCEDURE storeReports
    @startDate DATETIME,
    @endDate DATETIME,
    @firstModifier INT DEFAULT -1,
    @secondModifier INT DEFAULT -1,
    @voidFlag BIT DEFAULT = 0
AS
SELECT StoreID, 
       SUM(ABS(Quantity)*PosNegFlag) AS qty,
       SUM((abs(ExtendedSellingPrice)*PosNegFlag)) AS dollarAmount
FROM TransactionDetails
WHERE TransactionDetailDate between @startDate and @endDate
AND (    TransactionModifier IN ( @firstModifier, @secondModifier ) 
      OR ( @voidFlag = 1 AND TransactionVoidFlag = 1 AND TransactionModifier NOT IN (4,7) ) 
    )
GROUP BY StoreID
ORDER BY StoreID

If it's not obvious, you would then call the SP passing the two dates and then optionally passing the various other values.

For example:

Code:

-- returns by store
EXEC storeReports '3/1/2009','3/16/2009',1,3
-- voids by store
EXEC storeReports '3/1/2009','3/16/2009', -1, -1, 1
-- exchanges by store
EXEC storeReports '3/1/2009','3/16/2009',5