Solved

RE : SQL Server query 2008

Posted on 2013-07-01
6
284 Views
Last Modified: 2013-07-09
Hi,

I am facing challenge in creating SQL query in optimized way.

Find the attached sample data in excel sheet. I would like the date as per Output 1 and Output 2 sheet.

Do let me know if any question regarding the same.

Data requirement is that i have the consumption data now i want to place the cancellation data against each consumption. Attached the transactional data in excel.

Facing to
0
Comment
Question by:vikalgupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289746
Hi,

I think you have forgotten to attach the example data.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289804
and please post your existing SQL query (or include it in the spreadsheet)
0
 

Author Comment

by:vikalgupta
ID: 39290008
find the attached sheet.
rpt.xlsx
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39290128
Your not providing all the test data. You have not populated the shop master table. Although this does look like it will be a simple join.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39292018
The following result is produced by either of the SQL options below. Please note the date format used is an arbitrary choice which you can amend.
MERCHANTID	VOUCHERNUMBER	CONSUMEDSHOPNAME	CONSUMEDSHOPCODE	CONSUMEDDATE	REQUESTJOBNO	CANCELLEDSHOPNAME	CANCELLEDSHOPCODE	CANCELLEDDATE
3		BV34145412	Karol Bagh		BR-GP001	2013-07-01 14:48:54	23		Green Park		BR-GP000	2013-07-01 14:49:48
3		BV55625541	Green Park		BR-GP000	2013-07-01 14:49:24	78		Rohini			BR-GP002	2013-07-01 14:49:33
3		BV55625541	Green Park		BR-GP000	2013-07-01 14:48:32	12		Karol Bagh		BR-GP001	2013-07-01 14:49:16
3		BV61731976	Rohini			BR-GP002	2013-07-01 14:49:03	34		(null)			(null)		(null)
3		BV74939357	Green Park		BR-GP000	2013-07-01 12:57:33	ADMIN_EVOUCHER	Green Park		BR-GP000	2013-07-01 12:58:15

Open in new window

There does not appear to be a tidy method within the source transactions to relate a cancellation to a consumption and to overcome this I have used the row_number() function to produce sequence numbers to tie cancellation to consumption. This works in the sample provided but suggest you test this on a broader set of data.

These 2 SQL options are functionally the same, they just differ in technique (using CTEs or using nested subquery)
-- using CTEs
;WITH
consume AS (
            SELECT
                  MERCHANTID
                , VoucherNumber
                , ShopMaster.SHOPNAME AS ConsumedShopName
                , ShopMaster.SHOPCODE AS ConsumedShopCode
                , REQUESTDATE         AS ConsumedDate
                , REQUESTJOBNO
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CONSUME'
            ),
cancel  AS (
            SELECT
                  VoucherNumber
                , ShopMaster.SHOPNAME  AS CancelledShopName
                , ShopMaster.SHOPCODE  AS CancelledShopCode
                , REQUESTDATE          AS CancelledDate
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CANCEL'
            )
SELECT
        consume.MERCHANTID
      , consume.VOUCHERNUMBER
      , consume.CONSUMEDSHOPNAME
      , consume.CONSUMEDSHOPCODE
      , convert(varchar, consume.CONSUMEDDATE ,120) AS CONSUMEDDATE
      , consume.REQUESTJOBNO
      , cancel.CANCELLEDSHOPNAME
      , cancel.CANCELLEDSHOPCODE
      , convert(varchar, cancel.CANCELLEDDATE ,120) AS CANCELLEDDATE
FROM consume
LEFT JOIN cancel ON consume.VoucherNumber = cancel.VoucherNumber
                AND consume.row_sequence = cancel.row_sequence
ORDER BY
        consume.VoucherNumber
      , CONSUMEDDATE DESC
;

-- using nested subqueries
SELECT
        consume.MERCHANTID
      , consume.VOUCHERNUMBER
      , consume.CONSUMEDSHOPNAME
      , consume.CONSUMEDSHOPCODE
      , convert(varchar, consume.CONSUMEDDATE ,120) AS CONSUMEDDATE
      , consume.REQUESTJOBNO
      , cancel.CANCELLEDSHOPNAME
      , cancel.CANCELLEDSHOPCODE
      , convert(varchar, cancel.CANCELLEDDATE ,120) AS CANCELLEDDATE
FROM (
            SELECT
                  MERCHANTID
                , VoucherNumber
                , ShopMaster.SHOPNAME AS ConsumedShopName
                , ShopMaster.SHOPCODE AS ConsumedShopCode
                , REQUESTDATE         AS ConsumedDate
                , REQUESTJOBNO
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CONSUME'
     ) AS consume
LEFT JOIN (
            SELECT
                  VoucherNumber
                , ShopMaster.SHOPNAME  AS CancelledShopName
                , ShopMaster.SHOPCODE  AS CancelledShopCode
                , REQUESTDATE          AS CancelledDate
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CANCEL'
          ) AS cancel ON consume.VoucherNumber = cancel.VoucherNumber
                     AND consume.row_sequence = cancel.row_sequence
ORDER BY
        consume.VoucherNumber
      , CONSUMEDDATE DESC
;

Open in new window

also note you didn't specify the actual table names, and I removed spaces from any column labels in the spreadsheet.

these queries and the assumed shopmaster table may be seen in operation at:
http://sqlfiddle.com/#!3/84505/3
0
 

Author Closing Comment

by:vikalgupta
ID: 39313199
Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query output (script) from a stored procedure 4 54
how to make geography query faster?  SQL 7 64
If in a where clause in t-sql 7 53
Need more granular date groupings 4 46
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

710 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