Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

RE : SQL Server query 2008

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
vikalgupta
Asked:
vikalgupta
  • 2
  • 2
  • 2
1 Solution
 
EvilPostItCommented:
Hi,

I think you have forgotten to attach the example data.
0
 
PaulCommented:
and please post your existing SQL query (or include it in the spreadsheet)
0
 
vikalguptaAuthor Commented:
find the attached sheet.
rpt.xlsx
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
EvilPostItCommented:
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
 
PaulCommented:
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
 
vikalguptaAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now