Click here to Skip to main content
15,435,059 members
Home / Discussions / Database
   

Database

 
GeneralRe: update query not updating Pin
ZurdoDev7-May-15 10:35
professionalZurdoDev7-May-15 10:35 
QuestionSybase Access Violaion Error Pin
Ankit RS6-May-15 21:32
MemberAnkit RS6-May-15 21:32 
SuggestionRe: Sybase Access Violaion Error Pin
ZurdoDev7-May-15 4:08
professionalZurdoDev7-May-15 4:08 
GeneralRe: Sybase Access Violaion Error Pin
Sascha Lefèvre7-May-15 6:25
professionalSascha Lefèvre7-May-15 6:25 
QuestionRe: Sybase Access Violaion Error Pin
Ankit RS8-May-15 0:36
MemberAnkit RS8-May-15 0:36 
AnswerRe: Sybase Access Violaion Error Pin
Sascha Lefèvre8-May-15 10:02
professionalSascha Lefèvre8-May-15 10:02 
QuestionUnion on a join Pin
jkirkerx6-May-15 9:20
professionaljkirkerx6-May-15 9:20 
AnswerRe: Union on a join Pin
Richard Deeming6-May-15 9:40
mveRichard Deeming6-May-15 9:40 
Since you're using a relatively recent version of MS SQL Server, something like this should work:
SQL
DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;

SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999);

WITH cteProductInfo As
(
    SELECT
        PartNumber,
        Cost,
        Price,
        ShortDescription,
        LongDescription,
        PostageImage,
        Thumbnail,
        VendorID,
        VendorName,
        Weight,
        ShipHeight,
        ShipWidth,
        ShipDepth,
        LimitedItem
    FROM
        PRODUCTINFO
    
    UNION ALL
    
    SELECT
        PartNumber,
        Cost,
        Price,
        ShortDescription,
        LongDescription,
        PostageImage,
        Thumbnail,
        VendorID,
        VendorName,
        Weight,
        ShipHeight,
        ShipWidth,
        ShipDepth,
        LimitedItem
    FROM
        PRODUCTINFO_RECYCLEBIN
),
cteOrders As
(
    SELECT
        PartNumber,
        ManPartNumber, 
        SUM(coH.Qty) as TotalQty,
        CAST(SUM(coH.Qty * coh.Cost) AS decimal(10, 2)) as TotalCost,
        CAST(SUM(coH.Qty * coh.Price) AS decimal(10, 2)) as TotalPrice
    FROM
        CompletedOrdersCartHistory
    GROUP BY
        PartNumber,
        ManPartNumber
)
SELECT
    coH.PartNumber,
    coH.ManPartNumber,
    coH.TotalQty,
    coH.TotalCost,
    coH.TotalPrice,
    pI.Cost,
    pI.Price,
    pI.ShortDescription,
    pI.LongDescription,
    pI.PostageImage,
    pI.Thumbnail,
    pI.VendorID,
    pI.VendorName,
    pI.Weight,
    pI.ShipHeight,
    pI.ShipWidth,
    pI.ShipDepth,
    pI.LimitedItem,
    vI.Address1,
    vI.Address2,
    vI.City,
    vI.StateCode,
    vI.CountryCode,
    vI.ZipCode,
    vI.ContactName,
    vI.VendorPhone
FROM 
    cteOrders as coH
    LEFT JOIN cteProductInfo AS pI
    ON coH.PartNumber = pI.PartNumber
    LEFT JOIN VendorInfo AS vI
    ON pI.VendorID = vI.VendorID
WHERE 
    coh.OrderDate > @startDate
AND 
    coh.OrderDate < @stopDate
AND 
    coh.PartNumber = @PartNumber
ORDER BY 
    coH.PartNumber
;




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


GeneralRe: Union on a join Pin
jkirkerx6-May-15 11:40
professionaljkirkerx6-May-15 11:40 
GeneralRe: Union on a join Pin
jkirkerx7-May-15 12:24
professionaljkirkerx7-May-15 12:24 
Questionpostgresql VACUUM Pin
V.5-May-15 23:55
professionalV.5-May-15 23:55 
JokeRe: postgresql VACUUM Pin
Tim Carmichael6-May-15 4:52
MemberTim Carmichael6-May-15 4:52 
GeneralRe: postgresql VACUUM Pin
Mycroft Holmes6-May-15 14:06
professionalMycroft Holmes6-May-15 14:06 
GeneralRe: postgresql VACUUM Pin
Tim Carmichael6-May-15 14:49
MemberTim Carmichael6-May-15 14:49 
GeneralRe: postgresql VACUUM Pin
V.6-May-15 20:41
professionalV.6-May-15 20:41 
GeneralRe: postgresql VACUUM Pin
Mycroft Holmes6-May-15 22:04
professionalMycroft Holmes6-May-15 22:04 
GeneralRe: postgresql VACUUM Pin
V.6-May-15 22:06
professionalV.6-May-15 22:06 
GeneralRe: postgresql VACUUM Pin
Mycroft Holmes6-May-15 22:16
professionalMycroft Holmes6-May-15 22:16 
GeneralRe: postgresql VACUUM Pin
Tim Carmichael7-May-15 0:01
MemberTim Carmichael7-May-15 0:01 
AnswerRe: postgresql VACUUM Pin
Tim Carmichael7-May-15 2:15
MemberTim Carmichael7-May-15 2:15 
GeneralRe: postgresql VACUUM Pin
V.7-May-15 3:41
professionalV.7-May-15 3:41 
QuestionSequence number in DB2 Pin
sudevsu5-May-15 9:57
Membersudevsu5-May-15 9:57 
QuestionTest sol Pin
Stephen Holdorf30-Apr-15 11:24
MemberStephen Holdorf30-Apr-15 11:24 
GeneralRe: Help with a SQL query Pin
PIEBALDconsult30-Apr-15 19:33
professionalPIEBALDconsult30-Apr-15 19:33 
GeneralRe: Help with a SQL query Pin
Stephen Holdorf1-May-15 2:54
MemberStephen Holdorf1-May-15 2:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.